Mohamed Houri’s Oracle Notes

April 16, 2017

12cR2: unreproducible SPM and cursor bind awareness

Filed under: Sql Plan Managment — hourim @ 4:53 pm

In the previous article we knew that, as of Oracle 12cR2, a cursor can’t be kept bind aware when it is protected by a single enabled and accepted SPM plan baseline. In other words, the preceding article explained that if you want a cursor to be both bind aware and protected against any unexpected execution plan change, you need then to have at least two enabled and accepted SPM baselines for this cursor.

That’s said multiple enabled and accepted SPM plan doesn’t necessarily means multiple enabled, accepted and reproducible SPM plans. And, as this article will demonstrate it, this reproducible path seems to have been ignored by the Oracle optimizer group when disabling the bind awareness property explained in the previous article. The new 12cR2 internal code disabling and enabling ACS seems to be based only on the accepted and enabled property of the bind aware cursor SPM plan. Let’s demonstrate this with a simple example

Setting the Scenes

-- create table, index and gather statistics
create table t1
    (col1  number
    ,col2  varchar2(50)
    ,flag  varchar2(2));
 
 insert into t1
    select rownum
          ,lpad('X',50,'X')
          ,case when rownum = 1
            then 'Y1'
               when rownum = 2
            then 'Y2'
               when mod(rownum,2) = 0
            then 'N1'
            else 'N2'
           end
    from   dual
connect by rownum <= 100000;
 
create index i1 on t1(flag);

