Saturday, November 7, 2015

SQL execution in AWR used a baseline plan ?!?!?!

Sometimes simple question turns out to be harder. “Can we see if a particular SQL execution in AWR used a baseline plan? “
Initial thoughts would be
Does dbms_xplan.display_awr tell us?
Apprantly not. See below.
Does DBA_HIST_SQLSTAT tell us? – No there is SQL_PROFILE column, but no sql_plan_baseline column.
Does DBA_HIST_SQL_PLAN.OTHER_XML tell us? – No, it get notes about cardinality feedback and dynamic sampling, but not sqlplan baselines.
The usual setup.
rajesh@ORA11G> create table t as select * from all_objects;
 
Table created.
 
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select count(*) from t where owner ='SYS';
 
  COUNT(*)
----------
     37285
 
1 row selected.
 
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  9cjaxv5d7rg8m, child number 0
-------------------------------------
select count(*) from t where owner ='SYS'
 
Plan hash value: 2966233522
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   631 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 41685 |   692K|   631   (2)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OWNER"='SYS')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
23 rows selected.
 
Now, let us baseline this plan.
 
rajesh@ORA11G> exec dbms_output.put_line( dbms_spm.load_plans_from_cursor_cache('9cjaxv5d7rg8m'));
 
PL/SQL procedure successfully completed.
 
rajesh@ORA11G> select count(*) from t where owner ='SYS';
 
  COUNT(*)
----------
     37285
 
1 row selected.
 
rajesh@ORA11G> select count(*) from t where owner ='SYS';
 
  COUNT(*)
----------
     37285
 
1 row selected.
 
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  9cjaxv5d7rg8m, child number 0
-------------------------------------
select count(*) from t where owner ='SYS'
 
Plan hash value: 2966233522
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   631 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 41685 |   692K|   631   (2)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OWNER"='SYS')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_g2dvxc950ykh83fdbb376 used for this statement
 
 
24 rows selected.
rajesh@ORA11G> select sql_id,to_char(exact_matching_signature) sig,
  2             sql_plan_baseline,plan_hash_value
  3  from v$sql
  4  where sql_id ='9cjaxv5d7rg8m';
 
SQL_ID        SIG                  SQL_PLAN_BASELINE              PLAN_HASH_VALUE
------------- -------------------- ------------------------------ ---------------
9cjaxv5d7rg8m 17381499147962501640 SQL_PLAN_g2dvxc950ykh83fdbb376      2966233522
 
1 row selected.
 
rajesh@ORA11G>
 
Now, let’s add this statement to AWR and flush the shared pool.
 
rajesh@ORA11G> exec dbms_workload_repository.add_colored_sql(sql_id=>'9cjaxv5d7rg8m');
 
PL/SQL procedure successfully completed.
 
rajesh@ORA11G> exec dbms_workload_repository.create_snapshot;
 
PL/SQL procedure successfully completed.
 
rajesh@ORA11G> alter system flush shared_pool;
 
System altered.
 
rajesh@ORA11G> select sql_id,to_char(exact_matching_signature) sig,
  2             sql_plan_baseline,plan_hash_value
  3  from v$sql
  4  where sql_id ='9cjaxv5d7rg8m';
 
no rows selected
 
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(sql_id=>'9cjaxv5d7rg8m'));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID: 9cjaxv5d7rg8m, child number: 0 cannot be found
 
 
2 rows selected.
 
rajesh@ORA11G> select * from table(dbms_xplan.display_awr(sql_id=>'9cjaxv5d7rg8m'));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m
--------------------
select count(*) from t where owner ='SYS'
 
Plan hash value: 2966233522
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   631 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 41685 |   692K|   631   (2)| 00:00:04 |
---------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
18 rows selected.
 
rajesh@ORA11G>
 
"Can we see if a particular SQL execution in AWR used a baselined plan?"
 
  • Get the exact_matching_signature from the sql text
  • Get the phv2 out of DBA_HIST_SQL_PLAN.OTHER_XML
  • Match that phv2 with planid which is not exposed in DBA_SQL_PLAN_BASELINES but is in the underlying SYS.SQLOBJ$ tables
 
rajesh@ORA11G> with subq_mysql as
  2  ( select sql_id,
  3    to_char(dbms_sqltune.sqltext_to_signature(sql_text)) sig
  4  from dba_hist_sqltext
  5  where sql_id ='9cjaxv5d7rg8m' ),
  6  subq_mybaselines as (
  7    select b.signature,
  8      b.plan_name,
  9      b.accepted,
 10      b.created,
 11      o.plan_id
 12  from subq_mysql s,
 13    dba_sql_plan_baselines b,
 14    sys.sqlobj$ o
 15  where s.sig = b.signature
 16  and b.signature = o.signature
 17  and o.name = b.plan_name ) ,
 18  subq_myawrs as
 19  (
 20    select sn.snap_id,
 21      to_char(sn.end_interval_time,'DD-MON-YYYY hh24:mi') dt,
 22      st.sql_id, st.plan_hash_value,t.phv2,ms.sig
 23    from dba_hist_snapshot sn,
 24      dba_hist_sql_plan sp,
 25      dba_hist_sqlstat st,
 26      subq_mysql ms,
 27      xmltable( 'for $i in /other_xml/info
 28                 where $i/@type eq "plan_hash_2"
 29                 return $i'
 30                 passing xmltype(sp.other_xml)
 31                 columns phv2 number path '/') t
 32     where st.sql_id = ms.sql_id
 33     and sn.snap_id = st.snap_id
 34     and sn.instance_number = st.instance_number
 35     and sp.sql_id = st.sql_id
 36     and sp.plan_hash_value = st.plan_hash_value
 37     and sp.other_xml is not null )
 38  select awr.*,
 39      decode( (select b2.accepted
 40        from subq_mybaselines b2
 41          where b2.signature = awr.sig
 42          and b2.accepted ='YES'
 43          and rownum = 1 ),NULL,'NO','YES') as baselines_exists,
 44          decode(b.plan_id,NULL,'N','Y') as is_baselined_plan,
 45      to_char(b.created,'DD-MON-YYYY hh24:mi') as when_baseline_created
 46    from subq_myawrs awr,
 47      subq_mybaselines b
 48  where awr.sig = b.signature(+)
 49  and awr.phv2 = b.plan_id (+)
 50  /
 
   SNAP_ID DT                SQL_ID        PLAN_HASH_VALUE       PHV2 SIG                    BAS I WHEN_BASELINE_CRE
---------- ----------------- ------------- --------------- ---------- ---------------------- --- - -----------------
      1064 07-NOV-2015 15:36 9cjaxv5d7rg8m      2966233522 1071362934 17381499147962501640   YES Y 07-NOV-2015 15:34
 
1 row selected.
 
rajesh@ORA11G>

No comments:

Post a Comment