Skip to Main Content
  • Questions

  • SQL Performance degrades when added additional where clause

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ambuj.

Asked: June 12, 2020 - 2:37 am UTC

Last updated: June 16, 2020 - 2:22 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi ASK TOM Team,
I have a query when i run, it returns the results in few seconds(2-3). But when i add one additional where clause it take around 150-160 seconds to returns the results.
In 1st case it returns 170 rows and it second case it returns 165 rows.

Below is the SQL:

SELECT Prh.Segment1 Pr_Num, 
       Prh.Description,
       Prh.Authorization_Status,
       Prh.Creation_Date,
       Prl.Line_Num Pr_Line_Num,
       Prl.Unit_Meas_Lookup_Code,
       Prl.Quantity,
       Prl.Unit_Price,
       Prl.Item_Description,
       Prl.Line_Location_Id,
       Prd.Distribution_Id,
       glcc.segment2
  FROM Apps.Po_Requisition_Headers_All Prh,
  Apps.Po_Requisition_Lines_All     Prl,
  Apps.Po_Req_Distributions_All     Prd,
  Apps.Gl_Code_Combinations Glcc
 WHERE Prh.Type_Lookup_Code = 'PURCHASE'
  -- AND Prh.Authorization_Status = 'APPROVED'
   AND Prh.Requisition_Header_Id = Prl.Requisition_Header_Id
   AND Prd.Requisition_Line_Id = Prl.Requisition_Line_Id
   AND Glcc.Code_Combination_Id = Prd.Code_Combination_Id
   AND Glcc.Segment2 IN ('12145', '12155')
   AND (Glcc.Segment6  ='03271' );



Above query just takes 2-3 seconds to return the results.
here is the execution plan for this:


******************************************

SQL_ID  8u9qdk18j60pc, child number 0
-------------------------------------
SELECT Prh.Segment1 Pr_Num, --tstprh22        Prh.Description,        
Prh.Authorization_Status,        Prh.Creation_Date,        Prl.Line_Num 
Pr_Line_Num,        Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,     
   Prl.Unit_Price,        Prl.Item_Description,        
Prl.Line_Location_Id,        Prd.Distribution_Id,        glcc.segment2  
 FROM Apps.Po_Requisition_Headers_All Prh,   
Apps.Po_Requisition_Lines_All     Prl,   Apps.Po_Req_Distributions_All  
   Prd,   Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 
'PURCHASE'   -- AND Prh.Authorization_Status = 'APPROVED'    AND 
Prh.Requisition_Header_Id = Prl.Requisition_Header_Id    AND 
Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' )
 
Plan hash value: 2926323738
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            |       |       |  3671K(100)|          |
|   1 |  NESTED LOOPS                           |                            |   121 | 25894 |  3671K  (1)| 00:00:13 |
|   2 |   NESTED LOOPS                          |                            |   121 | 25894 |  3671K  (1)| 00:00:13 |
|   3 |    NESTED LOOPS                         |                            |   121 | 14520 |  3671K  (1)| 00:00:13 |
|*  4 |     HASH JOIN                           |                            |   121 |  4356 |  3671K  (1)| 00:00:13 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS       |     2 |    36 |     6   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | GL_CODE_COMBINATIONS_CAT   |     2 |       |     4   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                  | PO_REQ_DISTRIBUTIONS_ALL   |  3185K|    54M|  3670K  (1)| 00:00:13 |
|   8 |     TABLE ACCESS BY INDEX ROWID         | PO_REQUISITION_LINES_ALL   |     1 |    84 |     2   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN                  | PO_REQUISITION_LINES_U1    |     1 |       |     1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN                    | PO_REQUISITION_HEADERS_U1  |     1 |       |     1   (0)| 00:00:01 |
|* 11 |   TABLE ACCESS BY INDEX ROWID           | PO_REQUISITION_HEADERS_ALL |     1 |    94 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   6 - access("GLCC"."SEGMENT6"='03271')
       filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
   9 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
  10 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  11 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
 
*********************************************************


But when i add condition
"AND Prh.Authorization_Status = 'APPROVED'" to the query it takes 150-160 seconds to return the results.
There is index on column "Authorization_Status" on table "Po_Requisition_Headers_All" with that condition it start using this index and SQL runs slow.

below is the SQL and changed execution plan.

