Oracle Tips and Tricks — David Fitzjarrell

October 25, 2018

Tuning Time

Filed under: General,Performance — dfitzjarrell @ 15:42

"“You see,” he continued, beginning to feel better, “once there was no time at all, and people found it very inconvenient.
They never knew whether they were eating lunch or dinner, and they were always missing trains. So time was invented to
help them keep track of the day and get places when they should. When they began to count all the time that was available,
what with 60 seconds in a minute and 60 minutes in an hour and 24 hours in a day and 365 days in a year, it seemed as if
there was much more than could ever be used. ‘If there’s so much of it, it couldn’t be very valuable,’ was the general
opinion, and it soon fell into disrepute. People wasted it and even gave it away. Then we were given the job of seeing
that no one wasted time again,” he said, sitting up proudly.” 
-- Norton Juster, The Phantom Tollbooth

DBAs usually get questions on query performance when one runs slower than the user would like and. as a result, questions on how best to go about tuning a query soon follow. End-users usually don’t like waiting for results and queries that take longer than a few seconds tend to be considered as suspect. What many end-users don’t know is that query tuning is a combination of art, science and (gasp) intuition. Elegant tuning solutions are art, based on science, and that science uses two of Oracle’s events, 10046 and 10053, to provoide more detailed information. Which one is more beneficial depends on the query and the ‘bad’ execution plan. A 10046 trace can show what Oracle is waiting on and where time is being spent in the execution phase of running a query. Sometimes, however, the waits and elapsed time breakdowns aren’t enough, which is where the 10053 trace can be extremely helpful. Note that this post takes one particular situation and gives abbreviated steps in solving it; it’s not meant to be a treatise on the subject nor a detailed case study. With that in mind let’s look at a plan where that event can provide much-needed information.

Queries using nested subqueries can take a number of paths to return data; one possibility involves what Oracle calls ‘subquery unnesting’, a process where Oracle re-writes the nested query into an unnested join with the intent that this will improve data access and thus query performance. This can be done for several nested subqueries, resulting in one or more NESTED LOOP joins in the execution plan. This is where the road can take a less than desirable turn; joins consume time, making the query execution longer than it probably should be. If the query in question is a ‘one-off’, run once, maybe twice in a day, this may not be a real problem as a few seconds here and there won’t throw off the regular day’s work. If, on the other hand, this is a regularly run statement, the longer run times can stack up on each other and cause delays in processing, and, as the old addage states: “Time is money.” Since the query plan won’t give details on how Oracle eventually generated it one course of action is setting the 10053 event in the current session and executing the problem statement.

The first step in tuning is to obtain the current execution plan and see if it makes ‘sense’. A query with joins will likely produce NESTED LOOP JOIN or HASH JOIN steps. In this case the plan produced a NESTED LOOPS SEMI step that, on the face of it, made no sense as the query contained no join conditions as originally written. This could be a clue that the optimizer did something different and posibly unexpected with the plan. That is exactly what prompted this investigation which used a 10053 event trace to ‘track down’ what the optimizer chose to do. This, in turn, caused a slight rewrite of the query to include the necessary hints to ‘nudge’ the optimizer toward a more efficient plan.

Setting event 10053 is not a difficult task; let’s look at a script that does this and then executes several SELECT statements before turning the 10053 ‘switch’ off (unfortunately the actual queries can’t be published as they are proprietary so placeholders will be used):


alter session set tracefile_identifier='MAILQUEUE';
alter session set events = '10053 trace name context forever, level 2';

[First query statement here]

[Second query statement here]

[Third query statement here]

[Fourth query statement here]

alter system set events = '10053 trace name context off';

The important parts of the above example are the lines prior to any query statements, which turn on the 10053 event and set a trace file identifier so the generated trace file can be easily identified, and the statement after the queries have run, which stops the 10053 event. Unlike the 10046 event, which is more easily read after processing through the tkprof utility, the 10053 trace file needs no additional work. It can be a bit daunting at first since it’s like no other trace file most DBAs have seen. Let’s go through the example, using the actual trace file generated by the unobfuscated script, and see what it reveals.