BEGIN 
  dbms_stats.gather_table_stats
      (user
     ,'t1'
     ,method_opt => 'for all columns size skewonly' 
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

-- generate a bind aware cursor
alter session set cursor_sharing=force;

select count(*), max(col2) from t1 where flag = 'Y1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

Observation

The above script will generate a bind sensitive and bind aware cursor as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y Y          1             0 VALID              
6fbvysnhkvugw            2 Y Y          1             0 VALID 

Let’s load these two execution plans (child number 1 and 2) into a SPM baseline and make few verifications:

declare
   rs pls_integer;
begin
   rs := dbms_spm.load_plans_from_cursor_cache('6fbvysnhkvugw');
end;
/

col plan_name format a30
select
  plan_name ,origin, enabled
from dba_sql_plan_baselines;

PLAN_NAME                      ORIGIN                        ENA
------------------------------ ----------------------------- ---
SQL_PLAN_d9tch6banyzg9616acf47 MANUAL-LOAD-FROM-CURSOR-CACHE YES
SQL_PLAN_d9tch6banyzg98576eb1f MANUAL-LOAD-FROM-CURSOR-CACHE YES

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------------
6fbvysnhkvugw            1 Y Y          1             1 INVALID_UNAUTH     
6fbvysnhkvugw            2 Y Y          1             1 INVALID_UNAUTH

As you can see the creation of the two SPM plans has already invalidated the two underlying child cursors making them both not anymore shareable for the next execution(INVALID_UNAUTHORIZED).

Now that we have a bind aware cursor protected by two enabled and accepted SPM plans let’s launch it a couple of time in order see what will happen:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  6fbvysnhkvugw, child number 0
-------------------------------------
 
Plan hash value: 3625400295
-----------------------------------------------------
| Id  | Operation                    | Name | Rows  | 
-----------------------------------------------------
|   0 | SELECT STATEMENT             |      |       | 
|   1 |  SORT AGGREGATE              |      |     1 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 | 
|*  3 |    INDEX RANGE SCAN          | I1   |     1 | 
-----------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg98576eb1f used for this statement

As expected the apparition of the child number 0 above means that Oracle has effectively flushed out the previous couple of bind aware child cursor (1,2). We need now to go again through a warming up period for ACS to kick in as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y N          1             1 VALID     

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y Y          1             1 VALID              
6fbvysnhkvugw            3 Y Y          1             0 VALID    

We’ve, finally, succeeded building a situation with which we can proof that the Oracle optimizer group has ignored disabling ACS even when it is protected by a single reproducible SPM plan as the following will demonstrate:

-- disabling the i1 index so that the SPM plan SQL_PLAN_d9tch6banyzg98576eb1f 
-- will be irreproducible

alter index i1 invisible;

Setting the i1 index invisible, the index range scan SPM plan will inevitably be ignored by the CBO because it is not anymore reproducible. The Full table scan SPM plan will become the unique enforced execution plan whatever the bind variable selectivity will be as shown below:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  6fbvysnhkvugw, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :"SYS_B_0"
 
Plan hash value: 3724264953
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    54 |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

When Oracle has detected that the index range scan SPM plan is not anymore reproducible it did two things:

a. invalidated and flushed out both index scan (child number 3) and full table scan(child number 2) cursor
b. created a new bind sensitive and not yet bind aware cursor n°2 as proofed below:

select
   sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y N          1             2 VALID  

While officially there are still two accepted and enabled SPM plan baselines, in fact, only one SPM plan can be effectively be used to guarantee unpredictable performance degradation. So why Oracle has not disabled the bind sensitiveness property of this cursor? Is Oracle not claiming that it is useless to keep ACS working in presence of a single SPM plan?

select
   child_number
  ,bucket_id
  ,count
from
   gv$sql_cs_histogram
where
   sql_id = '6fbvysnhkvugw'
and child_number = '2';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           2          0          1
           2          1          0
           2          2          0

Given the above situation we need only two extra executions of the same query at bucket_id n°1 or 2 and the cursor will become bind aware as shown below:

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';

SQL> select
        sql_id
      ,child_number
      ,is_bind_sensitive 
      ,is_bind_aware
      ,executions
      ,invalidations
      ,object_status
    from
      gv$sql 
   where
     sql_id = '6fbvysnhkvugw'
   and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            3 Y Y          1             1 VALID  

Summary

The developer at Oracle corporation has not considered stopping a cursor from being bind aware when one of its two SPM plans is indirectly rendered irreproducible. The internal code implemented by Oracle when deciding to stop ACS seems to be based only the Enabled and Accepted property of the corresponding SPM plan. Unfortunately an accepted and enabled SPM plan is not guaranteed to be reproducible. This is why we can still find, in 12cR2, a bind aware cursor protected by a single SPM plan.

March 17, 2017

12cR2 : Capturing SPM plans from AWR

Filed under: Oracle,Sql Plan Managment — hourim @ 6:45 pm

A couple of years ago I set myself a best practice goal of preferring SPM baselines over SQL-Profiles. I must recognize that I failed to achieve this goal. Fortunately as of Oracle 12cR2 it becomes possible to load SPM baselined plans directly from AWR tables. This is why I am now unexcused to do not operate the switch.

Let’s see first how we can load SPM plans using AWR historical tables

Setting the Scenes

create table t_acs(n1 number, n2 number);

BEGIN
 for j in 1..1200150 loop
  if j = 1 then
    insert into t_acs values (j, 1);
  elsif j>1 and j<=101 then
    insert into t_acs values(j, 100);
  elsif j>101 and j<=1101 then
    insert into t_acs values (j, 1000);
  elsif j>10001 and j<= 110001 then
    insert into t_acs values(j,10000); 
  else
    insert into t_acs values(j, 1000000); end if;
 end loop; 
commit;
END;
/

create index t_acs_i1 on t_acs(n2);

BEGIN 
  dbms_stats.gather_table_stats
     (user
     ,'t_acs'
     ,method_opt => 'for all columns size skewonly' 
     ,cascade => true
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

exec dbms_workload_repository.create_snapshot;
var ln2 number;
exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 1e6
select count(1) from t_acs where n2 = :ln2;
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

exec dbms_workload_repository.create_snapshot;

If you copy and past the above SQL code into a SQL PLUS session and issue the following select you should find that you have already two bind aware cursors:

select 
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
from
   v$sql 
where
   sql_id = 'f2pmwazy1rnfd'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS
------------- ------------ - - ----------
f2pmwazy1rnfd            1 Y Y          1
f2pmwazy1rnfd            2 Y Y          1

The bind awareness property of the above cursor has nothing to do with the bottom line of this article. It is here just because I will use the same model in my next article where this time this particular property becomes necessary.

If I would have decided to create a SQL profile over the above cursor I would have then opted for Carlos Sierra coe_xfr_sql_profile.sql script as shown below:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: f2pmwazy1rnfd


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      535703726 ,054       
     1882749816 ,085       

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1882749816

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "f2pmwazy1rnfd"
PLAN_HASH_VALUE: "1882749816"

Execute coe_xfr_sql_profile_f2pmwazy1rnfd_1882749816.sql
on TARGET system in order to create a custom SQL Profile
with plan 1882749816 linked to adjusted sql_text.

SQL>@coe_xfr_sql_profile_f2pmwazy1rnfd_1882749816.sql
... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_f2pmwazy1rnfd_1882749816 completed

As such the next time I will run this query it will use the fixed SQL Profile as shown below:

SQL> select count(1) from t_acs where n2 = :ln2;

SQL> select * from table(dbms_xplan.display_cursor);

Plan hash value: 1882749816                                                     
                                                                                
------------------------------------------------------ 
| Id  | Operation         | Name     | Rows  | Bytes |  
------------------------------------------------------ 
|   0 | SELECT STATEMENT  |          |       |       |  
|   1 |  SORT AGGREGATE   |          |     1 |     3 |   
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |   300 |  
------------------------------------------------------
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                                                                                         
   2 - access("N2"=:LN2)                                                        
                                                                                
Note                                                                            
-----                                                                           
- SQL profile coe_f2pmwazy1rnfd_1882749816 used for this statement

However until the arrival of 12cR2 it was impossible to load a SPM plan baseline for the above cursor using corresponding AWR historical execution plan. Hopefully it is now possible. This is below how to capture SPM plan from AWR:

SQL>@LoadSPMfromAwr.sql
Listing latest AWR snapshots ...

   SNAP_ID END_INTERVAL_TIME          
---------- ---------------------------
        14 08/03/17 13:20:09,251000000
        15 08/03/17 14:00:13,233000000
        16 08/03/17 15:07:46,465000000
        17 09/03/17 01:13:41,092000000
        18 09/03/17 12:11:26,748000000
        19 10/03/17 01:07:36,836000000
        20 10/03/17 10:08:54,214000000
        21 10/03/17 12:35:26,590000000
        22 11/03/17 01:04:40,947000000
        23 12/03/17 12:41:35,578000000
        24 12/03/17 15:03:55,730000000
        25 13/03/17 02:21:01,517000000
        26 13/03/17 12:53:22,204000000
        27 13/03/17 15:43:46,522000000
        28 14/03/17 13:13:07,716000000
        29 15/03/17 12:55:54,089000000
        30 16/03/17 12:59:39,201000000
        31 17/03/17 02:09:14,047000000
        32 17/03/17 10:38:33,520000000
        33 17/03/17 12:50:59,072000000

20 rows selected. 

Enter begin snapshot id: 14
Enter end   snapshot id: 27
Enter value for sql_filter: sql_text like ''select count(1) from t_acs%''

SQL> select 
      plan_name ,origin
    from dba_sql_plan_baselines;

PLAN_NAME                                ORIGIN                       
---------------------------------------- ---------------------
SQL_PLAN_fn4mhg52jx5z125348c47           MANUAL-LOAD-FROM-AWR         
SQL_PLAN_fn4mhg52jx5z13069e6f9           MANUAL-LOAD-FROM-AWR   


SQL> select count(1) from t_acs where n2 = :ln2;

SQL> select * from table(dbms_xplan.display_cursor);


SQL_ID  f2pmwazy1rnfd, child number 1
-------------------------------------
select count(1) from t_acs where n2 = :ln2
 
Plan hash value: 1882749816
 
------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | 
------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |
|   1 |  SORT AGGREGATE   |          |     1 |     3 | 
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |   300 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:LN2)
 
Note
-----
   - SQL profile coe_f2pmwazy1rnfd_1882749816 used for this statement
   - SQL plan baseline SQL_PLAN_fn4mhg52jx5z125348c47 used for this statement

You have to choose the snap interval that includes historical details of your sql_id

Here’s below the content of the script I used to capture SPM plan from AWR

PROMPT Listing latest AWR snapshots ...
SELECT snap_id, end_interval_time 
FROM dba_hist_snapshot 
WHERE end_interval_time > SYSDATE - 30
ORDER BY end_interval_time;

ACCEPT bsnapid NUMBER PROMPT "Enter begin snapshot id: "
ACCEPT esnapid NUMBER PROMPT "Enter end   snapshot id: "

SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

declare
  rs pls_integer;
begin
  rs := dbms_spm.load_plans_from_awr('&bsnapid', '&esnapid', '&sql_filter');
end;
/

SET TERMOUT ON PAGESIZE 5000 HEADING ON

June 23, 2015

Real Time SQL Monitoring oddity

Filed under: Oracle,Sql Plan Managment — hourim @ 1:45 pm

This is a small note about a situation I have encountered and which I thought it is worth sharing with you. There was an insert/select executing in parallel DOP 16 on a 11.2.0.3 Oracle database for which the end user was complaining about the exceptional time it was taking without completing. Since the job was still running I tried getting its Real Time SQL monitoring report:


Global Information
------------------------------
 Status              :  DONE (ERROR)        
 Instance ID         :  1                   
 Session             :  XXXXX (392:229)    
 SQL ID              :  bbccngk0nn2z2       
 SQL Execution ID    :  16777216            
 Execution Started   :  06/22/2015 11:57:06 
 First Refresh Time  :  06/22/2015 11:57:06 
 Last Refresh Time   :  06/22/2015 11:57:46 
 Duration            :  40s                 

Global Stats
=================================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
=================================================================================================
|   15315 |   15220 |       54 |        0.38 |       40 |     2G | 8601 |   2GB |  5485 |   1GB |

The insert/select according to the above report summary is DONE with (ERROR).
So why the end user is still complaining about the not ending batch job? And why he didn’t receive an error?

After having ruled out the resumable time out hypothesis I came back to the v$sql_monitor and issued the following two selects:

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = 'bbccngk0nn2z2'
  7  AND status   ='EXECUTING'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- ------------
bbccngk0nn2z2 p000  EXECUTING
bbccngk0nn2z2 p001  EXECUTING
bbccngk0nn2z2 p002  EXECUTING
bbccngk0nn2z2 p003  EXECUTING
bbccngk0nn2z2 p004  EXECUTING
bbccngk0nn2z2 p005  EXECUTING
bbccngk0nn2z2 p006  EXECUTING
bbccngk0nn2z2 p007  EXECUTING
bbccngk0nn2z2 p008  EXECUTING
bbccngk0nn2z2 p009  EXECUTING
bbccngk0nn2z2 p010  EXECUTING
bbccngk0nn2z2 p011  EXECUTING
bbccngk0nn2z2 p012  EXECUTING
bbccngk0nn2z2 p013  EXECUTING
bbccngk0nn2z2 p014  EXECUTING
bbccngk0nn2z2 p015  EXECUTING
bbccngk0nn2z2 p019  EXECUTING
bbccngk0nn2z2 p031  EXECUTING

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = 'bbccngk0nn2z2'
  7  AND status   ='DONE (ERROR)'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- -------------------
bbccngk0nn2z2 ora   DONE (ERROR)
bbccngk0nn2z2 p016  DONE (ERROR)
bbccngk0nn2z2 p017  DONE (ERROR)
bbccngk0nn2z2 p018  DONE (ERROR)
bbccngk0nn2z2 p020  DONE (ERROR)
bbccngk0nn2z2 p021  DONE (ERROR)
bccngk0nn2z2  p022  DONE (ERROR)
bbccngk0nn2z2 p023  DONE (ERROR)
bbccngk0nn2z2 p024  DONE (ERROR)
bbccngk0nn2z2 p025  DONE (ERROR)
bbccngk0nn2z2 p026  DONE (ERROR)
bbccngk0nn2z2 p027  DONE (ERROR)
bbccngk0nn2z2 p028  DONE (ERROR)
bbccngk0nn2z2 p029  DONE (ERROR)
bbccngk0nn2z2 p030  DONE (ERROR)

Among the 32 parallel servers half are executing and half are in error! How could this be possible? I have already been confronted to a parallel process that ends in its entirety when a single parallel server is in error. For example I have encountered several times the following error which is due to a parallel broadcast distribution of a high data row source exploding henceforth the TEMP tablespace:

ERROR at line 1:
ORA-12801: error signaled in parallel query server P013
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

A simple select against v$active_session_history confirmed that the insert/select is still running and it is consuming CPU

SQL> select sql_id, count(1)
  2  from gv$active_session_history
  3  where sample_time between to_date('22062015 12:30:00', 'ddmmyyyy hh24:mi:ss')
  4                    and     to_date('22062015 13:00:00', 'ddmmyyyy hh24:mi:ss')
  5  group by  sql_id
  6  order by 2 desc;

SQL_ID          COUNT(1)
------------- ----------
bbccngk0nn2z2       2545
                       4
0uuczutvk6jqj          1
8f1sjvfxuup9w          1

SQL> select decode(event,null, 'on cpu', event), count(1)
  2  from gv$active_session_history
  3  where sample_time between to_date('22062015 12:30:00', 'ddmmyyyy hh24:mi:ss')
  4                    and     to_date('22062015 13:00:00', 'ddmmyyyy hh24:mi:ss')
  5  and sql_id = 'bbccngk0nn2z2'
  6  group by  event
  7  order by 2 desc;

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------  ---------
on cpu                            5439
db file sequential read           3

SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------- ---------
on cpu                            5460
db file sequential read           3

SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------  ---------
on cpu                            5470
db file sequential read           3

And after a while


SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)   COUNT(1)
---------------------------------- ---------
on cpu                             15152
db file sequential read            9

While the parallel insert is still running I took several SQL monitoring reports of which the two followings ones:

Parallel Execution Details (DOP=16 , Servers Allocated=32)
============================================================================================
|      Name      | Type  | Server# | Elapsed |Buffer | Read  |         Wait Events         |
|                |       |         | Time(s) | Gets  | Bytes |         (sample #)          |
============================================================================================
| PX Coordinator | QC    |         |    0.48 |  2531 | 16384 |                             |
| p000           | Set 1 |       1 |    1049 |  128M |  63MB | direct path read (1)        |
| p001           | Set 1 |       2 |    1518 |  222M |  61MB |                             |
| p002           | Set 1 |       3 |     893 |  109M |  59MB |                             |
| p003           | Set 1 |       4 |    1411 |  194M |  62MB | direct path read (1)        |
| p004           | Set 1 |       5 |     460 |   64M |  62MB | direct path read (1)        |
| p005           | Set 1 |       6 |     771 |   87M | 322MB | direct path read (1)        |
|                |       |         |         |       |       | direct path read temp (5)   |
| p006           | Set 1 |       7 |     654 |   67M |  62MB | direct path read (1)        |
| p007           | Set 1 |       8 |     179 |   24M |  55MB | direct path read (1)        |
| p008           | Set 1 |       9 |    1638 |  235M |  70MB |                             |
| p009           | Set 1 |      10 |     360 |   46M |  54MB | direct path read (1)        |
| p010           | Set 1 |      11 |    1920 |  294M | 337MB | direct path read temp (6)   | --> 1920s
| p011           | Set 1 |      12 |     289 |   30M |  69MB |                             |
| p012           | Set 1 |      13 |     839 |   98M |  66MB | direct path read (1)        |
| p013           | Set 1 |      14 |     524 |   63M |  55MB |                             |
| p014           | Set 1 |      15 |    1776 |  263M |  69MB |                             |
| p015           | Set 1 |      16 |    1016 |  130M |  61MB | direct path read (1)        |
| p016           | Set 2 |       1 |    0.22 |  1166 |   3MB |                             |
| p017           | Set 2 |       2 |    1.36 |  6867 |  51MB |                             |
| p018           | Set 2 |       3 |    1.02 |  1298 |  36MB |                             |
| p019           | Set 2 |       4 |    6.71 |  2313 | 129MB | direct path read temp (2)   |
| p020           | Set 2 |       5 |    0.40 |   978 |  16MB |                             |
| p021           | Set 2 |       6 |    1.32 |  8639 |  41MB | direct path read temp (1)   |
| p022           | Set 2 |       7 |    0.18 |   896 |   2MB |                             |
| p023           | Set 2 |       8 |    0.23 |   469 |   9MB |                             | --> 0.23s
| p024           | Set 2 |       9 |    0.52 |  3635 |  19MB |                             | --> 0.52s
| p025           | Set 2 |      10 |    0.33 |  1163 |   3MB |                             |
| p026           | Set 2 |      11 |    0.65 |   260 |  31MB | db file sequential read (1) |
| p027           | Set 2 |      12 |    0.21 |  1099 |   6MB |                             |
| p028           | Set 2 |      13 |    0.58 |   497 |  20MB |                             |
| p029           | Set 2 |      14 |    1.43 |  4278 |  54MB |                             |
| p030           | Set 2 |      15 |    0.30 |  3481 |   8MB |                             |
| p031           | Set 2 |      16 |    2.86 |   517 |  91MB |                             |
============================================================================================


Parallel Execution Details (DOP=16 , Servers Allocated=32)
=============================================================================================
|      Name      | Type  | Server# | Elapsed | Buffer | Read  |         Wait Events         |
|                |       |         | Time(s) |  Gets  | Bytes |         (sample #)          |
=============================================================================================
| PX Coordinator | QC    |         |    0.48 |   2531 | 16384 |                             |
| p000           | Set 1 |       1 |    1730 |   202M |  63MB | direct path read (1)        |
| p001           | Set 1 |       2 |    2416 |   351M |  61MB |                             |
| p002           | Set 1 |       3 |    1094 |   133M |  59MB |                             |
| p003           | Set 1 |       4 |    2528 |   348M |  64MB | direct path read (1)        |
| p004           | Set 1 |       5 |     965 |   129M |  63MB | direct path read (1)        |
| p005           | Set 1 |       6 |    1089 |   129M | 322MB | direct path read (1)        |
|                |       |         |         |        |       | direct path read temp (5)   |
| p006           | Set 1 |       7 |    1459 |   165M |  62MB | direct path read (1)        |
| p007           | Set 1 |       8 |     221 |    30M |  55MB | direct path read (1)        |
| p008           | Set 1 |       9 |    2640 |   357M |  70MB |                             |
| p009           | Set 1 |      10 |     952 |   115M |  54MB | direct path read (1)        |
| p010           | Set 1 |      11 |    3117 |   471M | 337MB | direct path read temp (6)   | --> 3117s
| p011           | Set 1 |      12 |     400 |    42M |  69MB |                             |
| p012           | Set 1 |      13 |    1621 |   195M |  66MB | direct path read (1)        |
| p013           | Set 1 |      14 |    1126 |   132M |  55MB |                             |
| p014           | Set 1 |      15 |    2662 |   370M |  72MB |                             |
| p015           | Set 1 |      16 |    1194 |   147M |  61MB | direct path read (1)        |
| p016           | Set 2 |       1 |    0.22 |   1166 |   3MB |                             |
| p017           | Set 2 |       2 |    1.36 |   6867 |  51MB |                             |
| p018           | Set 2 |       3 |    1.02 |   1298 |  36MB |                             |
| p019           | Set 2 |       4 |    6.72 |   2313 | 131MB | direct path read temp (2)   |
| p020           | Set 2 |       5 |    0.40 |    978 |  16MB |                             |
| p021           | Set 2 |       6 |    1.32 |   8639 |  41MB | direct path read temp (1)   |
| p022           | Set 2 |       7 |    0.18 |    896 |   2MB |                             |
| p023           | Set 2 |       8 |    0.23 |    469 |   9MB |                             | --> 0.23s
| p024           | Set 2 |       9 |    0.52 |   3635 |  19MB |                             | --> 0.52s
| p025           | Set 2 |      10 |    0.33 |   1163 |   3MB |                             |
| p026           | Set 2 |      11 |    0.65 |    260 |  31MB | db file sequential read (1) |
| p027           | Set 2 |      12 |    0.21 |   1099 |   6MB |                             |
| p028           | Set 2 |      13 |    0.58 |    497 |  20MB |                             |
| p029           | Set 2 |      14 |    1.43 |   4278 |  54MB |                             |
| p030           | Set 2 |      15 |    0.30 |   3481 |   8MB |                             |
| p031           | Set 2 |      16 |    2.89 |    517 |  92MB |                             |
=============================================================================================

If you look carefully to the above reports you will notice that the elapsed time of the parallel servers mentioned being in ERROR (p16-p30) is not increasing in contrast to the elapsed time of the parallel servers mentioned being in EXECUTION (p0-p15) which is continuously increasing.

Thanks to Randolf Geist (again) I knew that there is a bug in Real time SQL monitoring report which occurs when a parallel server is not working for more than 30 minutes. In such a case the Real time SQL monitoring will starts showing those parallel severs in in ERROR confusing the situation.

As far as I was able to reproduce the issue I started the process again at 16h03 and I kept executing the following select from time to time having no rows for each execution

SELECT 
  sql_id,
  process_name,
  status
FROM v$sql_monitor
WHERE sql_id = '5np4u0m0h69jx' –- changed a little bit the sql_id
AND status   ='DONE (ERROR)'
ORDER BY process_name ;

no rows selected

Until at around 16h37 i.e. after 30 minutes (and a little bit more) of execution the above select started showing processes in error:

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = '5np4u0m0h69jx'
  7  AND status   ='DONE (ERROR)'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- ---------------
5np4u0m0h69jx ora   DONE (ERROR)
5np4u0m0h69jx p016  DONE (ERROR)
5np4u0m0h69jx p017  DONE (ERROR)
5np4u0m0h69jx p018  DONE (ERROR)
5np4u0m0h69jx p020  DONE (ERROR)
5np4u0m0h69jx p021  DONE (ERROR)
5np4u0m0h69jx p022  DONE (ERROR)
5np4u0m0h69jx p023  DONE (ERROR)
5np4u0m0h69jx p024  DONE (ERROR)
5np4u0m0h69jx p025  DONE (ERROR)
5np4u0m0h69jx p026  DONE (ERROR)
5np4u0m0h69jx p027  DONE (ERROR)
5np4u0m0h69jx p028  DONE (ERROR)
5np4u0m0h69jx p029  DONE (ERROR)
5np4u0m0h69jx p030  DONE (ERROR)

At the very beginning of the process several parallel servers was not running while several others were busy. And when the first parallel server (p10 in this case) reaches more than 1800 seconds (1861 seconds in this case) the real time Sql monitoring started showing the not working parallel servers in ERROR.

Bottom line: don’t be confused (as I have been) by that DONE (ERROR) status, your SQL statement might still be running consuming time and energy despite this wrong real time SQL monitoring reporting status

March 17, 2014

SPM reproducibility: changing the _optimizer_skip_scan_enabled value

Filed under: Sql Plan Managment — hourim @ 9:36 am

I have had several articles about Sql Plan Management stability and evolution. I have also been recently focusing my attention particularly on the criteria, scenarios and circumstances that impeach an enabled and accepted SPM plan to be reproducible. Below are summarized those impeachment reasons:

  1. Changing the index name
  2. Changing the index type (with particular situations for function based and reverse indexes)
  3. Changing the index leading column(s)

Then I embarked on the investigation of the reaction a SPM plan could manifest, during a query execution time, to a change in one of the environment parameters that have been used during the SPM plan capture. I have investigated two situations in this context

  1. One for a change in an optimizer parameter (optimizer mode) value
  2. And the other one for a NLS parameter (nls_sort) change

For the optimizer mode I came up to a conclusion that a change of that mode in the current environment will not have an effect on the reproducibility of the SPM plan as far as this one will be reproduced using the optimizer mode stored against the SPM plan.

However, an nls_sort parameter change reveals to be a serious threat for the SPM reproducibility as far the CBO will use in this particular case the current nls_sort parameter value which might end up with an impossibility to reproduce the stored SPM plan.

Despite the above investigations, there is one thing that I wanted to investigate the effect it has on the reproducibility of a SPM plan: a change of an undocumented optimizer parameter value. This post is for that purpose. Follow me please

 create table t1
    (col1  number
    ,col2  varchar2(50)
    ,flag  varchar2(2));

 insert into t1
    select rownum
          ,lpad('X',50,'X')
          ,case when rownum = 1
            then 'Y1'
               when rownum = 2
            then 'Y2'
               when mod(rownum,2) = 0
            then 'N1'
            else 'N2'
           end
    from   dual
connect by rownum <= 100000;

create index i1 on t1(col1,flag);

exec dbms_stats.gather_table_stats(user ,'t1');

A simple model with an index which, as we will see later, has been engineered so that it will be skip scanned. The next lines of code will store a SPM plan for a given query using default optimizer parameters

SQL> var n varchar2(2);
SQL> exec :n := 'Y1'

SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

SQL> select count(1) from t1 where flag = :n;

SQL> /

SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

SQL> select count(1) from t1 where flag = :n;

SQL_ID  5k94675mwqz5j, child number 0
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 2775586896
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    67 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |     3 |            |          |
|*  2 |   INDEX SKIP SCAN| I1   | 25000 | 75000 |    67   (2)| 00:00:01 |
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."COL1" "T1"."FLAG"))
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------
1 - :N (VARCHAR2(30), CSID=873): 'Y1'

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
   filter("FLAG"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]