SELECT Prh.Segment1 Pr_Num, --tstprh24
       Prh.Description,
       Prh.Authorization_Status,
       Prh.Creation_Date,
       Prl.Line_Num Pr_Line_Num,
       Prl.Unit_Meas_Lookup_Code,
       Prl.Quantity,
       Prl.Unit_Price,
       Prl.Item_Description,
       Prl.Line_Location_Id,
       Prd.Distribution_Id,
       glcc.segment2
  FROM Apps.Po_Requisition_Headers_All Prh,
  Apps.Po_Requisition_Lines_All     Prl,
  Apps.Po_Req_Distributions_All     Prd,
  Apps.Gl_Code_Combinations Glcc
 WHERE Prh.Type_Lookup_Code = 'PURCHASE'
   AND Prh.Authorization_Status = 'APPROVED'
   AND Prh.Requisition_Header_Id = Prl.Requisition_Header_Id
   AND Prd.Requisition_Line_Id = Prl.Requisition_Line_Id
   AND Glcc.Code_Combination_Id = Prd.Code_Combination_Id
   AND Glcc.Segment2 IN ('12145', '12155')
   AND (Glcc.Segment6  ='03271' );


Execution plan:


***************************************************
SQL_ID  1p8826jqk0q9c, child number 0
-------------------------------------
SELECT Prh.Segment1 Pr_Num, --tstprh24        Prh.Description,        
Prh.Authorization_Status,        Prh.Creation_Date,        Prl.Line_Num 
Pr_Line_Num,        Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,     
   Prl.Unit_Price,        Prl.Item_Description,        
Prl.Line_Location_Id,        Prd.Distribution_Id,        glcc.segment2  
 FROM Apps.Po_Requisition_Headers_All Prh,   
Apps.Po_Requisition_Lines_All     Prl,   Apps.Po_Req_Distributions_All  
   Prd,   Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 
'PURCHASE'    AND Prh.Authorization_Status = 'APPROVED'    AND 
Prh.Requisition_Header_Id = Prl.Requisition_Header_Id    AND 
Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' )
 
Plan hash value: 3890956966
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            |       |       |  1880K(100)|          |
|*  1 |  HASH JOIN                              |                            |    15 |  3210 |  1880K  (2)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED   | GL_CODE_COMBINATIONS       |     2 |    36 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                     | GL_CODE_COMBINATIONS_CAT   |     2 |       |     4   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                          |                            |   401K|    74M|  1880K  (2)| 00:00:07 |
|   5 |    NESTED LOOPS                         |                            |   401K|    74M|  1880K  (2)| 00:00:07 |
|   6 |     NESTED LOOPS                        |                            |   397K|    67M|   673K  (2)| 00:00:03 |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_HEADERS_ALL |   162K|    14M| 17522   (3)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | PO_REQUISITION_HEADERS_N3  |   163K|       |   973   (8)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_LINES_ALL   |     2 |   168 |     4   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN                  | PO_REQUISITION_LINES_U2    |     2 |       |     2   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN                    | PO_REQ_DISTRIBUTIONS_N1    |     1 |       |     2   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID          | PO_REQ_DISTRIBUTIONS_ALL   |     1 |    18 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   3 - access("GLCC"."SEGMENT6"='03271')
       filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
   7 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
   8 - access("PRH"."AUTHORIZATION_STATUS"='APPROVED')
  10 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  11 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
 
***********************************************************

I also tried to use the hint to use index (PO_REQUISITION_HEADERS_U1) from table "PO_REQUISITION_HEADERS_ALL" to make it run as 1st case and avoid using index "PO_REQUISITION_HEADERS_N3" on table "PO_REQUISITION_HEADERS_ALL" but that is also not working.
Can you please help me to get results as fast in 2nd case as in 1st case.






and Chris said...

In the fast query, the optimizer estimated it'll fetch 3 million+ rows from PO_REQ_DISTRIBUTIONS_ALL.

In the slow query, it estimates it gets ~160 thousand from PO_REQUISITION_HEADERS_ALL using the index on AUTHORIZATION_STATUS.

It thinks getting ~160k rows will be faster than getting 3M+.

The big question to ask is:

Are these estimates accurate?

Do this by getting the execution plans with actual row figures like so:

set serveroutput off

select /*+ gather_plan_statistics */... <rest of your query>

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));


Compare the (starts * E-rows) and A-rows values. Are these broadly similar?

Post the plans here if you're not sure.

If no, check the stats on the tables.