The beginning of the trace file provides some basic information on the environment and provides query ‘signatures’ that the optimizer uses to identify query and subquery blocks:


Trace file ....
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = ...
System name:	SunOS
Node name:	...
Release:	5.10
Version:	Generic_150400-61
Machine:	sun4v
Instance name: ...
Redo thread mounted by this instance: 1
Oracle process number: 1134
Unix process pid: 22631, image: oracle@... (TNS V1-V3)

Query Block Registry:
SEL$2 0x77a10ca0 (PARSER)
  SEL$F5BB74E1 0x77a198b0 (VIEW MERGE SEL$1; SEL$2) [FINAL]
SEL$1 0x77a198b0 (PARSER)
  SEL$F5BB74E1 0x77a198b0 (VIEW MERGE SEL$1; SEL$2) [FINAL]

:
    call(in-use=12456, alloc=347568), compile(in-use=95008, alloc=158520), execution(in-use=15312, alloc=16200)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================
*** SESSION ID:(758.2581) 2018-08-21 09:40:23.286
 
Registered qb: SEL$1 0x77a198b0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=68513 hint_alias="DQ"@"SEL$1"

Registered qb: SEL$2 0x7751be68 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=69663 hint_alias="WPV"@"SEL$2"

Registered qb: SEL$3 0x7751b188 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$3 nbfros=1 flg=0
    fro(0): flg=4 objn=10290765 hint_alias="MRE"@"SEL$3"

SPM: statement not found in SMB
SPM: capture of plan baseline is OFF

This begins the portion of the trace file of interest, including the query [not posted due to confidentiality concerns] and the legend of abbreviations:


**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property.
table property forces parallelism

Global Manual DOP: 1 - Rounded?: no
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=*************) -----
[ Removed for confidentiality reasons ]
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed 
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans

Other areas of the trace file provide environment information that is not necessary for this discussion. Because the execution plan includes a NESTED LOOPS SEMI step the reason for this should be ascertained since the original query had no join conditions. Looking through the trace file doesn’t reveal anything that looks unusual to the modestly trained eye until the section provided below is examined. Searching through the trace file for indications of join activity allowed the DBA to eventually ‘zero in’ on the offending optimizer step. Subquery unnesting isn’t uaually an issue, but in this case it involved two similar nested subqueries that Oracle thought would perform better as joins. Unfortunately this optimizer decision ended up as the more costly alternative:


