Oracle Tips and Tricks — David Fitzjarrell

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.

Advertisements

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 with 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.

July 12, 2017

Latch, What Latch?!?

Filed under: General — dfitzjarrell @ 13:04

"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

Latches can be, well, confusing. Sessions can wait for them, and wait for them, and when those waits become excessive (kind of like at the doctor’s office) performance degrades. To many they appear to be the same as locks, but they aren’t, even though lock waits, when the times get excessive, can also degrade performance. So, what are the differences between latches and locks? Let’s examine both and see where they differ, what can cause latch waits and how latch waits can be investigated.

Latches are similar to locks but they operate on memory to protect code and internal data structures by preventing concurrent access. For example the LRU latches are used when managing the buffer cache, an operation that is restricted to being run by a single process; other processes must wait for the current process to complete its actions on the buffer cache before the next one in line can proceed. The latch holds this structure for the current process to access; when the current process is done the latch is released and the next process in the queue can acquire it. Since only one process at a time can hold a latch ‘popular’ actions can create a long line of processes waiting to obtain the associated latch. Normally latch waits are in milliseconds or fractions of milliseconds so waiting for a latch usually isn’t noticeable.

Let’s look at the differences between a lock and a latch:

    Locks

  • Protect the logical contents of the database object (table, index) from other transactions.
  • Are held for the transaction duration.
  • Provide rollback capability for the associated transaction.
  • Latches

  • Protect the critical sections of the associated internal data structures from other threads.
  • Are held only until the operation completes and then are released.
  • Prevent concurrent access to a memory structure.

Since memory operations aren’t transactions the ability to ‘rollback’ a change isn’t necessary; clearing a buffer so another process can use it is a fast, irreversible operation that isn’t designed to be undone. Contrast that to a lock, which controls access to change data in a table; such actions are transactional in nature and are designed with the ability to undo the changes should the need arise. Locks can also protect a data image while it’s being read which gives rise to the different types of locks that are available — shared, row shared, row shared exclusive, exclusive — that govern who and how the data can be modified. Without exception every lock in an Oracle database allows other sessions to read data in the locked table; the locks are there to control concurrent updates and protect against the possibility of ‘lost’ transactions.

Latches can indicate the existence of a locking problem, as the ‘cursor pin s wait on x’ latch illustrates. This latch, when its wait times become long, usually indicates an issue with a ‘hot block’, a data or index block that is so popular everyone and their Aunt Gertrude are waiting for it. Long waits for this latch usually accompany concurrent sessions inserting data into a table using a sequence; the sequence values will be clustered together and will usually access the same data (for direct-path inserts) or index block and can cause sessions to wait until the current process completes. And since the latch allows one, and only one, process access to it at a time the rest of the interested transactions (and their processes) form a long queue, sort of like a really busy day at the amusement park. In such cases addressing the locking problem also addresses the latching problem.

Various tools are available to investigate latching issues; Tanel Poder has written two scripts, latchprof.sql and latchprofx.sql, that provide a wealth of information about latch wait history. They can be invaluable in investigating latch waits and contention. An example of the output that can be generated is shown below:


