Quick overview of loading plans into a baseline

Quick overview of DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and DBMS_SPM.LOAD_PLANS_FROM_SQLSET:

load_plans_from_cursor_cache
(attribute_name   IN VARCHAR2,
 attribute_value  IN VARCHAR2,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Loads plans into baselines from cursor cache, for example get all plans with a certain MODULE or a certain parsing schema. Or just everything. Load it all up (NOT).

 
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Loads a specific sql_id, and a specific plan if specified by hash, into a baseline (must be an existing sql id and plan_hash).

                                       
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 sql_text         IN CLOB,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Allows you to use a baseline to apply a specific plan in the cache (must be an existing sql id and plan_hash)
to another sql statement (sql_text). Remember baselines are by signature so all we need is the sql text which will be passed through a filter to make it space and case desensitised. But using sql_text is a bit unwieldy, particularly for large statements so see method below.

                                       
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 sql_handle       IN VARCHAR2,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Similar to above, allows to you take an existing plan and apply it to an existing sql_handle in dba_sql_plan_baselines.

Using sql_text in previous method is not that convenient if you’re got big statements, etc. So you could load up the existing plans into a baseline, disable the existing baselined plans, then load up the desired plan from source statement to the target statement.

What is sql_handle?
Thanks to Hemant for pointing me in the direction of Marcin Przepiorowski – “SQL_HANDLE contain hexadecimal representation of EXACT_MATCHING_SIGNATURE from V$SQL”. I’d read that article before but that that particular info didn’t sink in.

                                       
load_plans_from_sqlset
(sqlset_name        IN VARCHAR2,
 sqlset_owner       IN VARCHAR2 := NULL,
 basic_filter       IN VARCHAR2 := NULL,
 fixed              IN VARCHAR2 := 'NO',
 enabled            IN VARCHAR2 := 'YES',
 commit_rows        IN NUMBER := 1000)

Loads plans into baselines from an existing sql tuning set.
Here’s an example on loading up the top 10 statments by elapsed time between two awr snaps into
a sqlset then a baseline:

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 l_sqlset_name VARCHAR2(200) := my_test_tuning_set;
 l_op   PLS_INTEGER;
BEGIN
 OPEN baseline_cursor FOR
      SELECT VALUE(p)
      FROM   TABLE(dbms_sqltune.select_workload_repository
             (begin_snap        => <begin_snap>,
              end_snap          => <end_snap>,
              basic_filter      => NULL,
              ranking_measure1  => 'elapsed_time',
              result_limit      => 10,
              attribute_list    => 'ALL'
              )) p; 
 DBMS_SQLTUNE.CREATE_SQLSET
 (sqlset_name     => l_sqlset_name);
 DBMS_SQLTUNE.LOAD_SQLSET
 (sqlset_name     => l_sqlset_name,
  populate_cursor => baseline_cursor);
l_op := 
 DBMS_SPM.load_plans_from_sqlset
 (sqlset_name        => l_sqlset_name);
END;
/

No need to change source code, hint it using a baseline II

Following on from my post yesterday, I just want to emphasise a point about the baseline mechanism.

When it comes to sql sharing, we’re used to the statement having to be EXACTLY the same, even down to space and case sensitivity.

For example:

SQL> select /*+dom_baseline*/ * from t1;

no rows selected

SQL> SELECT /*+dom_baseline*/ * FROM T1;

no rows selected

SQL> select /*+dom_baseline*/
  2         *
  3  from   t1;

no rows selected

SQL> select sql_id
  2  ,      to_char(exact_matching_signature) exact_signature
  3  ,      sql_text 
  4  from   v$sql 
  5  where  sql_text like '%dom_baseline%'
  6  and    sql_text not like '%sql_id%';

SQL_ID        EXACT_SIGNATURE                          SQL_TEXT
------------- ---------------------------------------- ---------------------------------------------
chbm0pdpb4q9a 3617515095634892221                      select /*+dom_baseline*/ * from t1
bmg3pd81wapdz 3617515095634892221                      SELECT /*+dom_baseline*/ * FROM T1
0g29rnfuf3a75 3617515095634892221                      select /*+dom_baseline*/        * from   t1

SQL> 

So slight differences lead to different sql cursors (sql_id).

But baselines work by signature (exact_matching_signature although profile can work via force_matching_signature) – see that the exact_matching_signature for the three statements is the same – 3617515095634892221

Now if we repeat yesterday’s transfer of the plan from one statement to the another:

SQL> select /*+ index(t1 pk_t1) */
  2         *
  3  from   t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  an6t9h9g5s3vh, child number 0
-------------------------------------
select /*+ index(t1 pk_t1) */        * from   t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     2 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


17 rows selected.

SQL> declare
  2     sqltext clob;
  3     spm_op pls_integer;
  4  begin
  5     sqltext := 'select /*+dom_baseline*/ * from t1';
  6     spm_op  :=
  7     dbms_spm.load_plans_from_cursor_cache
  8     (sql_id => 'an6t9h9g5s3vh',
  9      plan_hash_value => 646159151,
 10      sql_text => sqltext);
 11  end;
 12  / 

PL/SQL procedure successfully completed.

SQL> 

See what we’ve got in dba_sql_plan_baselines:

SQL> select to_char(signature)
  2  ,      sql_handle
  3  ,      plan_name 
  4  from   dba_sql_plan_baselines;

TO_CHAR(SIGNATURE)                       SQL_HANDLE                     PLAN_NAME
---------------------------------------- ------------------------------ ----------------------------
3617515095634892221                      SQL_3233fed024ee55bd           SQL_PLAN_34czyu0kfwpdx690169bf

SQL> 

And yet if we re-run those first three statements above:

SQL> select /*+dom_baseline*/ * from t1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  chbm0pdpb4q9a, child number 1
-------------------------------------
select /*+dom_baseline*/ * from t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    26 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |    82 |  1066 |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


17 rows selected.

SQL> SELECT /*+dom_baseline*/ * FROM T1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  bmg3pd81wapdz, child number 1
-------------------------------------
SELECT /*+dom_baseline*/ * FROM T1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    26 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |    82 |  1066 |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


17 rows selected.

SQL> select /*+dom_baseline*/
  2         *
  3  from   t1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  0g29rnfuf3a75, child number 1
-------------------------------------
select /*+dom_baseline*/        * from   t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    26 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |    82 |  1066 |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


17 rows selected.

SQL> 

Three statements – essentially the same – all changed by our baseline.

Cool.

No need to change source code, hint it using a baseline

Just a quick end-to-end walkthrough on how to transfer a plan from one sql statement to another via SQL Plan Baselines.

This has the potential to be very useful when it comes to tuning a production sql statement without actually touching the code and is much more straightforward than hacking outlines or profiles. Plus it’s supported – win/win.

Completely artificial test case – we’re going to force a “select * from t1″ to change from a full table scan to a full scan of an index.

Setup:

SQL>create table t1 
  2  (col1  number 
  3  ,constraint pk_t1 primary key (col1)); 

Table created.

The plan we want to force:

SQL>select /*+ index(t1 pk_t1) */ 
  2         * 
  3  from   t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  an6t9h9g5s3vh, child number 0
-------------------------------------
select /*+ index(t1 pk_t1) */        * from   t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     2 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

17 rows selected.

The statement whose plan we want to change:

SQL>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

17 rows selected.

Let’s change it using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE:

SQL>declare 
  2    sqltext clob; 
  3    spm_op pls_integer; 
  4  begin 
  5    sqltext := 'select * from t1'; 
  6    spm_op  := 
  7    dbms_spm.load_plans_from_cursor_cache 
  8    (sql_id => 'an6t9h9g5s3vh', 
  9     plan_hash_value => 646159151, 
 10     sql_text => sqltext); 
 12  end; 
 13  / 

PL/SQL procedure successfully completed.

And let’s check it’s changed:

SQL>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 1

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

NOTE: cannot fetch plan for SQL_ID: 27uhu2q2xuu7r, 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>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 1
-------------------------------------
select * from t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    26 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |    82 |  1066 |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


17 rows selected.

SQL>

Easy. Job done.

Note that you need the SQL_ID and PLAN_HASH_VALUE of the SOURCE statement (i.e. the plan you want to transfer).

This plan has to be in the cache – which shouldn’t be a problem because the main scenario we’re talking about here is doing some adhoc tuning on a statement, maybe by adding some hints to get a particular access pathe, with the aim of propagating the tuned plan to the production statement.

For the TARGET statement – i.e. the problem statement that you want to transfer the plan to – you just need the sql text.

If it’s a long sql statement it might be easier to this directly from v$sql.sql_fulltext and pass it in.

Why do you only need the sql text? Because it gets converted to a signature as per DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE. And signature is just a different sort of hash after the statement has been adjusted for white space and case insensitivity.

But you could use the overloaded version of LOAD_PLANS_FROM_CURSOR_CACHE and pass in SQL_HANDLE instead if this statement is already baselined. (Anyone know how SQL_HANDLE is generated? Yet another hash?).

SQL_IDs and baselines

There have been some interesting posts from Marcin Przepiorowski and Kerry Osborne about mapping sql plan management (spm) baselines to a corresponding sql id.

The key to sql plan baselines (DBA_SQL_PLAN_BASELINES) is SIGNATURE.

And why signature? It’s not just another change to the key to identifying sql statements (HASH_VALUE -> (NEW_)HASH_VALUE / OLD_HASH_VALUE -> SQL_ID).

It’s use in baselines because it’s a more flexible mechanism than a straight up hash – One sql plan baseline can be used against more than one sql id.

As Christian Antognini explains so effectively in his book, when baselines are involved, a sql statement is “normalized to make it both case-insensitive and independent of the blank spaces in the text”.

So, when you look at statements in V$SQL, we’re not talking here about the FORCE_MATCHING_SIGNATURE but EXACT_MATCHING_SIGNATURE.

For example, let’s find three statements with different sql ids but the same signature:

SQL> create table t1
  2  (col1 number);

Table created.

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where        col1 = 3;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cr6chh7p7vvzt, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where        col1 = 3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

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

   1 - filter("COL1"=3)


22 rows selected.

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where  col1 =        3;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8j52h3wtgvu3n, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where  col1 =        3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)


