Re: Cause behind execution plan change

  • From: Pap <oracle.developer35@xxxxxxxxx>
  • To: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
  • Date: Tue, 20 Jul 2021 18:07:28 +0530

Thank You Jonathan and Lok. Attaching again the query along with the
outline and note section.

I am seeing one usage of INTERNAL_FUNCTION around FFT.STCD but in the new
plan(post function change), I am seeing two more usage of INTERNAL_FUNCTION
around the ND.NE column. These columns are the same with respect to the
data type in both sides of the predicate, why are these appearing and if
anyway these are responsible for some wrong estimation?

I had checked the dba_hist_sqlstat but didn't see any profiles attached for
the old sql and als checked the plan from display_awr and the note section
was only showing below i.e. usage of dynamic sampling only and nothing
regarding sql profile or plan baselines either. But then when I query
dba_sql_plan_baselines manually with the sql_text like '%...sample query
text...%', I saw one entry there with ACCEPTED and ENABLED both columns set
as 'YES'. And also the signature is matching with the query
force_matching_signature. And I can see the last_executed column was also
showing the date close to when we introduced the new modified sql into
prod. So it seems this was the one getting used for old sql/query but the
note section does not state that.

 So is it true that it may be possible that the note section of the
display_awr function won't show the usage of profile/baseline but still it
may be used by that query internally?

 Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

On Mon, Jul 19, 2021 at 12:47 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:


You shouldn't be using the ORDERED hint, by the way, you should learn how
to use the LEADING() hint.
And since you've dictated the join order  for this query FT does not need
to be in the USE_NL() hint because it's the first table in the join order
so it's not going to appear as the second table in any of the joins. (See:
https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/ , and for the
equivalent comment on the use_hash() hint see:
https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/ ;)


Regards
Jonathan Lewis



On Sun, 18 Jul 2021 at 20:35, Pap <oracle.developer35@xxxxxxxxx> wrote:

Hello listers,  It's version 12.1.0.2.0 of oracle. We have done a change
to the code inside the function which gets called from the SELECT query.
But as its just been used in the SELECT part of the query ideally it should
not change sql_id of the query and also the plan, but we also add one new
additional input parameter(i.e. :B3 below) to the function call and thus
sql_id got changed which is understood. But something which we are not able
to understand is , why did the plan change occurred after this change?

Attached is both the plans i.e the one it used to take in the past vs the
current one which it's now taking. From the plan it does look like , its
cardinality estimation of global temporary table FT which causes the
difference, as it puts table RTNID in index access vs FTS access in a
nested loop. But the old query(before function change) was not taking the
bad plan ever, but it started taking after function change. So wondering
how a new input parameter addition to a function which is not part of the
WHERE clause, can cause this sort of impact and how to fix it?

In this query, all the tables are global temporary tables except FFT,
which is a list partition table with partition key as CKEY.

INSERT INTO RTF(...)
SELECT /*+ ordered use_nl(ft FFT nd curr)*/   ND.NE, ND.NID,  CUR.SCD,
FT.FXID, FT.TFXID,
          fun1 (FFT.AMT, FT.STS,  FT.PDT,    :B3, TRUNC ( :B2),   'S'),
          fun1 (FFT.AMT,  FT.STS,  FT.PDT,   :B3,   TRUNC ( :B2), 'F'),
          TRUNC ( :B1),
          ND.MCID
     FROM FT , FFT , RTNID ND, RDCUR CUR
    WHERE     FT.FFXID = FFT.FXID
          AND FT.ACK = FFT.CK
          AND FFT.CKEY = ND.NKEY
          AND ND.NE IN ('XX', 'YY', 'ZZ')
          AND FFT.STCD IN ('X', 'Y')
          AND FFT.CKEY = CUR.CKEY





INSERT INTO RTF(...)
SELECT /*+ ordered use_nl(ft FFT nd curr)*/   ND.NE, ND.NID,  CUR.SCD, FT.FXID, 
FT.TFXID,
          fun1 (FFT.AMT, FT.STS,  FT.PDT,    :B3, TRUNC ( :B2),   'S'),
          fun1 (FFT.AMT,  FT.STS,  FT.PDT,   :B3,   TRUNC ( :B2), 'F'),
          TRUNC ( :B1),
          ND.MCID
     FROM FT , FFT , RTNID ND, RDCUR CUR
    WHERE     FT.FFXID = FFT.FXID
          AND FT.ACK = FFT.CK
          AND FFT.CKEY = ND.NKEY
          AND ND.NE IN ('XX', 'YY', 'ZZ')
          AND FFT.STCD IN ('X', 'Y')
          AND FFT.CKEY = CUR.CKEY



*************Below is the plan it used to take 
before*****************************

Global Information
------------------------------
 STATUS                                 :  DONE                      
 Instance ID                            :  3                         
 SQL Execution ID                       :  50617726                  
 Execution Started                      :  07/18/2021 14:15:23       
 First Refresh Time                     :  07/18/2021 14:15:26       
 Last Refresh Time                      :  07/18/2021 14:16:03       
 Duration                               :  40s                       
                