*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery removal for query block SEL$3 (#3)
RSW: Not valid for subquery removal SEL$3 (#3)
Subquery unchanged.
Subquery Unnesting on query block SEL$2 (#2)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$2 (#2).
SU:   Checking validity of unnesting subquery SEL$3 (#3)
SU:   Passed validity checks.
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$BE5C8E5F 0x755990f8 (SUBQUERY UNNEST SEL$2; SEL$3)

The query unnesting results in performance degradation because Oracle now must process a nested loop join; hinting the subqueries altered the nested loops path into two filter operations, speeding up the execution time considerably. Having this information allows the DBA to use the proper hint (no_unnest) on each of the nested subqueries to eliminate this behavior.

Sometimes query tuning can require a considerable amount of time, especially when searching a trace file for a cause with little more than an execution plan and the fact that the plan doesn’t match how the query was constructed. Even DBAs experienced with the oddities of the optimizer may miss the cause when it’s buried in a section that doesn’t appear to apply at first glance.

No post on any aspect of query tuning can provide an absolute, foolproof method for improving performance. There are simply too many variables and too many optimizer possibilities to consider. Hopefully this post has provided a starting point for query and statement tuning; remember that each query is different and that performance tuning is statement-specific. Consider each query on its own and major improvements may not be guaranteed, but sometimes even the smallest change can produce measurable results.

Only time will tell.

Advertisements

August 10, 2018

Well, That Didn’t Work …

Filed under: General — dfitzjarrell @ 11:46

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

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

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


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

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


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

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

SQL>

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


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

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

SQL>

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


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

no rows returned

SQL>

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

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

Sometimes that fork in the road is actually a spoon …

August 4, 2018

Eviction Notice

Filed under: General — dfitzjarrell @ 17:22

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

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

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

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

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

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

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

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

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

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

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

Move along, now, you need to get going.

May 21, 2018

Export – Import Business, Again

Filed under: General — dfitzjarrell @ 19:05

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

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

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

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



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

TOP_N                                        100 TOP-FREQUENCY            95

HYBRID                                       100 HYBRID                   50


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

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

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


245 rows selected.

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

Table dropped.

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




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

TOP_N                                        100 FREQUENCY                95

HYBRID                                       100 FREQUENCY                50



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

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

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


245 rows selected.

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

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

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

February 20, 2018

Here We Go, Again

Filed under: General — dfitzjarrell @ 20:38

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

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

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


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

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.36

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


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

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.27
SQL> 

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


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

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

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

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

Elapsed: 00:00:00.01
SQL>

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


SQL> select 41.36/100000 from dual;

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

SQL>

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


SQL> select 1.27/100000 from dual;

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

SQL>

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

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

You just might need another map to get back home.

December 15, 2017

Unified Front

Filed under: General — dfitzjarrell @ 07:39

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

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


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

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


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

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

SQL>

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


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

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


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

Audit policy created.

SQL>

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


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

Audit succeeded.

SQL>

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


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

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

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

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

SQL>

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


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

Commit complete.

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

10 rows updated.

SQL>
SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

no rows selected

SQL>

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


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

Commit complete.

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

19 rows updated.

SQL>
SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

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

SQL>

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

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


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

PL/SQL procedure successfully completed.

SQL>

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


DBMS_AUDIT_MGMT.CONTAINER_CURRENT
DBMS_AUDIT_MGMT.CONTAINER_ALL

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

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

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

And it makes the trip so much easier to take.

November 20, 2017

“It’s The Latest Version!”

Filed under: General — dfitzjarrell @ 12:01

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

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

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

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


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

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


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

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


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

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

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

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

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



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

spool drop_unused.sql
/
spool off

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

so the upgrade won’t fail.


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

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

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


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

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


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

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

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

Which is, obviously, what you were expecting.

November 3, 2017

Go Big …

Filed under: General — dfitzjarrell @ 12:15

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

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

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

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

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


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

Table created.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

BING @ quanghoo > 

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


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

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

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


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

Session altered.

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

Session altered.

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

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

BING @ quanghoo > 

We verify the percent target that was set earlier:


BING @ quanghoo > show parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET

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

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


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

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

BING @ quanghoo > 

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


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

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

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

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

BING @ quanghoo > 

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


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

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


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

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

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

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

BING @ quanghoo > 

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

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

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

October 20, 2017

It’s From The Catalog

Filed under: General — dfitzjarrell @ 17:24

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

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

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

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


SQL> alter trigger VPC_CONTEXT_TRG disable;

Trigger altered.

SQL>

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

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

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


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

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


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

Checking the operating user... Passed

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

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

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

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


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

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

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

RMAN> upgrade catalog noprompt;

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

RMAN> exit


Recovery Manager complete.

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

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


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

Checking the operating user... Passed

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

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

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


SQL> alter trigger VPC_CONTEXT_TRG enable;

Trigger altered.

SQL>

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


SQL> connect yerble
Enter password:
Connected.
SQL>

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

And it’s much less annoying than that octopus.

October 16, 2017

Roll With It

Filed under: General — dfitzjarrell @ 11:07

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

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

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


set echo on pagesize 100 linesize 132

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

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

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


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

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

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

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

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

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

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

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

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

no rows selected

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

no rows selected

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

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

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

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

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

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

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

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

BING @ quanghoo >

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


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

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

BING @ quanghoo >

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

BING @ quanghoo >

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

no rows selected

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

no rows selected

BING @ quanghoo >

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

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

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

Next Page »

Blog at WordPress.com.