Outer Join, Ordered hint and SWAP_JOIN_INPUTS

Outer joins access tables in following order:

from t1, t2 where t1.col1(+)=t2.col1 –> table will be accessed in order t2, t1

from t1, t2 where t1.col1=t2.col1(+) –> table will be accessed in order t1,t2

One of my developer had to do some data cleanup so he quickly staged data in table t1, t2 was our large fact table and ran following query (test case). Query ran for hours on Exadata and did not finish.

select /*+ ordered full(t1) full(t2) parallel(t1,4) parallel(t2,4) */ t1.*, t2.* from t1,t2 where t1.col1(+)=t2.col1;

When I looked at SQL monitoring report driving table was t2 (large fact table). Below is the output from test case:

SQL> create table t1(c1, c2)
  2  as
  3  select level, rpad('x',10)
  4  from dual
  5  connect by level <= 200000
  6  ;

Table created.

SQL>
SQL> create table t2(c1, c2)
  2  as
  3  select level, rpad('x',10)
  4  from dual
  5  connect by level <= 100
  6  ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T2');

PL/SQL procedure successfully completed.


SQL> explain plan for
  2  select /*+ ordered */ t2.*,t1.*
  3  from t2, t1
  4  where t1.c1 = t2.c1(+);

Explained.

SQL>
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1823443478

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   200K|  5859K|       |   436   (1)| 00:00:06 |
|*  1 |  HASH JOIN OUTER           |      |   200K|  5859K|  5472K|   436   (1)| 00:00:06 |
|   2 |   TABLE ACCESS STORAGE FULL| T1   |   200K|  3125K|       |   165   (1)| 00:00:02 |
|   3 |   TABLE ACCESS STORAGE FULL| T2   |   100 |  1400 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1"(+))

22 rows selected.

SQL> explain plan for
  2  select t2.*,t1.*
  3  from t2, t1
  4  where t1.c1 = t2.c1(+);

Explained.

SQL>
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 312430291

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   200K|  5859K|   169   (2)| 00:00:03 |
|*  1 |  HASH JOIN RIGHT OUTER     |      |   200K|  5859K|   169   (2)| 00:00:03 |
|   2 |   TABLE ACCESS STORAGE FULL| T2   |   100 |  1400 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T1   |   200K|  3125K|   165   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1"(+))

When ORDERED hint was added it used t1 (large table as driving table) with hash join outer and when ORDERED hint was NOT used it used t2 (small table as driving table) with hash join right outer.

Output on left is without ORDERED hint and on right is with ORDERED hint.

 

So based on above output looks like that when ORDERED hint was used with OUTER JOIN, Oracle optimizer does not go through join permutations (in this particular case swapping join inputs) and hence choose T1 (larger table) as driving table. When ORDERED hint was removed, Oracle optimizer computes cost of swapping join inputs and choose T2 (smaller table) as driving table. Why Oracle considered join order as t1, t2 with ordered hint? I think because of outer join.

For my production issue, I asked ETL/development team to remove ORDERED hint and query ran in 12 minutes.

Monitoring Long Running Query using gv$sql_monitor

To proactively monitor long running queries, metric extensions can be created in Oracle Enterprise Manager 12c. I have created metric extension on Exadata platform but it can also be used on non Exadata platform.

Below are steps to create metric extension in Oracle Enterprise Manager 12c.

1. Navigate to Enterprise -> Monitoring -> Metric Extensions

2. Below Metric Extension click on Create

3. General properties tab: Select Target Type as Cluster Database, Specify name and display name, select Adapter as SQL and choose appropriate frequency based on your requirement.

4. Adapter: SQL Query select key,sql_id,username,sql_id,elapsed_time  from gV$SQL_MONITOR where status=’EXECUTING’ and username is not null

Columns: Add following as Name, Display Name, Column Name, Value Type, Comparison Operator, Warning & Critical
key – Key – Key Column – Number
sql_id – sql_id – Data Column – String
username – username – Data Column – String
sql_id – sql_id – Data Column – String
elapsed_time – elapsed_time – Data Column – Number – > 900000000 – 3600000000

Elapsed_time is in micro seconds so choose appropriate values according to your environment. You can choose lower values for aggressive monitoring.

5. Credentials: Database Credentials Use Default Monitoring Credentials.

6. Test Targets: Here you can add couple of targets and test whether Metric Extension is working or not.

7. Review: You can review selections you made on previous screens.

8. Finish: Click on Finish to create Metric Extension.

9. Once the Metric Extension is created, click on action tab and convert template to deployable draft. Again choose Metric Extension and choose Deploy to target.

10. Provided you have setup email/pager notification in your Oracle Enterprise Manager 12c, you will get notified on long running queries.

Example: I got notification for one sql_id that my adhoc user was running on Exadata platform. As you can see from below there was space missing after full hint and query was running in serial mode with smart scan feature turned off. I notified user and he fixed query hint and query ran in 4 minutes instead of 90 plus minutes.

SELECT /*+ parallel(r,16),full(r)*/  MIN(from_dos),MAX(from_dos) 
FROM CSUSER.REPOS_CLAIM_NEW r 
WHERE  r.proc_type ='C'   
AND r.CHARge > 0.99 
AND substr(r.error_flag, 17, 1) IN ('H', 'L', ' ')     
AND nvl(trim(mdr_flag),'~') IN('~','D','C') 
AND substr(error_flag,9,1) IN (' ','O') 