Note
-----
- SQL plan baseline SQL_PLAN_4njm93y44xuu38bc11ac1 used for this statement

A simple query constrained by a SPM plan using index skip scan access.

Having presented the model  I can now start my real investigations which can be summarize via this simple question : if I alter my current environment so that I will disable the use of index skip scan, will my query still be using the SPM plan? Or in more subtle and clear words: will the SPM plan be reproducible under such a change of an undocumented parameter value?

And the answer is : see below

SQL> alter session set "_optimizer_skip_scan_enabled"=FALSE;

SQL> alter session set optimizer_use_sql_plan_baselines = FALSE;

I have altered the hidden parameter and put on hold for a moment the use of sql plan baseline in order to show you that under this circumstances the CBO will come up with a new plan as shown below:

SQL> select count(1) from t1 where flag = :n;

SQL_ID  5k94675mwqz5j, child number 3
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 129980005
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    71 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I1   | 25000 | 75000 |    71   (6)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

There is now a new plan using an INDEX FAST FULL SCAN since I have disabled the use of INDEX SKIP SCAN. It is a clear indication that under this undocumented parameter change the CBO is not able to reproduce the index skip scan plan. Let’s see then what happen to our query.

SQL> alter session set optimizer_use_sql_plan_baselines = TRUE;

SQL> select count(1) from t1 where flag = :n;

---------------------------------------------------
SQL_ID  5k94675mwqz5j, child number 5
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 2775586896
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    67 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |     3 |            |          |
|*  2 |   INDEX SKIP SCAN| I1   | 25000 | 75000 |    67   (2)| 00:00:01 |
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."COL1" "T1"."FLAG"))
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------
1 - :N (VARCHAR2(30), CSID=873): 'Y1'

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
   filter("FLAG"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]

Note
-----
- SQL plan baseline SQL_PLAN_4njm93y44xuu38bc11ac1 used for this statement

The CBO has been able to reproduce the SPM INDEX SKIP SCAN plan.

Bottom line: if the CBO has been able to reproduce the SPM INDEX SKIP SCAN plan under a disabled _optimizer_skip_scan_enabled parameter in the query execution environment, this means that the CBO will use the value of that hidden parameter stored during the SPM plan capture and not the value this parameter has in the current execution environment.

February 14, 2014

What optimizer mode is used during the re-cost of accepted SPM plans?

Filed under: Sql Plan Managment — hourim @ 4:15 pm

This question is tormenting me since several months. So here it is my answer.

create table t1
    as
 with generator as (
   select  --+ materialize
          rownum id
   from dual
   connect by
   level <= 10000)
 select
    rownum                           id,
    trunc(dbms_random.value(1,1000)) n1,
    lpad(rownum,10,'0')        small_vc,
    rpad('x',100)              padding
 from
 generator   v1,
 generator   v2
 where
    rownum <= 1000000;

 create index t1_n1 on t1(id, n1);

 create table t2
   as
 with generator as (
 select  --+ materialize
     rownum id
 from dual
 connect by
 level <= 10000)
 select
    rownum                  id,
    trunc(dbms_random.value(10001,20001))   x1,
    lpad(rownum,10,'0') small_vc,
    rpad('x',100)       padding
 from
    generator   v1,
    generator   v2
 where
 rownum <= 1000000;

 create index t2_i1 on t2(x1);

 exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
 exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
 

Now that we are done with the model, let’s execute a query under two different optimizer modes the classical all_rows mode andthe unsuitable first_rows mode

 var n1 number
 exec :n1 := 17335

 -- all_rows
 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 -- first_rows
 alter session set optimizer_mode = FIRST_ROWS;

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;
 

Each of the above identical queries has been honored via a different execution plan due to the optimizer mode change. In order to complete my investigation setup, I will load the above two plans from the cursor cache into a SPM baseline

 DECLARE
  l_plans_loaded  PLS_INTEGER;
 BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '7bfbmwdq70vzp');
 END;
 /
 

I can now verify that I have successfully added two enabled and accepted SPM plans to be used by the above sql query

 select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

 SIGNATURE PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
 ---------- ------------------------------ --- --- --- --------------
 1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES      100315268  --> first_rows suboptimal plan
 1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES            203  --> all_rows optimal plan
 

And now, we are ready for the experiments

ALL_ROWS

show parameter optimizer_mode

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- --------------------
 optimizer_mode                       string      ALL_ROWS

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.

 Elapsed: 00:00:00.06 --> spot the execution time for the optimal plan

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 250297636
 ----------------------------------------------------------------------------------------
 | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT               |       |   100 | 13100 |   203   (2)| 00:00:01 |
 |   1 |  NESTED LOOPS                  |       |       |       |            |          |
 |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   202   (1)| 00:00:01 |
 |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   100   (0)| 00:00:01 |
 |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   100   (0)| 00:00:01 |
 |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
 |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     2   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")Note
 -----
 - SQL plan baseline "SQL_PLAN_g43cagumm9pj8c2e7a81f" used for this statement
 

FIRST_ROWS

 alter session set optimizer_mode = first_rows;

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.

 Elapsed: 00:01:20.04

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1518369540
 --------------------------------------------------------------------------------------
 | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |       |   100 | 13100 |   100M  (1)| 73:27:12 |
 |   1 |  NESTED LOOPS SEMI           |       |   100 | 13100 |   100M  (1)| 73:27:12 |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   115M|  5070   (2)| 00:00:14 |
 |   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |   722   (6)| 00:00:02 |
 |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   100   (0)| 00:00:01 |
 |*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 --------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)
 Note
 -----
 - SQL plan baseline "SQL_PLAN_g43cagumm9pj8886a3342" used for this statement
 

Under all_rows mode the CBO produces a plan that exists into the SPM baseline. This is why it has been used. Under the unsuitable first_rows mode the CBO produces a suboptimal plan which exists into the SPM baseline and this is why it  has also been used.

So far so good.

To push the investigations further, I need to disturb this smooth environment. The serious stuff start only when:

  1. The CBO generates a plan that is not into the SPM baseline
  2. The SPM baseline contains more than one accepted and enabled plan for the current executed query

In this case the SPM plans enter in competition. The one with the better cost will be used. For that, the CBO needs to

  1. Reproduce each plan
  2. Compute their cost
  3. And choose the best one

But the question is: will the CBO use the current optimizer mode to re-cost the SPM plans generated with different optimizer modes? Or will it use the optimizer mode used during the SPM plan capture?

Let’s see

In order to disturb the above clean situation, I will add an extra index so that the CBO will produce a plan that doesn’t exist in the SPM baseline

SQL> create index ind_t1_extra on t1(id);

SQL> show parameter optimizer_mode

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -----------
 optimizer_mode                       string      FIRST_ROWS