22 rows selected.

SQL> SELECT /*+ find_me */ *
  2  FROM   t1
  3  WHERE  col1 =        3;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  894k8t6nu8kbf, child number 0
-------------------------------------
SELECT /*+ find_me */ * FROM   t1 WHERE  col1 =        3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

22 rows selected.

SQL> select sql_id, hash_value, old_hash_value, exact_matching_signature, force_matching_signature
  2  from v$sql 
  3  where sql_id IN ('cr6chh7p7vvzt','8j52h3wtgvu3n','894k8t6nu8kbf');

SQL_ID        HASH_VALUE OLD_HASH_VALUE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ---------- -------------- ------------------------ ------------------------
894k8t6nu8kbf 2846116206     4072117629               4.4562E+18               1.2887E+19
8j52h3wtgvu3n  855500916     4269126066               4.4562E+18               1.2887E+19
cr6chh7p7vvzt 3934121977      717076022               4.4562E+18               1.2887E+19

Now, let’s load them into a SPM baseline:

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'cr6chh7p7vvzt');
  5   l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'8j52h3wtgvu3n');
  6   l_op := dbms_spm.load_plans_from_cursor_cache(sql_id =>'894k8t6nu8kbf');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> select to_char(signature), sql_text from dba_sql_plan_baselines where signature = 4456219368825