PLAN hash value: 3370285159 

--------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                           |       |       |   983K(100)|          |       |       | 
|   1 |  SORT AGGREGATE                     |                           |     1 |    83 |            |          |       |       | 
|   2 |   PARTITION RANGE ALL               |                           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| REPOS_CLAIM_NEW           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|   4 |     BITMAP CONVERSION TO ROWIDS     |                           |       |       |            |          |       |       | 
|   5 |      BITMAP AND                     |                           |       |       |            |          |       |       | 
|   6 |       BITMAP OR                     |                           |       |       |            |          |       |       | 
|*  7 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  8 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  9 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|* 10 |       BITMAP INDEX SINGLE VALUE     | REPOS_CLAIM_PROC_TYPE_IDX |       |       |            |          |     1 |   119 | 
--------------------------------------------------------------------------------------------------------------------------------- 

Blocking Lock script

Tool

I just learned from AOT training that v$session is a gold mine and has lot of information about blocking session and final blocking session. So it was time for me to modify my old blocking lock script.

If the script is run on 10g it will only display blocking session. If it is run on 11g it will display blocking and final blocking session.

Sample Output

                                                                                          final
                                                          blocking        blocking        blocking        final
                                                          instance        lockholder      instance        lockholder
Inst                           P2              P3         sid             name            sid             name
  Id  SID SERIAL# P1           ID1             ID2        status          mode            status          mode
---- ---- ------- ------------ --------------- ---------- --------------- --------------- --------------- ---------------
   1    9    6001 Name=TX mode usn<<16 | slot= sequence=3 1 10 VALID      Name=TX mode=6  1 591 VALID
                  =6 X         1245251         60                         X id1=1245251 i
                                                                          d2=360

   1   10    4925 Name=TX mode usn<<16 | slot= sequence=3 1 591 VALID     Name=TX mode=6  1 591 VALID     Name=TX mode=6
                  =6 X         1310780         56                         X id1=1310780 i                 X id1=1310780 i
                                                                          d2=356                          d2=356

SQL> @bl

                                                                                          final
                                                          blocking        blocking        blocking        final
                                                          instance        lockholder      instance        lockholder
Inst                           P2              P3         sid             name            sid             name
  Id  SID SERIAL# P1           ID1             ID2        status          mode            status          mode
---- ---- ------- ------------ --------------- ---------- --------------- --------------- --------------- ---------------
   1   10    4925 Name=TM mode object #=77657  table/part 1 591 VALID     Name=TM mode=3  1 591 VALID     Name=TM mode=3
                  =4 share                     ition=0                    row-X id1=77657                 row-X id1=77657
                                                                           id2=0                           id2=0

Download script

Oracle iostat

Tool:

I have written a simple script to display io statistics from database. This script will help in measuring how much IO/MBPS is reaching to Oracle database. It can also help to troubleshoot if there is queuing happening somewhere. For example if SAN is delivering 500 MBPS throughput but if DBA’s are seeing only 350 MBPS on database then there is possibility of queuing happening somewhere at hardware/software level like volumes, file systems etc.

In Solaris/Linux nicstat can be used to see total bytes sent/received via hba/network card. In AIX similar statistics can be viewed using nmon (-n flag).

iostat.sql displays current io statistics
awr_iostat.sql display historical io statistics based on AWR.

RD – Read
WR – Write
IOPS – IO operations per second
1BLK – single block read
MBRC – multi block read count
MBPS – Megabytes per second (throughput)
Cac – Cache reads
DR – Direct
RDTM – Read in temporary tablespace
WRTM – Write in temporary tablespace

Example:

SQL> @iostat 10 10
|--Tot IOPS----|-APPL IOPS----|------TYPE---------|--Total MBPS--|--APPL MBPS---|---------Type MBPS-----------|
--------------------------------------------------------------------------------------------------------------|
|RD  |WR  |Tot |RD  |WR  |Tot |RD  |RD  |WR  |WR  |RD  |WR  |Tot |RD  |WR  |Tot |Cac |DR  |DR  |Cac |DR  |DR  |
|IOPS|IOPS|IOPS|IOPS|IOPS|IOPS|1BLK|MBRC|1BLK|MBRC|MBPS|MBPS|MBPS|MBPS|MBPS|MBPS|RD  |RD  |RDTM|WR  |WR  |WRTM|
--------------------------------------------------------------------------------------------------------------|
|2656|   1|2657|2656|   0|2656|   0|2656|   1|   0| 125|   0| 125| 125|   0| 125|   0|   0| 125|   0|   0|   0|
|2633|   1|2634|2633|   0|2633|   0|2633|   1|   0| 123|   0| 123| 123|   0| 123|   0|   0| 123|   0|   0|   0|
|2643|   1|2644|2643|   0|2643|   0|2643|   1|   0| 124|   0| 124| 124|   0| 124|   0|   0| 124|   0|   0|   0|
|2647|   1|2648|2647|   0|2647|   0|2647|   1|   0| 124|   0| 124| 124|   0| 124|   0|   0| 124|   0|   0|   0|
|2572|   1|2573|2572|   0|2572|   0|2572|   1|   0| 121|   0| 121| 121|   0| 121|   0|   0| 121|   0|   0|   0|
|2600|   1|2601|2600|   0|2600|   0|2600|   1|   0| 122|   0| 122| 122|   0| 122|   0|   0| 122|   0|   0|   0|
|2697|   1|2698|2697|   0|2697|   0|2697|   1|   0| 126|   0| 126| 126|   0| 126|   0|   0| 126|   0|   0|   0|
|2439|   1|2440|2439|   0|2439|   0|2439|   1|   0| 114|   0| 114| 114|   0| 114|   0|   0| 114|   0|   0|   0|
|2338|   1|2339|2338|   0|2338|   0|2338|   1|   0| 110|   0| 110| 110|   0| 110|   0|   0| 110|   0|   0|   0|
|2181|   1|2182|2181|   0|2181|   0|2181|   1|   0| 102|   0| 102| 102|   0| 102|   0|   0| 102|   0|   0|   0| 

