Did it really fix it 2: Plan flip, unprofileable, baseline OFE round 2 and “stability”

Snappy title, huh?

Aka: Why a sql plan baseline may be no guarantee of stability.

The other day, a problematic plan flip…

Tangent 1:
Cue much discussion about plan flips etc.
My thoughts on stability are that the priority for most application owners is stability and predictability but that does not tally with the defaul CBO behaviour and potentially you have to turn off a helluva lot to even get close.

Tangent 2:
I have commented before about the common sensitive of many databases to concurrent direct path reads (or rather the sensitivity is on the underlying SAN).

Here is one such illustration caused by this particular plan flip.

See that our single block read times go out from a “normal” range of 5 – 20ms to an average of 50+ ms. At this point anything doing physical IO starts to notice.
(Needless to say, I’m not a fan of these “normal” IO times – I think they’re awful but the SLA of the tier 2 (non flash) SAN storage is 20ms so we don’t alert until we breach that.)
sbr

Source:

select snap_id, begin_time, end_time, round(average,2)
from   dba_hist_sysmetric_summary
where  metric_name     = 'Average Synchronous Single-Block Read Latency'
order by snap_id;

Back to reality
Anyway, standard immediate-term fix for a plan flip is to hunt down a better plan from AWR and fix it with a profile or a baseline.

Not a problem to find one for this SQL as it nearly always executed with the same decent plan.
(AWR history is much longer than this but it gives the picture)

select to_char(trunc(sn.end_interval_time),'DD-MON-YYYY') dt
,      st.sql_id
,      st.plan_hash_value
,      sum(rows_processed_delta) rws
,      sum(executions_delta)     execs
,      round(sum(elapsed_time_delta)/1000/1000)   elp
,      round(sum(elapsed_time_delta)/1000/1000/nvl(nullif(sum(executions_delta),0),1),2)   elpe
,      round(sum(iowait_delta)/1000/1000)         io
,      round(sum(cpu_time_delta)/1000/1000)       cpu
,      sum(buffer_gets_delta)    gets
,      round(sum(disk_reads_delta))         disk_reads
from   dba_hist_snapshot sn
,      dba_hist_sqlstat  st
where  st.snap_id            = sn.snap_id
and    sn.instance_number = st.instance_number
and    st.sql_id             IN ('6xrx006fmqbq2')
group by trunc(sn.end_interval_time), st.sql_id, st.plan_hash_value
order by trunc(sn.end_interval_time) desc, elp desc; 
DT          SQL_ID        PLAN_HASH_VALUE        RWS      EXECS        ELP       ELPE         IO        CPU       GETS DISK_READS
----------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10-NOV-2015 6xrx006fmqbq2      3871506300          5       1815     236211     130.14     226340       9993  757853211  745588219 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3803735407         53       6835        743        .11          3        723  190488567         41 
07-NOV-2015 6xrx006fmqbq2      3803735407         31       3079        230        .07          3        218   37385043         56 
06-NOV-2015 6xrx006fmqbq2      3803735407        166      18931       1200        .06         11       1128  180142678        484 
05-NOV-2015 6xrx006fmqbq2      3803735407        305       9904        553        .06          5        508   75239139         93 
04-NOV-2015 6xrx006fmqbq2      3803735407        160      18900       1089        .06          9       1027  150523728        204 
03-NOV-2015 6xrx006fmqbq2      3803735407        226      12048        859        .07         58        794  154111239        424 
02-NOV-2015 6xrx006fmqbq2      3803735407       1081      13327       1276         .1         66       1200  278129234        465 

Problem plan is obvious, right?

Started during the day on the 9th, continued overnight until eventually noticed.

PHV 3871506300 does a FTS and gets direct path reads which causes our IO stress.

So, as mentioned, no problem, hint it with a sql profile via COE_XFR_SQL_PROFILE.sql (see Metalink note 215187.1)

But it didn’t work.

Not properly.

See top line.

DT          SQL_ID        PLAN_HASH_VALUE        RWS      EXECS        ELP       ELPE         IO        CPU       GETS DISK_READS
----------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10-NOV-2015 6xrx006fmqbq2      3353364157         20       4485     114084      25.44          4     111246 2907305774        216
10-NOV-2015 6xrx006fmqbq2      3871506300          5       1815     236211     130.14     226340       9993  757853211  745588219 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3803735407         53       6835        743        .11          3        723  190488567         41 
07-NOV-2015 6xrx006fmqbq2      3803735407         31       3079        230        .07          3        218   37385043         56 
06-NOV-2015 6xrx006fmqbq2      3803735407        166      18931       1200        .06         11       1128  180142678        484 
05-NOV-2015 6xrx006fmqbq2      3803735407        305       9904        553        .06          5        508   75239139         93 
04-NOV-2015 6xrx006fmqbq2      3803735407        160      18900       1089        .06          9       1027  150523728        204 
03-NOV-2015 6xrx006fmqbq2      3803735407        226      12048        859        .07         58        794  154111239        424 
02-NOV-2015 6xrx006fmqbq2      3803735407       1081      13327       1276         .1         66       1200  278129234        465 

We didn’t get “good” plan PHV 3803735407, we avoided “nasty” plan PHV 3871506300 but we got not-quite-so-nasty-but-still-not-nice PHV 3353364157.

The SQL Profile was not able to reproduce the desired PHV.

But at least PHV was not doing the problematic direct path reads!

I took the hints from PHV 3803735407 via

select * from table(dbms_xplan.display_awr('6xrx006fmqbq2',3803735407,format=>'+OUTLINE'));

I double checked they matched the hints in the SQL Profile – they did.

I tried using the hints manually in a SQL statement and the result was consistent with the SQL Profile – PHV 3353364157.

At this point, the DBA suggested using a SQL Plan Baseline.

I resisted but eventually gave in.

Why would it make any difference?

When a plan cannot be reproduced then there are good reasons why a baseline is much, much safer than a profile.

A baseline must reproduce the desired PHV or it will be rejected completely.