610060 order by created desc;

TO_CHAR(SIGNATURE)
----------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
4456219368825610060
select /*+ find_me */
       *
from   t1
where        col1 = 3


SQL> 

Note how the SQL text is from the first statement, the only statement that caused the baseline plan to be created.

What’s the primary purpose of the SQL text in DBA_SQL_PLAN_BASELINES – well, probably as Christian again suggests, to check that the statement for the baseline is actually equivalent to the one being run in the event of hash collisons.

So, now let’s re-run those SELECTs from earlier and watch the baseline kick in for all three:

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where        col1 = 3;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  cr6chh7p7vvzt, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where        col1 = 3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

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

23 rows selected.

SQL> select /*+ find_me */
  2         *
  3  from   t1
  4  where  col1 =        3;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  8j52h3wtgvu3n, child number 0
-------------------------------------
select /*+ find_me */        * from   t1 where  col1 =        3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

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

23 rows selected.

SQL> SELECT /*+ find_me */ *
  2  FROM   t1
  3  WHERE  col1 =        3;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  894k8t6nu8kbf, child number 0
-------------------------------------
SELECT /*+ find_me */ * FROM   t1 WHERE  col1 =        3

Plan hash value: 3617692013

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   1 - filter("COL1"=3)

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

23 rows selected.