And now I will execute my query under the unsuitable first_rows mode

 SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.
 Elapsed: 00:00:00.06

 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 SQL_ID  7bfbmwdq70vzp, child number 1
 -------------------------------------
 select * from t1 where id in (select id from t2 where x1 = :n1) order
 by id

 Plan hash value: 250297636
 ----------------------------------------------------------------------------------------
 | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT               |       |       |       |   203 (100)|          |
 |   1 |  NESTED LOOPS                  |       |       |       |            |          |
 |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   202   (1)| 00:00:01 |
 |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   100   (0)| 00:00:01 |
 |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   100   (0)| 00:00:01 |
 |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
 |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     2   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------

 Query Block Name / Object Alias (identified by operation id):
 -------------------------------------------------------------
 1 - SEL$5DA710D3
 4 - SEL$5DA710D3 / T2@SEL$2
 5 - SEL$5DA710D3 / T2@SEL$2
 6 - SEL$5DA710D3 / T1@SEL$1
 7 - SEL$5DA710D3 / T1@SEL$1

 Outline Data
 -------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
 DB_VERSION('11.2.0.3')
 ALL_ROWS                       ------------------------------> ALL_ROWS
 OUTLINE_LEAF(@"SEL$5DA710D3")
 UNNEST(@"SEL$2")
 OUTLINE(@"SEL$1")
 OUTLINE(@"SEL$2")
 INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."X1"))
 INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
 LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
 USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
 NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
 END_OUTLINE_DATA
 */
 Peeked Binds (identified by position):
 --------------------------------------
 1 - :N1 (NUMBER): 17335

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 5 - access("X1"=:N1)
 6 - access("ID"="ID")

 Column Projection Information (identified by operation id):
 -----------------------------------------------------------
 1 - "ID"[NUMBER,22], "T1"."N1"[NUMBER,22], "T1"."SMALL_VC"[VARCHAR2,40],
 "T1"."PADDING"[VARCHAR2,400]
 2 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
 3 - (#keys=1) "ID"[NUMBER,22]
 4 - "ID"[NUMBER,22]
 5 - "T2".ROWID[ROWID,10]
 6 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
 7 - "T1"."SMALL_VC"[VARCHAR2,40], "T1"."PADDING"[VARCHAR2,400]

 Note
 -----
 - SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode
 

Spot how, despite I’ve executed my query under the first_rows mode, I ended up by using the SPM plan that corresponds to the all_rows mode. This is confirmed via the outline, the Note and the execution time shown above.

In this particular case, thanks to the new extra index, the CBO comes up with a new plan that is not into the SPM baseline as shown via the following select:

SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

SIGNATURE  PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
---------- ------------------------------ --- --- --- --------------
1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode
1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO  YES 100315135 -- first_rows mode with new index
1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203       -- all_rows mode

The new CBO plan has been added to the SPM baseline with the ACCPETED attributed put to NO for eventual future evolution. If you want to see the new “Non Accepted” execution plan generated by the CBO then you can get it using:

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8c1c6dd02'));

--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8c1c6dd02         Plan id: 3251035394
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2352314251
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100 | 13100 |   102M  (1)| 01:06:26 |
|   1 |  NESTED LOOPS SEMI           |              |   100 | 13100 |   102M  (1)| 01:06:26 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1000K|   115M| 19786   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IND_T1_EXTRA |  1000K|       |  2234   (1)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |    10 |   102   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I1        |   100 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)

The second interesting test to do is to execute the same query this time under all_rows mode:

SQL> alter session set optimizer_mode=all_rows;

SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  7bfbmwdq70vzp, child number 0
-------------------------------------
select * from t1 where id in (select id from t2 where x1 = :n1) order by id

Plan hash value: 250297636
----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |       |       |   253 (100)|          |
|   1 |  NESTED LOOPS                  |       |       |       |            |          |
|   2 |   NESTED LOOPS                 |       |   100 | 13100 |   253   (0)| 00:00:01 |
|   3 |    SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")

Note
-----
- SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode

Where again it is the SPM plan of the all_rows mode that has been used.

Under the all_rows mode the CBO came up with a new plan which has also been consrtained by the SPM plan having the best cost reproduced using the optimizer mode stored against this SPM baseline and not using the current environnment optimizer mode.

The new generated CBO plan under all_rows mode is shown here below:

SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

SIGNATURE  PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
---------- ------------------------------ --- --- --- --------------
1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode
1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO  YES 100315135 -- first_rows mode with new index
1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203       -- all_rows mode
1,7442E+19 SQL_PLAN_g43cagumm9pj8ff8eddc2 YES NO YES  253       -- all_rows mode with new index

With a corresponding execution plan shown also here below:

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8ff8eddc2'));

--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8ff8eddc2         Plan id: 4287552962
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2225889631
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |   100 | 13100 |   253   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |   100 | 13100 |   253   (0)| 00:00:01 |
|   3 |    SORT UNIQUE                 |              |   100 |  1000 |   103   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2           |   100 |  1000 |   103   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2_I1        |   100 |       |     3   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | IND_T1_EXTRA |     1 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T1           |     1 |   121 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")

It is time now to summarize

When the CBO comes up with a plan that exists into the SPM plan baseline then this plan is used without regards to the optimizer environment be it the current environment or the one used during the SPM plan capture

When the CBO comes up with a plan that doesn’t exists into a SPM baseline while this baseline contains more than one plan that can likely honor the CBO query, those SPM plans enter in a cost competition. This plan reproduction and cost reevaluation will be done using the optimizer mode stored against each baseline plan. It will not use the query current optimizer mode to reproduce the SPM plan. This is why we have seen and prove by example that when you execute the query under first_rows mode it is the SPM plan of the all_rows mode that has been used.

In the following picture I tried to simplify the view by showing each plan name with its corresponding plan_id.

 SQL> select plan_name, enabled, accepted,reproduced, optimizer_cost
     from dba_sql_plan_baselines;

PLAN_NAME                                     ENA ACC REP OPTIMIZER_COST  PLAN_ID
--------------------------------------------- --- --- --- --------------  ----------
SQL_PLAN_g43cagumm9pj8886a3342                YES YES YES      102039969  2288661314   -- first rows mode without new index
SQL_PLAN_g43cagumm9pj8c1c6dd02                YES NO  YES      102039425  3251035394   -- first rows mode with new index
SQL_PLAN_g43cagumm9pj8c2e7a81f                YES YES YES            253  3269961759   -- all rows mode without index
SQL_PLAN_g43cagumm9pj8ff8eddc2                YES NO  YES            253  4287552962   -- all rows mode with new index

As such I can easily explain the 10053 trace file I have obtained when trying to execute my query under the first_rows mode which of course has been constrained with the SPM plan of the all_rows mode (plan_id 3269961759)

The below CBO trace file corresponds to the above query executed under the unsuitable first_rows mode which comes up with a plan having the plan_id =  3251035394 and that has been constrained with the all_rows SPM plan having plan_id =    3269961759

SPM: statement found in SMB --> signaling the presence of accepted SPM plans
SPM: setup to add new plan to existing plan baseline, sig = 17441749208451569192, planId = 3251035394
SPM: sql stmt=select * from t1 where id in (select id from t2 where x1 = :n1) order by id

The above lines indicates that the CBO comes up with a new plan (planId = 3251035394) that is going to be added into the SPM plan baseline

While the next lines indicates that the CBO is aware of the presence of two SPM plans that are enabled and accepted the first one for the first_rows mode and the second planid for the all_rows mode

SPM: planId's of plan baseline are: 2288661314 3269961759

In the next step the CBO will try to reproduce and to re cost the two plans starting by the first one

SPM: using qksan to reproduce, cost and select accepted plan, sig = 17441749208451569192
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 2288661314

Taking into account the following information

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted               = true
optimizer_features_hinted           = 12.1.0.1
optimizer_mode                      = first_rows
is_recur_flags                      = 8

Which is clearly indicating that it is using the first_rows mode to reproduce the SPM first_rows mode. What is not clear is whether this is due to the current optimizer mode or to the optimizer mode stored against the SPM plan. But things will be clear later when the CBO will try to reproduce the all_rows plan

SPM: planId in plan baseline = 2288661314, planId of reproduced plan = 2288661314
SPM: best cost so far = 102039969.31, current accepted plan cost = 102039969.31

This is the CBO saying that it has succeeded to reproduce the first_rows mode plan.

Now it will start reproducing the all_rows mode (planid = 3269961759)

SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3269961759

But spot below that the compilation environment is not showing any more that the optimizer_mode to be used is the first_rows mode. Don’t forget that I have executed the query under the first_rows mode. The below CBO trace file is clearly showing that the CBO is using the all_rows mode to reproduce the all_rows SPM plan confirming that the CBO will use the optimizer mode stored against the SPM baseline when reproducing their plans.

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted               = true
optimizer_features_hinted           = 12.1.0.1
is_recur_flags                      = 8
*************************************

PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_features_enable           = 12.1.0.1
optimizer_mode                      = all_rows

And finally the CBO saying that it has also reproduced the all_rows mode

SPM: planId in plan baseline = 3269961759, planId of reproduced plan = 3269961759
SPM: best cost so far = 253.11, current accepted plan cost = 253.11

And that, as far as this plan is having the best cost, it will be used

SPM: re-parsing to generate selected accepted plan,  planId = 3269961759

Bottom line:  when the CBO is trying to reproduce a plan stored into a SPM baseline it will use the optimizer mode stored against this SPM plan and not the current environment optimizer mode. Be aware that this is not a conclusion to be extended without tests for the other optimizer parameters particularly for the hidden ones

February 11, 2014

How to attach a hinted SPM baseline to a non hinted sql query?

Filed under: Sql Plan Managment — hourim @ 8:37 am

The last Jonathan Lewis post on RAC Plans which finished by the following phrases:

“If you’re going to hard-code hints into a query then take a look at the outline it generates when it does the right thing, and that will tell you about the 15 or so hints you’ve missed out. (Better still, consider generating an SQL Baseline from the hinted code and attaching it to the unhinted code.)”

Prompted me to write this blog post. This is also a good coincidence because I have recently answered a similar question in the French forum. Here below is an example on how to attach a SQL plan Baseline of a hinted sql query to the same query without hint.

First the usual model (thanks Dom Brooks )

 SQL> create table t1
          (col1  number
          ,col2  varchar2(50)
          ,flag  varchar2(2));

SQL>  insert into t1
          select rownum
             ,lpad('X',50,'X')
             ,case when rownum = 1
              then 'Y1'
                   when rownum = 2
              then 'Y2'
                   when mod(rownum,2) = 0
              then 'N1'
                    else 'N2'
              end
          from   dual
          connect by rownum <= 100000;