A profile has no knowledge of PHV so it is possible that under certain circumstances the hints in a profile may be only partially applied.

Profile was dropped and baseline was created.

This was the immediate aftermath (top line)… Success!

And V$SQL was showing that the baseline was being used.

DT          SQL_ID        PLAN_HASH_VALUE        RWS      EXECS        ELP       ELPE         IO        CPU       GETS DISK_READS
----------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10-NOV-2015 6xrx006fmqbq2      3803735407          4       1866        120        .06          1        116   17401768         30 
10-NOV-2015 6xrx006fmqbq2      3353364157         20       4485     114084      25.44          4     111246 2907305774        216
10-NOV-2015 6xrx006fmqbq2      3871506300          5       1815     236211     130.14     226340       9993  757853211  745588219 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3803735407         53       6835        743        .11          3        723  190488567         41 
07-NOV-2015 6xrx006fmqbq2      3803735407         31       3079        230        .07          3        218   37385043         56 
06-NOV-2015 6xrx006fmqbq2      3803735407        166      18931       1200        .06         11       1128  180142678        484 
05-NOV-2015 6xrx006fmqbq2      3803735407        305       9904        553        .06          5        508   75239139         93 
04-NOV-2015 6xrx006fmqbq2      3803735407        160      18900       1089        .06          9       1027  150523728        204 
03-NOV-2015 6xrx006fmqbq2      3803735407        226      12048        859        .07         58        794  154111239        424 
02-NOV-2015 6xrx006fmqbq2      3803735407       1081      13327       1276         .1         66       1200  278129234        465 

I maintained that this didn’t make sense but the ticket was closed so I couldn’t get an SPM trace.

At this point a copy of the statement without profile and without baseline and without the manual fullset of hints was returning the original PHV 3803735407.

I went away that night and on the train I had a thought: “what if it wasn’t round 1 of the baseline which worked but round 2”.

Reminder of the baseline reproduction system

Round 2 is OFE only.

Doesn’t matter whether it is round 1 or round 2, if it’s reproduced it’s reproduced.

But if it was under OFE only then that was no guarantee of stability.

Then the following night, late night call, the baseline was no longer reproducing PHV 3803735407 but was back to the big problem PHV 3871506300!

Although I didn’t have an SPM trace, I’m adamant it verified my theory about it being round 2 only.

DT          SQL_ID        PLAN_HASH_VALUE        RWS      EXECS        ELP       ELPE         IO        CPU       GETS DISK_READS
----------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11-NOV-2015 6xrx006fmqbq2      3871506300          0        471      80513     170.94      77936       2543  193096137  188992511 
11-NOV-2015 6xrx006fmqbq2      3803735407        212       9933        690        .07         14        586   97253038        154 
10-NOV-2015 6xrx006fmqbq2      3803735407          4       1866        120        .06          1        116   17401768         30 
10-NOV-2015 6xrx006fmqbq2      3353364157         20       4485     114084      25.44          4     111246 2907305774        216
10-NOV-2015 6xrx006fmqbq2      3871506300          5       1815     236211     130.14     226340       9993  757853211  745588219 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3803735407         53       6835        743        .11          3        723  190488567         41 
07-NOV-2015 6xrx006fmqbq2      3803735407         31       3079        230        .07          3        218   37385043         56 
06-NOV-2015 6xrx006fmqbq2      3803735407        166      18931       1200        .06         11       1128  180142678        484 
05-NOV-2015 6xrx006fmqbq2      3803735407        305       9904        553        .06          5        508   75239139         93 
04-NOV-2015 6xrx006fmqbq2      3803735407        160      18900       1089        .06          9       1027  150523728        204 
03-NOV-2015 6xrx006fmqbq2      3803735407        226      12048        859        .07         58        794  154111239        424 
02-NOV-2015 6xrx006fmqbq2      3803735407       1081      13327       1276         .1         66       1200  278129234        465 

Note that at times of change, no database structures were being changed.
Stats will have been gathered, sure – that was the agent of change no doubt.
But no index maintenance was happening, nothing was going unusable, no partition maintenance was happening.
Nothing was changing which should mean that a particular plan was impossible.

This means that the set of hints for this particular plan was not deterministic or rather the hintset was surely incomplete in some way which mean that the optimizer was free to do certain transformations which meant that the hintset was then invalid? Not 100% sure, still to be looked at in more detail…

At this point we dropped the baseline and went with a cut-down sql profile which applied only two hints – to politely request index scans for the two problematic FTS in each of PHV 3871506300 and 3353364157.

And this worked and has been ok since (so far) but it will not be the longer term solution.

DT          SQL_ID        PLAN_HASH_VALUE        RWS      EXECS        ELP       ELPE         IO        CPU       GETS DISK_READS
----------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
16-NOV-2015 6xrx006fmqbq2      3803735407         80       6887        348        .05         11        310   44574494         86 
13-NOV-2015 6xrx006fmqbq2      3803735407        114      13054        842        .06         19        713  117260543        482 
12-NOV-2015 6xrx006fmqbq2      3803735407        585      28074       1854        .07         12       1823  321890748       1983 
11-NOV-2015 6xrx006fmqbq2      3871506300          0        471      80513     170.94      77936       2543  193096137  188992511 
11-NOV-2015 6xrx006fmqbq2      3803735407        212       9933        690        .07         14        586   97253038        154 
10-NOV-2015 6xrx006fmqbq2      3871506300          5       1815     236211     130.14     226340       9993  757853211  745588219 
10-NOV-2015 6xrx006fmqbq2      3353364157         20       4485     114084      25.44          4     111246 2907305774        216 
10-NOV-2015 6xrx006fmqbq2      3803735407          4       1866        120        .06          1        116   17401768         30 
09-NOV-2015 6xrx006fmqbq2      3871506300          1       1232     156412     126.96     149660       6846  507571690  499305166 
09-NOV-2015 6xrx006fmqbq2      3803735407         53       6835        743        .11          3        723  190488567         41 
07-NOV-2015 6xrx006fmqbq2      3803735407         31       3079        230        .07          3        218   37385043         56 
06-NOV-2015 6xrx006fmqbq2      3803735407        166      18931       1200        .06         11       1128  180142678        484 
05-NOV-2015 6xrx006fmqbq2      3803735407        305       9904        553        .06          5        508   75239139         93 
04-NOV-2015 6xrx006fmqbq2      3803735407        160      18900       1089        .06          9       1027  150523728        204 
03-NOV-2015 6xrx006fmqbq2      3803735407        226      12048        859        .07         58        794  154111239        424 
02-NOV-2015 6xrx006fmqbq2      3803735407       1081      13327       1276         .1         66       1200  278129234        465