SQL> 

That’s it really.

So, just showing that signature to sql id is not necessarily a one-to-one relationship but can be one-to-many.

Whilst we’re talking baselines, have you read these interesting posts from Maxym Kharchenko (they’re from a while back but I’ve only recently discovered his blog) showing that, amongst other things, because baselines are essentially based on a sql text match, they can kick in where you don’t intend them to, for example statements that look the same but aren’t and might involve objects with the same name but in different schemas, even different structures. When you think about it, it makes sense how it can happen but it surely can’t be intended that way.

Cardinality Feedback

True to form, Kerry Osborne has another excellent post, this time on Cardinality Feedback.

If the mark of a good post is that it sets you off thinking and investigating then he’s nailed it again.

I started to comment on his post, but it was getting long and messy and I thought it easy to make a comment with a post of my own. Apart from anything else, I always seem to cock up the formatting when making longer comments with examples, etc.

So, hopefully, you’ve read Kerry’s article and he asked where Oracle stored the opt_estimate fudge factor feedback that he could see in his trace and that came through when using a baseline created from the plan changed by cardinality feedback.

My initial reaction was that I thought they would be independent and so began this little investigation (which isn’t finished yet).

First tangent of the day … on the subject of OPT_ESTIMATE – it was my belief that this was one of the principal mechanisms used by sql plan baselines. I know it was very popular with standalone sql profiles. I’ve got a controlled number of baselines in my environment – a couple of hundred – none of them use OPT_ESTIMATE in SQLOBJ$DATA.COMP_DATA. Surprising.

Anyway, I thought in terms of having a standalone demo on Cardinality Feedback, why reinvent the wheel?

On his blog article on the subject, Tom Kyte has a standalone demo. Perfect I thought, I’ll reuse it.

And it goes a little something like this:

1. Create table and package:

create or replace type str2tblType as table of varchar2(30);
/
create or replace function str2tbl( 
  p_str in varchar2, 
  p_delim in varchar2 default ',' )
return str2tblType
PIPELINED
as
  l_str      long default p_str || p_delim;
  l_n        number;
begin
   loop
       l_n := instr( l_str, p_delim );
       exit when (nvl(l_n,0) = 0);
       pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
       l_str := substr( l_str, l_n+1 );
   end loop;
   return;
end;
/

2. Set up variable in sql*plus and execute sql statement once, get plan

var in_list varchar2(255)
exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';

select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

select * from table(dbms_xplan.display_cursor);

Which produced the following plan:

SQL> select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  d7bc3g2ccymkt, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


13 rows selected.

SQL> 

Hopefully, no surprises there.

According to the original demo, if I rinse and repeat and should get some feedback kicking right in there.

SQL> select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  d7bc3g2ccymkt, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


13 rows selected.

SQL> 

Oh!

Nope.

And doesn’t matter how often I repeated, same difference.

So, this probably the right time to mention version, right?

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

At this point, something I’d read by the Optimzer Development Group rang a bell:

For similar reasons, execution statistics for queries containing bind variables can be problematic. In cardinality feedback, we limit the feedback to portions of the plan whose estimates are not affected by bind variables. For instance, if a table has a filter comparing a column to a bind value, the cardinality of that table will not be used. However, cardinality feedback can still be used for cardinalities elsewhere in the plan.

Sounds relevant, doesn’t it?

Let’s substitute the bind with a literal.

So, we’re going to run this:

select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

select * from table(dbms_xplan.display_cursor);

which gave me:

SQL> select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8udfh089hyh6d, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as
str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


14 rows selected.

And again

SQL> select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8udfh089hyh6d, child number 1
-------------------------------------
select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as
str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    33 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |     6 |    12 |    33   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


18 rows selected.

SQL> 

So, cardinality feedback successfully demoed with cardinality estimates reduced from the blocksize-related default for a collection to something altogether more accurate.

Now we need to move on to a case where these improved cardinality estimates actually affect the plan.

Fortunately, Tom’s demo does just that with a new table:

create table data  as
select * from all_objects;

create index data_idx on data(object_name);

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

And a new statement:

with T as
( select /*+ find_me */ 
         distinct * 
  from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  where rownum > 0 )
select * from data, t 
where data.object_name = t.column_value;

select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

which gives the following (actual sql output snipped):