-- LatchProfX 2.00 by Tanel Poder ( http://www.tanelpoder.com )

  SID NAME                                HMODE        FUNC                                             OBJECT SQLID               Held       Gets  Held %     Held ms Avg hold ms
----- ----------------------------------- ------------ ---------------------------------------- -------------- ------------- ---------- ---------- ------- ----------- -----------
  144 transaction allocation              exclusive    ktcxbr                                                0 9brz3jsxmf4ts        788         57   78.80     267.920       4.700
  144 DML lock allocation                 exclusive    ktadmc                                                0 9brz3jsxmf4ts        609         59   60.90     207.060       3.509
 1168 shared pool                         exclusive    kghalo                                                0 0zmr8ccx3xpck        177          5   17.70      60.180      12.036
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 8n2dsqpu94h4a        119          7   11.90      40.460       5.780
 1562 cache buffers chains                exclusive    kcbgtcr: slow path excl                        2403D825 8n2dsqpu94h4a         90          1    9.00      30.600      30.600
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 gt8xcw52s32ha         40         16    4.00      13.600        .850
 2794 redo copy                           exclusive    kcrfw_redo_gen: nowait                                0 4vs91dcv7u1p6         38          2    3.80      12.920       6.460
 2328 KTILM latch                         shared       ktilmht get                                           0 bnqcqzztx3a41         37          4    3.70      12.580       3.145
 2794 sim partition latch                 exclusive    kcbm_simulate                                         0 4vs91dcv7u1p6         37          1    3.70      12.580      12.580
 1562 KTILM latch                         shared       ktilmht get                                           0 8n2dsqpu94h4a         30          6    3.00      10.200       1.700
 2328 cache buffers chains                exclusive    kcbgcur: fast path excl                          87316B 9zg9qd9bm4spu         26          1    2.60       8.840       8.840
 2412 row cache objects                   exclusive    kqrso                                                 0                       24         15    2.40       8.160        .544
 1413 cache buffers chains                exclusive    kcbgcur: fast path excl                          873294 9zg9qd9bm4spu         22          1    2.20       7.480       7.480
 2412 row cache objects                   exclusive    kqrpre: find obj                                      0                       21         11    2.10       7.140        .649
 1168 row cache objects                   exclusive    kqrpre: find obj                                      0 0zmr8ccx3xpck         20         10    2.00       6.800        .680
 2328 row cache objects                   exclusive    kqrso                                                 0                       20         13    2.00       6.800        .523
 1168 cache buffers chains                exclusive    kcbgcur: fast path excl                          873294 9zg9qd9bm4spu         19          1    1.90       6.460       6.460
 2328 row cache objects                   exclusive    kqrpre: find obj                                      0 5wwkp7spyq2fn         19         11    1.90       6.460        .587
 2412 cache buffers chains                exclusive    kcbgcur: fast path excl                          87260B 9zg9qd9bm4spu         18          1    1.80       6.120       6.120
 1562 KTILM latch                         shared       ktilmht get                                           0 gt8xcw52s32ha         18          5    1.80       6.120       1.224
 1413 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu         18          1    1.80       6.120       6.120
 2794 row cache objects                   exclusive    kqrso                                                 0                       17         13    1.70       5.780        .445
 1168 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu         17          1    1.70       5.780       5.780
 2328 row cache objects                   exclusive    kqrpre: find obj                                      0                       17         10    1.70       5.780        .578
 2328 shared pool                         exclusive    kghalo                                                0                       15          7    1.50       5.100        .729
 2794 row cache objects                   exclusive    kqrpre: find obj                                      0                       14         12    1.40       4.760        .397
 2412 shared pool                         exclusive    kghalo                                                0                       14          4    1.40       4.760       1.190
 2794 row cache objects                   exclusive    kqrpre: find obj                                      0 4k3y3hrxfzh6v         14          8    1.40       4.760        .595
 2794 shared pool                         exclusive    kghalo                                                0 4k3y3hrxfzh6v         13          6    1.30       4.420        .737
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 149htpw5s5n0c         13          5    1.30       4.420        .884
 2412 row cache objects                   exclusive    kqreqd                                                0                       13          9    1.30       4.420        .491
 2794 row cache objects                   exclusive    kqrso                                                 0 4k3y3hrxfzh6v         11          9    1.10       3.740        .416
 1562 KTILM latch                         shared       ktilmht get                                           0 by143mnp65wgp         10          2    1.00       3.400       1.700
 1562 cache buffers chains                exclusive    kcbzgb: scan from tail. nowait                        0 gt8xcw52s32ha         10          8    1.00       3.400        .425
 2794 cache buffers chains                exclusive    kcbgtcr: slow path excl                          400276 4k3y3hrxfzh6v          9          1     .90       3.060       3.060
 2794 cache buffers lru chain             exclusive    kcbzgws                                               0 4k3y3hrxfzh6v          9          1     .90       3.060       3.060
 2412 row cache objects                   exclusive    kqrpre: find obj                                      0 5wwkp7spyq2fn          9          7     .90       3.060        .437
 2412 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          34 5wwkp7spyq2fn          9          1     .90       3.060       3.060
 2328 row cache objects                   exclusive    kqreqd                                                0                        9          6     .90       3.060        .510
 1168 cache buffers chains                exclusive    kcbgtcr: slow path excl                          402106 0zmr8ccx3xpck          9          1     .90       3.060       3.060
 1562 object queue header operation       exclusive    kcbo_link_q                                           0 gt8xcw52s32ha          9          6     .90       3.060        .510
 2328 KTILM latch                         shared       ktilmht get                                           0 4twyxstjxggc1          9          2     .90       3.060       1.530
 1168 cache buffers lru chain             exclusive    kcbzgws                                               0 0zmr8ccx3xpck          9          1     .90       3.060       3.060
  144 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          3C 9brz3jsxmf4ts          9          1     .90       3.060       3.060
 1168 KTILM latch                         shared       ktilmht get                                           0 24kkd5u3fdf0c          9          1     .90       3.060       3.060
 2328 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          3A 24kkd5u3fdf0c          9          1     .90       3.060       3.060
 2412 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          39 5wwkp7spyq2fn          8          1     .80       2.720       2.720
 2412 cache buffers chains                maybe-shared kcbgtcr: fast path exam                          4003C1 5wwkp7spyq2fn          8          1     .80       2.720       2.720
 2412 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu          8          1     .80       2.720       2.720
 2328 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu          8          1     .80       2.720       2.720

In this example the latchprofx.sql script is being called; it reports the SID of the holding session, the name of the latch, the mode in which the latch is held (notice that almost all of the latches reported are exclusive), the associated kernel function, the object involved (for latches associated with transactional activity), the number of times the latch has been held, the number of times the latch has been gotten successfully, the percentage of gets to holds, the total hold time in milliseconds and the average hold time, again in milliseconds. The report is orderd by the the HELD values to make it easier to see which latches are being held and for how long. Other values can be output depending upon the parameters passed to the script.

As latches control access to memory structures anything that affects how memory is allocated and used can affect them. It’s possible to run afoul of latch contention when memory ‘leaks’ occur (usually caused by code that doesn’t properly manage the memory structures it uses). Common memory ‘leaks’ involve memory areas that are no longer used but are no longer accessible because the code that created the memory structure didn’t free it properly. As available memory slowly decreases from these leaks it can become more difficult to obtain a latch, resulting in queues that are much longer than they would normally be. Another problem that can create the same situation is remote user sessions that ‘die’ at the client but can’t clean up properly, leaving orphan processes holding latches that can’t be released. How such situations are managed is dependent upon the specific conditions surrounding them and resolution in extreme cases could require drastic action. Since these are usually rare occurrences these won’t be discussed further.

AWR and ASH reports are a good place to start when investigating wait activity and knowing whether the waits are lock or latch related; of course those reports require the Diagnostic and Tuning pack license and may not be available if your site isn’t licensed. Statspack reports are also good sources of information to start an investigation and list latch activity in a dedicated report section; a part of that section is reproduced below:


Latch Activity  DB/Inst: FNORG/fnorg  Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ Background: interrupt              1    0.0             0            0
AQ deq hash table latch               1    0.0             0            0
ASM db client latch                   5    0.0             0            0
ASM map operation hash t              1    0.0             0            0
Change Notification Hash              3    0.0             0            0
Consistent RBA                       40    0.0             0            0
DML lock allocation                 706    0.0             0            0
Event Group Locks                     1    0.0             0            0
File State Object Pool P              1    0.0             0            0
I/O Staticstics latch                 1    0.0             0            0
ILM Stats Stripe Latch                1    0.0             0            0
ILM Stats main anchor la            155    0.0             0            0
ILM access tracking exte              1    0.0             0            0
ILM activity tracking la              1    0.0             0            0
IM area sb latch                      1    0.0             0            0
IM area scb latch                     1    0.0             0            0
IM emb latch                          1    0.0             0            0
IM seg hdr latch                      1    0.0             0            0
IPC stats buffer allocat              1    0.0             0            0
In memory undo latch                  1    0.0             0            0
JS Sh mem access                      1    0.0             0            0
JS queue access latch                 1    0.0             0            0
JS queue state obj latch             84    0.0             0            0
JS slv state obj latch                1    0.0             0            0
KCNIBR - invalid block r              1    0.0             0            0
KFC FX Hash Latch                     1    0.0             0            0
KFC Hash Latch                        1    0.0             0            0
KFCL LE Freelist                      1    0.0             0            0
KGNFS-NFS:SHM structure               1    0.0             0            0
KGNFS-NFS:SVR LIST                    1    0.0             0            0
KJC message pool free li              1    0.0             0            0
KJC message pool pending              1    0.0             0            0
KJCT flow control latch               1    0.0             0            0
KMG MMAN ready and start              2    0.0             0            0
KSFS OFS ctx level paren              1    0.0             0            0
KSFS OFS req layer paren              1    0.0             0            0
KSFS OFS sess layer pare              1    0.0             0            0
KSFS id table parent lat              1    0.0             0            0
KSFS ksfs_node latch                  1    0.0             0            0
KSFSD name cache parent               1    0.0             0            0
KSXR hang analysis phase              1    0.0             0            0
KSXR hang analysis phase              1    0.0             0            0
KTF sga latch                         0                    0            1    0.0
KTILM latch                           1    0.0             0            0
KWQP Prop Status                      2    0.0             0            0
Locator state objects po              1    0.0             0            0
Lsod array latch                      1    0.0             0            0
Memory Management Latch               1    0.0             0            2    0.0
Memory Queue                          1    0.0             0            0
Memory Queue Message Sub              1    0.0             0            0
...

Latch contention occurs with the ‘willing to wait’ latch calls; nowait calls won’t build a queue and will be retried at a later time.

Knowing the difference between a latch and a lock, and that latches can be affected by locks (but in most cases they are not) can make troubleshooting application and database performance a bit easier. Since latches are memory control structures they can be affected by the amount of available memory as well as by the overall activity of the database. Keeping this in mind during such investigations can prevent the DBA from being led into trying to address a lock problem when it’s really a latch affecting performance. Also, having the right tools available can greatly assist in determining where the problem area or areas lie.

Yes, latches can be confusing. Hopefully this has provided guidance and insight into what latches are, how they can affect performance and what tools to use to find additional information.

Now, go back where you weren’t.

June 19, 2017

Extended Engagement

Filed under: General — dfitzjarrell @ 12:03

"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

Oracle offers, in release 12.1.0.2, the option of increasing the maximum string length from the standard 4000 to a whopping 32767; depending on the nls_length_semantics setting this could be in bytes or in characters. When nls_length_semantics is set to BYTE then the traditional behavior can be expected, that is when VARCHAR2 fields are declared longer than 4000 bytes there is an issue when creating indexes, especially for unique or primary key constraints. The following error can be thrown:


ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

The three factors that affect this behavior are the overall length of the index key, the nls_length_semantics setting and the block size of the database or tablespace in use. The issue arises when the total length of the VARCHAR2 key columns exceeds a pre-determined length, dependent upon the nls_length_semantics of the database or session, the block size and the database characterset in use. The following example illustrates these limits under both length semantics conditions using the AL32UTF8 characterset, a characterset where one character can consume up to four bytes. The example creates the same table with differing definitions and attempts to create a unique constraint on the table. For byte-length semantics the following results were obtained with an 8k block size in use. Multiple tests were run varying the VARCHAR2 lengths of all columns in the constraint/index, which may seem repitious but it more clearly illustrates the issue:


SQL> 
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32767) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6347)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6348)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6327)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6328)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6307)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6308)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6287)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6288)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 

