Skip to Main Content

Query Performance Issue

User_OCZ1TJul 8 2020 — edited Jul 9 2020

Hi, We are using version 11.2.0.4 of oracle exadata. Table STAGE is stage table and TAB1 and TAB2 are transaction tables and both are range partitioned on column PART_DT. We are fetching most of the data from TAB2 only but there is noway we can JOIN table stage to TAB2, so we have to go through TAB1 here. these table TAB1 and TAB2 are big in size and heavy DML's happen so we are hesitating to go for creating new indexes on them. Other tables used in the query are reference data tables. Below query is running longer and i see few anomalies.

1)The table TAB1 is getting full scanned in a nested loop join thus seems consuming lot of time. I understand there is some deviation in the estimation of rows for the driving/stage table STAGE, but even i forced the estimation by using cardinality hint, it still putting the table TAB1 in a nested loop join. Also i see estimation of TAB1 is underestimated by 1/10th but even i tried force the cardinality estimation still , I am wondering why its not doing HASH join with TAB1? Is it that the way table REF1 is used in the query posing any difficulty for optimizer to join TAB1 using HASH JOIN?

2)Should we modify the query(mainly usage of table REF1 in the query) anyway to make simple for optimizer and then we can get better execution path?

3)Filter operation at line -2 is reducing the result sets from 26million to ~1million. Is there a way, we can we modify the query such that restrict the data much earlier in the path to avoid reading those unnecessary rows?

4)I do see OR expansion is happening here and major DB resource is spent on the first part of the OR expansion. Should we stop the OR expansion someway by modifying the query to stop it?

select...

FROM STAGE STAGE, TAB1 ,  TAB2,REF1 ,REF2

WHERE     STAGE.SID = TAB1.SID  AND STAGE.PART_DT =TAB1.PART_DT   AND STAGE.STAT = 'I'  AND STAGE.NAME = 'XXX'

       AND TAB1.TXN_ID = TAB2.TXN_ID    AND TAB1.PART_DT = TAB2.PART_DT AND TAB2.CODE = REF1.VAL

       AND REF1.PUSER = 'XXX'    AND REF1.NAME = 'CODE'

       AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE  AND TAB1.DID = REF2.DID

       AND ( (   REF1.VAL NOT IN

                (SELECT VAL   FROM REF1  WHERE   PUSER = 'XXX' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE)

                              OR REF2.CUST_ID IN

                                    (SELECT CUST_ID  FROM CUST_ORG_PK  WHERE CUST_ORG_PK.INDICATR = 'Y'

                                     MINUS

                                     SELECT TO_NUMBER (VAL)  FROM REF1  WHERE  PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE

                                     )

            ))

      AND EXISTS (SELECT 1   FROM CONTROLTAB   WHERE     CONTROLTAB.CNTRLID = 9999 AND NVL (CONTROLTAB.STATUS, 'F') = 'S');           

                                           

                                           

Global Information

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

Status              :  DONE (ALL ROWS)                 

Instance ID         :  2                               

SQL Execution ID    :  33554636                        

Execution Started   :  07/02/2020 06:53:12             

First Refresh Time  :  07/02/2020 06:53:16             

Last Refresh Time   :  07/02/2020 10:17:17             

Duration            :  12245s                          

Fetch Calls         :  8640                            

Global Stats

=================================================================================================================

| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  | Fetch | Buffer | Read | Read  |  Cell   |

| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |

=================================================================================================================

|   12618 |    2184 |    10392 |        0.83 |        0.09 |       41 |  8640 |    13G | 108M |  97TB |  99.44% |

=================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1662617358)

===============================================================================================================================================================================================================================

| Id |                 Operation                  |            Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   |  Mem  | Activity |             Activity Detail             |