SQL> with T as
  2  ( select /*+ find_me */
  3           distinct *
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t
  5    where rownum > 0 )
  6  select * from data, t
  7  where data.object_name = t.column_value;

.......

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 0
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 892089582

------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |       |   318 (100)|          |
|*  1 |  HASH JOIN                             |         | 15014 |   318   (5)| 00:00:02 |
|   2 |   VIEW                                 |         |  8168 |    36   (9)| 00:00:01 |
|   3 |    HASH UNIQUE                         |         |  8168 |    36   (9)| 00:00:01 |
|   4 |     COUNT                              |         |       |            |          |
|*  5 |      FILTER                            |         |       |            |          |
|   6 |       COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 |    34   (3)| 00:00:01 |
|   7 |   TABLE ACCESS FULL                    | DATA    | 58781 |   279   (3)| 00:00:02 |
------------------------------------------------------------------------------------------

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

   1 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")
   5 - filter(ROWNUM>0)


28 rows selected.

SQL> 

As expected, no cardinality feedback yet, collection estimate back to default.

And I’m just going to monitor what’s in V$SQL:

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 Y

1 row selected.

SQL> 

Now re-run:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

...............

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 1
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 3947981921

--------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       |    43 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |            |          |
|   2 |   NESTED LOOPS                          |          |     6 |    43   (3)| 00:00:01 |
|   3 |    VIEW                                 |          |     3 |    34   (3)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |     3 |    34   (3)| 00:00:01 |
|   5 |      COUNT                              |          |       |            |          |
|*  6 |       FILTER                            |          |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |     6 |    33   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   6 - filter(ROWNUM>0)
   8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
   - cardinality feedback used for this statement


34 rows selected.

SQL> 

And in V$SQL:

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 N
c4m3t9jwvs3ht            1      3947981921          1 Y

2 rows selected.

SQL> 

So, cardinality feedback has come to the rescue, adjusted our estimates and this time we’ve got a better plan for the numbers concerned with NESTED LOOPS rather than a HASH JOIN.

What happens if, as Kerry did, I create a baseline for this improved statement using his create_baseline.sql script?

