Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

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.

User_OCZ1T

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.

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.

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

User_OCZ1T

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.

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

User_OCZ1T

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.

1 person found this helpful
User_OCZ1T

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)
JohnWatson2

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

Jonathan Lewis
Answer 
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

Marked as Answer by User_OCZ1T · Sep 27 2020
User_OCZ1T

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.

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

1 person found this helpful
1 - 12

Post Details

Added on Jul 8 2020
#general-database-discussions
12 comments
859 views