Output from nmon on AIX:

I/F Name Recv=KB/s Trans=KB/s
     en8  114489.4      835.0

SQL> @awr_iostat.sql
Please enter start_date(mm/dd/yy)    :01/05/11
Please enter end_date  (mm/dd/yy)    :01/06/11
old   2: where begin_interval_time>=to_date('&start_date','mm/dd/yy')
new   2: where begin_interval_time>=to_date('01/05/11','mm/dd/yy')
old   3: and   begin_interval_time<=to_date('&end_date','mm/dd/yy')+1
new   3: and   begin_interval_time<=to_date('01/06/11','mm/dd/yy')+1

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
      1773 05-JAN-11 12.00.31.153 AM
      1774 05-JAN-11 01.00.27.309 AM
      1775 05-JAN-11 02.00.29.137 AM
      1776 05-JAN-11 03.00.27.934 AM
      1777 05-JAN-11 04.00.33.089 AM
      1778 05-JAN-11 05.00.37.860 AM
      1779 05-JAN-11 06.00.36.449 AM
      1780 05-JAN-11 07.00.39.974 AM
      1781 05-JAN-11 08.00.40.605 AM
      1782 05-JAN-11 09.00.32.146 AM
      1783 05-JAN-11 10.00.29.752 AM
      1784 05-JAN-11 11.00.24.355 AM
      1785 05-JAN-11 12.00.21.982 PM
      1786 05-JAN-11 01.00.28.256 PM
      1787 05-JAN-11 02.00.40.490 PM
      1788 05-JAN-11 03.00.52.797 PM
      1789 05-JAN-11 04.00.08.024 PM
      1790 05-JAN-11 05.00.17.315 PM
      1791 05-JAN-11 06.00.29.626 PM
      1792 05-JAN-11 07.00.41.831 PM
      1793 05-JAN-11 08.00.57.045 PM
      1794 05-JAN-11 09.00.06.255 PM
      1795 05-JAN-11 10.00.24.512 PM
      1796 05-JAN-11 11.00.42.825 PM
      1797 06-JAN-11 12.01.04.130 AM
      1798 06-JAN-11 01.00.16.404 AM
      1799 06-JAN-11 02.00.34.637 AM
      1800 06-JAN-11 03.00.46.922 AM
      1801 06-JAN-11 04.01.05.180 AM
      1802 06-JAN-11 05.00.20.395 AM
      1803 06-JAN-11 06.00.35.606 AM
      1804 06-JAN-11 07.00.50.808 AM
      1805 06-JAN-11 08.00.03.012 AM
      1806 06-JAN-11 09.00.21.245 AM

34 rows selected.

Enter value for start snap_id   :1773
Enter value for end snap_id     :1806

PL/SQL procedure successfully completed.

-Timestamp-|--Tot IOPS-------|-APPL IOPS-------|------TYPE-------------|--Total MBPS--|--APPL MBPS---|---------Type MBPS----
-----------|----------------------------------------------------------------------------------------------------------------
-----------|RD   |WR   |Tot  |RD   |WR   |Tot  |RD   |RD   |WR   |WR   |RD  |WR  |Tot |RD  |WR  |Tot |Cac |DR  |DR  |Cac |DR
-----------|IOPS |IOPS |IOPS |IOPS |IOPS |IOPS |1BLK |MBRC |1BLK |MBRC |MBPS|MBPS|MBPS|MBPS|MBPS|MBPS|RD  |RD  |RDTM|WR  |WR
----------------------------------------------------------------------------------------------------------------------------

