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?).

MERGE oddity

Here’s a little observation on some slightly odd behaviour with MERGE and some apparent inconsistency of error ORA-30926 (prompted by a question on the otn forums).

If you’re not familar with ORA-30926:

ORA-30926: unable to get a stable set of rows in the source tables

then this is expected with MERGE if the set of SOURCE rows (identified in the USING clause) that you’re merging into the TARGET (i.e. the MERGE INTO <target>) contain duplicate entries for the key columns concerned, i.e. if there are duplicates, which one do you want to be preserved by the MERGE.

Setup:

SQL> create table a
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> create table b
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> insert into a values(1,'A');

1 row created.

SQL> 
SQL> insert into b values(1,1);

1 row created.

SQL> 
SQL> insert into b values(1,2);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> select * from b;

       KEY V
---------- -
         1 1
         1 2
SQL> 

We might expect ORA-30926 to be raised in this next example, but it’s not:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 
SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> 

But if we run it again:

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');
merge into a
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables



SQL> rollback;

Rollback complete.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

If we change this slightly by adding and altering an ORDER BY:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val ASC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val DESC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

Perhaps we get an insight into how the check that results in ORA-30926 is implemented?

It seems to be not as simple as a question of whether there are duplicates in the SOURCE but whether the MERGE would update the same row twice – i.e. an UPDATE that results in an UPDATE not just updating it to the existing value.

The bottom line is that you should never be merging in multiple updates to the same target row.
If there are such incoming data, you have to have logic to filter that down.

Any thoughts on stats validation?

There is an application running on 9.2.0.8 and this application’s approach to object statistics is that they are gathered along with each quarterly release of code and do not change outside of the release schedule.

There are a number of other additional issues with some elements of this particular implementation, discussion of which are outside the scope of this.

Now, as part of the upgrade to 11.2, it’s highly likely that we will move to the default built-in stat proc, albeit gathering in a weekend window not daily.

One of the biggest advantages of the current situation is that the stats are tested along with the release and released with the release (of course, plans can still change depite stats being fixed – even because they are fixed due to time ticking ever onwards, sequences increasing, etc).

And so, as part of a possibly non-negotiable comfort blanket to move to the default 11g stats job, there is a management requirement to have some sort of validation routine – i.e. a set of sql statements broadly representing important or problem areas of the application – that will be run before/after and compared on some sort of metric and judgement passed on whether the stats are “good”.

There are a number of nifty features like pending stats that we can use here but my initial thoughts on this was that this sounded like a fairly easy job for SQL Tuning Sets (STS) and the SQL Performance Analyzer (SPA).

SPA requires the additional Real Application Testing (RAT) license and, now that our licensing has been negotiated, this isn’t something we’ve got.

So… hand-rolling a validation routine.

I would see SQL Tuning Sets with the sql statements and bind sets as still part of a bespoke solution
(STS are available with the Tuning Pack license which is on top of EE both of which we have).

I could picture a simple approach that would execute these statements in parallel sessions probably via DBMS_SQL, fetch all the rows, record the session stats before/after, pick a metric e.g. buffer gets, elapsed time, or some sort of ratio between those sorts of things and then and do some sort of metric comparison – yes, that run was broadly the same or better, or at least not a catastrophic degradation – these stats are ok.

(Of course, being able to picture a possible approach does not mean that I advocate it)

But I just wondered whether this is something anyone else has done?

I’d be very interested in hearing more if you have.

Edit:
I’ve tried to reword this to avoid some confusion.
Alex – I’ve deleted your comments and my replies because a) they reduce the chance of getting the input the article was aiming for b) they were based on nested recursive misunderstandings and c) much of what you were saying is preaching to the choir.

In Support of DBMS_JOB

DBMS_SCHEDULER – great set of functionality, vastly more powerful and flexible than DBMS_JOB but there is at least one thing that DBMS_JOB is still best suited for and that is why DBMS_JOB is still not, as far as I know, officially deprecated.

The documentation on DBMS_JOB may say that it’s only supported for backward compatibility but there’s one massive, great thing that it overlooks – transaction control.

And it’s a point that is quickly forgotten on the forums for example when someone is often leapt upon for even suggesting DBMS_JOB (and probably rightly so 93% of the time).

You might as part of a wider transaction want to submit a piece of asychronous work, something that can be fired off in the background, a job.

The oft-cited example is an email, i.e.

start transaction;
do some work;
send a related email;
maybe do some more work;
commit;

And the sending of the email should stand or fail with the transaction.

If the transaction completes the email gets sent.

If the transaction fails along the line, the job to send the email gets rolled back along with everything else.

DBMS_SCHEDULER is not suitable for this.

Yes, it’s vastly more powerful and flexible than DBMS_JOB but it has a flaw – the call to DBMS_SCHEDULER commits.

So, not only does that mean that in the example mentioned, the job to send the email cannot be rolled back but you’ve only just gone and committed your whole transaction when you didn’t want to.

So then you go down the route of using an autonomous transaction to call DBMS_SCHEDULER and so, whilst your wider transaction control hasn’t been violated, that email is going regardless and it’s going just as soon as the scheduler picks it up.

When what you really should be still using is DBMS_JOB.

And I can’t see DBMS_JOB ever going until that oversight in the implementation of DBMS_SCHEDULER is addressed.

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.

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 75 other followers