12 Replies Latest reply on Jul 9, 2020 4:05 PM by Jonathan Lewis

    Query Performance Issue

    933257

      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@!))
      
        • 1. Re: Query Performance Issue
          Luís Gustavo Lira

          Hi,

           

          check if the filters related to the join are not generating a Cartesian product.

           

          if you have diagnostic pack try to run a SQL Tuning Advisor for this query, which will give you general recommendations related to indexes, statistics, etc.

          If it is possible to optimize by creating indexes, I don't see why not create it.

          • 2. Re: Query Performance Issue
            933257

            Thank You . My thought was before going for considering new indexes here in this query may be optimizer is choosing some wrong Order + JOIN mechanism causing this to run longer. And if we can tweak the query to help the optimizer by simplifying the query.

            And yes again considering the base transaction tables are experiencing heavy DML and are big in size(~5TB+ but are partitioned though), so we need to evaluate those additionally indexes carefully.

            • 3. Re: Query Performance Issue
              Luís Gustavo Lira

              OK, but anyway SQL Tuning Advisor can recommend you a profile for example that will give you a better plan than the current one (be careful that it also recommends profiles to use parallelism that is not always recommended).

              Is this a new query or was it already executed and did you notice a performance degradation?

              If it is old you can check if the degradation was caused by a change in plan.

              • 4. Re: Query Performance Issue
                evgenyg

                Hi

                Please correct me if I am wrong:

                Currently, in where clause you have: STAGE.PART_DT =TAB1.PART_DT  AND  TAB1.PART_DT = TAB2.PART_DT so you may add STAGE.PART_DT = TAB2.PART_DT

                Now to be able to remove TAB1 at all (you saying you not use its data) you need to consider if the following condition is necessary: TAB1.DID = REF2.DID

                 

                Then you may remove TAB1 from the query seems it can help..

                 

                 

                Regards

                • 5. Re: Query Performance Issue
                  933257

                  Yes that condition is required and apart from those partition key column joins i.e. PART_DT the other condition i.e. "AND TAB1.TXN_ID = TAB2.TXN_ID " is required to filter out records from TAB2. So it look like TAB1 cant be bypassed.

                  • 6. Re: Query Performance Issue
                    evgenyg

                    Then, maybe, you can prepare the subset from tab1 that you need for the join and then join only on subset? and/or introduce HASH instead of NL

                    • 7. Re: Query Performance Issue
                      933257

                      I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.

                       

                      Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is  s small reference data table holding only ~600 rows.

                      • 8. Re: Query Performance Issue
                        933257

                        I tried tweaking the query by evaluating table REF1, REF2 separately using WITH clause + Added Materialize hint to restrict it getting merged with the main query and i am seeing its finishing considerably faster time as compared to earlier even with more data volume.

                         

                        So my next thought is , as its clear that the way table REF1 and REF2 is used/joined in the query causing optimizer estimating the cardinality wrongly and thus a bad plan(mainly as it puts FTS of table TAB1 in a nested loop), so can i achieve same result without using the hints 'MATERIALIZE' and by some additional tweak or will it require any fixing of statistics by anyway rather simplifying/tweaking the query?

                         

                        WITH tab as
                        (   select /*+materialize*/REF1.VAL,REF2.DID
                         from REF1, REF2   where REF1.PUSER = 'ZZZ'
                              AND REF1.NAME = 'CODE' AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE
                               AND ( (   REF1.VAL NOT IN
                                        (SELECT VAL   FROM REF1      WHERE     PUSER = 'ZZZ' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE)
                                                      OR REF2.CUST_ID IN
                                                            (SELECT CUST_ID       FROM CUST_ORG WHERE CUST_ORG.INDICATOR = 'Y'
                                                             MINUS
                                                             SELECT TO_NUMBER (VAL)   FROM REF1  WHERE     PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE
                                                             )
                                     )
                                    )
                        )
                        SELECT
                          ....
                          FROM STAGE,       TAB1,       TAB2, TAB
                         WHERE     STAGE.SID = TAB1.SID  AND STAGE.SBMT_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 = TAB.VAL AND TAB1.DID = TAB.DID
                               AND EXISTS  (SELECT 1      FROM CONTROLTAB     WHERE     CONTROLTAB.CNTRLID = 999   AND NVL (CONTROLTAB.STATUS, 'F') = 'F')
                        
                        
                        

                         

                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)          
                         Instance ID         :  4                        
                         SQL Execution ID    :  67108864                 
                         Execution Started   :  07/09/2020 03:54:05      
                         First Refresh Time  :  07/09/2020 03:54:11      
                         Last Refresh Time   :  07/09/2020 03:56:02      
                         Duration            :  117s                     
                         Module/Action       :  SQL*Plus/-               
                         Program             :  sqlplus.exe              
                         Fetch Calls         :  186                      
                        
                        Global Stats
                        ===================================================================================================
                        | Elapsed |   Cpu   |    IO    | Application | Cluster  | Fetch | Buffer | Read | Read  |  Cell   |
                        | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |
                        ===================================================================================================
                        |     105 |      78 |       27 |        0.00 |     0.47 |   186 |    17M |   1M |  92GB |  80.58% |
                        ===================================================================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=39601017)
                        ============================================================================================================================================================================================================================
                        | 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                       |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |     3.96 | Cpu (1)                                |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | SQL*Net more data to client (3)        |
                        |  1 |   TEMP TABLE TRANSFORMATION            |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |          |                                        |
                        |  2 |    LOAD AS SELECT                      |                             |         |      |         1 |     +6 |     1 |        1 |       |       |         |  276K |          |                                        |
                        |  3 |     CONCATENATION                      |                             |         |      |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
                        |  4 |      FILTER                            |                             |         |      |         3 |     +4 |     1 |      845 |       |       |         |       |     0.99 | Cpu (1)                                |
                        |  5 |       MERGE JOIN CARTESIAN             |                             |   12361 | 1225 |         1 |     +6 |     1 |       2M |       |       |         |       |          |                                        |
                        |  6 |        TABLE ACCESS BY INDEX ROWID     | REF1                        |       1 |    3 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        |  7 |         INDEX RANGE SCAN               | REF1_PK                     |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        |  8 |        BUFFER SORT                     |                             |      2M | 1222 |         1 |     +6 |     1 |       2M |       |       |         |   64M |          |                                        |
                        |  9 |         INDEX STORAGE FAST FULL SCAN   | MTD_PK                      |      2M | 1222 |         1 |     +6 |     1 |       2M |       |       |         |       |          |                                        |
                        | 10 |       MINUS                            |                             |         |      |         1 |     +6 |  861K |       17 |       |       |         |       |          |                                        |
                        | 11 |        TABLE ACCESS BY INDEX ROWID     | CUST_ORG                    |       1 |    3 |         1 |     +6 |  861K |       36 |       |       |         |       |          |                                        |
                        | 12 |         INDEX UNIQUE SCAN              | MC_PK                       |       1 |    2 |         1 |     +6 |  861K |     861K |       |       |         |       |          |                                        |
                        | 13 |        SORT UNIQUE NOSORT              |                             |       1 |    4 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
                        | 14 |         TABLE ACCESS BY INDEX ROWID    | REF1                        |       1 |    3 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
                        | 15 |          INDEX RANGE SCAN              | REF1_PK                     |       1 |    2 |         6 |     +1 |  861K |       23 |       |       |         |       |     3.96 | Cpu (4)                                |
                        | 16 |      FILTER                            |                             |         |      |           |        |     1 |          |       |       |         |       |          |                                        |
                        | 17 |       MERGE JOIN CARTESIAN             |                             |     618 | 1225 |           |        |     1 |          |       |       |         |       |          |                                        |
                        | 18 |        TABLE ACCESS BY INDEX ROWID     | REF1                        |       1 |    3 |           |        |     1 |          |       |       |         |       |          |                                        |
                        | 19 |         INDEX RANGE SCAN               | REF1_PK                     |       1 |    2 |           |        |     1 |          |       |       |         |       |          |                                        |
                        | 20 |          TABLE ACCESS BY INDEX ROWID   | REF1                        |       1 |    3 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        | 21 |           INDEX RANGE SCAN             | REF1_PK                     |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        | 22 |        BUFFER SORT                     |                             |      2M | 1222 |           |        |       |          |       |       |         |       |          |                                        |
                        | 23 |         INDEX STORAGE FAST FULL SCAN   | MTD_PK                      |      2M | 1222 |           |        |       |          |       |       |         |       |          |                                        |
                        | 24 |       MINUS                            |                             |         |      |         1 |     +6 |  861K |       17 |       |       |         |       |          |                                        |
                        | 25 |        TABLE ACCESS BY INDEX ROWID     | CUST_ORG                    |       1 |    3 |         1 |     +6 |  861K |       36 |       |       |         |       |          |                                        |
                        | 26 |         INDEX UNIQUE SCAN              | MC_PK                       |       1 |    2 |         1 |     +6 |  861K |     861K |       |       |         |       |          |                                        |
                        | 27 |        SORT UNIQUE NOSORT              |                             |       1 |    4 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
                        | 28 |         TABLE ACCESS BY INDEX ROWID    | REF1                        |       1 |    3 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
                        | 29 |          INDEX RANGE SCAN              | REF1_PK                     |       1 |    2 |         1 |     +6 |  861K |       23 |       |       |         |       |          |                                        |
                        | 30 |    FILTER                              |                             |         |      |        94 |    +24 |     1 |     924K |       |       |         |       |          |                                        |
                        | 31 |     NESTED LOOPS                       |                             |       1 |   6M |        94 |    +24 |     1 |     924K |       |       |         |       |     0.99 | Cpu (1)                                |
                        | 32 |      NESTED LOOPS                      |                             |    1826 |   6M |        94 |    +24 |     1 |     924K |       |       |         |       |     0.99 | Cpu (1)                                |
                        | 33 |       HASH JOIN                        |                             |      11 |   6M |       112 |     +6 |     1 |       1M |       |       |         |  104M |    22.77 | Cpu (23)                               |
                        | 34 |        JOIN FILTER CREATE              | :BF0001                     |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
                        | 35 |         PART JOIN FILTER CREATE        | :BF0000                     |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
                        | 36 |          MERGE JOIN CARTESIAN          |                             |     500 |  659 |         1 |     +6 |     1 |     845K |       |       |         |       |          |                                        |
                        | 37 |           VIEW                         |                             |       1 |    2 |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
                        | 38 |            TABLE ACCESS STORAGE FULL   | SYS_TEMP_0FDA48F5B_58EE133C |       1 |    2 |         1 |     +6 |     1 |      845 |       |       |         |       |          |                                        |
                        | 39 |           BUFFER SORT                  |                             |     500 |  659 |         1 |     +6 |   845 |     845K |       |       |         |  104K |          |                                        |
                        | 40 |            TABLE ACCESS BY INDEX ROWID | STAGE                       |     500 |  657 |         1 |     +6 |     1 |     1000 |       |       |         |       |          |                                        |
                        | 41 |             INDEX RANGE SCAN           | STAGE_IDX1                  |    2025 |   28 |         1 |     +6 |     1 |     1000 |       |       |         |       |          |                                        |
                        | 42 |        JOIN FILTER USE                 | :BF0001                     |    802M |   6M |       112 |     +6 |     1 |     249M |       |       |         |       |          |                                        |
                        | 43 |         PARTITION RANGE JOIN-FILTER    |                             |    802M |   6M |       112 |     +6 |     1 |     249M |       |       |         |       |          |                                        |
                        | 44 |          TABLE ACCESS STORAGE FULL     | TAB1                        |    802M |   6M |       112 |     +6 |     1 |     249M | 96540 |  85GB |  87.94% |    7M |     4.95 | Cpu (5)                                |
                        | 45 |       PARTITION RANGE ITERATOR         |                             |     166 |    3 |        94 |    +24 |    2M |     924K |       |       |         |       |     1.98 | Cpu (2)                                |
                        | 46 |        INDEX RANGE SCAN                | TAB2_IX1                    |     166 |    3 |        94 |    +24 |    2M |     924K |  862K |   7GB |         |       |    36.63 | gc current block 2-way (1)             |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | gc current grant busy (1)              |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | Cpu (20)                               |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell list of blocks physical read (14) |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell single block physical read (1)    |
                        | 47 |      TABLE ACCESS BY LOCAL INDEX ROWID | TAB2                        |       1 |   12 |        94 |    +24 |    1M |     924K |  150K |   1GB |         |       |    21.78 | Cpu (11)                               |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell list of blocks physical read (4)  |
                        |    |                                        |                             |         |      |           |        |       |          |       |       |         |       |          | cell single block physical read (7)    |
                        | 48 |     TABLE ACCESS BY INDEX ROWID        | CONTROLTAB                  |       1 |    1 |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        | 49 |      INDEX UNIQUE SCAN                 | CONTROLTAB_PK               |       1 |      |         1 |     +6 |     1 |        1 |       |       |         |       |          |                                        |
                        ============================================================================================================================================================================================================================
                        
                        
                        924077 rows selected.
                        
                        Elapsed: 00:01:57.22
                        
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                           4 - filter( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND   TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!))))
                           6 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
                           7 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
                          11 - filter("CUST_ORG"."INDICATOR"='Y')
                          12 - access("CUST_ID"=:B1)
                          14 - filter("EDATE">=TRUNC(SYSDATE@!))
                          15 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
                               filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
                          16 - filter(LNNVL( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1"   "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND    TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))))
                          18 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
                          19 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
                               filter( NOT EXISTS (SELECT 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND "NAME"='YYY' AND "PUSER"='ZZZ'   AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1))
                          20 - filter("EDATE">=TRUNC(SYSDATE@!))
                          21 - access("PUSER"='ZZZ' AND "NAME"='YYY' AND "VAL"=:B1        AND "SDATE"<=TRUNC(SYSDATE@!))
                               filter("VAL"=:B1)
                          25 - filter("CUST_ORG"."INDICATOR"='Y')
                          26 - access("CUST_ID"=:B1)
                          28 - filter("EDATE">=TRUNC(SYSDATE@!))
                          29 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
                               filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
                          30 - filter( EXISTS (SELECT 0 FROM "TH_STG_PROD"."CONTROLTAB" "CONTROLTAB" WHERE    "CONTROLTAB"."CNTRLID"=999 AND NVL("CONTROLTAB"."STATUS",'F')='F'))
                          33 - access("STAGE"."SBMT_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID" AND "TAB1"."DID"="TAB"."DID")
                          41 - access("STAGE"."NAME"='ZZZ' AND  "STAGE"."STAT"='I')
                          44 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
                               filter(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
                          46 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND  "TAB2"."CODE"="TAB"."VAL")
                          47 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")
                          48 - filter(NVL("CONTROLTAB"."STATUS",'F')='F')
                          49 - access("CONTROLTAB"."CNTRLID"=999)
                        
                        • 9. Re: Query Performance Issue
                          JohnWatson2

                          If you add an ORDER BY to your CTE, that should force Oracle to materialize it.

                          • 10. Re: Query Performance Issue
                            Jonathan Lewis

                            933257 wrote:

                             

                            I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.

                             

                            Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is s small reference data table holding only ~600 rows.

                             

                            Good inference.

                            Note the predicates on the two objects feeding that MJC - we can compare actuals and estimates and see that the STAGE estimate as out by a factor of about 12, and the REF1 estimate must be very much less than 1 to give you a product where (rounded) 1 * 84 = 1.

                             

                            1.   10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!)) 
                            2.   13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')

                             

                            I suggest you create extended stats on ref1 (puser, name) - but make sure that 'XXX' and 'CODE' are NOT out of range for the individual columns - this may address the estimate < 1.

                            Also (name, stat) on stage may give you a better estimate closer to 1,000.  Possibly, though, you may need a  histogram on STAT (and also on (name, stat)) - that's a guess based on the column name. ("State/Status"? => small number of distinct values with extreme skew).

                             

                            Regards

                            Jonathan Lewis

                             

                             

                            • 11. Re: Query Performance Issue
                              933257

                              You are spot on.

                               

                              There does exists frequency histogram on column NAME(having 14 distinct value) and STAT(having 7 distinct values) of table STAGE. Also there already exists a frequency histogram on column PUSER and height balanced histogram on column NAME of table REF1. But still they were not helping the existing query.

                               

                              By creating a column group on both on ref1 (puser, name) and stage(name, stat)  with histogram for both i.e. 'FOR COLUMNS SIZE 254'. The merge Cartesian removed automatically from the path by optimizer and its doing a hash join now with TAB1 finishing the query in quick time.

                              • 12. Re: Query Performance Issue
                                Jonathan Lewis

                                I've written up a blog note giving some explanation of the suggestions I made: https://jonathanlewis.wordpress.com/2020/07/09/execution-plans-6/

                                 

                                Regards

                                Jonathan Lewis