Avoid Merge Join Cartesian in a SQL Tunning Exercise

Encountered a query that caused CPU utilization high. In a 15 min AWR, this query executes 78 times with total Buffer Gets 949M, which contributes 78.8% of the total. I filled in some bind variable values by checking v$sql_bind_capture view. And I executed the sql from sqlplus with gather_plan_statistics hint. Below is the execution plan witn E-Rows and A-Rows info :

Plan hash value: 4161037915

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------ 
|   1 |  SORT ORDER BY                             |                        |      1 |      1 |    113K|00:02:27.54 |    4242K|  12873 |   8580 |    76M|  3013K|   67M (0)|         | 
|   2 |   HASH UNIQUE                              |                        |      1 |      1 |    113K|00:02:23.74 |    4242K|  12873 |   8580 |    72M|  7323K| 9538K (1)|   73728 | 
|*  3 |    FILTER                                  |                        |      1 |        |    114K|00:03:25.41 |    4242K|   4293 |      0 |       |       |          |         | 
|   4 |     NESTED LOOPS                           |                        |      1 |      1 |    114K|00:00:15.77 |    4239K|   4291 |      0 |       |       |          |         | 
|   5 |      MERGE JOIN CARTESIAN                  |                        |      1 |      1 |    199K|00:00:01.71 |    6001 |    123 |      0 |       |       |          |         | 
|   6 |       MERGE JOIN CARTESIAN                 |                        |      1 |      1 |   7112 |00:00:00.44 |    5998 |    123 |      0 |       |       |          |         | 
|*  7 |        HASH JOIN OUTER                     |                        |      1 |      1 |    889 |00:00:00.15 |    5995 |    123 |      0 |   985K|   927K| 1229K (0)|         |
|   8 |         NESTED LOOPS                       |                        |      1 |      1 |    889 |00:00:00.11 |    5979 |    123 |      0 |       |       |          |         | 
|   9 |          NESTED LOOPS                      |                        |      1 |      1 |    889 |00:00:00.10 |    5088 |    123 |      0 |       |       |          |         | 
|* 10 |           HASH JOIN OUTER                  |                        |      1 |      1 |    889 |00:00:00.09 |    4197 |    123 |      0 |   928K|   928K| 1265K (0)|         | 
|  11 |            NESTED LOOPS                    |                        |      1 |      1 |    865 |00:00:00.10 |    4193 |    123 |      0 |       |       |          |         |
|  12 |             NESTED LOOPS                   |                        |      1 |      1 |   1839 |00:00:00.07 |     504 |    123 |      0 |       |       |          |         | 
|  13 |              MERGE JOIN CARTESIAN          |                        |      1 |      1 |      1 |00:00:00.01 |      12 |      0 |      0 |       |       |          |         | 
|  14 |               NESTED LOOPS                 |                        |      1 |      1 |      1 |00:00:00.01 |       8 |      0 |      0 |       |       |          |         | 
|  15 |                NESTED LOOPS                |                        |      1 |      1 |      1 |00:00:00.01 |       5 |      0 |      0 |       |       |          |         | 
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |      1 |     26 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  22 |               BUFFER SORT                  |                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |     492 |    123 |      0 |       |       |          |         | 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |      16 |     13 |      0 |       |       |          |         |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |   1839 |      1 |    865 |00:00:00.03 |    3689 |      0 |      0 |       |       |          |         |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |   1839 |      1 |   1839 |00:00:00.01 |    1841 |      0 |      0 |       |       |          |         | 
|  29 |            VIEW                            |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  30 |             SORT UNIQUE                    |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |  4096 |  4096 | 4096  (0)|         | 
|  31 |              UNION-ALL                     |                        |      1 |        |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |      1 |     20 |     20 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |      1 |      9 |      9 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|  34 |           TABLE ACCESS BY INDEX ROWID      | SPEED_CODES            |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         |
|* 35 |            INDEX UNIQUE SCAN               | PK_SPEED_CODES         |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         | 
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  38 |         INDEX FAST FULL SCAN               | PK_OFFER_PROD          |      1 |   1255 |   1255 |00:00:00.01 |      16 |      0 |      0 |       |       |          |         | 
|  39 |        BUFFER SORT                         |                        |    889 |      8 |   7112 |00:00:00.01 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  40 |         TABLE ACCESS FULL                  | BILLING_FREQ           |      1 |      8 |      8 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|  41 |       BUFFER SORT                          |                        |   7112 |     28 |    199K|00:00:00.20 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  42 |        TABLE ACCESS FULL                   | UNIT_TYPES             |      1 |     28 |     28 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 43 |      TABLE ACCESS BY INDEX ROWID           | UHCDN_RATES            |    199K|      1 |    114K|00:02:16.61 |    4233K|   4168 |      0 |       |       |          |         | 
|* 44 |       INDEX RANGE SCAN                     | PK_UHCDN_RATES         |    199K|     36 |   4879K|00:01:48.28 |     727K|    911 |      0 |       |       |          |         | 
|* 45 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |    976 |      1 |    933 |00:00:00.02 |    2928 |      2 |      0 |       |       |          |         | 
|* 46 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |      9 |      1 |      4 |00:00:00.01 |      18 |      0 |      0 |       |       |          |         |

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------