Notice in all cases the maximum key length calculates to 6387 bytes which means that the sum of the declared lengths of all VARCHAR2 columns to be included in a given index cannot exceed that maximum. This changes dramatically when CHAR length semantics are in use, again with the AL32UTF8 character set:


SQL> 
SQL> --
SQL> -- Now set length semantics to CHAR
SQL> --
SQL> -- Using AL32UTF8 character set the VARCHAR2
SQL> -- lengths reduce to 1/4 of those for BYTE
SQL> -- semantics
SQL> --
SQL> alter session set nls_length_semantics=CHAR;

Session altered.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1576)  not null,
  4  stringm	     varchar2(10) ,
  5  stringz	     varchar2(10)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1577)  not null,
  4  stringm	     varchar2(10) ,
  5  stringz	     varchar2(10)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1556)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1557)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1536)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1537)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1516)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1517)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1496)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1497)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1476)  not null,
  4  stringm	     varchar2(60) ,
  5  stringz	     varchar2(60)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1477)  not null,
  4  stringm	     varchar2(60) ,
  5  stringz	     varchar2(60)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1456)  not null,
  4  stringm	     varchar2(70) ,
  5  stringz	     varchar2(70)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1457)  not null,
  4  stringm	     varchar2(70) ,
  5  stringz	     varchar2(70)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1436)  not null,
  4  stringm	     varchar2(80) ,
  5  stringz	     varchar2(80)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1437)  not null,
  4  stringm	     varchar2(80) ,
  5  stringz	     varchar2(80)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 