SQL> CREATE INDEX i1 ON t1 (flag);

SQL> exec dbms_stats.gather_table_stats(user, 't1');

Second my two queries, the unhinted one

SQL>  var n varchar2(2);
SQL>  exec :n := 'Y1';

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

And the hinted one

SQL> select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  cnazndd6j7d1v, child number 0
-------------------------------------
select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

I have two queries an unhinted and a hinted one honored via two different execution plans. I want my unhinted query to be always honored by the plan of the hinted query. In order to achieve this goal I need to

  1. Create a SQL Baseline for the hinted query
  2. Attach this SQL Baseline to the unhinted query

So I will start by loading the above two plans into a SPM baseline using their corresponding sql_id

-- unhinted
SQL>declare
      l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
    end;
   /

-- hinted
SQL> declare
      l_op pls_integer;
     begin
      l_op := dbms_spm.load_plans_from_cursor_cache('cnazndd6j7d1v');
     end;
    /

I can now verify that I have two accepted and enabled SPM plans

SQL> select sql_handle, plan_name, sql_text, accepted, enabled
     from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                      ACC  ENA
-------------------- --------- -------------------- --------------------------------------------- ---- ---
SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f select count(*), max(col2) from t1 where flag YES  YES
 = :n
SQL_ba68302db880365b SQL_PLAN_bnu1h5qw80dkv616acf47 select /*+ full(t1) */ count(*), max(col2) fr YES YES
 om t1 where flag = :n

At this step we are very close to our goal which is to make the unhinted sql query(index range scan) use the baseline of the same but hinted sql query(full table scan). The first thing to do is to disable the index range scan SPM plan

declare
  ln_ps number;
begin
  ln_ps := dbms_spm.alter_sql_plan_baseline(sql_handle     => 'SQL_11f0e4472549338d'
                                           ,plan_name      => 'SQL_PLAN_13w748wknkcwd8576eb1f'
                                           ,attribute_name => 'enabled'
                                           ,attribute_value => 'NO');
end;
/

so that the original index range scan is not constrained anymore by its original SPM plan

SQL> select count(*), max(col2) from t1 where flag = :n;

SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID 731b98a8u0knf, child number 0
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)

And finally I will attach the full table scan SPM plan to the sql_id of the index range scan

 SQL> declare
       l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache
                 (sql_id          => 'cnazndd6j7d1v'        -- sql_id of the full table scan
                 ,plan_hash_value => 3724264953             -- plan hash value of the full table scan
                 ,sql_handle      => 'SQL_11f0e4472549338d' -- sql handle of the index range scan query
                );
   end;
/

Spot now how the goal has been achieved

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement

January 20, 2014

SQL Plan Management and Indexes: Part ii

Filed under: Sql Plan Managment — hourim @ 1:30 pm

It is strange sometimes in the Oracle word to find a similarity between two concepts that seem to be not related at all. These two concepts I am insinuating to are the functioning of indexes in (a) covering the deadlock threat of unindexed foreign key constraints and (b) reproducibility of a SQL plan baseline.

In my last post about the reproducibility of an index based SQL plan baseline I have found that this plan baseline ceases to be used when

  1.    The index is dropped (obvious)
  2.    The index is renamed(not obvious)
  3.    The leading column(s) of the index is (are) changed

In this post I would like to show another situation where this SPM plan stops to be reproducible and will write few words about the point related to the change of the index leading column(s). You will see the resemblance between the  functioning of the foreign key indexes and the reproducibility of the ”indexed” SPM plans.

As always I will start by the model

 CREATE TABLE t_range
(
ID           NUMBER              NOT NULL,
X            VARCHAR2(30 CHAR)   NOT NULL,
D            DATE,
C1           NUMBER
)
PARTITION BY RANGE (ID)
(
PARTITION P_10000 VALUES LESS THAN (10000) ,
PARTITION P_20000 VALUES LESS THAN (20000) ,
PARTITION P_30000 VALUES LESS THAN (30000) ,
PARTITION P_40000 VALUES LESS THAN (40000) ,
PARTITION P_50000 VALUES LESS THAN (50000) ,
PARTITION P_60000 VALUES LESS THAN (60000)
);

INSERT INTO t_range VALUES (150,   'First Part', sysdate - 2, 42);
INSERT INTO t_range VALUES (11500, 'Second Part',sysdate + 12, 82 );
INSERT INTO t_range VALUES (25000, 'Third Part',sysdate + 5, 102);
INSERT INTO t_range VALUES (34000, 'Fourt Part',sysdate -25, 302);
INSERT INTO t_range VALUES (44000, 'Fifth Part',sysdate -1, 525);
INSERT INTO t_range VALUES (53000, 'Sixth Part',sysdate +15, 1000);
commit;

create index t_r_i1 on t_range(id, c1);
exec dbms_stats.gather_table_stats(user, 't_range');

I have a simple query with two predicates on a range partitioned table which looks like

var n1 number;
var n2 number;
exec :n1 := 150
exec :n2 := 42;

select * from t_range where id = :n1 and c1 = :n2;

Already constrained or protected against any plan instability by the following ”indexed” SPM baseline (see Note below)

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------

select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:N1 AND "C1"=:N2)

Note
-----
- SQL plan baseline SQL_PLAN_9q2w8c3tu6407af6ef80e used for this statement

I said above that changing the leading column(s) of the index will stop the reproducibility of the SPM plan. Let me be more precise about this point. I will reverse the order of the t_r_i1 index columns as shown below:

drop index t_r_i1;

create index t_r_i1 on t_range(c1,id); --> columns are reversed

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

SQL> start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:N2 AND "ID"=:N1)

Note
-----
- SQL plan baseline SQL_PLAN_9q2w8c3tu6407af6ef80e used for this statement

Despite I have reversed the index columns the baseline is still used. When you have a predicate on two (or more) columns covered by an index on these columns, the new index you might re-create should always start by these two columns in any order if you want to ensure the reproducibility of the originally attached SPM plan. Spot the resemblance between the indexes on the foreign key which should start by the foreign key columns in any order in order to be able to avoid the deadlock threat when deleting from the parent table.

Having made this precision, let me now investigate the index type influence. Let’s change the orginal baselined index from b-tree to a locally partitioned index type.

drop index t_r_i1;

create index t_r_i1 on t_range(id,c1) local;

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------

select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 963134062
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE            |         |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N1 AND "C1"=:N2)

The SPM baseline ceases to be used.

Changing the index type has an influence on the re-usability of the SPM plan even when you don’t change any order of the indexed columns . In the same context of index type, I have also tried several changes (a) from b-tree to bitmap (b) from global to local and vice versa. For each index type change, the SPM stops to be reproducible.

Nevertheless, I have found two situations where changing the index type did not influenced the reproducibility of the SPM plan : (1) function based index under certain circumstances and (b) reversing an index. Here below how I did observed these two situations respectively

drop index t_r_i1;

create index t_r_i1 on t_range(c1,id, trunc(d));

I added an extra column to the “baselined index” which has the consequence to change its type from b-tree to function based index. Spot what happens to the SPM plan in this case:


select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:N2 AND "ID"=:N1)

Note
-----
- SQL plan baseline SQL_PLAN_9q2w8c3tu6407af6ef80e used for this statement

The baseline is still reproducible. When you change a “baselined” b-tree index to a function based index starting with the “baselined” index columns, then the SPM will still be reproducible. Spot the resemblance with the function based index that are able to cover the foreign key(FK) deadlock threat when they start by the FK columns in any order.

And finally Reversing my original ”baselined” index


create index t_r_i1 on t_range(id, c1) reverse;

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:N1 AND "C1"=:N2)

Note
-----
- SQL plan baseline SQL_PLAN_9q2w8c3tu6407af6ef80e used for this statement

Not good. I would have been inclined  to say that reversing the index should stop the reproducibility of the baseline plan. Because reversing an index might solve a buffer busy wait problem but might also in the meantime create a db file sequential read wait problem. Why then continue restricting other plans to be used because of this SPM plan reproducibility which is, in contrast to its original situation, is using a costly reversed index?

 

January 2, 2014

SQL Plan Management and indexes Part I: What matters? The index name or the indexed columns?

Filed under: Sql Plan Managment — hourim @ 1:09 pm

Here’s a point that I wanted to investigate since several months. Hopefully time comes when I deigned digging into it. The issue could be stated as follows: If I have an accepted SQL plan baseline using a given index, is this SPM reproducibility depending on that index name or on its indexed columns?

Let’s see

As always when blogging about SPM I use the same model and the same SPM baseline

  select sql_text, plan_name
  from dba_sql_plan_baselines
  where signature = '1292784087274697613';

  SQL_TEXT                                            PLAN_NAME
  --------------------------------------------------- ------------------------------
  select count(*), max(col2) from t1 where flag = :n  SQL_PLAN_13w748wknkcwd8576eb1f
  

The following select will use the above stored Baseline as mentioned by the Note at the end of the execution plan

  SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)

 ---------- --------------------------------------------------

 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 3625400295

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)

 Note
 -----
 - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement

 

The most important object in this baseline is the index I1 which has been defined with a single column as shown below:

 SQL> select table_name, index_name,column_name, column_position
 from user_ind_columns
 where table_name = 'T1';

 TABLE_NAME   INDEX_NAME  COLUMN_NAME  COLUMN_POSITION
 ------------ ----------- ------------ ---------------
 T1           I1         FLAG           1
 