If the stats are reasonably accurate, increasing the dynamic stats level (with the /*+ dynamic_sampling(11) */ hint) may help the optimizer find the faster plan.

Failing that, you may need to look into using SQL profiles to help the optimizer find a better plan.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ambuj Kumar, June 12, 2020 - 10:56 pm UTC

I ran the execution plan for faster query below is the plan
********************************
SQL_ID  4yx8q19gyg2tw, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ Prh.Segment1 Pr_Num, --tst100      
  Prh.Description,        Prh.Authorization_Status,        
Prh.Creation_Date,        Prl.Line_Num Pr_Line_Num,        
Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,        Prl.Unit_Price,  
      Prl.Item_Description,        Prl.Line_Location_Id,        
Prd.Distribution_Id,        glcc.segment2   FROM 
Apps.Po_Requisition_Headers_All Prh,   Apps.Po_Requisition_Lines_All    
 Prl,   Apps.Po_Req_Distributions_All     Prd,   
Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 'PURCHASE' 
  -- AND Prh.Authorization_Status = 'APPROVED'    AND 
Prh.Requisition_Header_Id = Prl.Requisition_Header_Id    AND 
Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' )
 
Plan hash value: 2926323738
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            |      1 |        |    170 |00:00:04.19 |   83356 |  65713 |
|   1 |  NESTED LOOPS                           |                            |      1 |    121 |    170 |00:00:04.19 |   83356 |  65713 |
|   2 |   NESTED LOOPS                          |                            |      1 |    121 |    170 |00:00:03.98 |   83183 |  65606 |
|   3 |    NESTED LOOPS                         |                            |      1 |    121 |    170 |00:00:03.98 |   82848 |  65511 |
|*  4 |     HASH JOIN                           |                            |      1 |    121 |    170 |00:00:03.94 |   82338 |  65276 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS       |      1 |      2 |     18 |00:00:00.01 |      23 |      3 |
|*  6 |       INDEX RANGE SCAN                  | GL_CODE_COMBINATIONS_CAT   |      1 |      2 |     18 |00:00:00.01 |       4 |      3 |
|   7 |      TABLE ACCESS FULL                  | PO_REQ_DISTRIBUTIONS_ALL   |      1 |   3185K|   3239K|00:00:02.16 |   82315 |  65273 |
|   8 |     TABLE ACCESS BY INDEX ROWID         | PO_REQUISITION_LINES_ALL   |    170 |      1 |    170 |00:00:00.15 |     510 |    235 |
|*  9 |      INDEX UNIQUE SCAN                  | PO_REQUISITION_LINES_U1    |    170 |      1 |    170 |00:00:00.08 |     338 |    112 |
|* 10 |    INDEX UNIQUE SCAN                    | PO_REQUISITION_HEADERS_U1  |    170 |      1 |    170 |00:00:00.06 |     335 |     95 |
|* 11 |   TABLE ACCESS BY INDEX ROWID           | PO_REQUISITION_HEADERS_ALL |    170 |      1 |    170 |00:00:00.07 |     173 |    107 |
-----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   6 - access("GLCC"."SEGMENT6"='03271')
       filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
   9 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
  10 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  11 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
 
********************************


Then i ran the execution plan for slower query

********************************
SQL_ID  00p1xpq5k4ahw, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ Prh.Segment1 Pr_Num, --tst101      
  Prh.Description,        Prh.Authorization_Status,        
Prh.Creation_Date,        Prl.Line_Num Pr_Line_Num,        
Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,        Prl.Unit_Price,  
      Prl.Item_Description,        Prl.Line_Location_Id,        
Prd.Distribution_Id,        glcc.segment2   FROM 
Apps.Po_Requisition_Headers_All Prh,   Apps.Po_Requisition_Lines_All    
 Prl,   Apps.Po_Req_Distributions_All     Prd,   
Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 'PURCHASE' 
   AND Prh.Authorization_Status = 'APPROVED'    AND 
Prh.Requisition_Header_Id = Prl.Requisition_Header_Id    AND 
Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' )
 