SQL> @create_baseline
Enter value for sql_id: c4m3t9jwvs3ht
Enter value for plan_hash_value: 3947981921
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): my_cf_baseline
old  16: l_sql_id := '&&sql_id';
new  16: l_sql_id := 'c4m3t9jwvs3ht';
old  17: l_plan_hash_value := to_number('&&plan_hash_value');
new  17: l_plan_hash_value := to_number('3947981921');
old  18: l_fixed := '&&fixed';
new  18: l_fixed := 'NO';
old  19: l_enabled := '&&enabled';
new  19: l_enabled := 'YES';
old  40:     decode('&&plan_name','X0X0X0X0','SQLID_'||'&&sql_id'||'_'||'&&plan_hash_value','&&plan_
new  40:     decode('my_cf_baseline','X0X0X0X0','SQLID_'||'c4m3t9jwvs3ht'||'_'||'3947981921','my_cf_
Baseline MY_CF_BASELINE created.
SQL> 

And now I re-run the statement:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

....

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 2

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

NOTE: cannot fetch plan for SQL_ID: c4m3t9jwvs3ht, CHILD_NUMBER: 2
      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> 

Oh dear. Can we just ignore that and come back to it at a later date? Let’s stick a pin in it, as they say.

I’ll just try again:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

........

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 2
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 3947981921

--------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       | 24591 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |            |          |
|   2 |   NESTED LOOPS                          |          | 15014 | 24591   (1)| 00:02:03 |
|   3 |    VIEW                                 |          |  8168 |    36   (9)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |  8168 |    36   (9)| 00:00:01 |
|   5 |      COUNT                              |          |       |            |          |
|*  6 |       FILTER                            |          |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |  8168 |    34   (3)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   6 - filter(ROWNUM>0)
   8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

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


34 rows selected.

SQL> 

And what do we have in V$SQL?

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 N
c4m3t9jwvs3ht            2      3947981921          1 Y SQL_PLAN_6w2j9bx7xvu6h5268a54a

2 rows selected.

SQL> 

So we can see that the baseline worked.

We can also see that the baseline, in this example, operated independently of the cardinality feedback so whilst we got our nice plan from the more accurate cardinality, we got the estimates from the default estimate of the collection.

Which is not exactly what I expected.

From Kerry’s post, he managed to get a statement using both a baseline and cardinality feedback as illustrated by his dbms_xplan output of:

Note
-----
   - SQL plan baseline SQLID_0CM4R08VJ075R_1255158658 used for this statement
   - cardinality feedback used for this statement

This is what I expected to get and then I was going to flush the shared pool in the hope of having a baseline and the original non-feedback estimates.

But no matter how many times I re-ran the statement I couldn’t get the cardinality feedback to kick back in.

So, unfortunately, I got to that end result without the all-important bit in the middle. Which is a bit weird. And warrants further investigation at some point.

I thought maybe the MONITOR hint might be the key difference to getting feedback working with baselines but not according to what I’ve tried so far. I couldn’t get the two features to work together.

But the bottom line is that I think this shows that the baseline did not use anything to maintain that those nice accurate estimates from cardinality feedback, only the end result plan.

And that’s backed up by the set of hints in the baseline which doesn’t include any OPT_ESTIMATE.

Suffice to say that this is really fledgling functionality and, as such, you expect subtle and no-so-subtle tweaks along the way as illustrated by the possible change of behaviour with bind variables when comparing my results above with the ones in Tom’s original article.

SQL Tuning Set to Baseline to Advisor

In my previous post “Advisor to Baseline – am I missing something?”, the answer was an inevitable “Yes”.

Just as a reminder, what I had tried to do was:

  1. Create a Tuning Set from some statements in AWR
  2. Create and execute a Tuning Task based on the Tuning Set
  3. Accept the simple profile recommendations from the Advisor
  4. Create a SQL Plan Baseline from the Tuning Set and with it all the new profiles

What happened was that I ended up with a bunch of standalone profiles from the Advisor – see DBA_SQL_PROFILES and/or SQLOBJ$ of object type 1, looking at hints in COMP_DATA of SQLOBJ$DATA or packing to staging table and inspecting there.

And I ended up with a bunch of SQL Plan Baselines with hints from the old plans for statements that I had run through the Advisor because they were rubbish (DBA_SQL_PLAN_BASELINES and SQLOBJ$ of object type 2, looking at hints in COMP_DATA of SQLOBJ$DATA or packing to staging table and inspecting there.)

Quick question – what happens if you have some SQL Plan Baselines with some “bad” hints whilst there also exist some standalone sql profiles with some “good” hints?

From my observations, the Baselines will win. The bad plans will be used. However, because when baselines are being used, on hard parse the optimizer will generate a plan anyway and record any differences in plans generated. So when generating the plan anyway, the standalone sql profiles kick in and so the baseline will contain unaccepted “better” plans ready to be evolved for subsequent executions (unaccepted depending on whether you’re runing with automatic evolution or not).

And back to what I should have done initially and that’s:

  1. Create a Tuning Set from some statements in AWR
  2. Create a SQL Plan Baseline from the Tuning Set
  3. Create and execute a Tuning Task based on the Tuning Set
  4. Accept the simpler, non-parallel profile recommendations from the Advisor

This way the profiles get created not as standalone profiles but part of SQL Plan Baselines – SQLOB$ object type 2 – and accepted and enabled in DBA_SQL_PLAN_BASELINES (FLAGS=11 in SQLOBJ$).

I’ll back this all up with some proof and isolated examples one day.
At the minute, everything’s just too manic, manic trying to get this stuff to work, manic fighting management FUD about just about any new feature since Oracle 9…

Advisor to Baseline – am I missing something?

I’ve mentioned before that I’m in the middle of an upgrade to 11.2.0.2.

I’ve been looking at the SQL Tuning Advisor, looking at Baselines, running into a performance crippling bug with baseline capture, running away from baselines, and now coming back to baselines now that the bug has been approved by the client internally.

Here’s what I want to do:

  • Create a Guaranteed Restore Point
  • Run “The Batch”
  • Run a Tuning Advisor on the top N statements
  • Accept some of the Advisor recommendations
  • Export these recommendations
  • Flashback to restore point
  • Import
  • Rerun same “The Batch”

(It may be that RAT would be a good solution for this cycle – we’re not licensing it I believe)

So, running the batch – done.

Some performance degradations – check.

Given 30 minutes to look at some pretty big statements statement, the good old SQL Tuning Advisor can come up with far more than I can (there’s still a place for manual tuning, of course).

So, if I take from AWR the top N statements by some metric from “The Batch” and give the SQL Tuning Advisor a few hours to chunter away, it should be a really positive contribution in a short space of time.

First up, this is a nice little function – DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY.

So, I know the AWR snaps that cover “The Batch” so to get the top 50 statements for example by elapsed time from AWR, I do this:

SELECT *
FROM   TABLE(dbms_sqltune.select_workload_repository
             (begin_snap        => 4005,
              end_snap          => 4011,
              basic_filter      => 'parsing_schema_name in (''ABC'',''XYZ'')',
              ranking_measure1  => 'elapsed_time',
              result_limit      => 50,
              attribute_list    => 'ALL'
              )) p;

I’ve not included the output because it’s a bit wide. Suffice to say, I’ve got my 50 statements.

I can load these up into a SQL tuning set like this:

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MY_11G_TUNING_SET', 
    description  => 'My 11g tuning set');
 OPEN baseline_cursor FOR
      SELECT VALUE(p)
      FROM   TABLE(dbms_sqltune.select_workload_repository
             (begin_snap        => 4005,
              end_snap          => 4011,
              basic_filter      => 'parsing_schema_name in (''ABC'',''XYZ'')',
              ranking_measure1  => 'elapsed_time',
              result_limit      => 50,
              attribute_list    => 'ALL'
              )) p; 
    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'MY_11G_TUNING_SET',
             populate_cursor => baseline_cursor);