[Again multiple examples were configured to more clearly illustrate the problem.] The maximum length now becomes 1596, and remember that refers to characters; the overall byte length is three bytes shorter than when nls_length_semantics was set to BYTE this is due to the 1 character = 4 bytes conversion for the AL32UTF8 character set. Such behavior is repeated for any of the UTF-8 family of character sets as they all map one character to up to four bytes, and Oracle defaults to the 1:4 conversion for these character sets regardless of the actual storage a character may consume. These limitations present themselves when the database block size is 8192, or 8K; as stated previously the block size does influence when the limit is reached and larger block sizes will result in longer keys and a slightly different error message. The following results are generated using a 16k block size tablespace (the maximum block size for a Windows PC environment):


SQL>
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32761) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
  2  id              number primary key,
  3  stringtst       varchar2(12907)  not null,
  4  stringm         varchar2(20) ,
  5  stringz         varchar2(20)  not null) tablespace yerg_16k;

Table created.

SQL>
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz)
  3  using index (
  4  create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
  5  tablespace yerg_16k);

Table altered.

SQL>
SQL> drop table hashtest purge;

Table dropped.

SQL>
SQL> create table hashtest(
  2  id              number primary key,
  3  stringtst       varchar2(12908)  not null,
  4  stringm         varchar2(20) ,
  5  stringz         varchar2(20)  not null) tablespace yerg_16k;