Never before seen an execution plan which had quite this problem interacting with SPM.

A test case to get to the root of the problem is still on the TODO list.

Something to do with a statement involving a WITH subquery and NO_MERGE
e.g.

WITH x AS (SELECT /*+ no_merge...)
SELECT 
FROM ...
WHERE EXISTS (SELECT
              FROM   x
              ...
              WHERE...);

SQL Plan Management Choices

My thoughts on SQL plan management decision points: SPM SQL Patches are also available, primarily to avoid a specific problem not to enforce a particular plan, and are not covered in the above flowchart.

SQL Plan Baseline Manual Evolution

I’ve never really spent time on evolution because most of the time I use baselines for emergency sql plan management and not with the added value of controlled evolution with which the feature was conceived.

But some observations on sql plan baseline evolution originating from the questions:

  • What does evolution actually do?
  • What information does it use?
  • What happens with DML?

Starting point – one baselined plan

1. FTS plan in memory from SQL which should do FTS and put in a baseline

drop table t1;
drop table t2;

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; 

exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE); 
 
create index i1 on t1 (flag);

var n varchar2(2) 
exec :n := 'N1';
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);

declare
  l_op pls_integer;
begin
  l_op :=
  dbms_spm.load_plans_from_cursor_cache('45sygvgu8ccnz');
end;
/

select to_char(signature), sql_handle, sql_text, origin, last_verified, enabled, accepted from dba_sql_plan_baselines;

TO_CHAR(SIGNATURE)  SQL_HANDLE           SQL_TEXT                                                          ORIGIN       LAST_VERIFIED ENABLED ACCEPTED
------------------- -------------------- ----------------------------------------------------------------- ------------ ------------- ------- --------
8384366354684364262 SQL_745b40c6fdb2c5e6 select /*+ domtest */ count(*), max(col2) from t1 where flag = :n MANUAL-LOAD                YES     YES      

We have a full table scan baselined.

Step 2 – an alternative plan

Next, let’s execute a SQL statement which would rather do an index scan for a different set of binds.

When there is a baselined plan, then it does this automatically at hard parse time.

If a lower cost plan is generated then store it for future evaluation / evolution.

We need to flush or invalidate the existing cursor to get that hard parse though.

Then we get an AUTO_CAPTURE plan in DBA_SQL_PLAN_BASELINES, ACCEPTED = NO

comment on table t1 is 'flushme';
var n varchar2(2) 
exec :n := 'Y1';
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);

select to_char(signature), sql_handle, sql_text, origin, last_verified, enabled, accepted from dba_sql_plan_baselines;

TO_CHAR(SIGNATURE)  SQL_HANDLE           SQL_TEXT                                                          ORIGIN       LAST_VERIFIED ENABLED ACCEPTED
------------------- -------------------- ----------------------------------------------------------------- ------------ ------------- ------- --------
8384366354684364262 SQL_745b40c6fdb2c5e6 select /*+ domtest */ count(*), max(col2) from t1 where flag = :n MANUAL-LOAD                YES     YES      
8384366354684364262 SQL_745b40c6fdb2c5e6 select /*+ domtest */ count(*), max(col2) from t1 where flag = :n AUTO-CAPTURE               YES     NO   

Step 3 – Manual Evolution

Now let’s do an evolution.
And I’m going to set MODULE so we can track some sql.
(Perhaps we could/should do a sql trace but, having done it, it doesn’t really tell us much more in this case).

exec dbms_application_info.set_module('DOMTEST','SPM');

set serveroutput on
set long 10000
DECLARE
    r CLOB;
BEGIN
    r := 
    dbms_spm.evolve_sql_plan_baseline
    (sql_handle => 'SQL_745b40c6fdb2c5e6',verify=>'YES',commit=>'NO');
    dbms_output.put_line(r);
END;
/

Why would you do VERIFY => ‘NO’?

Only use case really is if you’ve already evaluated the plan yourself and want to just get it accepted.

The combination of VERIFY=> NO and COMMIT=> NO does nothing.

Our report looks like this:

------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_745b40c6fdb2c5e6
  PLAN_NAME  = 
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = NO

Plan: SQL_PLAN_78qu0svyv5jg68576eb1f
------------------------------------
  Plan was verified: Time used .1 seconds.
  Plan passed performance criterion: 314.79 times better than baseline plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       1              1
  Elapsed Time(ms):                 3.708           .047             78.89
  CPU Time(ms):                     3.777              0
  Buffer Gets:                        944              3            314.67
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0

I’ve also run this on 12.1.0.2 and there doesn’t seem to be a massive difference in behaviour although there are a few extra columns and stats in the output.
(including the two plans which aren’t in the output of 11g because they’re not stored although they could be derived from the analysis sql).

What? Where? How?

What has been run behind the scenes to do this?

What information has been used to do the evaluation?

select * from v$sql where module = 'DOMTEST' order by first_load_time;

Statements of interest:

/* SQL Analyze(686,0) */ select /*+ domtest */ count(*), max(col2) from t1 where flag = :n

/* SQL Analyze(686,0) */ select /*+ domtest */ count(*), max(col2) from t1 where flag = :n

SELECT obj_type, plan_id, comp_data FROM sqlobj$data WHERE signature = :1 AND category = :2;