Let’s first rename it and see the consequences

 SQL> alter index i1 rename to i2;
 Index altered.

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 718843153

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I2   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

The SQL plan baseline has not been used thanks to this index name change. The index name matters.

Let’s now go back to the original I1 index but this time I will add to it an extra column

 SQL> drop index i1;

 SQL> create index i1 on t1(flag,col1);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 3625400295

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   130 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   130   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    19   (6)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)

 Note
 -----
 - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
 

The SQL plan baseline has been used in this situation (a) the new index has the same name as the index name in the stored SPM plan i.e. I1 (b) and the new index has an extra column but it is still starting with the same column as the index originally used during the baseline capture.

Finally let’s change the index columns order and use the same index name.

 SQL> drop index i1;

 SQL> create index i1 on t1(col1,flag);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 2289867667

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   123 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   123   (2)| 00:00:01 |
 |*  3 |    INDEX SKIP SCAN           | I1   | 25000 |       |    67   (2)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 filter("FLAG"=:N)
 

Look now how it is the indexed column position that matters in this situation. The SPM baseline is not anymore used because the leading column of the I1 index (col1) is not the same leading column of the I1 index (flag) when compared to the index definition at the baseline capture time.

Bottom line: Baseline selection depends on both the index name and its leading columns as it was during the baseline capture.

  1. If you change the index name, whatever the index definition will be, the baseline will not be selected
  2. If you keep the index name and you change the leading column (s) of the index, the baseline will not be selected
  3. If you keep the index name and you add an extra column at the trailing edge of the index the baseline might be selected

When we are in the third case above, the clustering factor of the I1 index might change to a bad value making the index cost bigger than the full table scan cost. In such a situation, choosing to use the SPM baseline might not be the good choice.  This is why it is nice to have an extra accepted baseline that foresee an extra plan (full table scan for example)  in order to not see the CBO plan constrained by this index SPM baseline.

PS : I might have been very quick making the above conclusions. I will update this post if needed when I will investigate a little bit further or when comments will introduce doubts on what I have stated above

SQL Plan Management: what’s new in 12c

Filed under: Sql Plan Managment — hourim @ 9:25 am

Don’t be disappointed by the title. This is a simple note to show one difference I have noticed in how a stored SPM execution plan is managed when a dependent object is dropped or renamed. To keep it simple, let me start by an existing SPM baseline as shown below

 select sql_text, plan_name
 from dba_sql_plan_baselines
 where signature = '1292784087274697613';

 SQL_TEXT                                            PLAN_NAME
 --------------------------------------------------- ------------------------------
 select count(*), max(col2) from t1 where flag = :n  SQL_PLAN_13w748wknkcwd8576eb1f
 

If I want to display the execution plan of this stored SPM baseline I will proceed as follow:

 select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_13w748wknkcwd8576eb1f'));

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |     1 |    59 |   124   (2)| 00:00:01 |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

It is a baseline using an index range scan. What happen to that stored execution plan when I drop or rename that index

 drop index i1;

 select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_13w748wknkcwd8576eb1f'));
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n

 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3724264953

 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     1 |    59 |   241   (3)| 00:00:01 |
 |   1 |  SORT AGGREGATE    |      |     1 |    59 |            |          |
 |*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1440K|   241   (3)| 00:00:01 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - filter("FLAG"=:N)
 

The baseline stored execution plan has been automatically updated to reflect the disappeared index.  But wait; this is an 11g behavior. Do the same thing in 12c and you will get a different functioning.

 SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_13w748wknkcwd7823646b'));

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
 Plan rows: From dictionary
 --------------------------------------------------------------------------------

 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

This time dropping the index in a 12c release will not influence the stored SPM execution plan as shown below:


 SQL> drop index I1;

 SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_13w748wknkcwd7823646b'));

 SQL_PLAN_13w748wknkcwd8576eb1f
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

 Plan rows: From dictionary
 --------------------------------------------------------------------------------
 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

The interesting question which motivated this post is: is this change good or bad for our proper use/debug of SPM baseline?

When I was deeply investigation, in the last release, how a baseline is chosen or discarded following a drop of a dependent object, I have already remarked that kind of automatic update of the stored execution plan. I was having two stored baselines one using full table scan and the other one was using the index range scan. In such a situation this index drop let me with the two different stored baselines but this time having both an identical full table scan plan. I knew why, because I originated the two baselines and I provoked that change. But a new developer who might come after me would then question what are those two different baselines having the same execution plan. Would he be able to understand that this situation has been made as such because someone in the past has dropped and index? Not sure.

So, saving definitely the execution plan of the stored SPM baseline as it was at its creation time is a good news in my honest opinion. Why?  Because if you see your query not using the baseline your want it to be used, you will dig into its stored plan and you will remark that this stored plan is using an index named I1. It is then easy to go to that table and verify if the I1 index exists or has been dropped which might immediately explain why your stored baseline is not anymore selected.

Bottom line: I think that the most important thing to remember when looking to the stored SPM execution plan is that, starting from 12c, the displayed execution plan is the one that was available at the baseline captured time. It could be now still available and it might be not anymore reproducible

May 5, 2013

SPM baseline selection: how it works?

Filed under: Sql Plan Managment — hourim @ 4:20 pm

In my last post about SQL Plan Management (SPM) I investigated the behavior of Adaptive Cursor Sharing (ACS) feature in the presence of SPM baselines. I will now start focusing my interests on the interaction between the CBO and the SPM plan selection steps using the model of the last post.  During this entire blog article I will be working with an existing SPM baseline which contains two accepted and enabled plans as shown below:

 SIGNATURE            SQL_HANDLE               PLAN_NAME                      ENA ACC  PLAN_ID    DESCRIPTION
 -------------------- ------------------------ ------------------------------ --- --- ---------- -------------------
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES 1634389831 FULL TABLE SCAN(T1)
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES 2239163167 INDEX RANGE SCAN(i1)
 

And I will try to investigate the following issues

  1. SPM contains two plans (1634389831, 2239163167) and CBO comes up with a plan that matches one of these existing SPM plans
  2. SPM contains two plans (1634389831, 2239163167) and I drop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the bind variable value that favor the index range scan
  3. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline.
  4. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline but I drop also the existing index i1 (making the plan 2239163167 not anymore reproducible)

In order to follow my investigation without a lot of difficulties, please would you mind to remember that plan id finishing by 831 (1634389831) corresponds to T1 Table FULL SCAN while the plan id finishing by 167 (2239163167) corresponds to the index i1 RANGE SCAN.

Let’s now embark in the investigations starting by case number 1.

Case1: SPM contains two plans and CBO comes up with a plan that matches one of these existing SPM plans

I executed my query using bind variable = ‘Y1’ which is the bind variable that favor the i1 index range scan. The CBO should come up with the index i1 range scan plan matching the existing plan in the baseline (2239163167). Let’s see how the selection occurs via the 10053 trace file

 SPM: statement found in SMB

The first thing the CBO does is signaling that it realizes the presence of a SPM baseline. Then, follows the classical CBO query optimization

 ****************
 QUERY BLOCK TEXT
 ****************
 select count(*), max(col2) from t1 where flag = :n

 Access path analysis for T1
 ***************************************
 SINGLE TABLE ACCESS PATH
 Single Table Cardinality Estimation for T1[T1]

 Table: T1  Alias: T1
 Card: Original: 100000.000000  Rounded: 9  Computed: 9.10  Non Adjusted: 9.10
 Access Path: TableScan
 Cost:  275.38  Resp: 275.38  Degree: 0
 Cost_io: 272.00  Cost_cpu: 31121440
 Resp_io: 272.00  Resp_cpu: 31121440

 Access Path: index (AllEqRange)
 Index: I1
 resc_io: 2.00  resc_cpu: 18993
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange                 ---> Best Access Path Index I1 Range Scan with cost =2
 Index: I1
 Cost: 2.00
 ***************************************
 

Where the CBO found that the best access path is the Index Range scan (Index I1 with a cost of 2). However, as far as the CBO has already signaled the presence of SPM plan it knows that it is constrained. It can’t decide to use the plan it comes up with without comparing it to the existing SPM plans. This is why we see the following lines into the same 10053 trace file

 SPM: cost-based plan found in the plan baseline, planId = 2239163167 ---> 167 is the index range scan
 SPM: cost-based plan was successfully matched, planId = 2239163167 --> CBO comes up with a plan that matches a SPM plan
 

That’s all for this case:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case2: SPM contains two plans but Idrop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the index bind variable value

The 10053 trace file in this case looks as follows

 SPM: statement found in SMB

****************
QUERY BLOCK TEXT
****************
select count(*), max(col2) from t1 where flag = :n

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Best:: AccessPath: TableScan        ---> CBO comes up with a FULL TABLE SCAN plan having cost = 273
Cost: 272.96
***************************************

In the absence of the i1 index, the CBO produces a FULL TABLE SCAN as the best access path. As far as this generated plan exists in the baseline it will be used as shown below:

SPM: cost-based plan found in the plan baseline, planId = 1634389831 ---> T1 FULL TABLE SCAN plan
SPM: cost-based plan was successfully matched, planId = 1634389831 --> CBO comes up with a plan that matches a SPM plan

That’s all for this case also:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case 3: SPM contains two plans and I add a new index i2 that will produce a new plan which is not in the SPM baseline