Table created.

SQL>
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz)
  3  using index (
  4  create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
  5  tablespace yerg_16k);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (12958) exceeded


SQL>
SQL> drop table hashtest purge;

Table dropped.

SQL>

One way to prevent this is to keep track of the total VARCHAR2 length; when using BYTE semantics this must not exceed 6387 for an 8K block size and it must not exceed 12947 for a 16K block size. Of course adding up all of the VARCHAR2 lengths could take some time when a large number of columns are included in an index (not a wise idea to begin with) and indexing long VARCHAR2 columns may not be very helpful (which depends on the data they contain). Another way around this dilemma is to modify the table in question to create virtual columns based upon the standard hash values of the longer VARCHAR2 columns, then using those virtual columns in the unique index/constraint. An example of this technique follows:


SQL>
SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Create virtual columns using the long text columns
SQL> -- as the source
SQL> --
SQL> -- Use the standard_hash function to ensure the key length
SQL> -- will not be excessive
SQL> --
SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));

Table altered.

SQL>
SQL> --
SQL> -- Use these virtual columns in the unique key/index structure
SQL> --
SQL> alter table longstrings add constraint longstrings_text_unq unique (text_hash, moretext_hash, smalltxt);

Table altered.

SQL>
SQL> --
SQL> -- Try to populate the table with non-unique data
SQL> --
SQL> -- The constraint works as expected and the inserts
SQL> -- eventually fail
SQL> --
SQL> begin
  2     for i in 1..10000 loop
  3             insert into longstrings(id, text, moretext, smalltxt)
  4             values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
  5     end loop;
  6     commit;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (BING.LONGSTRINGS_TEXT_UNQ) violated
ORA-06512: at line 3


SQL>

Describing the table after the virtual columns are created reveals:


SQL> desc longstrings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TEXT                                               VARCHAR2(32000)
 MORETEXT                                           VARCHAR2(32000)
 SMALLTXT                                           VARCHAR2(20)
 TEXT_HASH                                          RAW(20)
 MORETEXT_HASH                                      RAW(20)

SQL>

Each of the long VARCHAR2 columns are reduced to a 20-byte hash of the original values, allowing Oracle to create constraints in situations where this would not normally be possible.