Plan hash value: 3890956966
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            |      1 |        |    165 |00:00:00.73 |    8001K|    372K|
|*  1 |  HASH JOIN                              |                            |      1 |     15 |    165 |00:00:00.73 |    8001K|    372K|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED   | GL_CODE_COMBINATIONS       |      1 |      2 |     18 |00:00:00.01 |      22 |      2 |
|*  3 |    INDEX RANGE SCAN                     | GL_CODE_COMBINATIONS_CAT   |      1 |      2 |     18 |00:00:00.01 |       4 |      2 |
|   4 |   NESTED LOOPS                          |                            |      1 |    401K|   2903K|00:03:23.37 |    8001K|    372K|
|   5 |    NESTED LOOPS                         |                            |      1 |    401K|   2903K|00:02:43.88 |    6882K|    306K|
|   6 |     NESTED LOOPS                        |                            |      1 |    397K|   2831K|00:02:28.47 |    3510K|    291K|
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_HEADERS_ALL |      1 |    162K|   1172K|00:00:25.61 |   86462 |  55192 |
|*  8 |       INDEX RANGE SCAN                  | PO_REQUISITION_HEADERS_N3  |      1 |    163K|   1172K|00:00:04.94 |    9042 |   6466 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_LINES_ALL   |   1172K|      2 |   2831K|00:02:11.01 |    3424K|    236K|
|* 10 |       INDEX RANGE SCAN                  | PO_REQUISITION_LINES_U2    |   1172K|      2 |   2831K|00:00:13.60 |    2034K|  16302 |
|* 11 |     INDEX RANGE SCAN                    | PO_REQ_DISTRIBUTIONS_N1    |   2831K|      1 |   2903K|00:00:17.12 |    3371K|  14973 |
|  12 |    TABLE ACCESS BY INDEX ROWID          | PO_REQ_DISTRIBUTIONS_ALL   |   2903K|      1 |   2903K|00:00:37.59 |    1118K|  65871 |
-----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   3 - access("GLCC"."SEGMENT6"='03271')
       filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
   7 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
   8 - access("PRH"."AUTHORIZATION_STATUS"='APPROVED')
  10 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  11 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
 
********************************

For the faster query estimated rows and actual rows are almost accurate in most cases. But for slower query estimates are horrendously wrong. Estimated rows are no where near to actual rows. Some cases estimated rows are just 1 but actual rows are 3 million. I checked the stats on those tables. Stats are looking good
Table name Stat Count Actual Count
PO_REQUISITION_LINES_ALL 3,158,548 3,166,998
PO_REQ_DISTRIBUTIONS_ALL 3,185,569 3,239,953
PO_REQUISITION_HEADERS_ALL 1,296,423 1,300,087

Then with this state do not know why the optimizer will predict so badly. Then i used hint /*+ dynamic_sampling(11) */ as you suggested, to my surprise i got the explain plan same as fast query and performance was also same. below is execution plan with hint.
***********************************
SQL_ID  7j0bhm4ung00c, child number 0
-------------------------------------
SELECT /*+ dynamic_sampling(11) */ Prh.Segment1 Pr_Num, --tst102        
Prh.Description,        Prh.Authorization_Status,        
Prh.Creation_Date,        Prl.Line_Num Pr_Line_Num,        
Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,        Prl.Unit_Price,  
      Prl.Item_Description,        Prl.Line_Location_Id,        
Prd.Distribution_Id,        glcc.segment2   FROM 
Apps.Po_Requisition_Headers_All Prh,   Apps.Po_Requisition_Lines_All    
 Prl,   Apps.Po_Req_Distributions_All     Prd,   
Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 'PURCHASE' 
   AND Prh.Authorization_Status = 'APPROVED'    AND 
Prh.Requisition_Header_Id = Prl.Requisition_Header_Id    AND 
Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' )
 
Plan hash value: 2128049585
 
---------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | E-Rows |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            |        |
|*  1 |  HASH JOIN                              |                            |    382 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED   | GL_CODE_COMBINATIONS       |     56 |
|*  3 |    INDEX RANGE SCAN                     | GL_CODE_COMBINATIONS_N6    |     33 |
|   4 |   NESTED LOOPS                          |                            |    401K|
|   5 |    NESTED LOOPS                         |                            |    401K|
|   6 |     NESTED LOOPS                        |                            |    397K|
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_HEADERS_ALL |    162K|
|*  8 |       INDEX RANGE SCAN                  | PO_REQUISITION_HEADERS_N3  |   1183K|
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| PO_REQUISITION_LINES_ALL   |      2 |
|* 10 |       INDEX RANGE SCAN                  | PO_REQUISITION_LINES_U2    |      2 |
|* 11 |     INDEX RANGE SCAN                    | PO_REQ_DISTRIBUTIONS_N1    |      1 |
|  12 |    TABLE ACCESS BY INDEX ROWID          | PO_REQ_DISTRIBUTIONS_ALL   |      1 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   2 - filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
   3 - access("GLCC"."SEGMENT6"='03271')
   7 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
   8 - access("PRH"."AUTHORIZATION_STATUS"='APPROVED')
  10 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  11 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=0)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