01/05 01:00| 2776| 1828| 4604| 2774| 1828| 4602| 1381| 1395|    1| 1827| 103|  85| 188| 103|  85| 188|  18|   0|  85|   0|
01/05 02:00| 2654| 1749| 4403| 2652| 1748| 4400| 1320| 1334|    1| 1748|  98|  81| 179|  98|  81| 179|  17|   0|  81|   0|
01/05 03:00| 2666| 1790| 4456| 2664| 1789| 4453| 1322| 1345|    1| 1789| 101|  83| 184| 101|  83| 184|  18|   0|  83|   0|
01/05 04:00| 2609| 1768| 4377| 2607| 1767| 4374| 1297| 1313|    1| 1766|  97|  82| 179|  97|  82| 179|  15|   0|  82|   0|
01/05 05:00| 4280|  730| 5010| 4277|  730| 5007|  969| 3310|    1|  729| 175|  34| 209| 175|  34| 209|   7|   0| 168|   0|
01/05 06:00| 2933|  163| 3096| 2924|  126| 3050|  383| 2551|    6|  155| 125|  57| 182| 124|  29| 153|   0|   0| 124|   0|
01/05 07:00| 3620|  119| 3739| 3613|   92| 3705|  468| 3152|    5|  112| 154|  41| 195| 154|  21| 175|   0|   0| 154|   0|
01/05 08:00| 2363|  640| 3003| 2355|  612| 2967|  320| 2043|   10|  630| 120|  71| 191| 120|  47| 167|   0|  22|  97|   0|
01/05 09:00|  250| 1205| 1455|  246| 1197| 1443|    4|  246|    2| 1203|  57|  68| 125|  57|  61| 118|   0|  51|   6|   0|
01/05 10:00| 1031|  295| 1326| 1022|  231| 1253|    9| 1022|    7|  272|  48|  77| 125|  48|  50|  98|   0|   0|  48|   0|
01/05 11:00|  622|  802| 1424|  620|  801| 1421|   25|  597|    8|  793|  56|  58| 114|  56|  58| 114|   0|  31|  25|   0|
01/05 12:00| 1368| 1190| 2558| 1366| 1189| 2555|    3| 1365|    1| 1189| 110|  56| 166| 110|  56| 166|   0|  51|  59|   0|
01/05 13:00| 4038|    2| 4040| 4036|    1| 4037|    3| 4036|    1|    1| 189|   0| 189| 189|   0| 189|   0|   0| 189|   0|
01/05 14:00| 4037|    2| 4039| 4034|    1| 4035|    6| 4031|    1|    1| 189|   0| 189| 189|   0| 189|   0|   0| 189|   0|
01/05 15:00| 3969|   43| 4012| 3967|   15| 3982|    3| 3967|    1|   28| 186|   0| 186| 186|   0| 186|   0|   0| 186|   0|
01/05 16:00| 3698|   19| 3717| 3695|   18| 3713|    3| 3695|    1|   18| 237|   1| 238| 237|   1| 238|   0|  71| 166|   0|
01/05 17:00| 3246|    4| 3250| 3244|    3| 3247|    3| 3244|    1|    3| 160|   0| 160| 160|   0| 160|   0|   9| 151|   0|
01/05 18:00| 3450|    1| 3451| 3447|    0| 3447|    3| 3447|    1|    0| 162|   0| 162| 162|   0| 162|   0|   0| 162|   0|
01/05 19:00| 2641|    1| 2642| 2638|    0| 2638|    3| 2638|    1|    0| 124|   0| 124| 124|   0| 124|   0|   0| 124|   0|
01/05 20:00| 3000|    1| 3001| 2998|    0| 2998|    2| 2998|    1|    0| 141|   0| 141| 141|   0| 141|   0|   0| 141|   0|
01/05 21:00| 3625|    1| 3626| 3622|    1| 3623|    4| 3621|    1|    1| 170|   0| 170| 170|   0| 170|   0|   0| 170|   0|
01/05 22:00| 3609|    5| 3614| 3607|    4| 3611|   62| 3547|    2|    3| 174|   0| 174| 174|   0| 174|   9|   0| 165|   0|
01/05 23:00| 3550|    2| 3552| 3548|    1| 3549|   23| 3527|    1|    1| 175|   0| 175| 175|   0| 175|  11|   0| 164|   0|
01/06 00:01| 3287|    4| 3291| 3285|    3| 3288|   24| 3263|    1|    3| 167|   0| 167| 167|   0| 167|  16|   0| 151|   0|
01/06 01:00| 3556|    3| 3559| 3554|    2| 3556|   28| 3529|    1|    2| 179|   0| 179| 179|   0| 179|  15|   0| 164|   0|
01/06 02:00| 3623|    2| 3625| 3621|    1| 3622|   18| 3606|    1|    1| 181|   0| 181| 181|   0| 181|  13|   0| 168|   0|
01/06 03:00| 3709|    3| 3712| 3707|    2| 3709|   25| 3685|    1|    2| 179|   0| 179| 179|   0| 179|   7|   0| 172|   0|
01/06 04:01| 3350|    3| 3353| 3348|    2| 3350|   20| 3331|    1|    2| 172|   0| 172| 172|   0| 172|  18|   0| 154|   0|
01/06 05:00| 3710|    3| 3713| 3707|    3| 3710|   18| 3692|    1|    2| 188|   0| 188| 188|   0| 188|  17|   0| 171|   0|
01/06 06:00| 3898|    1| 3899| 3896|    0| 3896|    3| 3895|    1|    0| 183|   0| 183| 183|   0| 183|   0|   0| 183|   0|
01/06 07:00| 3702|    1| 3703| 3699|    1| 3700|    3| 3699|    1|    1| 174|   0| 174| 174|   0| 174|   0|   0| 173|   0|
01/06 08:00| 3611|    1| 3612| 3609|    0| 3609|    3| 3609|    1|    0| 169|   0| 169| 169|   0| 169|   0|   0| 169|   0|
01/06 09:00| 2681|    1| 2682| 2678|    0| 2678|    2| 2678|    1|    0| 126|   0| 126| 126|   0| 126|   0|   0| 126|   0|
SQL>

Download scripts

Performance troubleshooting after 11g upgrade

Problem

One of our customer started facing lot of performance issues with data loads after upgrading database to 11.2.0.1.