SELECT st.sql_text, sod.comp_data, sox.optimizer_env, sox.bind_data, sox.parsing_schema_name, sox.creator
FROM   sql$text st, sqlobj$data sod, sqlobj$auxdata sox
WHERE  sod.signature = st.signature 
AND    sod.signature = :1 
AND    sod.category  = :2 
AND    sod.obj_type  = :3 
AND    sod.plan_id   = :4 
AND    sod.signature = sox.signature 
AND    sod.category  = sox.category
AND    sod.obj_type  = sox.obj_type
AND    sod.plan_id   = sox.plan_id;

What was run to evaluate the performance of the relative plans?

See the first two /* SQL Analyze */ statements.

We can look more closely at v$sql for these:

select s.sql_text, s.child_number, s.plan_hash_value phv
,      (select extractvalue(xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]')
        from   v$sql_plan p
        where  p.sql_id          = s.sql_id
        and    p.plan_hash_value = s.plan_hash_value
        and    p.other_xml is not null) plan_hash_2
, s.executions, s.elapsed_time/1000/s.executions elapsed_ms, s.cpu_time/1000/s.executions cpu_ms, s.buffer_gets/s.executions, b.value_string
from   v$sql s, table(dbms_sqltune.extract_binds(s.bind_data)) b
where  s.module = 'DOMTEST' 
and    s.sql_id = '8rnh80j2b09kt';

SQL_TEXT                                                                                    CHILD_NUMBER PHV        PLAN_HASH_2 EXECUTIONS ELAPSED_MS CPU_MS S.BUFFER_GETS/S.EXECUTIONS VALUE_STRING
------------------------------------------------------------------------------------------- ------------ ---------- ----------- ---------- ---------- ------ -------------------------- ------------
/* SQL Analyze(686,0) */ select /*+ domtest */ count(*), max(col2) from t1 where flag = :n  0            1634389831 1634389831  10         4.0923     4.0994 944                        Y1
/* SQL Analyze(686,0) */ select /*+ domtest */ count(*), max(col2) from t1 where flag = :n  1            2239163167 2239163167  10         1.1728     0.1999 3                          Y1

We can see where some of the stats from the report have come from – they look to be an average over a number of executions in this example. The timings aren’t an exact match.

Note that in this case PHV = PHV2, but PHV2 is the plan_id in the underlying baselined tables as I have discussed previously elsewhere.

Where did the SQL statement come from?

Where did the bind data come from?

That’s the last statement in my “statements of interest” above.

SELECT st.sql_text, sod.comp_data, sox.optimizer_env, sox.bind_data, sox.parsing_schema_name, sox.creator
FROM   sql$text st, sqlobj$data sod, sqlobj$auxdata sox
WHERE  sod.signature = st.signature 
AND    sod.signature = :1 
AND    sod.category  = :2 
AND    sod.obj_type  = :3 
AND    sod.plan_id   = :4 
AND    sod.signature = sox.signature 
AND    sod.category  = sox.category
AND    sod.obj_type  = sox.obj_type
AND    sod.plan_id   = sox.plan_id;

The basic sql plan baseline mechanism is by signature (V$SQL.EXACT_MATCHING_SIGNATURE) but SQL$TEXT stores the actual sql text.

How else could be evaluate the baselined plans during evolution because the sql might not be in memory or in AWR!

The statement to execure bind data is from the hard parse which generated the lower cost of plan of interest and is stored in SYS.SQLOBJ$AUXDATA.BIND_DATA.

Makes sense, right?

What happens with DML?

There’s nothing I can find which spells it out in black and white but only the SELECT of the DML, be it INSERT SELECT, UPDATE or MERGE.

Even a sql trace doesn’t make this crystal clear.

But using a similar method to above:

create table t2
(cnt   number 
,col2  varchar2(50) 
,flag  varchar2(2));

var n varchar2(2) 
exec :n := 'N1';
insert into t2
select /*+ domtest */ count(*), max(col2), :n from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);

select sql_id, child_number, is_bind_aware, is_bind_sensitive, is_shareable, to_char(exact_matching_signature) sig
,      executions, plan_hash_value
from   v$sql 
where  sql_id = '08ravsws1s6bn';

declare
  l_op pls_integer;
begin
  l_op :=
  dbms_spm.load_plans_from_cursor_cache('08ravsws1s6bn');
end;
/

select to_char(signature), sql_handle, sql_text, origin, last_verified, enabled, accepted from dba_sql_plan_baselines;

comment on table t2 is 'flushme';

var n varchar2(2) 
exec :n := 'Y1';
insert into t2
select /*+ domtest */ count(*), max(col2), :n from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);

select to_char(signature), sql_handle, sql_text, origin, last_verified, enabled, accepted from dba_sql_plan_baselines;

exec dbms_application_info.set_module('DOMTEST2','SPM');
select * from v$sql where module = 'DOMTEST2' order by first_load_time;

set serveroutput on
set long 10000
DECLARE
    r CLOB;
BEGIN
    r := 
    dbms_spm.evolve_sql_plan_baseline
    (sql_handle => 'SQL_4ed45b79c88f3392',verify=>'YES',commit=>'NO');
    dbms_output.put_line(r);
END;
/

We get a similar report and similar evaluation sql.
We can see from ROWS_PROCESSED that no rows were actually inserted.

select s.sql_text, s.child_number, s.plan_hash_value phv
,      (select extractvalue(xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]')
        from   v$sql_plan p
        where  p.sql_id          = s.sql_id
        and    p.plan_hash_value = s.plan_hash_value
        and    p.other_xml is not null) plan_hash_2
, s.rows_processed,s.executions, s.elapsed_time/1000/s.executions elapsed_ms, s.cpu_time/1000/s.executions cpu_ms, s.buffer_gets/s.executions
from   v$sql s
where  s.module = 'DOMTEST2' 
and    s.sql_id = 'b04dpwja6smx7';