The AUTOTRACE statistics is as follows:

Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
    4242325  consistent gets
      13434  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13494  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     113976  rows processed

By comparing the E-rows and A-rows from the execution, it is easy to identify that the problem starts from the operation id 25 and 26, where E-rows=1 and A-rows=1839 and 1894.

|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |        ... 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |        ...                   

With E-rows=1, Oracle CBO decides to use "MERGE JOIN CARTESIAN". Notice at the end, CBO estimate only 1 row whereas actual number of rows is 113K. So the key to tune this query is to avoid the Cartesian join at operation id 5 and 6. I modified the query by adding the following hints and of course make sure the tables order is correct in the FROM clause:

/*+ ordered use_hash(E), use_hash(F) */

Here is the execution plan  of the modified query:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   1 |  SORT ORDER BY                             |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   2 |   HASH UNIQUE                              |                        |     1 |  2841 |    73   (7)| 00:00:01 |
|*  3 |    FILTER                                  |                        |       |       |         |     |
|*  4 |     HASH JOIN OUTER                        |                        |     1 |  2841 |    72   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                          |                        |     1 |  2835 |    68   (5)| 00:00:01 |
|   6 |       NESTED LOOPS                         |                        |     1 |  2828 |    67   (5)| 00:00:01 |
|*  7 |        HASH JOIN                           |                        |     1 |  2813 |    66   (5)| 00:00:01 |
|   8 |         NESTED LOOPS                       |                        |     1 |  2801 |    64   (5)| 00:00:01 |
|   9 |          NESTED LOOPS                      |                        |     1 |  2781 |    63   (5)| 00:00:01 |
|* 10 |           HASH JOIN OUTER                  |                        |     1 |  2682 |    17  (18)| 00:00:01 |
|  11 |            NESTED LOOPS                    |                        |     1 |   578 |    11   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                   |                        |     1 |   326 |    10   (0)| 00:00:01 |
|  13 |              MERGE JOIN CARTESIAN          |                        |     1 |   263 |     7   (0)| 00:00:01 |
|  14 |               NESTED LOOPS                 |                        |     1 |   153 |     5   (0)| 00:00:01 |
|  15 |                NESTED LOOPS                |                        |     1 |   125 |     3   (0)| 00:00:01 |
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |     1 |   120 |     2   (0)| 00:00:01 |
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |     1 |       |     1   (0)| 00:00:01 |
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |     1 |     5 |     1   (0)| 00:00:01 |
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |     1 |       |     0   (0)| 00:00:01 |
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |     1 |    28 |     2   (0)| 00:00:01 |
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |     1 |       |     1   (0)| 00:00:01 |
|  22 |               BUFFER SORT                  |                        |     1 |   110 |     5   (0)| 00:00:01 |
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |     1 |   110 |     2   (0)| 00:00:01 |
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |     1 |       |     1   (0)| 00:00:01 |
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |     1 |    63 |     3   (0)| 00:00:01 |
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |     1 |       |     2   (0)| 00:00:01 |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |     1 |   252 |     1   (0)| 00:00:01 |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |     1 |       |     0   (0)| 00:00:01 |
|  29 |            VIEW                            |                        |    29 | 61016 |     5  (40)| 00:00:01 |
|  30 |             SORT UNIQUE                    |                        |    29 |  1138 |     5  (80)| 00:00:01 |
|  31 |              UNION-ALL                     |                        |       |       |         |     |
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |    20 |   760 |     1   (0)| 00:00:01 |
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |     9 |   378 |     2   (0)| 00:00:01 |
|* 34 |           TABLE ACCESS BY INDEX ROWID      | UHCDN_RATES            |    21 |  2079 |    46   (0)| 00:00:01 |
|* 35 |            INDEX RANGE SCAN                | PK_UHCDN_RATES         |    72 |       |     2   (0)| 00:00:01 |
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |     1 |    20 |     1   (0)| 00:00:01 |
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |     1 |       |     0   (0)| 00:00:01 |
|  38 |         TABLE ACCESS FULL                  | BILLING_FREQ           |     8 |    96 |     2   (0)| 00:00:01 |
|  39 |        TABLE ACCESS BY INDEX ROWID         | UNIT_TYPES             |     1 |    15 |     1   (0)| 00:00:01 |
|* 40 |         INDEX UNIQUE SCAN                  | PK_UNIT_TYPES          |     1 |       |     0   (0)| 00:00:01 |
|  41 |       TABLE ACCESS BY INDEX ROWID          | SPEED_CODES            |     1 |     7 |     1   (0)| 00:00:01 |
|* 42 |        INDEX UNIQUE SCAN                   | PK_SPEED_CODES         |     1 |       |     0   (0)| 00:00:01 |
|  43 |      INDEX FAST FULL SCAN                  | PK_OFFER_PROD          |  1255 |  7530 |     3   (0)| 00:00:01 |
|* 44 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |     1 |    12 |     3   (0)| 00:00:01 |
|* 45 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