It was one of the classic performance issues and customer complained that it was working fine in past and after 11g upgrade it is slow.

Troubleshooting

Let’s start by troubleshooting data loads as it was lagging by 30 days since database was upgraded to 11g.

Code:

INSERT
  /*+ PARALLEL (d, 8 ) APPEND NOLOGGING */
INTO HAJI.OPPS_PART_A_DATA D
  (
    HOSP_ID,
    RUN_DT,
    LINK_NUM,
    HSE_CLM_THRU_DT,
    HSE_CLM_STUS_CD,
    FINL_ACTN_SW,
    BENE_DOB,
    BENE_AGE,
    DGNS_CD,
    SQNC_NUM,
    HCPCS_CD,
    HSE_CLM_FROM_DT
  )
SELECT FA.PROVDR_NUM     AS HOSP_ID,
  RCC.RUN_DT             AS RUN_DT,
  RCC.PTA_LINK_NUM       AS LINK_NUM,
  RCC.CLM_THRU_DT        AS HSE_CLM_THRU_DT,
  DE.PTNT_DSCHRG_STUS_CD AS HSE_CLM_STUS_CD,
  'Y'                    AS FINL_ACTN_SW,
  A.BENE_DOB,
  A.BENE_AGE,
  NULL        AS DGNS_CD,
  NULL        AS SQNC_NUM,
  HB.HCPCS_CD AS HCPCS_CD,
  FA.CLM_FROM_DT
FROM PART_A.PART_A_RCC_FACT RCC,
  PART_A.PART_A_FACT FA,
  PART_B.HCPCS_DIMENSION HB,
  PART_A.PART_A_DEMOG_DIMENSION DE,
  HAJI.OPPS_HAJI_CLM_A_5 A,
  PART_A.PTA_BENE_AT_TIME_CLM B
WHERE RCC.HCPCS_ID     = HB.HCPCS_ID
AND RCC.RUN_DT         = FA.RUN_DT
AND RCC.PTA_LINK_NUM   = FA.PTA_LINK_NUM
AND FA.PART_A_DEMOG_ID = DE.PART_A_DEMOG_ID
AND FA.NOT_FINAL_ID    = 1
AND FA.CLM_FROM_DT BETWEEN :B2 AND :B1
AND HB.HCPCS_CD               IS NOT NULL
AND RCC.RUN_DT                 = A.RUN_DT
AND RCC.PTA_LINK_NUM           = A.LINK_NUM
AND FA.PTA_BENE_AT_TIME_CLM_ID = B.PTA_BENE_AT_TIME_CLM_ID
AND B.BENE_CLM_NUM             = A.BENE_CLM_NUM

One obvious thing is that parallel hint in SQL is missing but lets look at AWR statistics.

SQL> @awr_plan_stats.sql
Enter value for sql_id: dxh8ua3m4r1cm
old  19: where s.sql_id = '&&sql_id'
new  19: where s.sql_id = 'dxh8ua3m4r1cm'

SQL_ID        PLAN_HASH_VALUE TIMESTAMP        COST        EXECS          ETIME    AVG_ETIME AVG_CPU_TIME            AVG_LIO          AVG_PIO
------------- --------------- ---------- ---------- ------------ -------------- ------------ ------------ ------------------ ----------------
dxh8ua3m4r1cm      3805054770 05/20/2010    1856142            7      797,194.0  113,885.000   94,881.000      765,562,270.0    155,392,524.0
dxh8ua3m4r1cm       186961658 08/26/2010   23659798            1      230,488.0  230,488.000  156,687.000    1,838,600,713.0    489,677,147.0
dxh8ua3m4r1cm      3349766997 10/16/2010    6857347           -1      926,089.0  926,089.000  685,247.000    5,789,878,135.0    294,945,596.0
dxh8ua3m4r1cm      2612482448 10/21/2010    2012203           -1      117,218.0  117,218.000   30,378.000      502,440,465.0    129,779,745.0

old   2: where sql_id='&&sql_id' order by timestamp
new   2: where sql_id='dxh8ua3m4r1cm' order by timestamp

SQL_ID        PLAN_HASH_VALUE TIMESTAMP
------------- --------------- ----------
dxh8ua3m4r1cm      3805054770 05/20/2010
dxh8ua3m4r1cm       186961658 08/26/2010
dxh8ua3m4r1cm      3349766997 10/16/2010
dxh8ua3m4r1cm      2612482448 10/21/2010

7 rows selected.