SQL_TEXT                                                                                                       CHILD_NUMBER PHV        PLAN_HASH_2 ROWS_PROCESSED EXECUTIONS ELAPSED_MS CPU_MS S.BUFFER_GETS/S.EXECUTIONS
-------------------------------------------------------------------------------------------                    ------------ ---------- ----------- -------------- ---------- ---------- ------ --------------------------
/* SQL Analyze(771,0) */ insert into t2 select /*+ domtest */ count(*), max(col2), :n from t1 where flag = :n  0            1634389831 1634389831  0              10         4.1668     4.1993 944                       
/* SQL Analyze(771,0) */ insert into t2 select /*+ domtest */ count(*), max(col2), :n from t1 where flag = :n  1            2239163167 2239163167  0              10         0.1512     0.1  3                         

I also tried with the addition of a PK just to double check that it couldn’t be inserted. Nothing to see.

Finally…

One note about evolution behaviour and the adjustment of the threshold for plan verification, i.e. the bit which results in this:

Plan was verified: Time used .1 seconds.
Plan passed performance criterion: 314.79 times better than baseline plan.

You can use parameter _plan_verify_improvement_margin, e.g.

ALTER SYSTEM SET “_plan_verify_improvement_margin”=120;

Unfortunately valid range for parameter is between 100 and 10000 so not high enough for me to show it’s influence on the results above (> 300x improvment).

AWR: Was a baselined plan used?

Sometimes a simple question turns out to be harder than expected.

“Can we see if a particular SQL execution in AWR used a baselined plan?”

Initial thoughts might be:

Q: Does DBMS_XPLAN.DISPLAY_AWR tell us this?
A: Apparently not. See below. This question could also be rephrased as two other possible questions:

Q:Isn’t there a column on DBA_HIST_SQLSTAT which tell us this?
A: No. You’d think there should be. There is a SQL_PROFILE column. There isn’t a SQL_PLAN_BASELINE column.
There also isn’t an EXACT_MATCHING_SIGNATURE although there is a FORCE_MATCHING_SIGNATURE.

Q: Is it in DBA_HIST_SQL_PLAN.OTHER_XML?
A. No although this is where DBMS_XPLAN.DISPLAY_AWR gets it’s notes about cardinality feedback and dynamic sampling from.

First of all, Let’s see that it’s not shown in DBMS_XPLAN.DISPLAY_AWR.
Let’s get a sql statement baselined, in AWR and not in memory.

The usual setup

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

Table created.

SQL> insert into t1
  2  select rownum
  3  ,      lpad('X',50,'X')
  4  ,      case when rownum = 1
  5              then 'Y1'
  6              when rownum = 2
  7              then 'Y2'
  8              when mod(rownum,2) = 0
  9              then 'N1'
 10              else 'N2'
 11         end
 12  from   dual
 13* connect by rownum <= 100000
SQL> /

100000 rows created.

SQL> commit;

Commit complete.

Get a plan and put it in a baseline:

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

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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   |      |       |       |   221 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50254 |  1472K|   221   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> declare
  2   l_op pls_integer;
  3  begin
  4    l_op :=
  5    dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
  6* end;
SQL> /

PL/SQL procedure successfully completed.

Verify that DBMS_XPLAN.DISPLAY_CURSOR reports baseline usage:

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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   |      |       |       |   221 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50254 |  1472K|   221   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement

See that dynamic_sampling note most likely comes from OTHER_XML and Baseline note doesn’t, it comes from V$SQL:

SQL>select sql_id
  2  ,      to_char(exact_matching_signature) sig
  3  ,      plan_hash_value
  4  ,      sql_plan_baseline
  5  from   v$sql
  6* where  sql_id = '731b98a8u0knf'
SQL> /

SQL_ID        SIG                                      PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ---------------------------------------- --------------- ------------------------------
731b98a8u0knf 1292784087274697613                           3724264953 SQL_PLAN_13w748wknkcwd616acf47

SQL> set long 10000
SQL> select other_xml
  2  from   v$sql_plan
  3  where  sql_id = '731b98a8u0knf'
  4* and    other_xml is not null
SQL> /