Looking at how the virtual columns are defined reveals that no additional triggers or processing will be necessary to populate them when inserts are processed:


SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));

The standard_hash function will be applied to all values of text and moretext at the time the insert is processed, populating the index columns ensuring the constraint will function properly. As a consequence of this inserts will need to specify all but the _hash columns in the insert list:


insert into longstrings(id, text, moretext, smalltxt) …

Additionally any forms, code or web pages that return data from tables modified in this manner will also need to list the columns to be returned, which would exclude any _hash named columns. This prevents forms from failing from the following error:


SQL> declare
  2     v_id    longstrings.id%type;
  3     v_text  longstrings.text%type;
  4     v_mretxt longstrings.moretext%type;
  5     v_smlltxt longstrings.smalltxt%type;
  6
  7     cursor getall is
  8     select * From longstrings
  9     where rownum <=12;
 10  begin
 11     open getall;
 12     loop
 13             fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
 14             exit when getall%notfound;
 15
 16             dbms_output.put_line(v_id||':'||v_text||':'||v_mretxt||':'||v_smlltxt);
 17     end loop;
 18  end;
 19  /
                fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
                *
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored


SQL>

Implied select lists like those generated by ‘select * from …’ queries can no longer be used in situations illustrated in the above example after such table modifications have been made since the returned list of values is greater than the number of variables declared to hold such values. Of course a PL/SQL for loop and ‘select * …’ cursors can be used since it’s necessary to append the column name to the cursor variable to return the selected value:


SQL> declare
  2     cursor getall is
  3     select * from longstrings
  4     where rownum <=10;
  5  begin
  6  for i in 1..10000 loop
  7          insert into longstrings(id, text, moretext, smalltxt)
  8          values (i, 'Borscht '||i, 'BorschtBorscht '||i, 'Hubba');
  9  end loop;
 10  commit;
 11     for ls in getall loop
 12             dbms_output.put_line(ls.id||'   '||ls.text||'   '||ls.moretext||'  '||ls.smalltxt);
 13     end loop;
 14  end;
 15  /
1   Borscht 1   BorschtBorscht 1  Hubba
2   Borscht 2   BorschtBorscht 2  Hubba
3   Borscht 3   BorschtBorscht 3  Hubba
4   Borscht 4   BorschtBorscht 4  Hubba
5   Borscht 5   BorschtBorscht 5  Hubba
6   Borscht 6   BorschtBorscht 6  Hubba
7   Borscht 7   BorschtBorscht 7  Hubba
8   Borscht 8   BorschtBorscht 8  Hubba
9   Borscht 9   BorschtBorscht 9  Hubba
10   Borscht 10   BorschtBorscht 10  Hubba

PL/SQL procedure successfully completed.

SQL>

Yet ANOTHER way around this limitation, if constraints are not being created, is to apply the standard_hash() function to the column or columns directly in the index definition:


SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));

Table created.

SQL> 
SQL> --
SQL> -- Create index using the extended string length columns
SQL> -- as the source
SQL> --
SQL> create index longstrings_idx on longstrings(standard_hash(text), standard_hash(moretext), smalltxt);

Index created.

SQL> 
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2  	for i in 1..10000 loop
  3  		insert into longstrings(id, text, moretext, smalltxt)
  4  		values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
  5  	end loop;
  6  	commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Select using the index
SQL> --
SQL> -- Works for equality queries only
SQL> --
SQL> set autotrace on linesize 132
SQL> column text format a45
SQL> column moretext format a45
SQL> 
SQL> select *
  2  from longstrings
  3  where text = 'Borscht 7';

        ID TEXT                                          MORETEXT                                      SMALLTXT                     
---------- --------------------------------------------- --------------------------------------------- --------------------         
       313 Borscht 7                                     BorschtBorscht 1                              Hubba                        
        79 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       196 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       781 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       898 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       430 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       547 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       664 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1249 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1366 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1015 Borscht 7                                     BorschtBorscht 1                              Hubba                        
...
      6874 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      6991 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7693 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7810 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7342 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7459 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7576 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8278 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7927 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8044 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8161 Borscht 7                                     BorschtBorscht 10                             Hubba                        