AUTOTRACE statistics of the modified query is also shown below:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     479315  consistent gets
       2097  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13535  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     113976  rows processed

It can be seen that after tunning the "consistent gets" drop to 479,315 from 4,242,325. In the  production database, I created a SQL Profile to enforce the better plan.

My Experience of Data Pump Export and Import for Database Migration

Recently I have carried out a test of using Data Pump Export and Import utilities to migrate a 17 TB database from Solaris to Linux platform, also from single-instance, conventional file system to a two-node RAC on ASM. During the exercise I have learned and become more familiar with quite a few interesting features or options of Data Pump Export and Import utilites and encountered a few difficulties as well.

The first obstacle I met was that I was unable to do Data Pump Export to the NFS storage. We have the NFS storage that is shared between the Solaris and Linux server. At first I tried to perform Data Pump Export to the NFS storage directly. Unfortunately, the job was just stuck there for long time. Having researched this issue for a while and tried the options mentioned in a MOS note (See References [1]) with the help of a SA, I just could not get this worked out. Finally we decided to export to the local file system first then move the dump files to the NFS. I was able to move about 150 GB dump file per hour. Fortunately, the Data Pump Import from NFS storage did not pose any problems.

Table mode Data Pump Export and Import were used in the test. Large tables were typically exported or imported individually and smaller tables were grouped together into several batches. For each large table or a group of smaller tables, I prepared the corresponding parameter files with parallel option if appropriate. In the Import job, I always excluded the index, constraint and statistics. I used SQLFILE option of the impdp command to extract the DDL commands related to the tables, including create table, index and constraint statements etc. Those DDLs were executed after the Import with parallel option for index creation and "ENABLE NOVALIDATE" for constraints typically. Therefore the database migration actually consisted of multiple tables migration jobs. For each tables migration, I performed and recorded the timing of the following steps: export, move dump file to NFS, import, DDLs for index etc. Those tables migration jobs were overlapped as two or three of them were executed at the same time and could be in different steps.