OTHER_XML
--------------------------------------------------------------------------------
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![C
DATA["E668983_DBA"]]></info><info type="dynamic_sampling">2</info><info type="pl
an_hash">3724264953</info><info type="plan_hash_2">1634389831</info><peeked_bind
s><bind nam=":N" pos="1" dty="1" csi="178" frm="1" mxl="32">4e31</bind></peeked_
binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><!
[CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[DB_VERSION(
'11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('query_rewrite_enabled' 'false')]]
></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]
></hint><hint><![CDATA[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></oth
er_xml>

Now, let’s add statement to AWR, do an AWR snap and flush the SP:

SQL> exec dbms_workload_repository.add_colored_sql('731b98a8u0knf');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

Now if we look back at the plan in memory, there’s nothing there.

SQL> select * from v$sql where sql_id = '731b98a8u0knf';

no rows selected

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID: 731b98a8u0knf, child number: 0 cannot be found

But the SQL is in AWR and let’s see that DBMS_XPLAN.DISPLAY_AWR does not tells us that a baseline is used:

SQL> select * from table(dbms_xplan.display_awr('731b98a8u0knf'));

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

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   221 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50238 |  1471K|   221   (1)| 00:00:03 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

But it will tell us if a sql profile is used because it can via DBA_HIST_SQLSTAT.SQL_PROFILE – I leave that exercise to the reader.

So, back to the original question:

“Can we see if a particular SQL execution in AWR used a baselined plan?”

I’ve never needed to do this but as an exercise we can infer this connection by the presence of a baselined plan with the same phv2 but it’s convoluted.

It may be that there are simpler ways but it seems to me that we need to:

  1. Get the exact_matching_signature from the sql text
  2. Get the phv2 out of DBA_HIST_SQL_PLAN.OTHER_XML
  3. Match that phv2 with planid which is not exposed in DBA_SQL_PLAN_BASELINES but is in the underlying SYS.SQLOBJ$ tables
SQL> with subq_mysql as
  2  (select sql_id
  3   ,      (select dbms_sqltune.sqltext_to_signature(ht.sql_text)
  4           from dual) sig
  5   from   dba_hist_sqltext       ht
  6   where  sql_id = '731b98a8u0knf')
  7  ,    subq_baselines as
  8  (select b.signature
  9   ,      b.plan_name
 10   ,      b.accepted
 11   ,      b.created
 12   ,      o.plan_id
 13   from   subq_mysql             ms
 14   ,      dba_sql_plan_baselines b
 15   ,      sys.sqlobj$            o
 16   where  b.signature   = ms.sig
 17   and    o.signature   = b.signature
 18   and    o.name        = b.plan_name)
 19  ,    subq_awr_plans as
 20  (select  sn.snap_id
 21   ,       to_char(sn.end_interval_time,'DD-MON-YYYY HH24:MI') dt
 22   ,       hs.sql_id
 23   ,       hs.plan_hash_value
 24   ,       t.phv2
 25   ,       ms.sig
 26   from    subq_mysql        ms
 27   ,       dba_hist_sqlstat  hs
 28   ,       dba_hist_snapshot sn
 29   ,       dba_hist_sql_plan hp
 30   ,       xmltable('for $i in /other_xml/info
 31                     where $i/@type eq "plan_hash_2"
 32                     return $i'
 33                    passing xmltype(hp.other_xml)
 34                    columns phv2 number path '/') t
 35   where   hs.sql_id          = ms.sql_id
 36   and     sn.snap_id         = hs.snap_id
 37   and     sn.instance_number = hs.instance_number
 38   and     hp.sql_id          = hs.sql_id
 39   and     hp.plan_hash_value = hs.plan_hash_value
 40   and     hp.other_xml      is not null)
 41  select awr.*
 42  ,       nvl((select max('Y')
 43               from   subq_baselines b
 44               where  b.signature = awr.sig
 45               and    b.accepted  = 'YES'),'N') does_baseline_exist
 46  ,      nvl2(b.plan_id,'Y','N') is_baselined_plan
 47  ,      to_char(b.created,'DD-MON-YYYY HH24:MI')  when_baseline_created
 48  from   subq_awr_plans awr
 49  ,      subq_baselines b
 50  where  b.signature (+) = awr.sig
 51  and    b.plan_id   (+) = awr.phv2
 52* order by awr.snap_id
SQL> /

   SNAP_ID DT                SQL_ID        PLAN_HASH_VALUE       PHV2        SIG D I WHEN_BASELINE_CRE
---------- ----------------- ------------- --------------- ---------- ---------- - - -----------------
      8703 05-FEB-2014 15:18 731b98a8u0knf      3724264953 1634389831 1.2928E+18 Y Y 05-FEB-2014 15:08

SQL>

Surprising.
Unless, of course, I’ve overlooked something bleeding obvious.

Plan_hash_value and internal temporary table names

Here’s a little thing about plan_hash_2 that’s come via a pointer from Jonathan Lewis to bug 10162430.

From querying DBA_HIST_SQLSTAT I happened to notice that over a period of 65 days a particular query had 63 different plans (PLAN_HASH_VALUE).

I found that the differences were down done to the different internal temporary table names used for a materialized subquery, i.e. the plans were essentially the same but for the name of SYS_TEMP_0FD9D6786_B6EF87D2, SYS_TEMP_0FD9D6825_BE8671F, SYS_TEMP_0FD9D6684_EAD00827, etc.

Exactly what bug 10162430 addresses is unclear because it is listed as fixed in 11.2.0.3 and my different plan hashes come from an 11.2.0.3 database.

But the key information in the bug description highlighted that PLAN_HASH_2 is not affected by these differing temp table names.

Exactly what plan_hash_2 is and how it differs from plan_hash_value is undocumented but:

  • it is found in V$SQL_PLAN.OTHER_XML;
  • also in the AWR repository DBA_HIST_SQL_PLAN.OTHER_XML;
  • and is used as the enforcing plan hash in sql plan baselines – not exposed in DBA_SQL_PLAN_BASELINES but internally represented in column PLAN_ID in SYS.SQLOBJ$, SYS.SQLOBJ$DATA, SYS.SQLOBJ$AUXDATA.

So, if you regularly look at DBA_HIST_SQLSTAT to look at historic execution statistics of a SQL statement and your queries use materialized subqueries then I recommend adding PLAN_HASH_2 to the output, something like this:

select sn.snap_id
,      sn.end_interval_time
,      st.module
,      st.sql_id
,      st.plan_hash_value
,      (select extractvalue(xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]')
        from   dba_hist_sql_plan hp
        where  hp.sql_id          = st.sql_id
        and    hp.plan_hash_value = st.plan_hash_value
        and    hp.other_xml is not null) plan_hash_2
,      rows_processed_delta rws
,      executions_delta     execs
,      elapsed_time_delta   elp
,      cpu_time_delta       cpu
,      buffer_gets_delta    gets
,      iowait_delta         io
from   dba_hist_snapshot sn
,      dba_hist_sqlstat  st
where  st.snap_id            = sn.snap_id
and    st.sql_id             = '&sql_id'
and    st.elapsed_time_delta > 0
order by sn.snap_id desc; 

And just to show this difference between PHV and PH2:

   SNAP_ID END_INTERVAL_TIME         PLAN_HASH_VALUE PLAN_HASH_2
---------- ------------------------- --------------- ------------
      9131 12/07/2012 00:00               1987570133 3399935153
      9083 11/07/2012 00:00               1567016512 3399935153
      9035 10/07/2012 00:00               3386529786 3399935153
      8891 07/07/2012 00:00               2197008736 3399935153
      8842 05/07/2012 23:30                991904690 3399935153
      8794 04/07/2012 23:30               1331394991 3399935153
      8746 03/07/2012 23:30               2577119670 3399935153
      8699 03/07/2012 00:00               3774955286 3399935153
      8698 02/07/2012 23:30               3774955286 3399935153
      8555 30/06/2012 00:00               3308813978 3399935153
      8507 29/06/2012 00:00                796849390 3399935153
      8459 28/06/2012 00:00                917735929 3399935153
      8410 26/06/2012 23:30                139934889 3399935153
      8363 26/06/2012 00:00               1936665492 3399935153
      8219 23/06/2012 00:00                666306815 3399935153
      8171 22/06/2012 00:00               1053584101 3399935153
      8123 21/06/2012 00:00               1353471101 3399935153
...

SQL Patch IV – Why is the SQL Patch applied but not effective?

(or Part II Addendum)

In a comment on one of my previous articles about SQL Patches, a reader (hurrah!) effectively asks the question “Why is my SQL Patch being applied but the hint not obeyed?”

The original article itself was long enough without me adding a long comment so I’d like to break out into a fresh post.

Here is the test case.

Setup:

SQL> create table a_test as select * from dba_tables;

Table created.

SQL> create index a_test_idx on a_test(table_name);

Index created.

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

PL/SQL procedure successfully completed.

SQL> select table_name from a_test where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d4knkaxjhqpgw, child number 0
-------------------------------------
select table_name from a_test where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

Create a patch to hint a FULL TABLE SCAN:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'full(a)',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

See that the patch is applied but the hint apparently not obeyed:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement

The issue is that to use a SQL Patch to hint a statement, it seems that you have to be very careful to use the complete hint specification.

The complete hint specification is an ugly beast and should be considered a strong indicator that you really want to think twice before doing any hinting, and especially as Oracle versions march onwards and the Optimizer gets more complicated, the complete hint specification becomes ever more important.
See Jonathan Lewis’s How to Hint post for a good illustration.

Back to the test case…

If we drop the existing sql patch:

SQL> begin
  2   sys.dbms_sqldiag.drop_sql_patch('patch_test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Use the +OUTLINE format option for DBMS_XPLAN to get a nudge in the right direction:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(format=> '+OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "A"@"SEL$1" ("A_TEST"."TABLE_NAME"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

And create our patch with the complete specification:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'FULL(@"SEL$1" "A"@"SEL$1")',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

We should find that it is more effective:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    40 (100)|          |
|*  1 |  TABLE ACCESS FULL| A_TEST |     1 |    18 |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement


22 rows selected.

SQL> 

Bear in mind that the above is a test case only and consider what I said in the original article about what I consider to be the difference between a SQL Patch and a SQL Plan Baseline is and the different use cases.

Adaptive Cursor Sharing with SQL Plan Baselines

Maybe you, like me, have read documentation that says SQL Plan Baselines work together Adaptive Cursor Sharing and wondered what this means? This is something lower down the priority list that I’ve been meaning to take a closer look at for some time.

Executive Summary
ACS and baselines do work together.

But I know when I read the documentation, I came away with some questions that I wanted to look at. I wondered if they did more together than they do.

For example, did baselines preserve some of the ACS not-so-secret sauce such that if the ACS information aged or was flushed out of the cache, we didn’t have to repeat the multiple executions to get the bind awareness back?

And the answer to that is no. Which I’m glad Coskan has shown in his follow-up post.

1. The bind awareness will kick back in eventually.
2. The baseline mechanism will allow the optimizer to use the ACS feedback and consider both plans in the baseline.
3. But we need to repeat the executions to get back to the bind awareness i.e. three runs to have one bind aware plan, four runs to have the two bind aware plans that we preserved initially.

Is that a big deal?

We’ve had to do one extra execution each of the statement but if that initial execution with the “wrong” plan was a big enough performance problem, then the implications could be significant.

And the two mechanisms that I thought might be designed to work together to address it, don’t unless you can guarantee that the information will not age out of the cache.

More Detail
Let me show you what I looked at.

Recap on ACS

Adaptive Cursor Sharing refers to cursors being bind sensitive and then bind aware.

First, a quick recap on bind sensitivity from the Performance Tuning Guide:

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
– The optimizer has peeked at the bind values to generate selectivity estimates.
– A histogram exists on the column containing the bind value
….
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.

Setup

First up, here’s the table and data I’m going to be working with:

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

Table created.

SQL> 
SQL> insert into t1  
  2  select rownum  
  3  ,      lpad('X',50,'X')  
  4  ,      case when rownum = 1  
  5              then 'Y1'
  6              when rownum = 2  
  7              then 'Y2'  
  8              when mod(rownum,2) = 0
  9              then 'N1'
 10              else 'N2'  
 11         end  
 12  from   dual  
 13  connect by rownum <= 100000;  

100000 rows created.

SQL>   
SQL> select flag, count(*) from t1 group by flag;

FL   COUNT(*)
-- ----------
N2      49999
N1      49999
Y1          1
Y2          1

SQL> create index i1 on t1 (flag);

Index created.

SQL> 

(The ‘N2’ and ‘Y2’ values in the test data are not relevant to this article – something else I was looking at…)

Show that bind sensitivity requires histogram (and index so that we have an alternative execution plan)

I’ve some skew, but no stats. No stats, means no histogram. No histogram should mean no bind sensitivity.

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

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 44032 |  1290K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=4)


23 rows selected.

SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N N Y 1292784087274697613             1      3724264953

SQL> 

So, not bind-aware, not bind-sensitive.

Show that if we gather histogram we get bind sensitivity

If we gather some stats, then repeat the previous test:

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49257 |  1443K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y Y 1292784087274697613             1      3724264953

SQL> 

We have a cursor that IS bind sensitive but IS NOT bind aware.

Baselines, preserve us

If we stick that in a baseline:

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 

And delete the stats:

SQL> exec dbms_stats.delete_table_stats(USER,'T1',no_invalidate=>FALSE); 

PL/SQL procedure successfully completed.

SQL> 

And repeat the query:

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

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

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

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

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

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 44032 |  1290K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=4)
   - SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement


24 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N N Y 1292784087274697613             1      3724264953

SQL> 

then we have a plan that is NOT bind sensitive

And if we regather stats and repeat, it is back to being bind sensitive:

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE); 

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

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


23 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';  

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y Y 1292784087274697613             1      3724264953

SQL> 

So, in summary this clarifies that bind sensitivity is not an attribute that is preserved in a SQL Plan Baseline.

How then do ACS and SQL Plan Baselines work together?

From reading the documentation, you might get the impression – or at least I did – that there’s more to the combination of baselines and ACS than there is.

Let’s get two BIND AWARE plans.

Continuing on from above, let me delete the baseline, flush and start again with just the table and the data.

We start with one query that selects a large proportion of the data with a FTS:

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

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 

Then, let’s use a different bind that selects a much smaller proportion of the data, initially with a FTS as well because that’s what’s parsed and shareable:

SQL> exec :n := 'Y1'

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 

But after a couple of executions we get a more appropriate plan, thanks to ACS:

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

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

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
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             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     9 |   270 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FLAG"=:N)


20 rows selected.

SQL> 

And we see in V$SQL that we have the original cursor marked as NOT SHAREABLE and a new cursor marked now as BIND AWARE.

SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613             2      3724264953
731b98a8u0knf            1 Y Y Y 1292784087274697613             1      3625400295

SQL> 

And if we execute the SQL with the initial bind that favoured a FTS, we do get that FTS as is appropriate but we have a new child cursor that is also bind aware:

SQL> exec :n := 'N1'  

PL/SQL procedure successfully completed.

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

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

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

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

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';  

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613             2      3724264953
731b98a8u0knf            1 Y Y Y 1292784087274697613             1      3625400295
731b98a8u0knf            2 Y Y Y 1292784087274697613             1      3724264953

SQL> 

Stick the bind-aware plans in a baseline

Now, let’s baseline the two plans:

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 

And validate that that was successful because DBMS_SQLTUNE.LOAD_PLANS_FROM_CURSOR_CACHE does not error if no plans were loaded:

SQL> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted, reproduced
  2  from   dba_sql_plan_baselines 
  3  where   signature = 1292784087274697613;

SIGNATURE                SQL_HANDLE               PLAN_NAME                      ENA ACC REP
------------------------ ------------------------ ----------------------------
1292784087274697613          SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES YES
1292784087274697613          SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES YES

SQL> 

Rinse and repeat

Now lets flush the shared pool and see what happens when we run those statements which are meant to be
bind sensitive.

Firstly, if we initially run with the bind for the FTS, that’s what we get. It’s no surprise:

SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';

no rows selected

SQL> exec :n := 'N1'

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

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

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

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

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


23 rows selected.

SQL> 

And if we follow-up with the index-favouring value:

SQL> exec :n := 'Y1'

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

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


23 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';  

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y Y 1292784087274697613             2      3724264953

SQL> 

We’re back where we started. We’re using one of our baseline plans but we’ve lost our bind-awareness.

What do you mean we’ve lost our bind-awareness?

Well, previously we did all that hard work with multiple executions to get our bind-awareness. The baselines have preserved and are enforcing our ACCEPTED plans but we have to repeat the multiple executions to get back the bind-awareness.

Maybe it helps if we remind ourselves what the optimizer does when baseline plans are present.

At hard parse time, the optimizer will peek at the binds and generate a best cost plan anyway, regardless of the presence of a baselined plan.

If the best cost plan matches one that’s in a baseline, then we get a note in an optimizer trace file along the lines of:

SPM: cost-based plan found in the plan baseline, planId = 2239163167

If we don’t have the best cost plan in the baseline then we get a line in the optimizer trace along the lines of:

SPM: planId's of plan baseline are: 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831
  Bind List follows:
bind value pos=1 name=
    bind(0x2ad9a1c7cd40): flags=0 typ=1 data=0x2ad9a285af88 len=2
      bind_strval="Y2"
      bind in binary format:
2AD9A285AF80                   09E83259                   [Y2..]

and a new plan is added to the baseline as UNACCEPTED and stored for future evolution whilst a baseline is then used to generate the parsed plan.

So, whilst we do have our two baselined plans, the initial hard parse peeks at the binds and gets ‘N1’, generates the best cost plan which matches one of the baselines – job done. (And if the initial hard parse had peeked and found value ‘Y1’ we would have had our baselined index plan).

The subsequent execution of the cursor with a different value falls into the standard shareable SQL scenario – the initial cursor was parsed with peeked binds, the values of which do not suit our subsequent execution until ACS kicks in as previously.

If we return to our sql statement above and re-execute with the same bind that should be using an index, we see the switch from baseline plan SQL_PLAN_13w748wknkcwd616acf47 to SQL_PLAN_13w748wknkcwd8576eb1f:

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

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

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';  

SQL_ID        CHILD_NUMBER I I I SIG                    EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613             2      3724264953

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

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

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
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             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     9 |   270 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FLAG"=:N)

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


24 rows selected.

SQL> 
SQL> select sql_id
  2  ,      child_number
  3  ,      is_bind_aware 
  4  ,      is_bind_sensitive
  5  ,      is_shareable
  6  ,      to_char(exact_matching_signature) sig
  7  ,      executions
  8  ,      plan_hash_value
  9  from   v$sql  
 10  where  sql_id = '731b98a8u0knf';  

SQL_ID        CHILD_NUMBER I I I SIG                     EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ----------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613              2      3724264953
731b98a8u0knf            1 N Y Y 1292784087274697613              1      3625400295

SQL> exec :n := 'N1' 

PL/SQL procedure successfully completed.

SQL> 

Again, then marking of child 0 as NOT SHAREABLE, etc, etc.

So, what are you saying?

Well, not a lot really.

In summary, from the documentation, you may get the impression that there’s more to the combination of ACS and baselines than there really is.

ACS and baselines work alongside each other but somewhat independently.
ACS can provide SPM with bind-sensitive plans to evolve.
Whilst SPM can store the multiple plans that ACS requires.
But ACS attributes are not stored in SPM.

Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.

BOTTOM LINE: if you’re using binds when you should be using literals, there’s no silver bullet

I’ve made no mention here of views:

  • V$SQL_CS_HISTOGRAM
  • V$SQL_CS_SELECTIVITY
  • V$SQL_CS_STATISTICS

but these are worthy of further observation if you want to investigate ACS. However that was not the point of this post.

Follow

Get every new post delivered to your Inbox.

Join 319 other followers