SQL> @getplan_awr
Please enter the sql_id                      :dxh8ua3m4r1cm
Please enter the plan_hash_value             :3349766997
Please enter the format typical or advanced  :typical
old   1: SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id','&plan_hash_value',NULL,'&format1'))
new   1: SELECT * FROM TABLE(dbms_xplan.display_awr('dxh8ua3m4r1cm','3349766997',NULL,'typical'))
..
Plan hash value: 3349766997
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                           |                         |       |       |       |  6857K(100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                            |                         |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                      | :TQ10001                |       |       |       |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT                          |                         |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   4 |     FILTER                                 |                         |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   5 |      NESTED LOOPS                          |                         |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       NESTED LOOPS                         |                         |     1 |   152 |       |  6857K  (1)| 26:40:03 |       |       |  Q1,01 | PCWP |            |
|   7 |        NESTED LOOPS                        |                         |     1 |   139 |       |  6857K  (1)| 26:40:03 |       |       |  Q1,01 | PCWP |            |
|   8 |         NESTED LOOPS                       |                         |     1 |   132 |       |  6857K  (1)| 26:40:03 |       |       |  Q1,01 | PCWP |            |
|   9 |          BUFFER SORT                       |                         |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|  10 |           PX RECEIVE                       |                         |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|  11 |            PX SEND BROADCAST               | :TQ10000                |       |       |       |            |          |       |       |        | S->P | BROADCAST  |
|  12 |             HASH JOIN                      |                         | 38773 |  3900K|  5840K|  2060K  (3)| 08:00:53 |       |       |        |      |            |
|  13 |              NESTED LOOPS                  |                         |       |       |       |            |          |       |       |        |      |            |
|  14 |               NESTED LOOPS                 |                         | 78486 |  4905K|       |  1925K  (3)| 07:29:19 |       |       |        |      |            |
|  15 |                PARTITION RANGE ALL         |                         | 74063 |  3254K|       |  1881K  (3)| 07:18:57 |     1 |   105 |        |      |            |
|  16 |                 PARTITION LIST ALL         |                         | 74063 |  3254K|       |  1881K  (3)| 07:18:57 |     1 |  LAST |        |      |            |
|  17 |                  TABLE ACCESS FULL         | PART_A_FACT             | 74063 |  3254K|       |  1881K  (3)| 07:18:57 |     1 | 14999 |        |      |            |
|  18 |                INDEX UNIQUE SCAN           | PK_PTA_BENE_AT_TIME     |     1 |       |       |     1   (0)| 00:00:01 |       |       |        |      |            |
|  19 |               TABLE ACCESS BY INDEX ROWID  | PTA_BENE_AT_TIME_CLM    |     1 |    19 |       |     1   (0)| 00:00:01 |       |       |        |      |            |
|  20 |              TABLE ACCESS FULL             | OPPS_HAJI_CLM_A_5       |    38M|  1428M|       | 54822   (2)| 00:12:48 |       |       |        |      |            |
|  21 |          PX PARTITION LIST AND             |                         |     1 |    29 |       |   124   (0)| 00:00:02 |KEY(AP)|KEY(AP)|  Q1,01 | PCWC |            |
|  22 |           TABLE ACCESS BY LOCAL INDEX ROWID| PART_A_RCC_FACT         |     1 |    29 |       |   124   (0)| 00:00:02 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  23 |            INDEX RANGE SCAN                | IDX_PARCCF_PTA_LINK_NUM |     1 |       |       |   123   (0)| 00:00:02 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  24 |         TABLE ACCESS BY INDEX ROWID        | PART_A_DEMOG_DIMENSION  |     1 |     7 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  25 |          INDEX UNIQUE SCAN                 | PK_PADD_PTA_DEMOG_ID    |     1 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  26 |        INDEX UNIQUE SCAN                   | PK_HCPCSD_ID            |     1 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  27 |       TABLE ACCESS BY INDEX ROWID          | HCPCS_DIMENSION         |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=6)

59 rows selected.
SQL> 

61 rows selected.