Shortly after the onset of the test I found out that Data Pump Export was extremely slow on a big table with lob column. The  export job was unable to run in parallel regardless of the parallel setting. The segment sizes of this big table are 237 GB and 382 GB for table and lob respectively. The table uses a sequence-based PK column called "EMAIL_SEQ_ID". To speed up the export, I started 23 export jobs at the same time, each job exported a particular range of rows based on the PK. The key is to use the QUERY option for the expdp command. The QUERY option contains a WHERE clause that specifies the PK range. Finally I was able to export this table in 30 hours, move the 420 GB dump files in 2 hours, import in about 15 hours and execute DDL for indexes etc in 2 hours. It is worth noting that although we can perform the lob table export job in parallel by such an approach, we cannot do the same for import. The import job will hold a TM lock for the table, so only one import job can run at a time. I used this approach for several big lob tables. Appendix showed example Shell script to prepare expdp or impdp parfile. By the way, later I found that unable to export in parallel for lob table is a known issue in the Oracle community. (see Reference [3]). A blog post suggested using rowid based approach to separate the rows of lob tables. (see Reference [4])

Occasionally I made mistakes and needed to terminate an export or import job. I found it very convenient to use KILL_JOB command in the interactive mode. First, I need to know the job name either from log file or from dba_datapump_jobs view. Then I can enter the interactive mode using attach option:

expdp / attach=[job_name]

Then I issued "KILL_JOB" command. Note when using KILL_JOB to the expdb, all dump files will be wiped out.

During the test, I have also learned that Data Pump Import can be done through db link. When specifying a source database link for the NETWORK_LINK option of impdp command, import job will retrieve data from the db link, and write the data directly to the target database There are no dump files involved.

I have mentioned previously SQLFILE option can be used to extract DDLs. But this option only available for impdp. Suppose I have a table in a database and I want to extract DDLs related to this table including indexes, constraints, object grants, comments, triggers etc associated with it, how should I do? Previously I always use DBMS_METADATA package for such tasks. If we create a db link to the database itself, we can use impdp with NETWORK_LINK and SQLFILE options to accomplish it very neatly.

Using export/import to migrate the database has one advantage over Transportable Tablespace approach at least: it allows the re-organization of tables and indexes. i.e. it is easy to move tables and indexes to different tablespaces if desired.

I completed the 17 TB database migration with the size of about 12 TB in the target database in about a month. The test was interrupted by other tasks with higher priority. I did total 17 tables migrations to complete the whole database migration. Ordered by end-to-end time (including export, move to NFS, import, DDL steps), they were refered to as as batch 1 to 17 here:

batch   time (hours) 
-----   ----------- 
1        50
2        30
3        25
4        24
5        21
6        16
7        15
8        14
9        11
10       10
11       10
12       9.5
13       5.5
14       5
15       3.5
16       3
17       3
------------

Based on above timing, if I perform the migration task as continuely as possible, I may be able to compete it in 5-7 days. The ultimate goal is to do a zero-down time migration. We plan to use oracle Golden Gate to caputure the changes at source during the database migration. So my next task will be investiagating whether this is possible in practise in our environment.

References:
[1] 781349.1 - ORA-27054: NFS file system where the file is created or resides is not mounted with correct options [ID 781349.1])
[2] Sample Export and Import parameter files:

 -- expdp.par  ----


 DIRECTORY=dpump_dir3
 DUMPFILE=dpump_dir3:email%U_q2.dmp
 TABLES=TTQ_GMPX.TTQ_EMAIL_EVENT_DETAILS
 LOGFILE=dpump_dir3:TTQ_EMAIL_EVENT_DETAILS_q2.log
 #PARALLEL=20
 FILESIZE=5G
 JOB_NAME=exp_email_q2
 QUERY="WHERE email_seq_id >=2*200000000   and email_seq_id < 3*200000000"


  -- impdp.par ----

 DIRECTORY=dpump_dir
 DUMPFILE=dpump_dir:email%U_q2.dmp
 TABLES=TTQ_GMPX.TTQ_EMAIL_EVENT_DETAILS
 TABLE_EXISTS_ACTION=APPEND
 LOGFILE=dpump_dir:TTQ_EMAIL_EVENT_DETAILS_q2_imp.log
 PARALLEL=2
 JOB_NAME=imp_email_q2
 EXCLUDE=index,constraint,statistics
 #EXCLUDE=statistics
 transform=storage:n,oid:n
 CLUSTER=NO