Global Stats
==============================================================================
| Elapsed |   Cpu   |    IO    | Cluster  | PL/SQL  | Buffer | Read  | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) |  Gets  | Reqs  | Bytes |
==============================================================================
|      44 |      24 |       19 |     0.66 |    1.37 |   778K | 51848 | 405MB |
==============================================================================

SQL Plan Monitoring Details (Plan Hash Value=3120541595)
=====================================================================================================================================================================================================================
| Id |                     Operation                     |             Name     
         |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | 
Read  | Activity |           Activity Detail            |
|    |                                                   |                      
         | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | 
Bytes |   (%)    |             (# samples)              |
=====================================================================================================================================================================================================================
|  0 | INSERT STATEMENT                                  |                      
         |         |      |        38 |     +3 |     1 |        0 |       |     
  |          |                                      |
|  1 |   LOAD TABLE CONVENTIONAL                         | RTF                  
         |         |      |        38 |     +3 |     1 |        0 |    83 | 
664KB |    12.50 | Cpu (5)                              |
|  2 |    NESTED LOOPS                                   |                      
         |      81 | 116K |        38 |     +3 |     1 |     386K |       |     
  |          |                                      |
|  3 |     NESTED LOOPS                                  |                      
         |      81 | 116K |        38 |     +3 |     1 |     386K |       |     
  |          |                                      |
|  4 |      NESTED LOOPS                                 |                      
         |      81 | 116K |        38 |     +3 |     1 |     386K |       |     
  |          |                                      |
|  5 |       NESTED LOOPS                                |                      
         |    5558 | 110K |        38 |     +3 |     1 |       7M |       |     
  |          |                                      |
|  6 |        TABLE ACCESS STORAGE FULL                  | FT                   
         |    4231 |   19 |        38 |     +3 |     1 |     3976 |       |     
  |          |                                      |
|  7 |        PARTITION LIST ITERATOR                    |                      
         |       1 |   26 |        38 |     +3 |  3976 |       7M |       |     
  |          |                                      |
|  8 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFT                  
         |       1 |   26 |        40 |     +1 |  3333 |       7M | 43942 | 
343MB |    62.50 | Cpu (10)                             |
|    |                                                   |                      
         |         |      |           |        |       |          |       |     
  |          | cell single block physical read (15) |
|  9 |          INDEX RANGE SCAN                         | FFT_IX7              
         |     818 |    4 |        38 |     +3 |  3333 |       7M |  7823 |  
61MB |     7.50 | Cpu (1)                              |
|    |                                                   |                      
         |         |      |           |        |       |          |       |     
  |          | cell single block physical read (2)  |
| 10 |       TABLE ACCESS BY INDEX ROWID BATCHED         | RTNID                
         |       1 |    2 |        38 |     +3 |    7M |     386K |       |     
  |     2.50 | Cpu (1)                              |
| 11 |        INDEX RANGE SCAN                           | RTNID_IX6            
         |       1 |    1 |        38 |     +3 |    7M |     386K |       |     
  |    12.50 | Cpu (5)                              |
| 12 |      INDEX UNIQUE SCAN                            | RDCUR_PK             
         |       1 |      |        38 |     +3 |  386K |     386K |       |     
  |          |                                      |
| 13 |     TABLE ACCESS BY INDEX ROWID                   | RDCUR                
         |       1 |    1 |        38 |     +3 |  386K |     386K |       |     
  |     2.50 | Cpu (1)                              |
=====================================================================================================================================================================================================================


Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter(("FT"."ACK"="FFT"."CK" AND INTERNAL_FUNCTION("FFT"."STCD")))
   9 - access("FT"."FFXID"="FFT"."FXID")
  10 - filter(("ND"."NE"='YY' OR "ND"."NE"='XX' OR "ND"."NE"='ZZ'))
  11 - access("FFT"."CKEY"="ND"."NKEY")
  12 - access("FFT"."CKEY"="CUR"."CKEY")

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
      OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "RTF"@"INS$1")
      FULL(@"SEL$1" "FT"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "FFT"@"SEL$1" ("FFT"."FXID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "FFT"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ND"@"SEL$1" ("ND"."NKEY"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "ND"@"SEL$1")
      INDEX(@"SEL$1" "CUR"@"SEL$1" ("CUR"."CKEY"))
      LEADING(@"SEL$1" "FT"@"SEL$1" "FFT"@"SEL$1" "ND"@"SEL$1" "CUR"@"SEL$1")
      USE_NL(@"SEL$1" "FFT"@"SEL$1")
      USE_NL(@"SEL$1" "ND"@"SEL$1")
      USE_NL(@"SEL$1" "CUR"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "CUR"@"SEL$1")
      END_OUTLINE_DATA
  */
 
 
 Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   
   
 
 
 *************Below is the plan it started opting post function 
change*****************************
 
Global Information
------------------------------
 STATUS                                 :  EXECUTING                 
 Instance ID                            :  3                         
 SQL Execution ID                       :  50617607                  
 Execution Started                      :  07/18/2021 11:58:06       
 First Refresh Time                     :  07/18/2021 11:58:13       
 Last Refresh Time                      :  07/18/2021 13:40:03       
 Duration                               :  6117s                     


Global Stats
===========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Cluster  | PL/SQL  | Buffer | 
Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Time(s) |  Gets  | 
Reqs | Bytes |
===========================================================================================
|    6126 |    5941 |      185 |        0.00 |     0.04 |    6.09 |     2G | 
347K |   3GB |
===========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=4015732212)
=========================================================================================================================================================================================================================
| Id    |                     Operation                     |             Name  
            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | 
Read  | Activity |            Activity Detail            |
|       |                                                   |                   
            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | 
Bytes |   (%)    |              (# samples)              |
=========================================================================================================================================================================================================================
|     0 | INSERT STATEMENT                                  |                   
            |         |      |      6101 |     +7 |     1 |        0 |       |  
     |          |                                       |
|     1 |   LOAD TABLE CONVENTIONAL                         | RTF               
            |         |      |      6101 |     +7 |     1 |        0 |    54 | 
432KB |     0.31 | Cpu (19)                              |
|     2 |    NESTED LOOPS                                   |                   
            |       1 | 5005 |      6101 |     +7 |     1 |     760K |       |  
     |     0.02 | Cpu (1)                               |
|     3 |     NESTED LOOPS                                  |                   
            |       1 | 5005 |      6101 |     +7 |     1 |     760K |       |  
     |     0.02 | Cpu (1)                               |
|  -> 4 |      NESTED LOOPS                                 |                   
            |       1 | 5004 |      6113 |     +7 |     1 |     760K |       |  
     |     0.02 | Cpu (1)                               |
|  -> 5 |       NESTED LOOPS                                |                   
            |     259 | 4932 |      6113 |     +7 |     1 |      13M |       |  
     |          |                                       |
|  -> 6 |        TABLE ACCESS STORAGE FULL                  | FT                
            |     197 |    3 |      6113 |     +7 |     1 |     4051 |       |  
     |          |                                       |
|  -> 7 |        PARTITION LIST ITERATOR                    |                   
            |       1 |   25 |      6113 |     +7 |  4052 |      13M |       |  
     |          |                                       |
|  -> 8 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFT               
            |       1 |   25 |      6113 |     +7 |  4048 |      13M |  295K |  
 2GB |     3.52 | Cpu (30)                              |
|       |                                                   |                   
            |         |      |           |        |       |          |       |  
     |          | latch: gc element (1)                 |
|       |                                                   |                   
            |         |      |           |        |       |          |       |  
     |          | cell multiblock physical read (1)     |
|       |                                                   |                   
            |         |      |           |        |       |          |       |  
     |          | cell single block physical read (182) |
|  -> 9 |          INDEX RANGE SCAN                         | FFT_IX7           
            |     818 |    4 |      6113 |     +7 |  4048 |      13M | 52161 | 
408MB |     0.76 | Cpu (14)                              |
|       |                                                   |                   
            |         |      |           |        |       |          |       |  
     |          | cell single block physical read (32)  |
| -> 10 |       TABLE ACCESS STORAGE FULL                   | RTNID             
            |       1 |      |      6118 |     +2 |   13M |     760K |       |  
     |    95.33 | Cpu (5793)                            |
| -> 11 |      INDEX UNIQUE SCAN                            | RDCUR_PK          
            |       1 |      |      6113 |     +7 |  760K |     760K |       |  
     |          |                                       |
|    12 |     TABLE ACCESS BY INDEX ROWID                   | RDCUR             
            |       1 |    1 |      6101 |     +7 |  760K |     760K |       |  
     |     0.02 | Cpu (1)                               |
=========================================================================================================================================================================================================================
                 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter(("FT"."ACK"="FFT"."CK" AND INTERNAL_FUNCTION("FFT"."STCD")))
   9 - access("FT"."FFXID"="FFT"."FXID")
  10 - storage(("FFT"."CKEY"="ND"."NKEY" AND INTERNAL_FUNCTION("ND"."NE")))
       filter(("FFT"."CKEY"="ND"."NKEY" AND INTERNAL_FUNCTION("ND"."NE")))
  11 - access("FFT"."CKEY"="CUR"."CKEY")
  
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
      OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "RTF"@"INS$1")
      FULL(@"SEL$1" "FT"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "FFT"@"SEL$1" ("FFT"."FXID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "FFT"@"SEL$1")
      FULL(@"SEL$1" "ND"@"SEL$1")
      INDEX(@"SEL$1" "CUR"@"SEL$1" ("CUR"."CKEY"))
      LEADING(@"SEL$1" "FT"@"SEL$1" "FFT"@"SEL$1" "ND"@"SEL$1" "CUR"@"SEL$1")
      USE_NL(@"SEL$1" "FFT"@"SEL$1")
      USE_NL(@"SEL$1" "ND"@"SEL$1")
      USE_NL(@"SEL$1" "CUR"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "CUR"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   
 

Other related posts: