Query Performance Issue

User_OCZ1T · Jul 8, 2020 7:31AM

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([email protected]!) AND TO_NUMBER("VAL")=:B2              AND "SDATE"<=TRUNC([email protected]!))))   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([email protected]!))  10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))  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([email protected]!))  28 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC([email protected]!))       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC([email protected]!))  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([email protected]!)              AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC([email protected]!)))))  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([email protected]!))  37 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))       filter( NOT EXISTS (SELECT /*+ PUSH_SUBQ INDEX_RS_ASC ("REF1" "REF1_PK") */ 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC([email protected]!) AND              "NAME"='YYY' AND "PUSER"='XXX' AND "EDATE">=TRUNC([email protected]!) AND "VAL"=:B1))  38 - filter("EDATE">=TRUNC([email protected]!))  39 - access("PUSER"='XXX' AND "NAME"='YYY' AND "VAL"=:B1  AND "SDATE"<=TRUNC([email protected]!))       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([email protected]!))  57 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC([email protected]!))       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC([email protected]!))

Jonathan Lewis · Jul 9, 2020 5:19AM

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.

10 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))
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

Luís Gustavo Lira · Jul 8, 2020 8:49AM

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 · Jul 8, 2020 9:14AM

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 · Jul 8, 2020 9:35AM

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 · Jul 8, 2020 9:59AM

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 · Jul 8, 2020 10:36AM

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 · Jul 8, 2020 11:30AM

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 · Jul 8, 2020 2:31PM

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.

User_OCZ1T · Jul 9, 2020 5:03AM

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.22Predicate 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([email protected]!) AND   TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC([email protected]!))))   6 - filter("REF1"."EDATE">=TRUNC([email protected]!))   7 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))  11 - filter("CUST_ORG"."INDICATOR"='Y')  12 - access("CUST_ID"=:B1)  14 - filter("EDATE">=TRUNC([email protected]!))  15 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC([email protected]!))       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC([email protected]!))  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([email protected]!) AND    TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC([email protected]!)))))  18 - filter("REF1"."EDATE">=TRUNC([email protected]!))  19 - access("REF1"."PUSER"='ZZZ' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))       filter( NOT EXISTS (SELECT 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC([email protected]!) AND "NAME"='YYY' AND "PUSER"='ZZZ'   AND "EDATE">=TRUNC([email protected]!) AND "VAL"=:B1))  20 - filter("EDATE">=TRUNC([email protected]!))  21 - access("PUSER"='ZZZ' AND "NAME"='YYY' AND "VAL"=:B1        AND "SDATE"<=TRUNC([email protected]!))       filter("VAL"=:B1)  25 - filter("CUST_ORG"."INDICATOR"='Y')  26 - access("CUST_ID"=:B1)  28 - filter("EDATE">=TRUNC([email protected]!))  29 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC([email protected]!))       filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC([email protected]!))  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 · Jul 9, 2020 5:12AM

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

Jonathan Lewis · Jul 9, 2020 5:19AM

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.

10 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC([email protected]!))
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

Forum Stats

Quick Links

Categories

Query Performance Issue

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Query Performance Issue

Best Answer

Answers