END;
/

And then I can set the advisor on this bad boy thus:

DECLARE
 l_task_name   varchar2(200) := 'MY_11g_TUNING_TASK';
 l_sqlset_name varchar2(200) := 'MY_11G_TUNING_SET';
BEGIN
 l_task_name :=
 dbms_sqltune.create_tuning_task
 (sqlset_name  => l_sqlset_name,
  task_name    => l_task_name,
  time_limit   => 15300);
 dbms_sqltune.set_tuning_task_parameter
 (task_name    => l_task_name,
  parameter    => 'LOCAL_TIME_LIMIT',
  value        => DBMS_SQLTUNE.TIME_LIMIT_DEFAULT);
 dbms_sqltune.execute_tuning_task
 (task_name    => l_task_name);
END;
/

Note that I’ve given it a few hours to go off and do its stuff but I’ve restricted it to 30 minutes per statement using the TIME_LIMIT_DEFAULT argument.

Some time later, it’s done and I can get back the results like so:

SELECT dbms_sqltune.report_tuning_task('MY_11g_TUNING_TASK'') 
FROM DUAL; 

And I get a great big stonking report back. Again, no point copying it in here.

Suffice to say that it mentions a bunch of indexes, doing lots more work in parallel, but for now I don’t want to rush into accepting profiles running all 50 statements simultaneously with a DOP of 48 :) – I’m just interested in the simpler SQL profiles it is recommending.

Next, I accept the sql profiles.

I can see them in dba_sql_profiles.

And I know I can move these around using DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF, PACK_STGTAB_SQLPROF and UNPACK_STGTAB_SQLPROF.

That would do me fine. I can do this. Initial aims up top met.

But, I thought I’d be able to run the advisor, accept the profiles and somehow move all this stuff into a baseline, then move the baseline.

But it’s not working as planned.

I’ve already got the tuning set and I’ve accepted the profiles recommended.

I had thought that if I used DBMS_SPM.LOAD_PLANS_FROM_SQLSET that it would use the information from the profiles.

But that doesn’t seem possible.

What’s in the baseline is the hints to get the original plans from the SQL statements concerned – i.e. the hints to get the plans which the Advisor subsequently poo-pooed as trumpable with a profile.

And I think it’s because I need to run the SQL with the profiles so that the appropriate plans exist against the relevant SQL, then create a tuning set of those sql statements and their plans and then load that into a baseline which can use the specific hints for a specific executed plan.

Of course, I might have got it the wrong way round. I know a baseline re-optimises statements at hard parse time and the SQL Tuning Advisor can run on baselines.

But it definitely seems at the moment that the initial baseline population needs an executed plan.

More later perhaps if I decide to knock up a specific isolated example to demonstrate or work it out … but not today though. It’s Friday after all.

Follow

Get every new post delivered to your Inbox.

Join 70 other followers