In this case I executed my query with a bind variable that favors a fast full scan of the newly created index i2.  The CBO comes up with a plan that doesn’t match any plan in the SPM baseline. This is confirmed here below:

 Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 50563  Computed: 50563.46  Non Adjusted: 50563.46
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Access Path: index (index (FFS))
Index: I2
resc_io: 44.00  resc_cpu: 18103823
ix_sel: 0.000000  ix_sel_with_filters: 1.000000

Access Path: index (FFS)
Cost:  44.56  Resp: 44.56  Degree: 1
Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (AllEqRange)
Index: I1
resc_io: 995.00  resc_cpu: 26806643
ix_sel: 0.505635  ix_sel_with_filters: 0.505635
Cost: 995.83  Resp: 995.83  Degree: 1
******** End index join costing ********

Best:: AccessPath: IndexFFS    ---> I2 Index FFS of cost 44 is the best access path
Index: I2
Cost: 44.56
***************************************

The CBO comes up with an INDEX FAST FULL SCAN on the newly created index i2. Sure this will not match an existing plan baseline because we keep repeating that we have only two enabled and accepted plan baselines one for index i1 range scan and the other one for t1 table full scan. It is then very interesting to see how the CBO will react in such a situation.

SPM: planId's of plan baseline are: 2239163167 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 2239163167 --> I1 INDEX RANGE SCAN (167)

Since the CBO realizes that it has produced a non matching plan,  It will try to reproduce the existing SPM plan baseline using outline hint and session OFE.

Why the CBO tries to reproduce one of the existing SPM plan?  And why the CBO started by trying to reproduce the index range scan plan first? Why not simply use one of the existing SPM plan?

When the CBO comes up with a plan that is not in the SPM baseline, it considers that something has changed and it is not anymore sure that the existing SPM plans are still reproducible. It also considers that even if the SPM plans are reproducible their corresponding “stored” cost might have changed. This is why, as will we see later, the CBO will not only tries to reproduce the I1 index range scan plan but it will also try to reproduce the full table scan plan in order to compare their costs using current optimizer parameters and table/index statistics. If the two plans are reproducible, then the one having the best current re-computed cost will be used.

We can see this in the 10053 trace file

SELECT /*+ INDEX_RS_ASC ("T1" "I1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MHOURI"."T1" "T1" WHERE "T1"."FLAG"=:B1

Spot how the CBO is injecting the I1 index range scan hint in order to reproduce the SPM index i1 range scan plan

Access path analysis for T1

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 25000  Computed: 25000.00  Non Adjusted: 25000.00

Access Path: index (AllEqRange)
Index: I1
resc_io: 492.00  resc_cpu: 13254598
ix_sel: 0.250000  ix_sel_with_filters: 0.250000
Cost: 492.41  Resp: 492.41  Degree: 1

Best:: AccessPath: IndexRange
Index: I1
Cost: 492.41  Degree: 1  Resp: 492.41  Card: 25000.00  Bytes: 0
***************************************

SPM: planId in plan baseline = 2239163167, planId of reproduced plan = 2239163167 ---> INDEX_RS plan reproduced
SPM: best cost so far = 492.41, current accepted plan cost = 492.409691           ---> cost = 492
***************************************

Since it has successfully reproduced the I1 index range scan plan and recomputed its corresponding cost, the second step, as indicated above, will be to reproduce the full table scan plan (1634389831) and it corresponding cost

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831  ---> T1 FULL TABLE SCAN (831)

CBO succeed to reproduce the FULL table scan using the plan outline hint and calculated the “new” cost to be the 272 as shown below:

SPM: planId in plan baseline = 1634389831, planId of reproduced plan = 1634389831
SPM: best cost so far = 272.96, current accepted plan cost = 272.961944

So far, the CBO succeeded to reproduce the two SPM plans baseline and to calculate their corresponding cost. It found that the FULL TABLE SCAN cost (272) is better than the cost of the I1 INDEX RANGE SCAN (492). As such, it has decide to use the SPM FULL TABLE SCAN plan.

The above preceding 10053 trace file lines show clearly  how accepted SPM plans enter in competition when the generated CBO plan is not in the SPM baseline. The CBO doesn’t rely on the cost of the plan stored in the baseline. It has to reproduce all enabled and accepted plans and compares their costs using the current session CBO parameters. 

Now that the CBO succeeded to reproduce both plans and decide to use the FULL TABLE SCAN which is the plan with the smaller cost, there is bizarrely, a supplementary step that consist of re-parsing to generate the best costed reproduced plan i.e. the T1 FULL TABLE SCAN as shown below:

SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan, planId = 1634389831

Frankly speaking I didn’t understand the need of this last re-parsing step.

That’s all for this case also:  when the cost-based generated plan doesn’t match one of the existing SPM plans, the CBO will reproduce all the SPM plans and compare their cost. The reproduced plan having the best cost will be used.

Case4: SPM contains two plans and I added a new index i2 that will produce a new plan but I also dropped the existing index i1 (making the plan 2239163167 not anymore reproducible)

In this case, I dropped the i1 (flag) index and created a new index i2 (flag, col2) and executed the query with a bind variable that usually was favoring the INDEX i1 RANGE SCAN. Before exploring the corresponding 10053 trace file, let me tell you that the CBO in this configuration will comes up with a new plan using i2 INDEX RANGE SCAN which doesn’t exist in the SPM baseline as shown below:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0d019fe0  bln=32  avl=02  flg=05
value="Y1"   -----> this bind value was favoring I1 INDEX RANGE SCAN before I dropped this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Table: T1  Alias: T1
Access Path: TableScan                     ----> Full table scan ----> cost 272
Cost:    275.38  Resp: 275.38  Degree: 0
Cost_io: 272.00  Cost_cpu: 31121440
Resp_io: 272.00  Resp_cpu: 31121440

Access Path: index (index (FFS))           ----> I2 index fast full scan ----> cost 44
Index: I2
Access Path: index (FFS)
Cost:  45.97

Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (Index Only)           ----> I2 index range scan  ---> cost 3
Index: I2
resc_io: 3.00  resc_cpu: 21564
ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 3.00  Resp: 3.00  Degree: 1

Best: Access Path: Index Range            ----> best access path index range scan I2
Index: I2
Cost: 3.00
***************************************

Now that the CBO comes up with a non matching plan, as always, it will start trying to reproduce all  SPM plans. But this time, despite the index i1 range scan plan is not reproducible because I dropped that i1 index, the CBO will, nevertheless, try to reproduce this plan as shown below:

SPM: plan Ids of plan baseline are: 1634389831 2239163167
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 1634389831

During the first round, CBO is trying to reproduce the full table scan

SPM: plan Id in plan baseline = 1634389831, plan Id of reproduced plan = 1634389831
SPM: best cost so far = 275.38, current accepted plan cost = 275.379078

I will skip the  part showing  the FULL TABLE SCAN reproduction because the CBO has successfully reproduced it and there is no added value to present it here.

The next step in this round is to reproduce the I1 index range scan which is in fact impossible (How the CBO can ignore it?)

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167

Look below how the CBO is hinting an index I2 while trying to reproduce a plan with index I1 that is not any more present in the database

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_RS_ASC ("T1" "I2") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1

And how naturally it will be impossible to reproduce the I1 INDEX RANGES SCAN plan

SPM: plan Id in plan baseline = 2239163167, plan Id of reproduced plan = 3187078153
SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167  ---> I1 INDEX RANGE SCAN
plan_baseline hintset    :

hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
hint num  3 len 22 text: DB_VERSION('11.2.0.1')
hint num  4 len  8 text: ALL_ROWS
hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num  6 len 49 text: INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))
SPM: generated non-matching plan:

The CBO failed to reproduce the I1 index range scan and it produces an I2 index range scan instead. However the CBO is still considering that the game is not over and that there is a second chance. This is why we see the following lines about round 2 in the same  10053 trace file

SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167  ---> I1 INDEX RANGE SCAN

And the evident conclusion of a non reproducible plan even in this round 2

SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167
plan_baseline hintset    :
hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

SPM: generated non-matching plan:

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    42 |           |
| 1   |  SORT AGGREGATE    |         |     1 |    54 |       |           |
| 2   |   INDEX RANGE SCAN | I2      |   24K | 1318K |    42 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 2583336616
plan_hash_2    : 3187078153 ----> I2 INDEX RANGE SCAN

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG" "T1"."COL2"))  ---> (FLAG,COL2) are the I2 index columns
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------

Finally, after two impossible rounds, the CBO recognizes, what it should have recognized much earlier, that it is impossible to reproduce the I1 index range scan and decided to use the unique SPM plan it succeeded to reproduce .i.e. T1 TABLE FULL SCAN not without a extra re-parsing step

SPM: re-parsing to generate selected accepted plan, plan Id = 1634389831 ---> FULL TABLE SCAN

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   275 |           |
| 1   |  SORT AGGREGATE     |         |     1 |    54 |       |           |
| 2   |   TABLE ACCESS FULL | T1      |     1 |    54 |   275 |  00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 3724264953
plan_hash_2    : 1634389831
Peeked Binds

============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=178
char format=1
max length=32
value=Y1

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Bottom line: when using SPM baseline to guarantee plan stability, be warn that when you have several enabled and accepted plan for the same SQL matching signature and, if for any reason those plans become non reproducible,  you might pay a parsing time penalty because the CBO will use two rounds trying to reproduce all SPM plans – even though they are impossible to reproduce–

Next Page »

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)