[3] Bug 5599947 - Export Data Pump is slow when table has a LOB column - Defect: Bug:5599947 "DATAPUMP EXPORT VERY SLOW"

[4] http://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html

[5] Some other references:
Master Note for Data Pump [ID 1264715.1]
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) [ID 453895.1]
Parallel Capabilities of Oracle Data Pump [ID 365459.1]


Appendix - sample shell script to generate expdp/impdp par file for big lob table

#!/bin/ksh
i=21
while [[ $i -le 43 ]];do
 
  ((j=i+1))
  PARFILE="EMAIL_CONTENT_q$i.par"
  echo "DIRECTORY=dpump_dir3" > $PARFILE 
  echo "DUMPFILE=dpump_dir3:ecntnt_q${i}_%U.dmp" >> $PARFILE 
  echo "TABLES=TTQ_GMPX.EMAIL_CONTENT" >> $PARFILE
  echo "LOGFILE=dpump_dir3:EMAIL_CONTENT_q$i.log" >> $PARFILE 
  echo "FILESIZE=10G" >> $PARFILE
  echo "JOB_NAME=exp_ecntnt_q$i" >> $PARFILE
  echo "QUERY=\"WHERE email_seq_id > $i * 2000000  and email_seq_id <= $j * 2000000\"">> $PARFILE
 
 
  echo "i=$i"
  echo "j=$j"
  ((i=i+1))
done

--- sample script to generate impdp par file for big lob table -----------------

i=6
while [[ $i -le 43 ]];do
 
  ((j=i+1))
  PARFILE="EMAIL_CONTENT_imp_q$i.par"
  echo "DIRECTORY=dpump_dir" > $PARFILE 
  echo "DUMPFILE=ecntnt_q${i}_%U.dmp" >> $PARFILE 
  echo "TABLES=TTQ_GMPX.EMAIL_CONTENT" >> $PARFILE
  echo "TABLE_EXISTS_ACTION=APPEND" >> $PARFILE
  echo "LOGFILE=EMAIL_CONTENT_q${i}_imp.log" >> $PARFILE 
  echo "JOB_NAME=imp_ecntnt_q${i}" >> $PARFILE
  echo "#EXCLUDE=index,constraint,statistics" >> $PARFILE
  echo "EXCLUDE=statistics" >> $PARFILE 
  echo "transform=storage:n,oid:n" >> $PARFILE
  echo "CLUSTER=NO" >> $PARFILE
 
 
  echo "i=$i"
  echo "j=$j"
  ((i=i+1))
done

Find just one set of columns that uniquely identify a row

If a table has PK, we need to show the list of PK columns. If the table does not have PK, we need to see if we can find one set of columns that can uniquely identify the row. One of my colleagues wrote a PL/SQL script to look at dba_constraints to do that. But I have learned that tables could have unique indexes but don't have unique constraints; on the other hand, if we create a unique constraint, we will have a unique index. For the following test tables t1,t2,t3,t4,t5:

create table t1(c1 number, c2 number, c3 number);
alter table t1 add constraint t1_pk primary key (c1, c2);

create table t2(c1 number, c2 number, c3 number);
alter table t2 add constraint t2_uk unique (c1, c2);


create table t3(c1 number, c2 number, c3 number);
create unique index t3_ix on t3(c1,c2)

create table t4(c1 number, c2 number, c3 number);
create unique index t4_ix1 on t4(c1,c2);
create unique index t4_ix2 on t4(c1,c3);