1111 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1813482573                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------------                             
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |                             
-------------------------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT                    |                 |   829 |    25M|     1   (0)| 00:00:01 |                             
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LONGSTRINGS     |   829 |    25M|     1   (0)| 00:00:01 |                             
|*  2 |   INDEX RANGE SCAN                  | LONGSTRINGS_IDX |    30 |       |     1   (0)| 00:00:01 |                             
-------------------------------------------------------------------------------------------------------                             
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='Borscht 7' AND                                                                  
              INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))                                                                          
   2 - access(STANDARD_HASH("TEXT")=HEXTORAW('D55F75A7098D2100B35FEF4A344FDC64A4FFF29E'))                                           
                                                                                                                                    

SQL>

Range queries unfortunately use a table scan:

 
SQL> --
SQL> -- Try using a range
SQL> --
SQL> -- Generates tablescan
SQL> --
SQL> select *
  2  from longstrings
  3  where text between 'Borscht 6' and 'Borscht 8';

        ID TEXT                                          MORETEXT                                      SMALLTXT                     
---------- --------------------------------------------- --------------------------------------------- --------------------         
       214 Borscht 7                                     BorschtBorscht 6                              Hubba                        
       215 Borscht 8                                     BorschtBorscht 7                              Hubba                        
       222 Borscht 6                                     BorschtBorscht 1                              Hubba                        
       223 Borscht 7                                     BorschtBorscht 2                              Hubba                        
       224 Borscht 8                                     BorschtBorscht 3                              Hubba                        
       231 Borscht 6                                     BorschtBorscht 10                             Hubba                        
       232 Borscht 7                                     BorschtBorscht 11                             Hubba                        
       233 Borscht 8                                     BorschtBorscht 12                             Hubba                        
       240 Borscht 6                                     BorschtBorscht 6                              Hubba                        
       241 Borscht 7                                     BorschtBorscht 7                              Hubba                        
       242 Borscht 8                                     BorschtBorscht 8                              Hubba                        
...
      9484 Borscht 7                                     BorschtBorscht 7                              Hubba                        
      9485 Borscht 8                                     BorschtBorscht 8                              Hubba                        
      9492 Borscht 6                                     BorschtBorscht 2                              Hubba                        
      9493 Borscht 7                                     BorschtBorscht 3                              Hubba                        
      9494 Borscht 8                                     BorschtBorscht 4                              Hubba                        
      9501 Borscht 6                                     BorschtBorscht 11                             Hubba                        
      9502 Borscht 7                                     BorschtBorscht 12                             Hubba                        
      9503 Borscht 8                                     BorschtBorscht 0                              Hubba                        
      9510 Borscht 6                                     BorschtBorscht 7                              Hubba                        
      9511 Borscht 7                                     BorschtBorscht 8                              Hubba                        
      9512 Borscht 8                                     BorschtBorscht 9                              Hubba                        

3333 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 411405923                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------                                                   
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                   
---------------------------------------------------------------------------------                                                   
|   0 | SELECT STATEMENT  |             |  2471 |    75M|   163   (0)| 00:00:01 |                                                   
|*  1 |  TABLE ACCESS FULL| LONGSTRINGS |  2471 |    75M|   163   (0)| 00:00:01 |                                                   
---------------------------------------------------------------------------------                                                   
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">='Borscht 6' AND                                                                 
              "TEXT"<='Borscht 8' AND INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))                                                  
                                                                                                                                    

SQL> 

A caveat of this method is the index created works very well for equality predicates but fails with inequality and IN constructs so using this technique may not provide the performance desired if the queries against the table aren’t using equality predicates.

The method chosen to address such issues depends on the length of the VARCHAR2 columns involved; VARCHAR2 columns declared that are longer than 4000 characters (the standard default from Oracle) will need to have virtual columns created using the standard_hash() function to ensure the key length won’t exceed 80% of the usable space based on the block size; for 8K blocks there will be 7998 bytes available , and for 16K blocks there will be 16198 bytes available (the remaining space is the overhead for block management). How the nls_length_semantics parameter is configured for your database will make a difference when deciding how to proceed with index and constraint creation should the extended string length option be chosen.

The bigger they are …

Next Page »

Blog at WordPress.com.