Oracle Tips and Tricks — David Fitzjarrell

August 10, 2018

Well, That Didn’t Work …

Filed under: General — dfitzjarrell @ 11:46

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

Interesting behavior with the unified audit trail has been reported by Jeff Hunter and verified on at least one platform, RedHat Enterprise Linux 6. While other ports of Oracle behave as expected (Windows and Oracle Enterprise Linux 7, to name two that I’ve tested) the problem platform fails to return data from V$SESSION because the AUDSID values apparently don’t correspond to SESSIONID values returned by SYS_CONTEXT() or in the UNIFIED_AUDIT_TRAIL table. Let’s take this ‘for a spin’.

The following query returns data for the current user session from Oracle databases running on Windows, Solaris 10 or OEL 7:


select sid, serial#, os_username, machine, program
from v$session, unified_audit_trail
where audsid = sessionid
and sessionid = (select sys_context('userenv','sessionid') from dual);

As an example let’s run this on a Windows version of Oracle:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAME        MACHINE           PROGRAM
---------- ---------- ------------------ ----------------- -------------------------
       229       8579 SPLEEZO\fnordwamp  SPLEEZ0\NOTYOURPC sqlplus.exe

SQL>

From OEL 7 similar results are produced, again for the currently connected session:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAM   MACHINE           PROGRAM
---------- ---------- ------------ ----------------- ----------------------------------
        12      33089 oracle       mydbserver        sqlplus@mydbserver (TNS V1-V3)

SQL>

This is the output (or lack thereof) reported by Jeff Hunter for RHEL 6:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

no rows returned

SQL>

From the above tests and results it appears that Oracle 12.x doesn’t behave the same way on all platforms. The interesting part of this is that the values do appear to match when selected independently yet they don’t ‘match’ when the AUDSID and SESSIONID columns are joined for user sessions. I can’t believe that the RHEL 6 behavior is the ‘norm’ as audit records should be able to be linked to sessions so that activity can be tracked and recorded.

Oracle support provides no documents regarding this behavior so no work-around is provided, and this may be an issue to raise an SR for. All platforms should behave in the same way with unified auditing, otherwise it’s of no benefit to the DBA.

Sometimes that fork in the road is actually a spoon …

Advertisements

August 4, 2018

Eviction Notice

Filed under: General — dfitzjarrell @ 17:22

"Expectations is the place you must always go to before you get to where you're going. Of course,
some people never go beyond Expectations, but my job is to hurry them along whether they like it or not."
-- Norton Juster, The Phantom Tollbooth