|    |                                            |                            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload | (Max) |   (%)    |               (# samples)               |

===============================================================================================================================================================================================================================

|  0 | SELECT STATEMENT                           |                            |         |      |     12086 |     +5 |     1 |     864K |      |       |         |       |     0.01 | Cpu (1)                                 |

|  1 |   CONCATENATION                            |                            |         |      |     12086 |     +5 |     1 |     864K |      |       |         |       |          |                                         |

|  2 |    FILTER                                  |                            |         |      |     12191 |     +4 |     1 |     864K |      |       |         |       |     0.03 | Cpu (4)                                 |

|  3 |     FILTER                                 |                            |         |      |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.01 | Cpu (1)                                 |

|  4 |      NESTED LOOPS                          |                            |     241 | 251K |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.02 | Cpu (3)                                 |

|  5 |       NESTED LOOPS                         |                            |     241 | 251K |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.07 | Cpu (8)                                 |

|  6 |        NESTED LOOPS                        |                            |     241 | 251K |     12232 |     +4 |     1 |      26M |      |       |         |       |     0.05 | Cpu (6)                                 |

|  7 |         NESTED LOOPS                       |                            |    5407 | 233K |     12242 |     +4 |     1 |      86M |      |       |         |       |          |                                         |

|  8 |          MERGE JOIN CARTESIAN              |                            |       1 |   35 |     12242 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |

|  9 |           TABLE ACCESS BY INDEX ROWID      | REF1                       |       1 |    3 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 10 |            INDEX RANGE SCAN                | REF1_PK                    |       1 |    2 |     12242 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 11 |           BUFFER SORT                      |                            |      84 |   32 |     12242 |     +4 |     1 |     1000 |      |       |         |  104K |          |                                         |

| 12 |            TABLE ACCESS BY INDEX ROWID     | STAGE                      |      84 |   32 |         1 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |

| 13 |             INDEX RANGE SCAN               | STAGE_IDX1                 |      84 |    4 |         1 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |

| 14 |          PARTITION RANGE ITERATOR          |                            |    8292 | 232K |     12232 |     +4 |  1000 |      86M |      |       |         |       |          |                                         |

| 15 |           TABLE ACCESS STORAGE FULL        | TAB1                       |    8292 | 232K |     12245 |     +1 |  1000 |      86M | 103M | 521GB |   1.96% |    7M |    51.81 | gc buffer busy acquire (1)              |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | latch: cache buffers chains (1)         |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | Cpu (1196)                              |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gcs drm freeze in enter server mode (2) |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | reliable message (5)                    |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (2827)  |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell smart table scan (1977)            |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | read by other session (304)             |

| 16 |         PARTITION RANGE ITERATOR           |                            |       1 |   12 |     12191 |     +4 |   86M |      26M |      |       |         |       |     0.42 | Cpu (51)                                |

| 17 |          TABLE ACCESS BY LOCAL INDEX ROWID | TAB2                       |       1 |   12 |     12191 |     +4 |   86M |      26M |   4M |  28GB |         |       |    32.14 | gc cr grant 2-way (20)                  |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gc cr request (2)                       |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gc remaster (6)                         |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | Cpu (319)                               |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gcs drm freeze in enter server mode (4) |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | latch: gc element (2)                   |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (3563)  |

| 18 |           INDEX RANGE SCAN                 | TAB2_IX1                   |     166 |    3 |     12210 |     +2 |   86M |      26M |   1M |  11GB |         |       |    15.17 | Cpu (292)                               |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (1557)  |

| 19 |        INDEX UNIQUE SCAN                   | MTD_PK                     |       1 |    1 |     12242 |     +4 |   26M |      26M |  292 |   2MB |         |       |     0.17 | Cpu (20)                                |

|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (1)     |

| 20 |       TABLE ACCESS BY INDEX ROWID          | REF2                       |       1 |    2 |     12191 |     +4 |   26M |      26M |    7 | 57344 |         |       |     0.11 | Cpu (13)                                |

| 21 |      TABLE ACCESS BY INDEX ROWID           | CONTROLTAB                 |       1 |    1 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 22 |       INDEX UNIQUE SCAN                    | CONTROLTAB_PK              |       1 |      |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 23 |     MINUS                                  |                            |         |      |       102 |     +4 |    25 |        3 |      |       |         |       |          |                                         |

| 24 |      TABLE ACCESS BY INDEX ROWID           | CUST_ORG_PK                   |       1 |    3 |       942 |     +4 |    25 |       10 |      |       |         |       |          |                                         |

| 25 |       INDEX UNIQUE SCAN                    | MC_PK                      |       1 |    2 |       942 |     +4 |    25 |       25 |      |       |         |       |          |                                         |

| 26 |      SORT UNIQUE NOSORT                    |                            |       1 |    4 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

| 27 |       TABLE ACCESS BY INDEX ROWID          | REF1                       |       1 |    3 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

| 28 |        INDEX RANGE SCAN                    | REF1_PK                    |       1 |    2 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

| 29 |    FILTER                                  |                            |         |      |           |        |     1 |          |      |       |         |       |          |                                         |

| 30 |     FILTER                                 |                            |         |      |           |        |     1 |          |      |       |         |       |          |                                         |

| 31 |      NESTED LOOPS                          |                            |       4 | 236K |           |        |     1 |          |      |       |         |       |          |                                         |

| 32 |       NESTED LOOPS                         |                            |       4 | 236K |           |        |     1 |          |      |       |         |       |          |                                         |

| 33 |        NESTED LOOPS                        |                            |       4 | 236K |           |        |     1 |          |      |       |         |       |          |                                         |

| 34 |         NESTED LOOPS                       |                            |     270 | 233K |           |        |     1 |          |      |       |         |       |          |                                         |

| 35 |          MERGE JOIN CARTESIAN              |                            |       1 |   35 |           |        |     1 |          |      |       |         |       |          |                                         |

| 36 |           TABLE ACCESS BY INDEX ROWID      | REF1                       |       1 |    3 |           |        |     1 |          |      |       |         |       |          |                                         |

| 37 |            INDEX RANGE SCAN                | REF1_PK                    |       1 |    2 |           |        |     1 |          |      |       |         |       |          |                                         |

| 38 |             TABLE ACCESS BY INDEX ROWID    | REF1                       |       1 |    3 |         1 | +12245 |     1 |        1 |      |       |         |       |          |                                         |

| 39 |              INDEX RANGE SCAN              | REF1_PK                    |       1 |    2 |         1 | +12245 |     1 |        1 |      |       |         |       |          |                                         |

| 40 |           BUFFER SORT                      |                            |      84 |   32 |           |        |       |          |      |       |         |       |          |                                         |

| 41 |            TABLE ACCESS BY INDEX ROWID     | STAGE                      |      84 |   32 |           |        |       |          |      |       |         |       |          |                                         |

| 42 |             INDEX RANGE SCAN               | STAGE_IDX1                 |      84 |    4 |           |        |       |          |      |       |         |       |          |                                         |

| 43 |          PARTITION RANGE ITERATOR          |                            |    8292 | 232K |           |        |       |          |      |       |         |       |          |                                         |

| 44 |           TABLE ACCESS STORAGE FULL        | TAB1                       |    8292 | 232K |           |        |       |          |      |       |         |       |          |                                         |

| 45 |         PARTITION RANGE ITERATOR           |                            |       1 |   12 |           |        |       |          |      |       |         |       |          |                                         |

| 46 |          TABLE ACCESS BY LOCAL INDEX ROWID | TAB2                       |       1 |   12 |           |        |       |          |      |       |         |       |          |                                         |

| 47 |           INDEX RANGE SCAN                 | TAB2_IX1                   |     166 |    3 |           |        |       |          |      |       |         |       |          |                                         |

| 48 |        INDEX UNIQUE SCAN                   | MTD_PK                     |       1 |    1 |           |        |       |          |      |       |         |       |          |                                         |

| 49 |       TABLE ACCESS BY INDEX ROWID          | REF2                       |       1 |    2 |           |        |       |          |      |       |         |       |          |                                         |

| 50 |      TABLE ACCESS BY INDEX ROWID           | CONTROLTAB                 |       1 |    1 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 51 |       INDEX UNIQUE SCAN                    | CONTROLTAB_PK              |       1 |      |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |

| 52 |     MINUS                                  |                            |         |      |       101 |     +5 |    25 |        3 |      |       |         |       |          |                                         |

| 53 |      TABLE ACCESS BY INDEX ROWID           | CUST_ORG_PK                   |       1 |    3 |       102 |     +4 |    25 |       10 |      |       |         |       |          |                                         |

| 54 |       INDEX UNIQUE SCAN                    | MC_PK                      |       1 |    2 |       942 |     +4 |    25 |       25 |      |       |         |       |          |                                         |

| 55 |      SORT UNIQUE NOSORT                    |                            |       1 |    4 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

| 56 |       TABLE ACCESS BY INDEX ROWID          | REF1                       |       1 |    3 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

| 57 |        INDEX RANGE SCAN                    | REF1_PK                    |       1 |    2 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |

===============================================================================================================================================================================================================================

                                           

Predicate Information (identified by operation id):

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

   2 - filter( EXISTS ( (SELECT /*+ INDEX_RS_ASC ("CUST_ORG_PK" "MC_PK") */ "CUST_ID" FROM "USER1"."CUST_ORG_PK"  "CUST_ORG_PK" WHERE "CUST_ID"=:B1 AND "CUST_ORG_PK"."INDICATR"='Y')MINUS (SELECT /*+ INDEX_RS_ASC

              ("REF1" "REF1_PK") */ TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2

              AND "SDATE"<=TRUNC(SYSDATE@!))))

   3 - filter( EXISTS (SELECT /*+ INDEX_RS_ASC ("CONTROLTAB" "CONTROLTAB_PK") */ 0 FROM  "USER2"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=9999 AND

              NVL("CONTROLTAB"."STATUS",'F')='S'))

   9 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))

  10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))

  13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')

  15 - storage("STAGE"."PART_DT"="TAB1"."PART_DT" AND  "STAGE"."SID"="TAB1"."SID")

       filter("STAGE"."PART_DT"="TAB1"."PART_DT" AND   "STAGE"."SID"="TAB1"."SID")

  17 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")

  18 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND  "TAB2"."CODE"="REF1"."VAL")

  19 - access("TAB1"."DID"="REF2"."DID")

  21 - filter(NVL("CONTROLTAB"."STATUS",'F')='S') 22 - access("CONTROLTAB"."CNTRLID"=9999)

  24 - filter("CUST_ORG_PK"."INDICATR"='Y')

  25 - access("CUST_ID"=:B1)

  27 - filter("EDATE">=TRUNC(SYSDATE@!))

  28 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))

       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))

  29 - filter(LNNVL( EXISTS ( (SELECT /*+ INDEX_RS_ASC ("CUST_ORG_PK" "MC_PK") */ "CUST_ID" FROM "USER1"."CUST_ORG_PK" "CUST_ORG_PK" WHERE "CUST_ID"=:B1 AND "CUST_ORG_PK"."INDICATR"='Y')MINUS (SELECT

              /*+ INDEX_RS_ASC ("REF1" "REF1_PK") */ TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!)

              AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))))

  30 - filter( EXISTS (SELECT /*+ INDEX_RS_ASC ("CONTROLTAB" "CONTROLTAB_PK") */ 0 FROM  "USER2"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=9999 AND

              NVL("CONTROLTAB"."STATUS",'F')='S'))

  36 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))

  37 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))

       filter( NOT EXISTS (SELECT /*+ PUSH_SUBQ INDEX_RS_ASC ("REF1" "REF1_PK") */ 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND

              "NAME"='YYY' AND "PUSER"='XXX' AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1))

  38 - filter("EDATE">=TRUNC(SYSDATE@!))

  39 - access("PUSER"='XXX' AND "NAME"='YYY' AND "VAL"=:B1  AND "SDATE"<=TRUNC(SYSDATE@!))

       filter("VAL"=:B1)

  42 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')

  44 - storage("STAGE"."SID"="TAB1"."SID" AND  "STAGE"."PART_DT"="TAB1"."PART_DT")

       filter("STAGE"."SID"="TAB1"."SID" AND  "STAGE"."PART_DT"="TAB1"."PART_DT")

  46 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")

  47 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND "TAB2"."CODE"="REF1"."VAL")

  48 - access("TAB1"."DID"="REF2"."DID")

  50 - filter(NVL("CONTROLTAB"."STATUS",'F')='S')

  51 - access("CONTROLTAB"."CNTRLID"=9999)

  53 - filter("CUST_ORG_PK"."INDICATR"='Y')

  54 - access("CUST_ID"=:B1)

  56 - filter("EDATE">=TRUNC(SYSDATE@!))

  57 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))

       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))

This post has been answered by Jonathan Lewis on Jul 9 2020
Jump to Answer
Comments
Post Details
Added on Jul 8 2020
12 comments
382 views