***********************************

If you can explain me why would oracle predict so wrong in one case and predicts fine in another case and how dynamic sampling hint helps optimize and is there any criteria to decide level of dynamic sampling.

Just curious with hint i still see in execution plan "dynamic sampling (level=0)" but i passed it as 11.

Thanks you very much for your help and making our life easier with those tips and helps.
Cheers.

Ambuj
Chris Saxon
June 15, 2020 - 2:37 pm UTC

When comparing estimates to actuals, the formula to use is:

estimated rows * starts ~ actual rows

So the estimates are good for lines 9-12. Although the estimates are 1-2 rows, these operations started nearly 3 million times. Roughly the same as the actual number of rows.

The huge underestimate comes at lines 7-8:

-----------------------------------------------------------------------------------------
| Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------
...
| PO_REQUISITION_HEADERS_ALL |      1 |    162K|   1172K|00:00:25.61 |   86462 |  55192 |
| PO_REQUISITION_HEADERS_N3  |      1 |    163K|   1172K|00:00:04.94 |    9042 |   6466 |


You're getting nearly 10x as many rows as the estimate! This is also ~90% of the rows in that table.

So I'm guessing there's no histogram on this column. Adding one may help the optimizer find the better plan.

Looking at the buffers, this is slightly more logical I/O than full scanning PO_REQ_DISTRIBUTIONS_ALL. These are index lookups, which are slower than full scans. Which explains why the second plan is so much slower.

Just curious with hint i still see in execution plan "dynamic sampling (level=0)" but i passed it as 11.

Level 11 really means:

The database determines automatically if dynamic statistics are required

Ambuj Kumar, June 15, 2020 - 10:11 pm UTC

Thanks Chris,

I found another way also we achieve the faster result in case of 2nd(slower query) with command.

I am using "with" command with /*+ materialize */ hint to the query.
In "with" command i am running the 1st query which is faster and hint will load this to memory(since i know this only fetches smaller set of data and it will not overflow memory)
and outside that i just querying that view with filter "Authorization_Status = 'APPROVED'" and this giving me exact same performance as 1st query.

Query
***************************
WITH pr AS (SELECT /*+ gather_plan_statistics materialize */ Prh.Segment1 Pr_Num,
       Prh.Description,
       Prh.Authorization_Status,
       Prh.Creation_Date,
       Prl.Line_Num Pr_Line_Num,
       Prl.Unit_Meas_Lookup_Code,
       Prl.Quantity,
       Prl.Unit_Price,
       Prl.Item_Description,
       Prl.Line_Location_Id,
       Prd.Distribution_Id,
       glcc.segment2
  FROM Apps.Po_Requisition_Headers_All Prh,
  Apps.Po_Requisition_Lines_All     Prl,
  Apps.Po_Req_Distributions_All     Prd,
  Apps.Gl_Code_Combinations Glcc
 WHERE Prh.Type_Lookup_Code = 'PURCHASE'
   AND Prh.Requisition_Header_Id = Prl.Requisition_Header_Id
   AND Prd.Requisition_Line_Id = Prl.Requisition_Line_Id
   AND Glcc.Code_Combination_Id = Prd.Code_Combination_Id
   AND Glcc.Segment2 IN ('12145', '12155')
   AND (Glcc.Segment6  ='03271' ))
   
   SELECT * FROM pr
   WHERE Authorization_Status = 'APPROVED'
***************************


Below is the execution plan:

**********************************
SQL_ID  0hztsqb6qj0sb, child number 0
-------------------------------------
WITH pr AS (SELECT /*+ gather_plan_statistics materialize */ 
Prh.Segment1 Pr_Num,          Prh.Description,        
Prh.Authorization_Status,        Prh.Creation_Date,        Prl.Line_Num 
Pr_Line_Num,        Prl.Unit_Meas_Lookup_Code,        Prl.Quantity,     
   Prl.Unit_Price,        Prl.Item_Description,        
Prl.Line_Location_Id,        Prd.Distribution_Id,        glcc.segment2  
 FROM Apps.Po_Requisition_Headers_All Prh,   
Apps.Po_Requisition_Lines_All     Prl,   Apps.Po_Req_Distributions_All  
   Prd,   Apps.Gl_Code_Combinations Glcc  WHERE Prh.Type_Lookup_Code = 
'PURCHASE'    AND Prh.Requisition_Header_Id = Prl.Requisition_Header_Id 
   AND Prd.Requisition_Line_Id = Prl.Requisition_Line_Id    AND 
Glcc.Code_Combination_Id = Prd.Code_Combination_Id    AND Glcc.Segment2 
IN ('12145', '12155')    AND (Glcc.Segment6  ='03271' ))     SELECT * 
FROM pr    WHERE Authorization_Status = 'APPROVED'
 