Oracle has decided that Solaris needs to retire, giving those who are running Oracle on Solaris roughly two years to move to another operating system. (Oracle is still planning on supporting existing on-premises Solaris installations of 11 until 2034. For those on Solaris 10 the 2-year timeline is correct (https://blogs.oracle.com/solaris/oracle-solaris-10-support-explained). The push is to take Solaris to the cloud.) A possible choice involves commodity hardware (x86) and Linux. Since OEL is a ‘ready to use’ version of Red Hat Linux it would be a reasonable presumption that many companies will be choosing it as a Solaris replacement. Available as a full release for a bare-metal system and as a pre-configured VM it’s positioned to be a ‘go-to’ option for migration.

Solaris, on SPARC, is a big-endian operating system, where the most-significant byte is stored last. Linux, on the other hand, is a little-endian system where the bytes are stored in the order of significance (most-significant is stored first). This can make moving to Linux from Solaris a challenge, not for the software installation but for the database migration as the existing datafiles, logfiles and controlfiles can’t be used directly; they must be converted to the proper endian format. Time and space could be important issues with such conversions/migrations as there may be insufficient disk space to contain two copies of the database files, on big-endian and one little-endian. The actual conversion shouldn’t be difficult as RMAN can convert the files from a recent backup. Other considerations include access to the new servers (since they may not be on the production LAN while the build and conversions are taking place) and replicating the current Oracle environment, including scripts, NFS mounts, other shared file systems and utilities, from Solaris to Linux. RMAN isn’t the only method of migration as expdp/impdp can be used to transfer users, tables and data from the source database to its new home; using datapump means the destination database can’t be rolled foward after the import has completed which can cause a longer outage than the enterprise may want to endure since all user access to the application data must be stopped prior to the export.

Looking at the ‘worst-case scenario’ let’s proceed with the migration using Oracle Enterprise Linux and the datapump transfer of data and tables. (Installing the software should be a fairly simple task so that portion of the migration will not be covered here.) Once the server is running and Oracle is installed the first step in this migration is to create an “empty” Oracle database as the destination. This takes care of the endian issue since the new datafiles are created with the proper endian format. If the Linux server is created with the eame file system structure as the source then it’s simply a matter of performing the import and checking the results to verify nothing went awry. Any tnsnames.ora files that applications use for connectivity need to be modified to point to the replacement database server and remote connections need to be tested to verify they do, indeed, work as expected. After successfrul application testing has completed the database and its new ‘home’ should be ready to replace the original Solaris machines.

It’s possible that the new Linux server will use a different storage configuration; it’s also possible that the DBA team, in an effort to simplify tasks, decides to use Oracle Managed Files. In either case the import may not succeed since datapump can’t create the datafiles. This is when the tablespaces need to be created ahead of the datapump import; using the SQLFILE parameter to datapump import will place all of the DDL into the speficied file. Once this file is created it can be edited to change the file locations or to use OMF when creating tablespaces. It’s critical that file sizes are sufficient to contain the data and allow for growth. Datapump will create users if the necessary tablespaces exist so the only DDL that should be necessary to run prior to the import will be the CREATE TABLESPACE statements; all other DDL should be removed prior to running the script. It’s also possible to create a DBCA template to create the destination database by modifying an existing template. Creating the database manually or with a template is a DBA team decision; in the absence of database creation scripts that can be modified it might be a good decision to modify an existing template to minimize errors.

Presuming the storage configuration for the x86 system is different from that on Solaris, and that the file system structure doesn’t match the source server the tablespace DDL has been extracted, modified and is ready to run. After the tablespaces are in place the import can be run; it is always good practice to make a test run into the new database before the final import is executed, to verify that the process runs smoothly. The final import should put the new database in ‘proper working order’ for the applications that use it so all insert/update/delete activity must be stopped prior to the export. This will ensure data consistency across the tables.

One area that may be an issue is application account passwords in the new database. It’s a good idea to verify that the passwords from the source database work in the new database. If not they can be reset to the current values; end-users and batch jobs will be happier if logins are successful.

Connectivity to remote systems is also critical, so any tnsnames.ora files that are in use on the source system need to be copied to the destination system and database links need to be tested. This may involve the security and system adminstration teams to open ports, set firewall rules and ensure that any software packages not included with the operating system are installed. There should be no surprises once the new server and database are brought online.

Other choices, such as Unified Audting or Database Vault, that require an Oracle kernel relink need to be discussed and decided upon before the destination database is created. Re-linking the Oracle kernel before any database exists reduces overall downtime for the migration.

No document can cover every conceivable issue or problem that may arise so it’s possible the first pass at moving from Solaris to x86 may reveal issues that weren’t obvious at the beginning of this process. It may also take more than one pass to “iron out” all of the “kinks” to get a smoothly running process. The time and effort expended to ensure a smoothly running migration will pay off handsomely when the day finally arrives.

Moving from Solaris to x86 (or any other operating system) may not be at the top of your wish list, but it will become a necessity when Solaris is no longer supported. Getting a start on this now will provide the needed time to fix any issues the migration may suffer so that when the fateful weekend arrives the DBA team is ready to provide a (mostly) painless move.

Move along, now, you need to get going.

May 21, 2018

Export – Import Business, Again

Filed under: General — dfitzjarrell @ 19:05

"At first it was difficult to tell just what had changed — it all looked the same
and it all smelled the same — but, for some reason, nothing sounded the same."
-- Norton Juster, The Phantom Tollbooth

Doing things just because “they’ve always been done that way” may not be the best reason to stick with old and accepted processes and utilities. Oracle has changed significantly over the years and those improvements shouldn’t be ignored simply because “that’s always worked before.” Export and import have dramatically improved over the years, and not simply in how the user interface is called. New database constructs, new histograms, new index types are all thoughtfully covered with the Data Pump utiliies. Not so with the historical exp and imp tools; these have not been updated since Oracle 9i, when Data Pump was introduced. Because of this using conventional exp and imp in a modern version of Oracle can create some unpleasant surprises.

Histograms are important to performance as they inform the optimizer of data skew and other data patterns that can provide a better ‘picture’ to work with when creating an execution plan. Top-n and hybrid histograms can’t be handled with the exp and imp utilities, so using them to transfer data between two 12.1.0.2 databases will cause those histograms to be lost since exp and imp can’t know of such histograms. Let’s use an example from Jonathan Lewis to illustrate what happens when Data Pump isn’t used on databases newer than 9.x.

Let’s build a table (called T1), load it with data and create some histograms; the data loaded will create top-frequency and hybrid histograms. Looking at the results:



COLUMN_NAME                         NUM_DISTINCT HISTOGRAM       NUM_BUCKETS
----------------------------------- ------------ --------------- -----------
FREQUENCY                                    100 FREQUENCY               100

TOP_N                                        100 TOP-FREQUENCY            95

HYBRID                                       100 HYBRID                   50


COLUMN_NAME                         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE  ENDPOINT_REPEAT_COUNT
----------------------------------- --------------- -------------- ---------------------- ---------------------
FREQUENCY                                         3              1 1                                          0
                                                  8              2 2                                          0
                                                 15              3 3                                          0
                                                 24              4 4                                          0
                                                 35              5 5                                          0
                                                 48              6 6                                          0
                                                 63              7 7                                          0
                                                 80              8 8                                          0
                                                 99              9 9                                          0
                                                120             10 10                                         0
                                                143             11 11                                         0
                                                168             12 12                                         0
                                                195             13 13                                         0
                                                224             14 14                                         0
                                                255             15 15                                         0
                                                288             16 16                                         0
                                                323             17 17                                         0
                                                360             18 18                                         0
                                                399             19 19                                         0
                                                440             20 20                                         0
                                                483             21 21                                         0
                                                528             22 22                                         0
                                                575             23 23                                         0
                                                624             24 24                                         0
                                                675             25 25                                         0
                                                728             26 26                                         0
                                                783             27 27                                         0
                                                840             28 28                                         0
                                                899             29 29                                         0
                                                960             30 30                                         0
                                               1023             31 31                                         0
                                               1088             32 32                                         0
                                               1155             33 33                                         0
                                               1224             34 34                                         0
                                               1295             35 35                                         0
                                               1368             36 36                                         0
                                               1443             37 37                                         0
                                               1520             38 38                                         0
                                               1599             39 39                                         0
                                               1680             40 40                                         0
                                               1763             41 41                                         0
                                               1848             42 42                                         0
                                               1935             43 43                                         0
                                               2024             44 44                                         0
                                               2115             45 45                                         0
                                               2208             46 46                                         0
                                               2303             47 47                                         0
                                               2400             48 48                                         0
                                               2499             49 49                                         0
                                               2600             50 50                                         0
                                               2703             51 51                                         0
                                               2808             52 52                                         0
                                               2915             53 53                                         0
                                               3024             54 54                                         0
                                               3135             55 55                                         0
                                               3248             56 56                                         0
                                               3363             57 57                                         0
                                               3480             58 58                                         0
                                               3599             59 59                                         0
                                               3720             60 60                                         0
                                               3843             61 61                                         0
                                               3968             62 62                                         0
                                               4095             63 63                                         0
                                               4224             64 64                                         0
                                               4355             65 65                                         0
                                               4488             66 66                                         0
                                               4623             67 67                                         0
                                               4760             68 68                                         0
                                               4899             69 69                                         0
                                               5040             70 70                                         0
                                               5183             71 71                                         0
                                               5328             72 72                                         0
                                               5475             73 73                                         0
                                               5624             74 74                                         0
                                               5775             75 75                                         0
                                               5928             76 76                                         0
                                               6083             77 77                                         0
                                               6240             78 78                                         0
                                               6399             79 79                                         0
                                               6560             80 80                                         0
                                               6723             81 81                                         0
                                               6888             82 82                                         0
                                               7055             83 83                                         0
                                               7224             84 84                                         0
                                               7395             85 85                                         0
                                               7568             86 86                                         0
                                               7743             87 87                                         0
                                               7920             88 88                                         0
                                               8099             89 89                                         0
                                               8280             90 90                                         0
                                               8463             91 91                                         0
                                               8648             92 92                                         0
                                               8835             93 93                                         0
                                               9024             94 94                                         0
                                               9215             95 95                                         0
                                               9408             96 96                                         0
                                               9603             97 97                                         0
                                               9800             98 98                                         0
                                               9999             99 99                                         0
                                              10000            100 100                                        0

HYBRID                                            3              1 1                                          3
                                                224             14 14                                        29
                                                440             20 20                                        41
                                                675             25 25                                        51
                                                899             29 29                                        59
                                               1155             33 33                                        67
                                               1368             36 36                                        73
                                               1599             39 39                                        79
                                               1848             42 42                                        85
                                               2115             45 45                                        91
                                               2400             48 48                                        97
                                               2703             51 51                                       103
                                               2915             53 53                                       107
                                               3135             55 55                                       111
                                               3363             57 57                                       115
                                               3599             59 59                                       119
                                               3843             61 61                                       123
                                               4095             63 63                                       127
                                               4355             65 65                                       131
                                               4623             67 67                                       135
                                               4899             69 69                                       139
                                               5183             71 71                                       143
                                               5328             72 72                                       145
                                               5475             73 73                                       147
                                               5624             74 74                                       149
                                               5775             75 75                                       151
                                               5928             76 76                                       153
                                               6083             77 77                                       155
                                               6240             78 78                                       157
                                               6399             79 79                                       159
                                               6560             80 80                                       161
                                               6723             81 81                                       163
                                               6888             82 82                                       165
                                               7055             83 83                                       167
                                               7224             84 84                                       169
                                               7395             85 85                                       171
                                               7568             86 86                                       173
                                               7743             87 87                                       175
                                               7920             88 88                                       177
                                               8099             89 89                                       179
                                               8280             90 90                                       181
                                               8463             91 91                                       183
                                               8648             92 92                                       185
                                               8835             93 93                                       187
                                               9024             94 94                                       189
                                               9215             95 95                                       191
                                               9408             96 96                                       193
                                               9603             97 97                                       195
                                               9800             98 98                                       197
                                              10000            100 100                                        1

TOP_N                                             1              1 1                                          0
                                                 16              7 7                                          0
                                                 33              8 8                                          0
                                                 52              9 9                                          0
                                                 73             10 10                                         0
                                                 96             11 11                                         0
                                                121             12 12                                         0
                                                148             13 13                                         0
                                                177             14 14                                         0
                                                208             15 15                                         0
                                                241             16 16                                         0
                                                276             17 17                                         0
                                                313             18 18                                         0
                                                352             19 19                                         0
                                                393             20 20                                         0
                                                436             21 21                                         0
                                                481             22 22                                         0
                                                528             23 23                                         0
                                                577             24 24                                         0
                                                628             25 25                                         0
                                                681             26 26                                         0
                                                736             27 27                                         0
                                                793             28 28                                         0
                                                852             29 29                                         0
                                                913             30 30                                         0
                                                976             31 31                                         0
                                               1041             32 32                                         0
                                               1108             33 33                                         0
                                               1177             34 34                                         0
                                               1248             35 35                                         0
                                               1321             36 36                                         0
                                               1396             37 37                                         0
                                               1473             38 38                                         0
                                               1552             39 39                                         0
                                               1633             40 40                                         0
                                               1716             41 41                                         0
                                               1801             42 42                                         0
                                               1888             43 43                                         0
                                               1977             44 44                                         0
                                               2068             45 45                                         0
                                               2161             46 46                                         0
                                               2256             47 47                                         0
                                               2353             48 48                                         0
                                               2452             49 49                                         0
                                               2553             50 50                                         0
                                               2656             51 51                                         0
                                               2761             52 52                                         0
                                               2868             53 53                                         0
                                               2977             54 54                                         0
                                               3088             55 55                                         0
                                               3201             56 56                                         0
                                               3316             57 57                                         0
                                               3433             58 58                                         0
                                               3552             59 59                                         0
                                               3673             60 60                                         0
                                               3796             61 61                                         0
                                               3921             62 62                                         0
                                               4048             63 63                                         0
                                               4177             64 64                                         0
                                               4308             65 65                                         0
                                               4441             66 66                                         0
                                               4576             67 67                                         0
                                               4713             68 68                                         0
                                               4852             69 69                                         0
                                               4993             70 70                                         0
                                               5136             71 71                                         0
                                               5281             72 72                                         0
                                               5428             73 73                                         0
                                               5577             74 74                                         0
                                               5728             75 75                                         0
                                               5881             76 76                                         0
                                               6036             77 77                                         0
                                               6193             78 78                                         0
                                               6352             79 79                                         0
                                               6513             80 80                                         0
                                               6676             81 81                                         0
                                               6841             82 82                                         0
                                               7008             83 83                                         0
                                               7177             84 84                                         0
                                               7348             85 85                                         0
                                               7521             86 86                                         0
                                               7696             87 87                                         0
                                               7873             88 88                                         0
                                               8052             89 89                                         0
                                               8233             90 90                                         0
                                               8416             91 91                                         0
                                               8601             92 92                                         0
                                               8788             93 93                                         0
                                               8977             94 94                                         0
                                               9168             95 95                                         0
                                               9361             96 96                                         0
                                               9556             97 97                                         0
                                               9753             98 98                                         0
                                               9952             99 99                                         0
                                               9953            100 100                                        0


245 rows selected.

After exporting the table with conventional exp the original table is dropped:

Table dropped.

Import the table with conventional import (imp) and look at the results; notice that all of the specialty histograms are now converted to frequency histograms:




COLUMN_NAME                         NUM_DISTINCT HISTOGRAM       NUM_BUCKETS
----------------------------------- ------------ --------------- -----------
FREQUENCY                                    100 FREQUENCY               100

TOP_N                                        100 FREQUENCY                95

HYBRID                                       100 FREQUENCY                50



COLUMN_NAME                         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE  ENDPOINT_REPEAT_COUNT
----------------------------------- --------------- -------------- ---------------------- ---------------------
FREQUENCY                                         3              1                                            0
                                                  8              2                                            0
                                                 15              3                                            0
                                                 24              4                                            0
                                                 35              5                                            0
                                                 48              6                                            0
                                                 63              7                                            0
                                                 80              8                                            0
                                                 99              9                                            0
                                                120             10                                            0
                                                143             11                                            0
                                                168             12                                            0
                                                195             13                                            0
                                                224             14                                            0
                                                255             15                                            0
                                                288             16                                            0
                                                323             17                                            0
                                                360             18                                            0
                                                399             19                                            0
                                                440             20                                            0
                                                483             21                                            0
                                                528             22                                            0
                                                575             23                                            0
                                                624             24                                            0
                                                675             25                                            0
                                                728             26                                            0
                                                783             27                                            0
                                                840             28                                            0
                                                899             29                                            0
                                                960             30                                            0
                                               1023             31                                            0
                                               1088             32                                            0
                                               1155             33                                            0
                                               1224             34                                            0
                                               1295             35                                            0
                                               1368             36                                            0
                                               1443             37                                            0
                                               1520             38                                            0
                                               1599             39                                            0
                                               1680             40                                            0
                                               1763             41                                            0
                                               1848             42                                            0
                                               1935             43                                            0
                                               2024             44                                            0
                                               2115             45                                            0
                                               2208             46                                            0
                                               2303             47                                            0
                                               2400             48                                            0
                                               2499             49                                            0
                                               2600             50                                            0
                                               2703             51                                            0
                                               2808             52                                            0
                                               2915             53                                            0
                                               3024             54                                            0
                                               3135             55                                            0
                                               3248             56                                            0
                                               3363             57                                            0
                                               3480             58                                            0
                                               3599             59                                            0
                                               3720             60                                            0
                                               3843             61                                            0
                                               3968             62                                            0
                                               4095             63                                            0
                                               4224             64                                            0
                                               4355             65                                            0
                                               4488             66                                            0
                                               4623             67                                            0
                                               4760             68                                            0
                                               4899             69                                            0
                                               5040             70                                            0
                                               5183             71                                            0
                                               5328             72                                            0
                                               5475             73                                            0
                                               5624             74                                            0
                                               5775             75                                            0
                                               5928             76                                            0
                                               6083             77                                            0
                                               6240             78                                            0
                                               6399             79                                            0
                                               6560             80                                            0
                                               6723             81                                            0
                                               6888             82                                            0
                                               7055             83                                            0
                                               7224             84                                            0
                                               7395             85                                            0
                                               7568             86                                            0
                                               7743             87                                            0
                                               7920             88                                            0
                                               8099             89                                            0
                                               8280             90                                            0
                                               8463             91                                            0
                                               8648             92                                            0
                                               8835             93                                            0
                                               9024             94                                            0
                                               9215             95                                            0
                                               9408             96                                            0
                                               9603             97                                            0
                                               9800             98                                            0
                                               9999             99                                            0
                                              10000            100                                            0

HYBRID                                            3              1                                            0
                                                224             14                                            0
                                                440             20                                            0
                                                675             25                                            0
                                                899             29                                            0
                                               1155             33                                            0
                                               1368             36                                            0
                                               1599             39                                            0
                                               1848             42                                            0
                                               2115             45                                            0
                                               2400             48                                            0
                                               2703             51                                            0
                                               2915             53                                            0
                                               3135             55                                            0
                                               3363             57                                            0
                                               3599             59                                            0
                                               3843             61                                            0
                                               4095             63                                            0
                                               4355             65                                            0
                                               4623             67                                            0
                                               4899             69                                            0
                                               5183             71                                            0
                                               5328             72                                            0
                                               5475             73                                            0
                                               5624             74                                            0
                                               5775             75                                            0
                                               5928             76                                            0
                                               6083             77                                            0
                                               6240             78                                            0
                                               6399             79                                            0
                                               6560             80                                            0
                                               6723             81                                            0
                                               6888             82                                            0
                                               7055             83                                            0
                                               7224             84                                            0
                                               7395             85                                            0
                                               7568             86                                            0
                                               7743             87                                            0
                                               7920             88                                            0
                                               8099             89                                            0
                                               8280             90                                            0
                                               8463             91                                            0
                                               8648             92                                            0
                                               8835             93                                            0
                                               9024             94                                            0
                                               9215             95                                            0
                                               9408             96                                            0
                                               9603             97                                            0
                                               9800             98                                            0
                                              10000            100                                            0

TOP_N                                             1              1                                            0
                                                 16              7                                            0
                                                 33              8                                            0
                                                 52              9                                            0
                                                 73             10                                            0
                                                 96             11                                            0
                                                121             12                                            0
                                                148             13                                            0
                                                177             14                                            0
                                                208             15                                            0
                                                241             16                                            0
                                                276             17                                            0
                                                313             18                                            0
                                                352             19                                            0
                                                393             20                                            0
                                                436             21                                            0
                                                481             22                                            0
                                                528             23                                            0
                                                577             24                                            0
                                                628             25                                            0
                                                681             26                                            0
                                                736             27                                            0
                                                793             28                                            0
                                                852             29                                            0
                                                913             30                                            0
                                                976             31                                            0
                                               1041             32                                            0
                                               1108             33                                            0
                                               1177             34                                            0
                                               1248             35                                            0
                                               1321             36                                            0
                                               1396             37                                            0
                                               1473             38                                            0
                                               1552             39                                            0
                                               1633             40                                            0
                                               1716             41                                            0
                                               1801             42                                            0
                                               1888             43                                            0
                                               1977             44                                            0
                                               2068             45                                            0
                                               2161             46                                            0
                                               2256             47                                            0
                                               2353             48                                            0
                                               2452             49                                            0
                                               2553             50                                            0
                                               2656             51                                            0
                                               2761             52                                            0
                                               2868             53                                            0
                                               2977             54                                            0
                                               3088             55                                            0
                                               3201             56                                            0
                                               3316             57                                            0
                                               3433             58                                            0
                                               3552             59                                            0
                                               3673             60                                            0
                                               3796             61                                            0
                                               3921             62                                            0
                                               4048             63                                            0
                                               4177             64                                            0
                                               4308             65                                            0
                                               4441             66                                            0
                                               4576             67                                            0
                                               4713             68                                            0
                                               4852             69                                            0
                                               4993             70                                            0
                                               5136             71                                            0
                                               5281             72                                            0
                                               5428             73                                            0
                                               5577             74                                            0
                                               5728             75                                            0
                                               5881             76                                            0
                                               6036             77                                            0
                                               6193             78                                            0
                                               6352             79                                            0
                                               6513             80                                            0
                                               6676             81                                            0
                                               6841             82                                            0
                                               7008             83                                            0
                                               7177             84                                            0
                                               7348             85                                            0
                                               7521             86                                            0
                                               7696             87                                            0
                                               7873             88                                            0
                                               8052             89                                            0
                                               8233             90                                            0
                                               8416             91                                            0
                                               8601             92                                            0
                                               8788             93                                            0
                                               8977             94                                            0
                                               9168             95                                            0
                                               9361             96                                            0
                                               9556             97                                            0
                                               9753             98                                            0
                                               9952             99                                            0
                                               9953            100                                            0


245 rows selected.

Even though the query results label these values according to the histogram type that they used to be notice that they all are Frequency histograms, with no emdpoint_repeat_count other than 0.

Since this process might be used to populate dev and test databases with production data (that has been obfuscated before the developers and testers can use it) this would produce some possibly serious performance issues which might otherwise remain unexplained. Using the proper utilities really is required in such situations.

Because it can be difficult to tell just what has changed.

February 20, 2018

Here We Go, Again

Filed under: General — dfitzjarrell @ 20:38

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

Every once in a while someone decides that Oracle functionality isn’t fast enough and makes a valiant attempt to correct these perceived deficiencies. Most of the time such efforts are anything but successful. From ‘home-grown’ referential integrity (which doesn’t work) to trying to speed up existing functionality the efforts are amazing and dismal at the same time. But that doesn’t keep people from trying.

Listed below is a package that was posted on the web some years ago; the goal, as mentioned above, was to make such date math, well, easier, and the speed difference, in a single run, isn’t very noticeable. Let’s take a run at this and execute both the native Oracle functionality and the package function performing the same task for 100,000 executions and see how much time is consumed. The package code, as it was posted years ago, is shown along with some comments on the test. We begin:


SQL> 
SQL> /*
SQL> Once upon a time someone wrote this package to perform
SQL> date calculations:
SQL> 
SQL> CREATE OR REPLACE PACKAGE dates_pkg
SQL> AS
SQL> 	 FUNCTION julian_date
SQL> 	     ( date_to_convert DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> 	 FUNCTION minutes_since_midnight
SQL> 	     ( timevalue DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> 	 FUNCTION minutes_elapsed
SQL> 	     ( lowdate DATE
SQL> 	     , highdate DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> END dates_pkg;
SQL> /
SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY dates_pkg
SQL> AS
SQL> 	 FUNCTION julian_date
SQL> 	     ( date_to_convert DATE)
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     varch_value VARCHAR (10);
SQL> 	     num_value NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT TO_CHAR
SQL> 		    ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
SQL> 		    , 'J')
SQL> 	     INTO   varch_value
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT TO_NUMBER (varch_value)
SQL> 	     INTO   num_value
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (num_value);
SQL> 	 END julian_date;
SQL> 
SQL> 
SQL> 	 FUNCTION minutes_since_midnight (
SQL> 	     timevalue DATE)
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     secs_elapsed NUMBER (20);
SQL> 	     mins_elapsed NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT TO_NUMBER
SQL> 		    ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
SQL> 		    , 'SSSSS') )
SQL> 	     INTO   secs_elapsed
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (secs_elapsed / 60)
SQL> 	     INTO   mins_elapsed
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (mins_elapsed);
SQL> 	 END minutes_since_midnight;
SQL> 
SQL> 
SQL> 	 FUNCTION minutes_elapsed
SQL> 	     ( lowdate DATE
SQL> 	     , highdate DATE )
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     final_number NUMBER (20);
SQL> 	     low_julian NUMBER (20);
SQL> 	     high_julian NUMBER (20);
SQL> 	     num_days NUMBER (20);
SQL> 	     num_minutes NUMBER (20);
SQL> 	     temp_mins NUMBER (20);
SQL> 	     min_low NUMBER (20);
SQL> 	     min_high NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT julian_date (lowdate)
SQL> 	     INTO   low_julian
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT julian_date (highdate)
SQL> 	     INTO   high_julian
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (high_julian - low_julian)
SQL> 	     INTO   num_days
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (num_days * 1440)
SQL> 	     INTO   num_minutes
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT minutes_since_midnight (lowdate)
SQL> 	     INTO   min_low
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT minutes_since_midnight (highdate)
SQL> 	     INTO   min_high
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (min_high - min_low)
SQL> 	     INTO   temp_mins
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (num_minutes + temp_mins)
SQL> 	     INTO   final_number
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (final_number);
SQL> 
SQL> 	 END minutes_elapsed;
SQL> END dates_pkg;
SQL> /
SQL> 
SQL> Either this person didn't know Oracle could do this
SQL> without help or this person possibly thought the tasks
SQL> were too difficult to write.  Regardless of that this
SQL> package was created.
SQL> 
SQL> Unfortunately this package takes more time to run the
SQL> calculations than it takes to use the built-in functionality
SQL> Oracle provides.
SQL> 
SQL> Let's prove that.
SQL> 
SQL> First let's use the package to execute one of the functions
SQL> 100000 times and report how long the entire loop takes to run.
SQL> */
SQL> 
SQL> declare
  2  	     v_retval	     number:=0;
  3  begin
  4  	     for i in 1..100000 loop
  5  		     select dates_pkg.minutes_elapsed(sysdate - 10, sysdate)
  6  		     into v_retval
  7  		     from dual;
  8  	     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.36

Over 41 seconds to perform 100,000 date subtractions using the package code. That’s a lot of work to do for some basic calculations. Let’s let Oracle work its magic with some simple subtraction and multiplication and see how long 100,000 iterations takes:


SQL> 
SQL> /*
SQL> Now let's get Oracle to do the same calculation with native functionality
SQL> and report how long the entire loop takes to run.	Again this is a
SQL> 100000-iteration loop.
SQL> */
SQL> 
SQL> declare
  2  	     v_retval	     number:=0;
  3  begin
  4  	     for i in 1..100000 loop
  5  		     select  (sysdate - (sysdate - 10))*1440
  6  		     into v_retval
  7  		     from dual;
  8  	     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.27
SQL> 

Oracle did the same amount of work in less than a second and a half. It required a great deal of effort to write this package and it did take some serious thought to get the functions written to produce the correct results, but the overall execution time is far greater than it should be. To be fair the package functions do make the calculation mechanics easier to understand, and it can be argued that 100,000 repetitions is a bit excessive. So, let’s do single runs of each and report on the execution time:


SQL>
SQL> /*
SQL> Okay, so 100,000 repetitions is a bit much.  How long do single executions take?
SQL> Let's find out.  First, the package function:
SQL> */
SQL>
SQL> select dates_pkg.minutes_elapsed(sysdate - 20, sysdate)
  2  from dual;

DATES_PKG.MINUTES_ELAPSED(SYSDATE-20,SYSDATE)
---------------------------------------------
                                        28800

Elapsed: 00:00:00.01
SQL>
SQL> /*
SQL> Next let Oracle do its thing.
SQL> */
SQL>
SQL> select     (sysdate - (sysdate - 20))*1440
  2  from dual;

(SYSDATE-(SYSDATE-20))*1440
---------------------------
                      28800

Elapsed: 00:00:00.01
SQL>

A single execution doesn’t appear to take any longer than the built-in functionality, but that’s an issue with the precision of the timing values, not an indication that the package functions are as fast, or faster, than basic subtraction and multiplication. Dividing the 100,000 run elapsed times by 100,000 more accurate timings are reported. First, the per-execution time for the function calls:


SQL> select 41.36/100000 from dual;

41.36/100000
------------
    .0004136

SQL>

Next, the per-execution time for the native calculations:


SQL> select 1.27/100000 from dual;

1.27/100000
-----------
   .0000127

SQL>

Although there is a considerable difference in the per-execution times it’s not one that would be noticeable without timing data, so it’s not unusual for a programmer to conclude his or her code is just as fast as the native functionality. Usually, though, such functions are called many times by other package procedures and functions and these repeated calls cause the elapsed time to add up to a possibly significant amount. Just because it looks fast in testing doesn’t mean it’s going to be fast under actual conditions. Such results also point to a flawed testing methodology; if the testing doesn’t reflect how the code is actually going to be used then the results of those tests really can’t be relied upon to accurately reflect the performance.

Change is good, but some changes just don’t provide the expected improvements. Accurate and representative testing needs to be done, even if that testing appears to be out of the ordinary. Some problems won’t manifest themselves until extreme testing provides results ‘normal’ testing can’t reveal. True, there are no wrong roads to anywhere, but some roads may lead to unexpected places.

You just might need another map to get back home.

December 15, 2017

Unified Front

Filed under: General — dfitzjarrell @ 07:39

"The most important reason for going from one place to another is to see what's in between."
-- Norton Juster, The Phantom Tollbooth

In Oracle’s most recent release, the audit options have been expanded and modified to create the Unified Audit Trail, a ‘one-stop shop’ for recording and reporting audit information. A new view, UNIFIED_AUDIT_TRAIL, provides access to the audit details:


 Name                                                          Null?    Type
 ------------------------------------------------------------- -------- ----------------------------
 AUDIT_TYPE                                                             VARCHAR2(64)
 SESSIONID                                                              NUMBER
 PROXY_SESSIONID                                                        NUMBER
 OS_USERNAME                                                            VARCHAR2(30)
 USERHOST                                                               VARCHAR2(128)
 TERMINAL                                                               VARCHAR2(30)
 INSTANCE_ID                                                            NUMBER
 DBID                                                                   NUMBER
 AUTHENTICATION_TYPE                                                    VARCHAR2(1024)
 DBUSERNAME                                                             VARCHAR2(30)
 DBPROXY_USERNAME                                                       VARCHAR2(30)
 EXTERNAL_USERID                                                        VARCHAR2(1024)
 GLOBAL_USERID                                                          VARCHAR2(32)
 CLIENT_PROGRAM_NAME                                                    VARCHAR2(48)
 DBLINK_INFO                                                            VARCHAR2(4000)
 XS_USER_NAME                                                           VARCHAR2(128)
 XS_SESSIONID                                                           RAW(33)
 ENTRY_ID                                                               NUMBER
 STATEMENT_ID                                                           NUMBER
 EVENT_TIMESTAMP                                                        TIMESTAMP(6) WITH LOCAL TIME ZONE
 ACTION_NAME                                                            VARCHAR2(64)
 RETURN_CODE                                                            NUMBER
 OS_PROCESS                                                             VARCHAR2(16)
 TRANSACTION_ID                                                         RAW(8)
 SCN                                                                    NUMBER
 EXECUTION_ID                                                           VARCHAR2(64)
 OBJECT_SCHEMA                                                          VARCHAR2(30)
 OBJECT_NAME                                                            VARCHAR2(128)
 SQL_TEXT                                                               CLOB
 SQL_BINDS                                                              CLOB
 APPLICATION_CONTEXTS                                                   VARCHAR2(4000)
 CLIENT_IDENTIFIER                                                      VARCHAR2(64)
 NEW_SCHEMA                                                             VARCHAR2(30)
 NEW_NAME                                                               VARCHAR2(128)
 OBJECT_EDITION                                                         VARCHAR2(30)
 SYSTEM_PRIVILEGE_USED                                                  VARCHAR2(1024)
 SYSTEM_PRIVILEGE                                                       VARCHAR2(40)
 AUDIT_OPTION                                                           VARCHAR2(40)
 OBJECT_PRIVILEGES                                                      VARCHAR2(19)
 ROLE                                                                   VARCHAR2(30)
 TARGET_USER                                                            VARCHAR2(30)
 EXCLUDED_USER                                                          VARCHAR2(30)
 EXCLUDED_SCHEMA                                                        VARCHAR2(30)
 EXCLUDED_OBJECT                                                        VARCHAR2(128)
 ADDITIONAL_INFO                                                        VARCHAR2(4000)
 UNIFIED_AUDIT_POLICIES                                                 VARCHAR2(4000)
 FGA_POLICY_NAME                                                        VARCHAR2(30)
 XS_INACTIVITY_TIMEOUT                                                  NUMBER
 XS_ENTITY_TYPE                                                         VARCHAR2(32)
 XS_TARGET_PRINCIPAL_NAME                                               VARCHAR2(30)
 XS_PROXY_USER_NAME                                                     VARCHAR2(30)
 XS_DATASEC_POLICY_NAME                                                 VARCHAR2(30)
 XS_SCHEMA_NAME                                                         VARCHAR2(30)
 XS_CALLBACK_EVENT_TYPE                                                 VARCHAR2(32)
 XS_PACKAGE_NAME                                                        VARCHAR2(30)
 XS_PROCEDURE_NAME                                                      VARCHAR2(30)
 XS_ENABLED_ROLE                                                        VARCHAR2(30)
 XS_COOKIE                                                              VARCHAR2(1024)
 XS_NS_NAME                                                             VARCHAR2(30)
 XS_NS_ATTRIBUTE                                                        VARCHAR2(4000)
 XS_NS_ATTRIBUTE_OLD_VAL                                                VARCHAR2(4000)
 XS_NS_ATTRIBUTE_NEW_VAL                                                VARCHAR2(4000)
 DV_ACTION_CODE                                                         NUMBER
 DV_ACTION_NAME                                                         VARCHAR2(30)
 DV_EXTENDED_ACTION_CODE                                                NUMBER
 DV_GRANTEE                                                             VARCHAR2(30)
 DV_RETURN_CODE                                                         NUMBER
 DV_ACTION_OBJECT_NAME                                                  VARCHAR2(128)
 DV_RULE_SET_NAME                                                       VARCHAR2(90)
 DV_COMMENT                                                             VARCHAR2(4000)
 DV_FACTOR_CONTEXT                                                      VARCHAR2(4000)
 DV_OBJECT_STATUS                                                       VARCHAR2(1)
 OLS_POLICY_NAME                                                        VARCHAR2(30)
 OLS_GRANTEE                                                            VARCHAR2(30)
 OLS_MAX_READ_LABEL                                                     VARCHAR2(4000)
 OLS_MAX_WRITE_LABEL                                                    VARCHAR2(4000)
 OLS_MIN_WRITE_LABEL                                                    VARCHAR2(4000)
 OLS_PRIVILEGES_GRANTED                                                 VARCHAR2(30)
 OLS_PROGRAM_UNIT_NAME                                                  VARCHAR2(30)
 OLS_PRIVILEGES_USED                                                    VARCHAR2(128)
 OLS_STRING_LABEL                                                       VARCHAR2(4000)
 OLS_LABEL_COMPONENT_TYPE                                               VARCHAR2(12)
 OLS_LABEL_COMPONENT_NAME                                               VARCHAR2(30)
 OLS_PARENT_GROUP_NAME                                                  VARCHAR2(30)
 OLS_OLD_VALUE                                                          VARCHAR2(4000)
 OLS_NEW_VALUE                                                          VARCHAR2(4000)
 RMAN_SESSION_RECID                                                     NUMBER
 RMAN_SESSION_STAMP                                                     NUMBER
 RMAN_OPERATION                                                         VARCHAR2(20)
 RMAN_OBJECT_TYPE                                                       VARCHAR2(20)
 RMAN_DEVICE_TYPE                                                       VARCHAR2(5)
 DP_TEXT_PARAMETERS1                                                    VARCHAR2(512)
 DP_BOOLEAN_PARAMETERS1                                                 VARCHAR2(512)
 DIRECT_PATH_NUM_COLUMNS_LOADED                                         NUMBER

A wealth of information is supplied, at the database and O/S level, which makes auditing tasks much easier as only one view needs to be accessed to generate an audit report. To get started the ‘Unified Auditing’ option must be enabled:


SQL> --
SQL> -- Verify unified auditing option is enabled
SQL> --
SQL> select value from v$option where parameter = 'Unified Auditing';

VALUE
------------------------
TRUE

SQL>

If that value is FALSE some basic features of Unified Auditing will work but the full functionality will be disabled. To enable Unified Auditing the Oracle kernel will need to be re-linked to include Unified Auditing:


cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

Unified Auditing requires that an auditing policy be created against the table or tables that need to be audited. In the example below one table is created and selected for audit:


SQL>
SQL> --
SQL> -- Create a table to test the unified audit trail
SQL> --
SQL> create table audit_tst_tbl
  2  as select * from dba_objects
  3  where rownum 
SQL> --
SQL> -- Create an audit policy for the table
SQL> --
SQL> CREATE AUDIT POLICY my_audit_policy
  2  	ACTIONS INSERT ON audit_tst_tbl,
  3  	       UPDATE ON  audit_tst_tbl;

Audit policy created.

SQL>

The policy, once created, needs to be enabled using the AUDIT statement:


SQL>
SQL> --
SQL> -- Enable the policy
SQL> --
SQL> AUDIT POLICY my_audit_policy;

Audit succeeded.

SQL>

It’s a good idea to verify that the policy is enabled and contains the desired actions:


SQL>
SQL> --
SQL> -- Now verify that the policy is enabled
SQL> --
SQL> set line 180
SQL> col user_name format a20
SQL> col policy_name format a20
SQL> select * from audit_unified_enabled_policies
  2  where policy_name = 'MY_AUDIT_POLICY';

USER_NAME            POLICY_NAME          ENABLED_ SUC FAI
-------------------- -------------------- -------- --- ---
ALL USERS            MY_AUDIT_POLICY      BY       YES YES

SQL>
SQL> --
SQL> -- And verify that the policy has update and insert
SQL> --
SQL> set line 180
SQL> col audit_option format a20
SQL> col object_name format a20
SQL> select policy_name, audit_option, object_name, object_type
  2  from   AUDIT_UNIFIED_POLICIES
  3  where    policy_name in ('MY_AUDIT_POLICY');

POLICY_NAME          AUDIT_OPTION         OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- -------------------- -----------------------
MY_AUDIT_POLICY      INSERT               AUDIT_TST_TBL        TABLE
MY_AUDIT_POLICY      UPDATE               AUDIT_TST_TBL        TABLE

SQL>

The first act, after configuring and enabling the policy, is to test it. This, however, can provide some confusing results when run from the session that created and enabled the policy; in this case inserts and updates can be performed with absolutely no record generated in the Unified Audit Trail:


SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
  2  (select * from dba_objects where rownum 
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
  2  set object_id = object_id+1000;

10 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> -- This fails from the session that created the policy
SQL> --
SQL> -- It succeeds when executed from a new session
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
  2  from unified_audit_trail
  3  where (object_name = 'MY_AUDIT_POLICY'
  4  or object_name = 'AUDIT_TST_TBL'  )
  5  and action_name in ('INSERT','UPDATE')
  6  order by event_timestamp;

no rows selected

SQL>

Once the policy is created and enabled every new session that connects to the database will generate audit records if inserts, updates, or both are performed on the table configured for auditing:


SQL>
SQL> --
SQL> -- Create a new session
SQL> --
SQL>
SQL> connect bing/##############
Connected.
SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
  2  (select * from dba_objects where rownum 
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
  2  set object_id = object_id+1000;

19 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
  2  from unified_audit_trail
  3  where (object_name = 'MY_AUDIT_POLICY'
  4  or object_name = 'AUDIT_TST_TBL'  )
  5  and action_name in ('INSERT','UPDATE')
  6  order by event_timestamp;

EVENT_TIMESTAMP                ACTION_NAME          SYSTEM_PRIVILEGE_USE OBJECT_NAME
------------------------------ -------------------- -------------------- --------------------
02-MAR-17 10.48.25.687388 AM   INSERT                                    AUDIT_TST_TBL
02-MAR-17 10.48.25.689887 AM   UPDATE                                    AUDIT_TST_TBL

SQL>

Other records will be generated, including records for enabling the policy, but the session that creates the audit won’t be audited for insert and update operations.

The audit trail can grow large and Oracle has provided a procedure to purge the Unified Audit Trail that can be run manually or scripted to run through DBMS_SCHEDULER or through the operating system scheduler. An example of running it manually in a non-container 12c database is shown below:


SQL> BEGIN
  2    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  3     AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  4     USE_LAST_ARCH_TIMESTAMP    =>  FALSE
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

It can also process the Unified Audit Trail in container databases by using the CONTAINER parameter; that parameter accepts the following values:


DBMS_AUDIT_MGMT.CONTAINER_CURRENT
DBMS_AUDIT_MGMT.CONTAINER_ALL

Using the first processes the trail in the current container; the second processes all available containers in the CDB.

The USE_LAST_ARCH_TIMESTAMP parameter controls which records get purged, in a rather basic fashion. If the value passed is TRUE then all audit records written before the last archive timestamp are purged, leaving current records in the trail. Changing this to FALSE purges the entire trail.

It’s nice to see that Oracle has somewhat simplified the audit trail and has made it easier to audit specific actions on selected tables as well as auditing multiple tables in a single policy. You may not need this functionality now, but it’s good to know how it is configured so you’re ready when the requirement is made.

And it makes the trip so much easier to take.

November 20, 2017

“It’s The Latest Version!”

Filed under: General — dfitzjarrell @ 12:01

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

It’s inevitable that shops will eventually need to upgrade to Oracle 12.2.0.1 since 11.2.0.x is soon to be out of support and Oracle 18 (which would have been 12.2.0.2 but Oracle has decided to name new releases with the last two digits of the year of release) is looming on the horizon. There are good reasons for such an upgrade; the availabilty of expanded JSON functionality, APEX improvements and online table management such as moves and partition splits to name three. This means that careful planning is crucial to a successful upgrade, including object management tasks that may not seem important that can send an upgrade ‘sideways’. Let’s look at those possibly overlooked items.

Depending upon which release the upgrade starts from the path may become longer than expected, traveling through more than one Oracle release before finally ending up at 12.2.0.1. Upgrades from versions lower than 11.2.0.3 will necessitate getting to release 11.2.0.3 or 11.2.0.4 before proceeding further; it shall be presumed that the database is at either of those two releases for this discussion.

The obvious place to begin is with the pfile or spfile; deprecated parameters should be removed or commented to prevent them from being set. The following list shows all but one of the parameters deprecated by Oracle for releases up to and including 12.2.0.1:


active_instance_count
background_dump_dest
buffer_pool_keep
buffer_pool_recycle
commit_write
cursor_space_for_time
fast_start_io_target
global_context_pool_size
instance_groups
lock_name_space
log_archive_start
max_enabled_roles
parallel_automatic_tuning
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_debug
plsql_v2_compatibility
rdbms_server_dn
remote_os_authent
resource_manager_cpu_allocation
sec_case_sensitive_logon
serial_reuse
sql_trace
standby_archive_dest
user_dump_dest

The missing parameter is sec_case_sensitive_logon and is unsupported in what Oracle calls ‘Exclusive Mode’, a reference to how the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set (if it’s set at all) in the sqlnet.ora file. By default it’s set to 12, so that only version 12 clients can connect to the database. It can be set to values as low as 8 (to ensure backward compatibility for older applications) and it also affects accounts with a password version less than 11G. For compatibility reasons case-insensitive passwords are required for some older applications or applications that access both a 12.x database and an older release. The pre-upgrade tool provided by Oracle will report such issues so they can be addressed prior to the upgrade. The command to run the tool is:


$ORACLE_HOME/jdk/bin/java -jar {Oracle 12.2 home}/rdbms/admin/preupgrade.jar FILE TEXT DIR {directory to write the output to}

The $ORACLE_HOME variable, and the corresponding environment, must be set to the current, pre-12.2 Oracle home for the database to be upgraded. The tool is run from the 12.2 home. That utility will generate the following scripts and directories:


dbms_registry_basic.sql         postupgrade_fixups.sql          preupgrade.log                  upgrade
dbms_registry_extended.sql      preupgrade_driver.sql           preupgrade_messages.properties
oracle                          preupgrade_fixups.sql           preupgrade_package.sql

The preupgrade.log has all of the information necessary to prepare the database for upgrade. The .sql scripts generated tell you what they are for and the log will report which scripts to run to prepare the database. [For those who are curious setting SQLNET.ALLOWED_LOGON_VERSION_SERVER to 8 ‘turns off’ Exclusive Mode so accounts with a password version of 10G will still be accessible; not setting SQLNET.ALLOWED_LOGON_VERSION_SERVER when accounts have a 10G version password (such as those accounts created by a legacy application) will render them no longer accessible.] There may be post-upgrade actions to take (such as upgrading the database timezone) which will be reported once the postupgrade_fixups.sql script has run.

Invalid objects can interfere with an upgrade in such a way as to cause ORA-00600 errors to be generated. To be honest it is a special case that can cause this: views, materialized views, packages, functions and procedures that use database links that no longer exist can send an upgrade into a downward spiral. Obviously since these are already invalid and cannot be successfully compiled it’s highly likely they are not being used; another case, however, is when a test, dev or UA database is created from a production database and such database links are dropped for security reasons. It will still be necessary to find these objects (utlrp.sql will report them in the DBA_ERRORS table) so the DDL can be preserved and the objects dropped prior to starting the upgrade. After the upgrade is successfully completed these objects can be recreated, although they will still be invalid.

The dba_recyclebin MUST be purged prior to starting an upgrade; as the upgrade process accesses the data dictionary for the database undergoing the upgrade an recyclebin objects can stop an upgrade or cause the database to throw unexpected errors after the upgrade has apparently completed successfully.

Unused columns can also an issue as the data dictionary keeps track of them and sets their position in the table to 0; the upgrade process doesn’t check for a position value of 0, it simply checks the dictionary for columns that may need to be upgraded because of features that have been selected. Unlike invisible columns that CAN be used after they are set to INVISIBLE, dropped columns have no useful names and cannot be recovered. If developers have set set columns to UNUSED then they are really, truly gone and need to be dropped. A query of DBA_UNUSED_COL_TABS will provide a list of the owners, tables and number of columns marked UNUSED. It’s a simple task to use that view to create a script to rid those tables of their unused baggage:



select 'alter table '||owner||'.'||table_name||' drop unused columns;'
from dba_unused_col_tabs

spool drop_unused.sql
/
spool off

spool drop_unused_cols.log
set echo on
@drop_unused
set echo off
spool off

so the upgrade won’t fail.


[Compressed tables with unused columns won’t cause an upgrade to fail so there is no need to uncompress, drop the unused columns, then compress the table.]

Backup the database BEFORE starting an upgrade; should the upgrade fail the database can be restored into its original home so that the business isn’t without it during the ‘investigate the failure’ phase. Do not ignore this step; it has saved many a DBA and enterprise from losing a database to a failed upgrade.

It is no longer possible to run the catupgrd.sql script directly from the SQL> prompt; it’s a Java script that is called as shown below:


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl $ORACLE_HOME/rdbms/admin/catupgrd.sql

which runs in a mixture of serial and parallel modes; each step, and the mode used, are reported at the terminal screen during the upgrade. Sample output is shown below:


------------------------------------------------------
Phases [0-115]         Start Time:[2017_09_21 13:59:45]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [{sid}] Files:1    Time: 146s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [{sid}] Files:5    Time: 54s
Restart  Phase #:2    [{sid}] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [{sid}] Files:19   Time: 18s
Restart  Phase #:4    [{sid}] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [{sid}] Files:6    Time: 22s
*****************   Catproc Start   ****************
Serial   Phase #:6    [{sid}] Files:1    Time: 22s
*****************   Catproc Types   ****************
Serial   Phase #:7    [{sid}] Files:2    Time: 21s
Restart  Phase #:8    [{sid}] Files:1    Time: 1s
****************   Catproc Tables   ****************
Parallel Phase #:9    [{sid}] Files:69   Time: 35s
Restart  Phase #:10   [{sid}] Files:1    Time: 1s
...

When the upgrade completes the total time is reported and the names and locations of the log file and summary report are displayed. These files need to be checked before moving on to any post-upgrade activities as any errors encountered or generated during the upgrade will be reported in these files; no such output is displayed on the terminal during the upgrade process. If the steps recommended by the preupgrade tool have been followed it’s very unlikely that the upgrade will fail; obviously there is the odd chance that something in the database configuration specific to an application can cause problems but the ‘odds’ ase in the DBA’s favor if the preupgrade instructions are followed.

Upgrading to version 12.2.0.1 is different from previous upgrades but it doesn’t have to end in tragedy. Using the preupgrade tool (as Oracle recommends), following the instructions provided by that tool and addressing the issues listed here will go a long way toward making a 12.2.0.1 upgrade a success.

Which is, obviously, what you were expecting.

November 3, 2017

Go Big …

Filed under: General — dfitzjarrell @ 12:15

"You see, to tall men I'm a midget, and to short men I'm a giant;
to the skinny ones I'm a fat man, and to the fat ones I'm a thin man."
-- Norton Juster, The Phantom Tollbooth

For years DBAs (and users) have been searching for the elusive ‘go_faster’ initialization parameter in Oracle, only to be disappointed at every turn as no such setting exists, at least not with that name. Oracle has, though, moved in that direction lately with the 12.x releases by providing a way for speedier access to ‘big tables’. So what IS a ‘big table’? That’s up to how your database has the “_small_table_threshold” parameter configured. Let’s look at how Oracle has decided to make ‘big table’ access faster.

Oracle offers, in releases 12 and later, a new ‘twist’ that may improve performance of ‘big’ tables called, appropriately enough, Automatic Big Table Caching. This allows Oralce to automatically cache such tables into an area of the SGA reserved specifically for this purpose. The big table cache won’t ‘work’ for direct path reads; in the example that follows those have been ‘turned off’ for the session to ensure that the table will be loaded into the configured cache. Let’s look at how it’s configured and what it can do for you when it’s active.

Unlike the In-Memory database option Automatic Big Table Caching is a no additional cost feature; no additional licensing is required to use it. It does require a change to memory settings which may increase the shared pool/sga/memory settings and could require that additional memory be installed (if the database is heavily used and memory can’t be reallocated to the big table cache from the existing SGA/memory settings). That being said, configuring Automatic Big Table Caching is fairly simple as only one parameter, db_big_table_cache_percent_target, needs to be set. Depending upon the percentage configured this could create a situation where the current sga or memory settings need to be increased; that will depend upon the size of the big table or tables Oracle will be caching. [If the table is too large for the cache then it could be ‘cached’ to disk and the usage query will reflect that.] Let’s look at a possible configuration and see how it performs.

In this example the cache percent is set to 80 (a rather large number, true, but this is for a small, personal database and the table will be quite large in comparison to the available SGA. Let’s create and populate a table to run an example with:


BING @ quanghoo > 
BING @ quanghoo > create table bigly_bigly_table(
  2  smorgun	     number,
  3  fleester	     varchar2(800),
  4  yupqast	     date);

Table created.

BING @ quanghoo > 
BING @ quanghoo > begin
  2  	     for i in 1..1000000 loop
  3  		     insert into bigly_bigly_table
  4  		     values(i, 'Altoona'||lpad(i, 773,'0'), sysdate+mod(i,991));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

BING @ quanghoo > 
BING @ quanghoo > exec dbms_stats.gather_table_stats(user, 'BIGLY_BIGLY_TABLE', cascade=>true)

PL/SQL procedure successfully completed.

BING @ quanghoo > 

Let’s check on what the database considers a ‘small’ table:


BING @ quanghoo > 
BING @ quanghoo > connect sys/########### as sysdba
Connected.
SYS @ quanghoo > 
SYS @ quanghoo > SELECT a.ksppinm  "Parameter",  c.ksppstvl "Value"
  2  	   FROM   x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
  3  	   WHERE  a.indx = b.indx AND a.indx = c.indx
  4  	    AND   p.name(+) = a.ksppinm
  5  	    AND   UPPER(a.ksppinm) IN ( UPPER('_db_block_buffers'),
  6  					UPPER('_small_table_threshold'));

Parameter                                             Value
----------------------------------------------------- ---------------------------------
_db_block_buffers                                     208502         
_small_table_threshold                                4170    
                                                                                                                                                      
SYS @ quanghoo > 

The value is in blocks, so any table larger than 4170 blocks in this particular database will be considered a big table. Let’s now check on the number of blocks our example table occupies:


SYS @ quanghoo > connect bing/##########
Connected.
BING @ quanghoo > set echo on linesize 150 pagesize 100
BING @ quanghoo > column table_name format a35
BING @ quanghoo > alter session set parallel_degree_policy=auto;

Session altered.

BING @ quanghoo > alter session set "_serial_direct_read"=never;

Session altered.

BING @ quanghoo > select blocks from dba_tables where table_name = 'BIGLY_BIGLY_TABLE';

    BLOCKS                                                                                                                                            
----------                                                                                                                                            
    112097                                                                                                                                            

BING @ quanghoo > 

We verify the percent target that was set earlier:


BING @ quanghoo > show parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET

NAME                                 TYPE        VALUE                                                                                                
------------------------------------ ----------- ------------------------------                                                                       
db_big_table_cache_percent_target    string      80                                                                                                   
BING @ quanghoo > 

Now we check to see what the cache shows as being used; since this example has been run more than once the object count is greater than 1:


BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

Let’s run a count against the table (there are no indexes on the table) and exercise that cache a bit:


BING @ quanghoo > select count(*) from BIGLY_BIGLY_TABLE;

  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
   1000000                                                                                                                                            

BING @ quanghoo > 
BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

That the object count did not increase indicates that the object is cached and that Oracle is using it to provide the results we requested. Repeated queries against the table (using full table scans and NOT using direct reads) will provide the same results. Checking the query statistics we see nothing but consistent gets, which is what is to be expected when using the big table cache for cached objects:


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     111360  consistent gets
          1  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Running the same query again produces the same results (since the statistics didn’t change those numbers will not be repeated):


BING @ quanghoo > 
BING @ quanghoo > select count(*) from BIGLY_BIGLY_TABLE;

  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
   1000000                                                                                                                                            

BING @ quanghoo > 
BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

Should another ‘big’ table be accessed (by conventional reads) it would also go into the cache, provided there is enough space in the cache. Again, this is automatically done by Oracle once the big table cache has been configured.

Automatic Big Table Caching can be a performance improvement when large tables are the norm rather than the exception. That it’s also a feature that doesn’t require additional licensing makes it all the more desirable to configure and use.

Of course, it’s all in how you see things.

October 20, 2017

It’s From The Catalog

Filed under: General — dfitzjarrell @ 17:24

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
-- Norton Juster, The Phantom Tollbooth

Database upgrades aren’t terribly difficult in general, unless the upgrade is for an RMAN catalog database that has two catalogs with different versions. It’s completely ‘legal’ to have an 11.2.0.x RMAN catalog database with an additional 12.1.0.x catalog; this is where the going can get rough during an upgrade since the owner of the 12c catalog will likely have an after logon context trigger which will become invalid during the ‘startup upgrade’ step of the database upgrade. Once that trigger goes invalid the only accounts that can login to the database are SYS, SYSTEM and the catalog owner of the 11.2.0.x catalog.

All of the documentation on upgrading RMAN catalog databases and the associated catalogs address the one database/one catalog scenario, with that one catalog having the same version as the database. So what IF you have two catalogs of differing versions in an 11.2.0.x database? The upgrade can be completed successfully but it takes some additional steps. Let’s look at the differences between 11.2.0.x and 12.1.0.x (or 12.2.0.x) RMAN and then at those steps to see where this can differ from a ‘normal’ upgrade.

The trigger mentioned above is the first hurdle that must be addressed, and that’s an easy hurdle to jump. The name of that problematic trigger is VPC_CONTEXT_TRG and it sets a specific context for catalog owners. Prior to changing ORACLE_HOME locations to the 12.x version simply log in as the catalog owner of the 12.x catalog and disable the trigger:


SQL> alter trigger VPC_CONTEXT_TRG disable;

Trigger altered.

SQL>

Now the upgrade to 12.x can proceed and the invalid trigger won’t affect the second catalog owner.

Oracle, in the 12.x versions of RMAN, change the VPC configuration used in 11.2.0.x to a VPD configuration; because of this some additional steps need to be taken even with single-catalog repository databases. The first change in the procedure is a script, located in $ORACLE_HOME/rdbms/admin named dbmsrmansys.sql. This script adds privileges to the RECOVERY_CATALOG_OWNER role necessary to access and manage the VPD configuration and must be run before connecting to the RMAN catalog database otherwise the dreaded ORA-01031 “insufficient privileges” error will rear its ugly head. This will cover all recovery catalog owners in the repository database, regardless of the catalog version.

After that script is executed then each catalog owner needs to be granted the necessary privileges to access the VPD configuration so another script is run, this time for each catalog owner in the database:


sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman
sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman12c

The script exits after execution which is why the code listed above shows two calls to sqlplus. The output of that script is shown below:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman

Checking the operating user... Passed

Granting VPD privileges to the owner of the base catalog schema RMAN

========================================
VPD SETUP STATUS:
VPD privileges granted successfully!
Connect to RMAN base catalog and perform UPGRADE CATALOG.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Once this is done for all catalog owners in the database it’s time to start upgrading catalogs. Normally executing UPGRADE CATALOG; will prompt for a second submission of that command to verify that is exactly what you want to do. To get around this the NOPROMPT option is available so UPGRADE CATALOG need be entered only once:


Recovery Manager: Release 12.2.0.1.0 - Production on Fri Oct 20 15:15:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RCATT (DBID=4052274215)
recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RMAN database is too old

RMAN> upgrade catalog noprompt;

recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> exit


Recovery Manager complete.

This now needs to be done to the second (12.x) catalog, even if it’s at 12.2.0.1 so that the objects can be rebuilt.

A final script needs to be executed before the upgrade is actually completed:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -all

Checking the operating user... Passed

----------------------------------------
Removing old VPC views in the base catalog of RMAN12...
----------------------------------------
Removing old VPC views in the base catalog of RMAN...
========================================
UPGRADE STATUS:
The VPC user schemas of these catalogs: RMAN12, RMAN
have been successfully upgraded to the new VPD model!

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

The final step is to log in as the 12.x catalog owner (in this case RMAN12) and enable the trigger that was disabled prior to the upgrade:


SQL> alter trigger VPC_CONTEXT_TRG enable;

Trigger altered.

SQL>

The database upgrade, along with the RMAN catalog upgrades, are complete. Verify this by logging in as a ‘regular’ user; the connection should be successful:


SQL> connect yerble
Enter password:
Connected.
SQL>

It may not be the easiest upgrade to perform but having the steps beforehand can certainly help.

And it’s much less annoying than that octopus.

October 16, 2017

Roll With It

Filed under: General — dfitzjarrell @ 11:07

"I know one thing for certain; it is much harder to tell whether you are lost than whether you were lost,
for, on many occasions, where you are going is exactly where you are. On the other hand, if you often find
that where you've been is not at all where you should have gone, and, since it's much more difficult to
find your way back from someplace you've never left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

Waiting is, well, inconvenient at times because it interferes with getting work done yet there are times when there is nothing to do, really, except wait. Of course this usually happens during an important update to a critical application table, when the transaction fails on error or, horror of horrors, the network ‘gives way’ and the session is disconnected, forcing Oracle to rollback all of that work you just did. Unfortunately that rollback seems to take forever to complete. The reason for that is that large, long-running transactions can generate large volumes of UNDO. And UNDO is how Oracle restores the data to its starting point should the transaction fail or be cancelled (which, by golly, just happened). This, of course, generates long rollback times that can seem interminable when you are waiting for the rollback to complete before other work can begin. But, monitoring the rollback progress isn’t difficult since V$TRANSACTION, in conjunction with V$SESSION, can report on the UNDO blocks that have yet to be applied. Let’s see how that can be done.

V$TRANSACTION records various pieces of information about active and cancelled transactions, with one of those pieces being the blocks of UNDO that are used (either allocated or applied, depending on whether the transaction is running or being rolled back). That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:


set echo on pagesize 100 linesize 132

select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
                                          else 'Not Rolling Back'
       end   as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'));

select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
from v$transaction t, v$session s
where t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr;

The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done. The client_info column may or may not be populated (it isn’t in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored. Looking at an example should help; a long-running transaction is in progress in session #1; looking at the UNDO blocks being allocated (from a second session) we see the USED_UBLK column increasing in value and that the transaction is not rolling back:


BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              430

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              739

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >
...
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6475

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6755

BING @ quanghoo >

At the time the transaction is cancelled the used UNDO block total was:


--
-- Transaction still active and generating UNDO
--
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             7914

BING @ quanghoo >

From the second session we can now monitor the progress of the rollback, using the same queries as before. This time the first query reports a rollback is in progress:


--
-- Transaction cancelled
--
-- Successive executions of these queries return a decreasing used undo block count
-- thus tracking the rollback progress
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             5110

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             3491

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             1363

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688               70

BING @ quanghoo >

--
-- Transaction rollback complete
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >

The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.

Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyone’s life (at least for those who are waiting on a rollback to finish) a bit easier.

Which doesn’t make you lost, just … impatient.

July 24, 2017

That’s Not A Latch

Filed under: General — dfitzjarrell @ 08:22

"They all looked very much like the residents of any small valley to which you've never been."
-- Norton Juster, The Phantom Tollbooth

While investigating latches another interesting process popped up that’s smaller and lighter, that performs a similar function, called a mutex. So what is a mutex? By definition it’s a mutual exclusion object that locks memory structures to prevent concurrent sessions from changing the same object at the same time, so in at least one way a mutex is like a latch. [Operating systems also have a process called a mutex that behaves in a similar way.] In other ways it isn’t; let’s look into the mutex side of Oracle and see how they differ from latches.

A mutex is smaller than a latch but what does that mean? It’s a reference to the code path each must take to be executed; in terms of size code for a latch is around 110 bytes (which contains anywhere between 150 and 200 instructions) while the code for a mutex is a mere 28 bytes (having around 30 to 35 instructions), making a mutex faster to execute. Being smaller and lighter (gee, I’ve seen that phrase before …) a mutex won’t contain the level of information a latch contains; it won’t have data on who is waiting or how about the length of the wait, it won’t have information on requests and misses but there is sleep data available (how many, how long) and a couple of other details that can help monitor them.

Let’s look at what benefits a mutex can provide:

  • Less potential for false contention.
  • Replace both latches and pins.
  • Mutex structure is located in each child cursor.

Let’s look at each one of those benefits. First is the lower potential for false contention; a latch can protect multiple hash buckets so if two users were searching two different hash buckets the possibility exists that those two buckets could be protected by the same latch and, as a result, the two sessions would contend for access to that latch. With mutexes each hash bucket could easily have it’s own mutex for protection thus eliminating the contention to lock and access the bucket.

Second, mutexes can replace both latches and pins. A mutex can act as a serialization mechanism (just like a latch, restricting access to a single session) and also as a pin (to prevent an object from ‘aging out’ of the relevant cache). A mutex has both an exclusive and a shared mode, and in shared mode multiple sessions can access (using the term ‘reference’) the same mutex and the number of sessions currently referencing a given mutex is known as the ‘reference count’. That information is stored in the mutex. And, like a latch, a mutex can also be held in exclusive mode and the reference count of 1 could indicate an exclusive hold on that mutex. Additionally an object cannot be aged out of a cache until the mutex reference count is 0.

Third, the mutex structure is located in each child cursor handle with the mutex itself acting as the pin structure for the cursor. Changing the pin status is now simply a matter of changing the mutex reference count; this eliminates the need to get the library cache latch making the mutex even more efficient than the latch/pin combination.

So what information is available about mutexes and where can it be found? Oracle provides the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views that can be queried to get the following information:


SQL> desc v$mutex_sleep
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_TYPE                                         VARCHAR2(32)
 LOCATION                                           VARCHAR2(40)
 SLEEPS                                             NUMBER
 WAIT_TIME                                          NUMBER
 CON_ID                                             NUMBER

SQL> desc v$mutex_sleep_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_IDENTIFIER                                   NUMBER
 SLEEP_TIMESTAMP                                    TIMESTAMP(6)
 MUTEX_TYPE                                         VARCHAR2(32)
 GETS                                               NUMBER
 SLEEPS                                             NUMBER
 REQUESTING_SESSION                                 NUMBER
 BLOCKING_SESSION                                   NUMBER
 LOCATION                                           VARCHAR2(40)
 MUTEX_VALUE                                        RAW(8)
 P1                                                 NUMBER
 P1RAW                                              RAW(8)
 P2                                                 NUMBER
 P3                                                 NUMBER
 P4                                                 NUMBER
 P5                                                 VARCHAR2(64)
 CON_ID                                             NUMBER

SQL>

Querying V$MUTEX_SLEEP we see:


MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME     CON_ID
-------------------------------- ---------------------------------------- ---------- ---------- ----------
Library Cache                    kglllal3 111                                      5      21885       0
Library Cache                    kglllal1 109                                      1         78       0
Library Cache                    kglhdgn2 106                                     39     136235       0
Library Cache                    kglpnal1  90                                      5      46642       0
Library Cache                    kglhdgn1  62                                      5      35087       0
Library Cache                    kglscn1   18                                      3       9972       0
Library Cache                    kglpin1   4                                       1      25173       0
Library Cache                    kglget2   2                                       2      90986       0
Cursor Parent                    kkscsPruneChild [KKSPRTLOC35]                     4       8852       0
Cursor Parent                    kksfbc [KKSPRTLOC2]                               2      45124       0
Cursor Pin                       kkslce [KKSCHLPIN2]                             296     432125       0
Cursor Pin                       kksfbc [KKSCHLFSP2]                               2       1795       0

12 rows selected.

SQL>

MUTEX_TYPE indicates the action or object the mutex protects, and in the above example there are mutexes protecting library cache objects and acting as cursor pins. LOCATION is the code location where waiters slept for the indicated mutex. SLEEPS is the number of times a requestor had to sleep before getting the mutex and WAIT_TIME is, oddly enough, the wait time in microseconds. CON_ID is the container ID, which is only relevant in 12.1 and later releases; in 11.2 and earlier the CON_ID column does not exist.

V$MUTEX_SLEEP_HISTORY provides more information:


MUTEX_IDENTIFIER SLEEP_TIMESTAMP                                                             MUTEX_TYPE                             GETS     SLEEPS RE
QUESTING_SESSION BLOCKING_SESSION LOCATION                               MUTEX_VALUE              P1 P1RAW                    P2         P3         P4
 P5                                                                 CON_ID
---------------- --------------------------------------------------------------------------- -------------------------------- ---------- ---------- --
---------------- ---------------- ---------------------------------------- ---------------- ---------- ---------------- ---------- ---------- --------
-- ---------------------------------------------------------------- ----------
        71628033 10-JUL-17 07.00.07.021000 AM                                                Cursor Parent                    10          4
     6              133 kkscsPruneChild [KKSPRTLOC35]            0000008500000000         22 00                0          0          0
                                                         0
      3495269867 10-JUL-17 07.00.08.673000 AM                                                Cursor Pin                                1        101
           368               16 kkslce [KKSCHLPIN2]                      0000001000000000          2 00                        0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           135              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           133              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      4087692675 10-JUL-17 07.00.07.101000 AM                                                Library Cache                             7          4
             6              251 kglllal3 111                             000000FB00000000          0 000007FF86347838          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          2
           251              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          4
           249              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         60
           135              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         59
           133              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2034504591 10-JUL-17 07.00.01.555000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F64210          0          0          0
                                                                                 0
      3323840122 10-JUL-17 07.00.01.548000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F8B300          0          0          0
                                                                                 0
        71628033 10-JUL-17 07.00.06.980000 AM                                                Cursor Parent                     9          2
   133              364 kksfbc [KKSPRTLOC2]                      0000016C00000000         22 00                0          0          0
                                                         0
      1264706735 10-JUL-17 07.00.01.554000 AM                                                Library Cache                             3          1
             6              123 kglhdgn2 106                             0000007B00000000          0 000007FF86F68DD0          0          0          0
                                                                                 0
       746657336 10-JUL-17 07.00.06.893000 AM                                                Library Cache                            38          1
           364                0 kglpnal1  90                             00                        0 000007FF864AF268          0          0          0
                                                                                 0
...

Some of the same information in V$MUTEX_SLEEP also is present in v$MUTEX_SLEEP_HISTORY but WAIT_TIME is essentially replaced with SLEEP_TIMESTAMP and the number of gets is reported along with additional information on the requesting session and any blocking session for the mutex (Oracle collects that information at the time the mutex is requested; it’s not information stored internally in the mutex structure). ‘Internal use’ information will be found in the P1, P1RAW, P2, P3, P4 and P5 columns; P1, P2 and P3 can contain the hash value of the library cache objects under contention as well as other information. The PARAMETER1-3 columns in V$EVENT_NAME and the P1TEXT, P2TEXT and P3TEXT columns in V$SESSION_WAIT can shed light on what values those columns can hold and their meaning. Since such information is release-specific the following query can help identify what additional information may be available in the V$MUTEX_SLEEP_HISTORY view:


select name, parameter1, parameter2, parameter3
from v$event_name
where name in (select event from v$session_wait where (p1,p2,p3) in (select p1, p2, p3 from v$mutex_sleep_history));

The above query is provided to help identify possible additional data in the V$MUTEX_SLEEP_HISTORY view; as always you should test it on your own systems to see what results are provided.

Since it may not be obvious latches and mutexes are independent mechanisms; a process can hold both a latch and a mutex at the same time.

As of release 11.2.0.x the following latches have been replaced by mutexes:

  • library cache pin allocation
  • library cache lock allocation
  • library cache hash chains
  • library cache lock
  • library cache
  • library cache pin

The library cache waits listed below no longer exist in releases 11.2.0.x and later:

  • latch: library cache
  • latch: library cache lock
  • latch: library cache pin

As good as mutexes are there are still conditions, like excessive hard parsing and poor application cursor management (where cursors are closed after every execution and no cursor caching is in effect), that can cause hash collisions or continuous traversing of library cache hash chains and, thus, contention. On the whole, however, mutexes have improved concurrency and considerably reduced latch and pin contention.

Next time you don’t go somewhere you’ve never been, look carefully. You just might see something new.

Next Page »

Create a free website or blog at WordPress.com.