SQL> @getplan_awr
Please enter the sql_id                      :dxh8ua3m4r1cm
Please enter the plan_hash_value             :3805054770
Please enter the format typical or advanced  :typical
old   1: SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id','&plan_hash_value',NULL,'&format1'))
new   1: SELECT * FROM TABLE(dbms_xplan.display_awr('dxh8ua3m4r1cm','3805054770',NULL,'typical'))
...
Plan hash value: 3805054770

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                              |                        |       |       |  1856K(100)|          |       |       |        |      |            |
|   1 |  LOAD AS SELECT                               |                        |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                              |                        |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                        | :TQ10003               |     1 |   151 |  1856K (13)| 07:13:06 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     FILTER                                    |                        |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|   5 |      NESTED LOOPS                             |                        |     1 |   151 |  1856K (13)| 07:13:06 |       |       |  Q1,03 | PCWP |            |
|   6 |       NESTED LOOPS                            |                        |     1 |   139 |  1856K (13)| 07:13:06 |       |       |  Q1,03 | PCWP |            |
|   7 |        NESTED LOOPS                           |                        |     1 |   132 |  1856K (13)| 07:13:06 |       |       |  Q1,03 | PCWP |            |
|   8 |         HASH JOIN                             |                        |  5197 |   573K|  1853K (13)| 07:12:23 |       |       |  Q1,03 | PCWP |            |
|   9 |          HASH JOIN                            |                        |   716K|    50M|  1811K (14)| 07:02:36 |       |       |  Q1,03 | PCWP |            |
|  10 |           PX RECEIVE                          |                        |   636K|    27M| 77813   (1)| 00:18:10 |       |       |  Q1,03 | PCWP |            |
|  11 |            PX SEND HASH                       | :TQ10002               |   636K|    27M| 77813   (1)| 00:18:10 |       |       |  Q1,02 | P->P | HASH       |
|  12 |             PX PARTITION LIST ALL             |                        |   636K|    27M| 77813   (1)| 00:18:10 |     1 |  LAST |  Q1,02 | PCWC |            |
|  13 |              TABLE ACCESS BY LOCAL INDEX ROWID| PART_A_FACT            |   636K|    27M| 77813   (1)| 00:18:10 |     1 | 11877 |  Q1,02 | PCWP |            |
|  14 |               INDEX RANGE SCAN                | IDX_PAF_CLM_FROM_DT    |   770K|       |   438   (3)| 00:00:07 |     1 | 11877 |  Q1,02 | PCWP |            |
|  15 |           BUFFER SORT                         |                        |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  16 |            PX RECEIVE                         |                        |  4010M|   108G|  1684K (12)| 06:33:00 |       |       |  Q1,03 | PCWP |            |
|  17 |             PX SEND HASH                      | :TQ10000               |  4010M|   108G|  1684K (12)| 06:33:00 |       |       |        | S->P | HASH       |
|  18 |              PARTITION RANGE ALL              |                        |  4010M|   108G|  1684K (12)| 06:33:00 |     1 |   100 |        |      |            |
|  19 |               PARTITION LIST ALL              |                        |  4010M|   108G|  1684K (12)| 06:33:00 |     1 |  LAST |        |      |            |
|  20 |                TABLE ACCESS FULL              | PART_A_RCC_FACT        |  4010M|   108G|  1684K (12)| 06:33:00 |     1 |  2821 |        |      |            |
|  21 |          BUFFER SORT                          |                        |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  22 |           PX RECEIVE                          |                        |    25M|   961M| 41561   (3)| 00:09:42 |       |       |  Q1,03 | PCWP |            |
|  23 |            PX SEND HASH                       | :TQ10001               |    25M|   961M| 41561   (3)| 00:09:42 |       |       |        | S->P | HASH       |
|  24 |             TABLE ACCESS FULL                 | OPPS_HAJI_CLM_A_5      |    25M|   961M| 41561   (3)| 00:09:42 |       |       |        |      |            |
|  25 |         TABLE ACCESS BY INDEX ROWID           | PTA_BENE_AT_TIME_CLM   |     1 |    19 |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  26 |          INDEX UNIQUE SCAN                    | PK_PTA_BENE_AT_TIME    |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  27 |        TABLE ACCESS BY INDEX ROWID            | PART_A_DEMOG_DIMENSION |     1 |     7 |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  28 |         INDEX UNIQUE SCAN                     | PK_PADD_PTA_DEMOG_ID   |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  29 |       TABLE ACCESS BY INDEX ROWID             | HCPCS_DIMENSION        |     1 |    12 |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  30 |        INDEX UNIQUE SCAN                      | PK_HCPCSD_ID           |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

58 rows selected.

EXEC = -1 means DATA LOAD was started but killed as it was taking more time. As you can see from above window that load took 113,885 seconds compared to that of 926,089 and 117,218seconds and was eventually stopped. I have modified Kerry Osborne’s script awr_plan_stats.sql to show timestamp. ETL team confimred that load ran in acceptable timeframe on 05/20/2010.

Solution

Modify code

I modified SQL code to simulate plan of hash_value 3805054770 when load ran fine. Production support modified ETL package and load completed in 57,600 seconds. I couldn’t do explain plan on INSERT as being Developer DBA I did not have write access to table.

SQL> explain plan for
  2   select  /*+ full(rcc) full (a) PARALLEL (rcc,8) */ fa.provdr_num as HOSP_ID,
  3                 rcc.RUN_DT as RUN_DT,
  4                 rcc.pta_link_num as LINK_NUM,
  5                 rcc.clm_thru_dt AS HSE_CLM_THRU_DT,
  6                 De.ptnt_dschrg_stus_cd as HSE_CLM_STUS_CD,
  7                 'Y' as finl_actn_sw,
  8                 a.bene_dob,
  9                 a.bene_age,
 10                 NULL AS dgns_cd,
 11                 NULL AS sqnc_num,
 12                 hb.hcpcs_cd as HCPCS_CD,
 13                 fa.clm_from_dt
 14      from       part_a.part_a_demog_dimension de,
 15                 part_b.hcpcs_dimension        hb,
 16                 haji.opps_haji_clm_a_5        a,
 17                 part_a.pta_bene_at_time_clm   b,
 18                 part_a.part_a_rcc_fact        rcc,
 19                 part_a.part_a_fact            fa
 20           where rcc.hcpcs_id = hb.hcpcs_id
 21             and rcc.run_dt = fa.run_dt
 22             and rcc.pta_link_num = fa.pta_link_num
 23             and fa.part_a_demog_id = de.part_a_demog_id
 24             and fa.not_final_id = 1
 25             and fa.clm_from_dt BETWEEN :v_start_date AND :v_end_date
 26             and hb.hcpcs_cd is not null
 27             and rcc.run_dt = a.run_dt
 28             and rcc.pta_link_num = a.link_num
 29             and fa.pta_bene_at_time_clm_id = b.pta_bene_at_time_clm_id
 30             and b.bene_clm_num = a.bene_clm_num;