Plan hash value: 4186435202
 
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |      1 |        |    166 |00:00:02.51 |   83297 |  65612 |      5 |
|   1 |  TEMP TABLE TRANSFORMATION                |                             |      1 |        |    166 |00:00:02.51 |   83297 |  65612 |      5 |
|   2 |   LOAD AS SELECT                          |                             |      1 |        |      0 |00:00:02.51 |   83284 |  65594 |      5 |
|   3 |    NESTED LOOPS                           |                             |      1 |    122 |    170 |00:00:02.14 |   83275 |  65594 |      0 |
|   4 |     NESTED LOOPS                          |                             |      1 |    122 |    170 |00:00:02.11 |   83103 |  65516 |      0 |
|   5 |      NESTED LOOPS                         |                             |      1 |    122 |    170 |00:00:02.11 |   82769 |  65444 |      0 |
|*  6 |       HASH JOIN                           |                             |      1 |    122 |    170 |00:00:02.10 |   82260 |  65280 |      0 |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS        |      1 |      2 |     18 |00:00:00.01 |      21 |      0 |      0 |
|*  8 |         INDEX RANGE SCAN                  | GL_CODE_COMBINATIONS_CAT    |      1 |      2 |     18 |00:00:00.01 |       3 |      0 |      0 |
|   9 |        TABLE ACCESS FULL                  | PO_REQ_DISTRIBUTIONS_ALL    |      1 |   3240K|   3242K|00:00:02.81 |   82239 |  65280 |      0 |
|  10 |       TABLE ACCESS BY INDEX ROWID         | PO_REQUISITION_LINES_ALL    |    170 |      1 |    170 |00:00:00.09 |     509 |    164 |      0 |
|* 11 |        INDEX UNIQUE SCAN                  | PO_REQUISITION_LINES_U1     |    170 |      1 |    170 |00:00:00.04 |     337 |     86 |      0 |
|* 12 |      INDEX UNIQUE SCAN                    | PO_REQUISITION_HEADERS_U1   |    170 |      1 |    170 |00:00:00.03 |     334 |     72 |      0 |
|* 13 |     TABLE ACCESS BY INDEX ROWID           | PO_REQUISITION_HEADERS_ALL  |    170 |      1 |    170 |00:00:00.03 |     172 |     78 |      0 |
|* 14 |   VIEW                                    |                             |      1 |    121 |    166 |00:00:00.01 |       8 |     18 |      0 |
|  15 |    TABLE ACCESS FULL                      | SYS_TEMP_0FD9D66AC_39C97301 |      1 |    121 |    170 |00:00:00.01 |       8 |     18 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("GLCC"."CODE_COMBINATION_ID"="PRD"."CODE_COMBINATION_ID")
   8 - access("GLCC"."SEGMENT6"='03271')
       filter(("GLCC"."SEGMENT2"='12145' OR "GLCC"."SEGMENT2"='12155'))
  11 - access("PRD"."REQUISITION_LINE_ID"="PRL"."REQUISITION_LINE_ID")
  12 - access("PRH"."REQUISITION_HEADER_ID"="PRL"."REQUISITION_HEADER_ID")
  13 - filter("PRH"."TYPE_LOOKUP_CODE"='PURCHASE')
  14 - filter("AUTHORIZATION_STATUS"='APPROVED')
 

**********************************



I think /*+ materialize */ hint is not documented but it is giving me good results with this.
Let me know what you think.

About histogram i need to talk to DBAs, you know they ask so many questions.
What you think for quick fix which one would be better dynamic sampling hint or materialize hint.


Ambuj
Connor McDonald
June 16, 2020 - 2:22 am UTC

nice work. If this approach is working, I see no reason not to persist with it. Its a fairly commonly used technique
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.