create table t5(c1 number, c2 number, c3 number);

The original script will output something like:

T5 does not have PK or any unique cols
T4 does not have PK or any unique cols
T3 does not have PK or any unique cols
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

You can see that the statements about T3 and T4 are not correct, as they have unique indexes. Below is my modified script to look at dba_indexes in stead of dba_constraints.

-- List PK columns or unique columns
-- find PK columns and if no PK, find one of the unique columns
---


set serveroutput on

declare
cntpk number;
cntuk number;
initial number;
pk varchar2(1000);
uk varchar2(1000);

-- cursor tab_cur is  select upper(source_owner) source_owner,upper(source_object_name) source_object_name from dbscrub.config_table;

cursor tab_cur is  select upper(owner) source_owner,upper(table_name) source_object_name from dba_tables 
 where owner='Vxxxx' and table_name in ('T1','T2','T3','T4', 'T5');

begin

  for tab_rec in tab_cur
  loop

  pk :='';
  uk :='';
  initial := 1;

  select count(1) into cntpk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'P';
  -- select count(1) into cntuk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'U';
  select count(1) into cntuk from dba_indexes where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and uniqueness = 'UNIQUE';

  if cntpk != 0  then

    -- update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' has PK' );

    for i in (SELECT b.table_name, b.column_name, b.position
 FROM dba_constraints a, dba_cons_columns b
       WHERE     a.owner = b.owner
      AND a.constraint_name = b.constraint_name
      AND constraint_type = 'P'
       AND b.owner = tab_rec.source_owner
       AND b.table_name = tab_rec.source_object_name
       ORDER BY b.position)

    loop
    if initial=1 then
      pk:=i.column_name;
      initial:=initial+1;
    else
      pk:=pk||','||i.column_name;
    end if;
    end loop;

    -- update dbscrub.config_table set pk_uk_columns=pk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' PK cols : ' || pk );

  elsif cntpk = 0 and cntuk != 0 then

   --  update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK but has unique cols' );

    /*  -- seems not correct
  for i in (SELECT b.table_name, b.column_name, b.position
      FROM dba_constraints a, dba_cons_columns b
     WHERE     a.owner = b.owner
    AND a.constraint_name = b.constraint_name
    AND constraint_type = 'U'
     AND b.owner = tab_rec.source_owner
     AND b.table_name = tab_rec.source_object_name
       AND rownum = 1
     ORDER BY b.position)
   */
   -- replaced by :
   /*
   for i in ( 
   SELECT b.table_name, b.column_name, b.position
      FROM ( select * from  dba_constraints a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.constraint_type='U'
               and  rownum=1
           ) a,
           dba_cons_columns b
     WHERE     a.owner = b.owner
     AND       a.constraint_name = b.constraint_name
     ORDER BY b.position )
    */

   -- check dba_indexes and dba_ind_columns instead

  for i in (
  SELECT b.table_name, b.column_name, b.column_position
      FROM ( select * from  dba_indexes a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.UNIQUENESS='UNIQUE'
               and  rownum=1
           ) a,
           dba_ind_columns b
     WHERE     a.owner = b.index_owner
     AND       a.index_name = b.index_name
  ORDER BY b.column_position )
  
  loop
  if initial=1 then
  uk:=i.column_name;
  initial:=initial+1;
  else
  uk:=uk||','||i.column_name;
  end if;
  end loop;

  --  update dbscrub.config_table set pk_uk_columns=uk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' do not have PK but has unique cols such as ' || uk );

  else
   

   --    update dbscrub.config_table set has_pk_uk='NO' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;

    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK or any unique cols');

  end if;
  end loop;
  commit;
end;
/

The modified script's output looks like:

T5 does not have PK or any unique cols
T4 does not have PK but has unique cols
T4 does not have PK but has unique cols such as C1,C2
T3 does not have PK but has unique cols
T3 does not have PK but has unique cols such as C1,C2
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

BTW, the need for this script comes from our tasks of configuring Oracle GoldenGate.

My Oracle Database Troubleshooting Scripts