Explained.

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                        |     1 |   152 |   458K  (6)| 01:46:56 |       |       |
|*  1 |  PX COORDINATOR                                   |                        |       |       |    |          |       |       |
|   2 |   PX SEND QC (RANDOM)                             | :TQ10005               |       |       |    |          |       |       |
|*  3 |    FILTER                                         |                        |       |       |    |          |       |       |
|   4 |     NESTED LOOPS                                  |                        |       |       |    |          |       |       |
|   5 |      NESTED LOOPS                                 |                        |     1 |   152 |   458K  (6)| 01:46:56 |       |       |
|   6 |       NESTED LOOPS                                |                        |     1 |   139 |   458K  (6)| 01:46:56 |       |       |
|*  7 |        HASH JOIN                                  |                        |     1 |   132 |   458K  (6)| 01:46:56 |       |       |
|   8 |         PX RECEIVE                                |                        | 13752 |  1517K|   411K  (6)| 01:36:02 |       |       |
|   9 |          PX SEND HASH                             | :TQ10004               | 13752 |  1517K|   411K  (6)| 01:36:02 |       |       |
|* 10 |           HASH JOIN                               |                        | 13752 |  1517K|   411K  (6)| 01:36:02 |       |       |
|  11 |            PX RECEIVE                             |                        |  1990K|   140M|   356K  (6)| 01:23:14 |       |       |
|  12 |             PX SEND HASH                          | :TQ10003               |  1990K|   140M|   356K  (6)| 01:23:14 |       |       |
|* 13 |              HASH JOIN                            |                        |  1990K|   140M|   356K  (6)| 01:23:14 |       |       |
|  14 |               PX RECEIVE                          |                        |  1990K|    85M| 88870   (1)| 00:20:45 |       |       |
|  15 |                PX SEND BROADCAST                  | :TQ10002               |  1990K|    85M| 88870   (1)| 00:20:45 |       |       |
|  16 |                 PX PARTITION LIST ALL             |                        |  1990K|    85M| 88870   (1)| 00:20:45 |     1 |  LAST |
|  17 |                  TABLE ACCESS BY LOCAL INDEX ROWID| PART_A_FACT            |  1990K|    85M| 88870   (1)| 00:20:45 |     1 | 14999 |
|* 18 |                   INDEX RANGE SCAN                | IDX_PAF_NF_ID_CLM_DT   |  3643K|       |  2726   (1)| 00:00:39 |     1 | 14999 |
|  19 |               PX BLOCK ITERATOR                   |                        |  5775M|   155G|   263K  (7)| 01:01:32 |     1 |  LAST |
|  20 |                TABLE ACCESS FULL                  | PART_A_RCC_FACT        |  5775M|   155G|   263K  (7)| 01:01:32 |     1 |  3559 |
|  21 |            BUFFER SORT                            |                        |       |       |    |          |       |       |
|  22 |             PX RECEIVE                            |                        |    38M|  1428M| 54823   (2)| 00:12:48 |       |       |
|  23 |              PX SEND HASH                         | :TQ10000               |    38M|  1428M| 54823   (2)| 00:12:48 |       |       |
|  24 |               TABLE ACCESS FULL                   | OPPS_HAJI_CLM_A_5      |    38M|  1428M| 54823   (2)| 00:12:48 |       |       |
|  25 |         BUFFER SORT                               |                        |       |       |    |          |       |       |
|  26 |          PX RECEIVE                               |                        |    77M|  1408M| 46623   (2)| 00:10:53 |       |       |
|  27 |           PX SEND HASH                            | :TQ10001               |    77M|  1408M| 46623   (2)| 00:10:53 |       |       |
|  28 |            TABLE ACCESS FULL                      | PTA_BENE_AT_TIME_CLM   |    77M|  1408M| 46623   (2)| 00:10:53 |       |       |
|  29 |        TABLE ACCESS BY INDEX ROWID                | PART_A_DEMOG_DIMENSION |     1 |     7 |  0   (0)| 00:00:01 |          |       |
|* 30 |         INDEX UNIQUE SCAN                         | PK_PADD_PTA_DEMOG_ID   |     1 |       |  0   (0)| 00:00:01 |          |       |
|* 31 |       INDEX UNIQUE SCAN                           | PK_HCPCSD_ID           |     1 |       |  0   (0)| 00:00:01 |          |       |
|* 32 |      TABLE ACCESS BY INDEX ROWID                  | HCPCS_DIMENSION        |     1 |    13 |  0   (0)| 00:00:01 |          |       |
-------------------------------------------------------------------------------------------------------------------------------------------- 

Make it work without changing the code

Create SQL Tuning set using good plan from AWR and load plan in to SQL plan baseline. I did not had luxury to run this fully due to limited access. Last SQL statement will vouch that plan is loaded into SPM.

vdesai@HADWPR01> EXEC DBMS_SQLTUNE.CREATE_SQLSET('LOAD_PARTA','SYS');
PL/SQL PROCEDURE successfully completed.
vdesai@HADWPR01>
 DECLARE
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN baseline_ref_cursor FOR SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(31703, 38262, 6 'sql_id=' ||CHR(39) ||'dxh8ua3m4r1cm' ||CHR(39) ||' and plan_hash_value=3805054770',NULL,NULL,NULL, NULL,NULL,NULL,'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET('LOAD_PARTA', baseline_ref_cursor);
END;
/

PL/SQL PROCEDURE successfully completed. 

SELECT sqlset_name, sql_id,plan_hash_value 2 FROM dba_sqlset_statements;

SQLSET_NAME                    SQL_ID        PLAN_HASH_VALUE
------------------------------ ------------- ---------------
LOAD_PARTA                     dxh8ua3m4r1cm 3805054770 

1 row selected. 

EXEC DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( sqlset_name =>'LOAD_PARTA', sqlset_owner =>'VDESAI', fixed=>'YES', enabled=>'YES');

SELECT * FROM dba_sql_plan_baselines;