Whenever I troubleshoot Oracle production database performance issues or just need to do a health check, I will start with a script called event.sql, then I will most likely execute several other scripts from the SQL* Plus command line interface. In the above picture, I grouped my often-used scripts into four categories: Wait Events, Sessions, SQLs and Workload. After executing  event.sql, the second script I probably will use is among those green ones; and the third probably from those among blue ones. The methodology  I adopted is rooted in wait event analysis. All the scripts mentioned are in this zip file. Those scripts are intended to be used for pinpointing or narrowing down the problem area in the first 5 or 10 minnutes of troubleshooting production database issues, which often have a sense of urgency and require the solutions to stablize the system in short time.

In the following  I will give short descriptions of the purpose of each script. Notice in a previous post I already explained about a few scripts.

• Wait Events

event.sql  - first script to execute usually; giving the count of each wait event; a quick way to show if there are  any abnormalities; in a typical OLTP type database, we shall see 'db file sequential read' as the most counted event after idle events. Tanel Poder shared the thoughts about first round session troubleshooting here.

eventashg.sql - show top 5 wait events for a given interval from gv$active_session_history; kind of like AWR top 5 wait events section; RAC -aware makes it probably the first script I will use to check RAC database health.

sw.sql - from Tanel Poder;  given SID, show current wait event of a session.

snapper.sql - from Tanel Poder; very famous, check this link! In the SQL Server world, there is something similar.  (snapper_dflt.sql in the zip file is  a wrapper for convenience by me).

• Workload

logsw.sql - Display the number of log switch in every hour in a tabular format. Very useful to understand the workload distribution. From Jeff Hunter.

sysmetric.sql - Display some system metrics from gv$system_metric_history such as Redo Generated Per Sec, Host CPU Utilization (%) and User Transaction Per Sec etc in the past 60 minutes. RAC-aware makes it the 1st or 2nd script I use to check RAC database.

aas_ash.sql and aas_awr.sql - Display average active sessions from ASH view and AWR, respectively. AAS is an indicator for workload or performance changes.

• Sessions

sesevt.sql - Given wait event name, show the sessions basic information.

qlocks.sql - Display blockers and waiters based on v$lock view.

longsql.sql - Display long running SQLs; A quick way to find candidate "bad" sqls in the database.

longops.sql - Display long operations from v$session_longops.

pxses.sql - Display  parallel execution server sessions.

snapper.sql - Yes again! It is really about sessions statistics and wait events.

sessid.sql  - Given session SID, display the session related information.

ses*.sql - All those are querying v$session given some inputs such as: machine, server process id, OS user,  database user and module etc.

sess_kill_batch.sql - Generate kill database sessions commands.

sess_kill_os.sql  - Generate 'kill -9' command for killing server processes at OS level.

• SQLs

 xplan.sql - Given sql_id, show the execution plan from cursor through dbms_xplan.display_cursor().

sqlhistory.sql - by Tim Gorman; query the "history" of a specified SQLstatement, using its "SQL ID" across all database instances in a database, using the AWR repository. Show execution statistics per execution plan.

tabix.sql - List of the indexes of a table and show on which columns and in which order the indexes are.Very usefull when tune a SQL.

tabcols.sql - Display table column CBO statistics. Very useful when doing SQL tunning. (from: http://www.roughsea.com ).

bindvar.sql - When tunning a SQL, I often need to find representative bind values from this script.

get_ddl.sql - based on dbms_metadata package to obtain definitions of objects. When tuning a SQL, sometimes we want to know the underline table structure and index definitions

Below are the screenshots of  the output of several scripts:

Additional cool scripts -  Collection of DBA Scripts resources on the web:

http://tech.e2sn.com/oracle-scripts-and-tools (Tanel Poder)

http://blog.enkitec.com/scripts/

http://evdbt.com/scripts/ (Tim Gorman)

http://www.idevelopment.info/  (Jeffery M. Hunter)

http://karlarao.wordpress.com/scripts-resources/

http://www.oracle-base.com/dba/scripts.php