Search

OakieTags

Who's online

There are currently 0 users and 35 guests online.

Recent comments

Affiliations

Plan stability

SQL Gone Bad – But Plan Not Changed? – Part 2

In Part 1 of this series I talked about the basic problem, which is that plan_hash_values are not based on the whole plan. One of the main missing components is the predicates associated with a plan, but there are other missing parts as was pointed out in Part 1 of Randolf Geist’s post on the topic. At any rate, predicates seem to be the most critical of the missing parts.

The purpose of this second post on the topic is to talk about diagnosis. Basically how do you identify when some other part of a plan has changed that doesn’t affect the plan_hash_value, specifically a predicate.

SQL Gone Bad – But Plan Not Changed? – Part 1

Last week an interesting issue popped up on a mission critical production app (MCPA). A statement that was run as part of a nightly batch process ran long. In fact, the statement never finished and the job had to be killed and restarted. This particular system is prone to plan stability issues due to various factors outside the scope of this post, so the first thing that the guys checked was if there had been a plan change. Surprisingly the plan_hash_value was the same as it had been for the past several months. The statement was very simple and a quick look at the xplan output showed that the plan was indeed the same with one exception. The predicate section was slightly different.

Displaying SQL Baseline Plans

Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).

The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:

Autotrace Polluting The Shared Pool?

Introduction

Another random note that I made during the sessions attended at OOW was about the SQL*Plus AUTOTRACE feature. As you're hopefully already aware of this feature has some significant shortcomings, the most obvious being that it doesn't pull the actual execution plan from the Shared Pool after executing the statement but simply runs an EXPLAIN PLAN on the SQL text which might produce an execution plan that is different from the actual one for various reasons.

Now the claim was made that in addition to these shortcomings the plan generated by the AUTOTRACE feature will stay in the Shared Pool and is eligible for sharing, which would mean that other statement executions could be affected by a potentially bad execution plan generated via AUTOTRACE rather then getting re-optimized on their own.

New create_1_hint_sql_profile.sql

I modified my create_1_hint_sql_profile.sql script (which I blogged about here: Single Hint Profiles) to allow any arbitrary text sting including quote characters. This is a script that I use fairly often to apply a hint to a single SQL statement that is executing in a production system where we can’t touch the code for some reason. For example, it’s sometimes useful to add a MONITOR hint or a GATHER_PLAN_STATISTICS hint to a statement that’s behaving badly so we can get more information about what the optimizer is thinking. I recently updated the script to allow special characters in the hint syntax. This feature is useful when you want to add something like an OPT_PARAM hint that takes quoted arguments.

Cardinality Feedback

I ran into an interesting issue last week having to do with plan stability. The problem description went something like this:

“I have a statement that runs relatively quickly the first time I run it (around 12 seconds). Subsequent executions always run much slower, usually around 20 or 30 minutes. If I flush the shared pool and run it again elapsed time is back to 12 seconds or so.”

The query looked a little like this:

Licensing Requirements for SQL Profiles

Jonathan Lewis reminded me on my last post that using SQL Profiles (because they are part of the SQL Tuning Advisor) requires a license for Oracle Tuning Pack (which requires a license for the Diagnostics Pack). He also mentioned that Baselines did not require any additional license (at least creating and using Baselines on SQL statements). It’s been a while since I worked on a database that didn’t have both packs, but frankly I wasn’t sure I had a good handle of what was allowed and what wasn’t. So I thought it might be worthwhile to check. There is an easy way to check by the way. I did a post a while back on Tuning Pack and Diagnostic Pack license requirements for running AWR and how to check what was allowed and what wasn’t using the CONTROL_MANAGEMENT_PACK_ACCESS parameter. Here’s a link to the post:

Oracle Management Packs

Here’s an example using the same technique to show that SQL Profiles are indeed disabled by turning off the Diagnostic and Tuning Packs (at least on 11.2.02).

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 7 21:15:21 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SYS@SANDBOX1> @parms
Enter value for parameter: management_pack
 
 
Enter value for isset: Enter value for show_hidden: 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
control_management_pack_access                     DIAGNOSTIC+TUNING                                                      TRUE     TRUE       TRUE
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
 
 
24 rows selected.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> alter system set control_management_pack_access='none';
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
 
20 rows selected.
 
SYS@SANDBOX1> alter system set control_management_pack_access='DIAGNOSTIC+TUNING';
 
System altered.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
 
 
24 rows selected.

So as you can see, there was a SQL Profile on the statement that was used when Diagnostic and Tuning Packs were enabled, but when I set CONTROL_MANAGEMENT_PACK_ACCESS to NONE, the SQL Profile was ignored. Let’s try the same test with a Baseline.

 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 05cq2hb1r37tr
Enter value for plan_hash_value: 568322376
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: 05cq2hb1r37tr
plan_hash_value: 568322376
fixed: NO
enabled: YES
 
Baseline SQLID_05cq2hb1r37tr_568322376 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
   - SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
 
 
25 rows selected.
 
SYS@SANDBOX1> -- so Baseline and SQL Profile Used
SYS@SANDBOX1>
SYS@SANDBOX1> alter system set control_management_pack_access='none';
 
System altered.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
 
 
24 rows selected.

So Baselines apparently do not require licenses for the Tuning and Diagnostics Packs, at least they aren’t disabled by setting the CONTROL_MANAGEMENT_PACK_ACCESS parameter to NONE.

SQL Profiles Disable Automatic Dynamic Sampling

I had an interesting email exchange with a fellow Oracle practitioner, Bryan Grenn, about differences between SQL Profiles and Baselines last week. Basically Bryan observed that SQL Profiles appeared to disable Dynamic Sampling on Global Temporary Tables while Baselines did not. This caused the optimizer’s cost calculations (and therefore the estimated elapsed runtimes) to be incorrect – sometime grossly incorrect. So I did a little follow up testing with a GTT. I used 2 test cases inserting either one row or 100,000 rows into the GTT. With Dynamic Sampling the plans were different (as you might expect). I then tested with Profiles to see how the statement behaved. Here’s is some of the output I generated during the test (note this test was done on an Exadata but non-Exadata environments exhibit the same behavior):

-bash-3.2$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 1 09:42:39 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SYS@SANDBOX1> @parms
Enter value for parameter: dynamic
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         2                                                                      TRUE     FALSE      FALSE
 
SYS@SANDBOX1> !cat e.sql
@flush_pool
set echo on
insert into skew_gtt select * from kso.skew where pk_col = 13635;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
rollback;
set echo off
 
SYS@SANDBOX1> !cat e2.sql
@flush_pool
set echo on
insert into skew_gtt select * from kso.skew where rownum < 100000;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
rollback;
set echo off
 
SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
Enter value for sql_id: 
Enter value for inst_id: 
 
 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- ----------------------------------------------------------------------
    1 1jp7sjmt0wp1j      0 1853478750          2           .00      0 No                 .00 select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Got NL plan
SYS@SANDBOX1> 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       |       | 48466 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                    |              | 90961 |  1687K|  2224K| 48466   (2)| 00:09:42 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     | 90961 |  1154K|       |   130   (1)| 00:00:02 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M|       | 20693   (2)| 00:04:09 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Got Hash plan

Note that the the plans both clearly show that Dynamic Sampling was done at level 2. This is how the optimizer knew there were 999,999 rows in the GTT during one execution and 1 row in the GTT in the other. So at this point I decided to create a SQL Profile on the cursor with the Hash Join plan and see what happened when I ran it with 100K rows in the GTT again.

 
SYS@SANDBOX1> @create_sql_profile
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no (0): 
Enter value for profile_name (PROF_sqlid_planhash): 
Enter value for category (DEFAULT): 
Enter value for force_matching (FALSE): 
 
SQL Profile PROF_1jp7sjmt0wp1j_4093035962 created.
 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       | 20874 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |            |          |
|*  2 |   HASH JOIN                    |              |  8168 |   151K| 20874   (2)| 00:04:11 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M| 20693   (2)| 00:04:09 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - SQL profile PROF_1jp7sjmt0wp1j_4093035962 used for this statement
 
 
25 rows selected.

So the cardinality calculation is definitely wrong now. Notice in step 2 and 3 of the plan the estimated number of rows is 8168 instead of 999,999. This is the default calculated value if no stats are available and Dynamic Sampling is turned off. Note also that the Note section of the plan output does not mention Dynamic Sampling (because it wasn’t done). But the plan was the same even though the calculation was incorrect. That’s because the Profile contained all the hints it needed to coerce the optimizer into producing the desired plan (even though the cardinality was way off).

 
SYS@SANDBOX1> -- cardinality now wrong due to Profile - but still used correct plan
SYS@SANDBOX1> -- let's see hints
SYS@SANDBOX1> 
SYS@SANDBOX1> @sql_profile_hints 
Enter value for profile_name: PROF_1jp7sjmt0wp1j_4093035962
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "G"@"SEL$1")
INDEX_FFS(@"SEL$1" "S"@"SEL$1" ("SKEW"."PK_COL"))
LEADING(@"SEL$1" "G"@"SEL$1" "S"@"SEL$1")
USE_HASH(@"SEL$1" "S"@"SEL$1")
 
9 rows selected.
 
SYS@SANDBOX1> -- So as you can see, the hints force the HASH JOIN plan
SYS@SANDBOX1> -- let's drop the profile and turn off Dynamic_Sampling        
SYS@SANDBOX1> -- this should make the same error on cardinality calc and probably change backto NL plan
SYS@SANDBOX1> 
SYS@SANDBOX1> @drop_sql_profile
Enter value for profile_name: PROF_1jp7sjmt0wp1j_4093035962
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> alter session set optimizer_dynamic_sampling=0;
 
Session altered.
 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |  8201 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |  8168 |   151K|  8201   (1)| 00:01:39 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
SQL_ID  1jp7sjmt0wp1j, child number 1
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       |       | 48377 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                    |              | 99999 |  1855K|  2448K| 48377   (2)| 00:09:41 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     | 99999 |  1269K|       |    29   (0)| 00:00:01 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M|       | 20693   (2)| 00:04:09 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - cardinality feedback used for this statement
 
 
46 rows selected.
 
SYS@SANDBOX1> -- oops cardinality feedback kicked in

Oddly enough I got two child cursors on my two executions of the statement. The first dropped back to the NL plan as expected, but on the second execution Cardinality Feedback kicked in and pushed it back to the HASH Join (which is what it should be doing with 999,999 records in the GTT). Cardinality Feedback is an interesting new feature but since we’re probably already close to the limits of your attention span we’ll leave that for another day.

Bryan also mentioned that he had not seen this issue with Baselines so I thought I’d give that a quick try as well. By the way, here is a link to Bryan’s blog post on the issue. Sure enough he was right.

SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Now let's create a baseline and see if it behaves the same way.
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for plan_hash_value: 1853478750
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: 1jp7sjmt0wp1j
plan_hash_value: 1853478750
fixed: NO
enabled: YES
plan_name: SQLID_1jp7sjmt0wp1j_1853478750
sql_handle: SQL_936b37ad684d18d4
Baseline SQLID_1jp7sjmt0wp1j_1853478750 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQLID_1JP7SJMT0WP1J_1853478750 used for this statement
 
 
26 rows selected.

So it doesn’t appear that Baselines suffer from the same quirk. That is to say that Dynamic Sampling seems to work fine with Baselines as you can see from the cardinality estimate in lines 2 and 3 of the plan along with the Note section.

So why is this important? Well it may not actually be all that important, because the hints in my simple tests (and in Bryan’s production system for that matter), were sufficient to enforce the desired plan, despite the fact that Dynamic Sampling was disabled. However, it’s possible that this could have other negative side effects. For example, 11gR2 has the ability to automatically parallelize long running queries via setting PARALLEL_DEGREE_POLICY to AUTO. By default, any query that the optimizer estimates will run longer than 10 seconds will be evaluated and a DOP will be automatically calculated for that statement. This issue could affect how that feature behaves by affecting the estimated run time of the statements. I have not had a chance to play with that yet though. So much to learn, so little time.

========================================
1/7/2011 – Update due to Dominic’s comments:
========================================

Here’s some output showing that the dbms_xplan.display_sql_plan_baseline function does not show stored cardinality values (at least that’s what I think it shows).

SYS@LAB11202> @parms       
Enter value for parameter: dynamic
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         2                                                                      TRUE     TRUE       TRUE
 
1 row selected.
 
SYS@LAB11202> !cat a.sql
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
SYS@LAB11202> @a
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> /
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 
 
25 rows selected.

So that’s interesting that Dynamic Sampling level was automatically adjusted up to 5. I have no idea why at this point, but that’s a project for another day. By the way, here’s what the 10053 trace file had to say about that:

Dynamic sampling level auto-adjusted from 2 to 5

Now I’ll create a baseline on that statement and run my query again.

SYS@LAB11202> @create_baseline
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for plan_hash_value: 2943048660
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): 
 
Baseline created.
 
SYS@LAB11202> @a
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> /
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=5)
   - SQL plan baseline SQL_PLAN_96utrppn4u66ncefcc71f used for this statement
 
 
26 rows selected.

So Dynamic Sampling was still used and the Baseline did not affect the cardinality. But let’s see what happens if we use the dbms_xplan.display_sql_plan_baseline function to view the “expected” plan.

 
SYS@LAB11202> @baselines
Enter value for sql_text: %gtt%
Enter value for handle_name: 
Enter value for plan_name: 
 
SQL_HANDLE               PLAN_NAME                      SQL_TEXT                                           ENABLED ACC FIX LAST_EXECUTED
------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SQL_936b37ad684d18d4     SQL_PLAN_96utrppn4u66ncefcc71f select count(*) from kso.skew s , skew_gtt g where YES     YES NO  07-jan-11 10:12
 
1 row selected.
 
SYS@LAB11202> !cat dplan_baseline.sql
set lines 150
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'))
/
 
SYS@LAB11202> @dplan_baseline
Enter value for sql_handle: SQL_936b37ad684d18d4
Enter value for plan_name: SQL_PLAN_96utrppn4u66ncefcc71f
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_936b37ad684d18d4
SQL text: select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_96utrppn4u66ncefcc71f         Plan id: 3472672543
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    19 |  8200   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |  8168 |   151K|  8200   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
27 rows selected.
 
SYS@LAB11202> @baseline_hints
Enter value for baseline_plan_name: SQL_PLAN_96utrppn4u66ncefcc71f
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 5)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "G"@"SEL$1")
INDEX(@"SEL$1" "S"@"SEL$1" ("SKEW"."PK_COL"))
LEADING(@"SEL$1" "G"@"SEL$1" "S"@"SEL$1")
USE_NL(@"SEL$1" "S"@"SEL$1")
 
10 rows selected.

Note that the cardinality displayed is the default calculated value we would get if Dynamic Sampling was turned off. So my guess is that the dbms_xplan.display_sql_plan_baseline function does something like Explain Plan, coming up with an “expected” plan. It probably disables some things that it thinks could throw it off like Dynamic Sampling, Cardinality Feedback, Bind Variable Peeking, etc… Note, this is just a guess though as I haven’t got the time to test that right now.

I will add that although Profiles do appear to explicitly disable Dynamic Sampling, I still see them as being more flexible than Baselines because of the ease with which we can manipulate them via the dbms_sqltune.import_sql_profile procedure. If this issue of disabling Dyanmic Sampling causes any undesirable side affects, it would be a simple matter to create a Baseline on top of the Profile and drop the Profile, or manually add a DYNAMIC_SAMPLING hint to your Profile.

Interaction Between Baselines and SQL Profiles

In a previous post (GATHER_PLAN_STATISTICS) I mentioned that SQL Profiles and Baselines can both be applied to a single statement. In this case, it appears that the hints are merged. The Notes section of the XPLAN output shows that both the Baseline and the Profile are in effect. I wanted to prove to myself that the hints from both the Profile and Baseline were indeed applied. So here’s my simple test case:

Basic Design For the Test:

  1. Use a Baseline to make a statement do something it wouldn’t normally do
  2. Add a Profile that makes the same statement do something else it wouldn’t normally do
  3. Verify that the statement now does both “thingies”
> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 2 20:09:53 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB112> -- first the set up
SYS@LAB112> -- 32M row table with an index on col1
SYS@LAB112> !cat avgskewi.sql
select avg(pk_col) from kso.skew
where col1 = 136133
/
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:00.00
SYS@LAB112>
SYS@LAB112> @find_sql
Enter value for sql_text: %where col1 = 136133%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          3           .00           36 select avg(pk_col) from kso.skew where col1 = 136133
 
1 row selected.
 
Elapsed: 00:00:00.12
SYS@LAB112>
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
20 rows selected.
 
Elapsed: 00:00:00.03
SYS@LAB112>
SYS@LAB112> -- so the index is used as expected - does 36 lio's and completes in < 1/100 of a second
SYS@LAB112> -- now let's make it do something it wouldn't normally do (with a Baseline)
SYS@LAB112> -- one way is to create a Profile, create a Baseline on top, drop the Profile
SYS@LAB112>
SYS@LAB112>
SYS@LAB112> 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): PROFILE_84q0zxfzn5u6s_GPS
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full(skew@sel$1)
Profile PROFILE_84q0zxfzn5u6s_FULL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
Elapsed: 00:00:12.71
SYS@LAB112>
SYS@LAB112> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
no rows selected
 
Elapsed: 00:00:00.18
SYS@LAB112>
SYS@LAB112> -- run again, SPM makes you run it twice ...   
SYS@LAB112> 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
Elapsed: 00:00:07.32
SYS@LAB112>
SYS@LAB112> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0  568322376          1          7.31      162,301 select avg(pk_col) from kso.skew where col1 = 136133
 
Elapsed: 00:00:00.10
SYS@LAB112>
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28360 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    35 |   840 | 28360   (1)| 00:05:41 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_FULL used for this statement
 
 
23 rows selected.
 
Elapsed: 00:00:00.05
SYS@LAB112>
SYS@LAB112> -- so it's now doing a full table scan, 162K lio's and takes several seconds
SYS@LAB112> 
SYS@LAB112> -- now create the Baseline on the statement that's already using a Profile
SYS@LAB112> 
SYS@LAB112> @create_baseline
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for plan_hash_value: 568322376
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQL_sqlid_planhashvalue): 
sql_id: 84q0zxfzn5u6s
plan_hash_value: 568322376
fixed: NO
enabled: YES
plan_name: SQL_84q0zxfzn5u6s_568322376
sql_handle: SYS_SQL_94dc89c011141f02
Baseline SQL_84q0zxfzn5u6s_568322376 created.
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.06
SYS@LAB112>
SYS@LAB112> @avgskewi                       
 
AVG(PK_COL)
-----------
   15636133
 
Elapsed: 00:00:07.44
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
Elapsed: 00:00:07.52
SYS@LAB112>
SYS@LAB112> -- obviously still doing the full table scan, but let's check anyway
SYS@LAB112> 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28360 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    35 |   840 | 28360   (1)| 00:05:41 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_FULL used for this statement
   - SQL plan baseline SQL_84Q0ZXFZN5U6S_568322376 used for this statement
 
 
24 rows selected.
 
Elapsed: 00:00:00.03
SYS@LAB112>
SYS@LAB112> -- let's check hints in Profile and Baseline
SYS@LAB112> 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_FULL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full(skew@sel$1)
 
1 rows selected.
 
Elapsed: 00:00:00.10
SYS@LAB112>
SYS@LAB112> @baseline_hints
Enter value for baseline_plan_name: SQL_84Q0ZXFZN5U6S_568322376
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
 
6 rows selected.
 
Elapsed: 00:00:00.09
SYS@LAB112>
SYS@LAB112> -- so the Baseline has inherited the full hint
SYS@LAB112> 
SYS@LAB112> -- now let's drop the original Profile and add another non-standard hint to see if they are merged
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_FULL
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.02
SYS@LAB112>
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): PROFILE_84q0zxfzn5u6s_GPS
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: GATHER_PLAN_STATISTICS
Profile PROFILE_84q0zxfzn5u6s_GPS created.
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.05
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:07.51
SYS@LAB112> /
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:08.22
SYS@LAB112>
SYS@LAB112> -- if that worked we should still have the full table scan from the Baseline, and the extended stats from the Profile with GATHER_PLAN_STATISTICS
SYS@LAB112> 
SYS@LAB112> !cat dplan_allstats.sql
set lines 180
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats  +peeked_binds'))
/
 
SYS@LAB112> @dplan_allstats
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 1
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      2 |        |      2 |00:00:15.69 |     324K|    324K|
|   1 |  SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:15.69 |     324K|    324K|
|*  2 |   TABLE ACCESS FULL| SKEW |      2 |     35 |     64 |00:00:12.02 |     324K|    324K|
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_GPS used for this statement
   - SQL plan baseline SQL_84Q0ZXFZN5U6S_568322376 used for this statement
 
 
24 rows selected.
 
Elapsed: 00:00:00.03
SYS@LAB112>
SYS@LAB112> -- notice that the XPLAN output has A-Rows and A-Time columns - means GATHER_PLAN_STATISTICS was used 
SYS@LAB112> -- otherwise it would have thrown a warning message 
SYS@LAB112>
SYS@LAB112> -- so that worked - it merged the hints! 
SYS@LAB112> 
SYS@LAB112> -- quick verify
SYS@LAB112> 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_GPS
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
GATHER_PLAN_STATISTICS
 
1 rows selected.
 
Elapsed: 00:00:00.04
 
SYS@LAB112> @baseline_hints
Enter value for baseline_plan_name: SQL_84Q0ZXFZN5U6S_568322376
 
OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
 
6 rows selected.
 
Elapsed: 00:00:00.09
SYS@LAB112>
SYS@LAB112> -- one more test, drop the Profile and the extended stats should go away
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_GPS
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.02
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:07.20
SYS@LAB112> /
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:07.91
SYS@LAB112> @dplan_allstats
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 1
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  SORT AGGREGATE    |      |      1 |
|*  2 |   TABLE ACCESS FULL| SKEW |     35 |
--------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL plan baseline SQL_84Q0ZXFZN5U6S_568322376 used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
26 rows selected.
 
Elapsed: 00:00:00.03
SYS@LAB112>
SYS@LAB112> -- yep no more extended stats (warning message), but the full table scan is still working
SYS@LAB112> 
SYS@LAB112> -- let's put it back the way it was
SYS@LAB112>
SYS@LAB112> @baselines
Enter value for sql_text: 
Enter value for name: 
Enter value for plan_name: SQL_84Q0ZXFZN5U6S_568322376
 
SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_94dc89c011141f02       SQL_84Q0ZXFZN5U6S_568322376    select avg(pk_col) from kso.skew                   YES     YES NO  02-feb-10 20:19
 
1 row selected.
 
Elapsed: 00:00:00.04
SYS@LAB112>
SYS@LAB112> @drop_baseline
Enter value for sql_handle: SYS_SQL_94dc89c011141f02
Enter value for plan_name: SQL_84Q0ZXFZN5U6S_568322376
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:00.01
SYS@LAB112> /
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:00.00
SYS@LAB112> @dplan_allstats
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
-----------------------------------------------------------
| Id  | Operation                    | Name      | E-Rows |
-----------------------------------------------------------
|   0 | SELECT STATEMENT             |           |        |
|   1 |  SORT AGGREGATE              |           |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |     35 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |     35 |
-----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
26 rows selected.
 
Elapsed: 00:00:00.03
SYS@LAB112>
SYS@LAB112> -- back to using the index and no stats

As usual I used a bunch of my scripts. I believe all the scripts referenced can be found in one of these two zip files:

Controlling Execution Plans Zip File

My Favorite Scripts 2010 zip file

I have a few thoughts as to why the developers decided to implement these two features this way. Remember that SQL Profiles were designed to be generated by the SQL Tuning Advisor which does statistical analysis on a query and potentially produces hints to correct calculations that the optimizer would otherwise get wrong. So it’s possible that the developers decided they wanted these types of statistical correction hints to be combined with plans already being “enforced” by Baselines. I must say though that I don’t really think these two constructs would work well together in most cases as the hints may well end up working against each other. And since Baselines are aware of the plan they are trying to reproduce, anything that actually alters the plan would basically disable all the hints associated with the Baseline. Although I expect that the plan output would probably still say the Baseline had been used. – I haven’t tested that though – so much to do, so little time.

How to Lock SQL Profiles Generated by SQL Tuning Advisor

I’ve mentioned (many times) that I think SQL Profiles that are generated by the SQL Tuning Advisor (STA) tend to sour over time.

After seeing it happen at a few sites I began to wonder why. So first a few facts about the SQL Profiles that STA generates:

  1. They are simply a set hints that get applied to statements behind the scenes during parsing
  2. They consist mainly of OPT_ESTIMATE hints which modify optimizer calculations
  3. They also may contain direct statistics modification hints (COLUMN_STATS, TABLE_STATS)
  4. They usually contain a OPTIMIZER_FEATURES_ENABLED hint
  5. They very occasionally contain other environment type hints (FIRST_ROWS, etc…)
  6. They do not contain directive hints (FULL, INDEX, NESTED_LOOP, etc..)
  7. The names of STA profiles start with SYS_SQLPROF
  8. STA’s goal is to do a more through job of analyzing a SQL statement to get a better plan

I wrote a little query (sql_profile_distinct_hint.sql) to pull a list of hints from a 10g database along with the number of their occurrences and ran it on several production systems where STA Profiles had been created. Here’s the output from a  system that had 14 STA Profiles.

SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%
 
HINT                                                 COUNT(*)
-------------------------------------------------- ----------
COLUMN_STATS                                               13
FIRST_ROWS                                                  1
IGNORE_OPTIM_EMBEDDED_HINTS                                 1
INDEX_STATS                                                 1
OPTIMIZER_FEATURES_ENABLE                                  14
OPT_ESTIMATE                                              178
TABLE_STATS                                                 2

Notice that the vast majority of hints are of the OPT_ESTIMATE variety. Now let’s have a look at the actual hints contained in a STA Profile.

 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_0126f1743c7d0005
 
HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default) 
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)
COLUMN_STATS("KSO"."SKEW", "PK_COL", scale, length=5)
COLUMN_STATS("KSO"."SKEW", "COL1", scale, length=4 distinct=828841 nulls=12.8723033 min=1 max=1000000)
TABLE_STATS("KSO"."SKEW", scale, blocks=162294 rows=35183107.66)
 
7 rows selected.

So on this particular STA Profile, the OPT_ESTIMATE hint has been used to tell the optimizer to change the estimate of rows for table A in query block SEL$6 by multiplying it by 2207 (roughly). In addition, there are hints which are basically hard coding table stats and column stats. So as you can see, these hints, while they may be accurate when the Profile is created, are unlikely to remain accurate over the long haul. In fairness, the OPT_ESTIMATE hint does make sense in situations where the optimizer will never get a calculation correct because of a short coming in it’s abilities (correlated columns is a good example of this type of situation). And in those conditions, implementing a STA generated Profile is a valid long term approach. But in my experience this is the exception rather than the rule.

So what are STA Profiles good for? Well two things:

First, they are very good at showing us where the optimizer is having a problem. If you look at the hints that are generated, it is easy to identify the OPT_ESTIMATE hints where the scaling factors are off the chart (hint: anything with an exponent is a place where the optimizer is struggling). This is easy to do with my sql_profile_hints.sql script by the way. Here’s a set of OPT_ESTIMATE hints. Can you spot the place where the optimizer is really having a problem?

OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06) 
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=4.446153275) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=7.884506683) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1"), SCALE_ROWS=26.34181566) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=839.9683673) 
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=5.083144565e+11)
OPT_ESTIMATE(@"SEL$5", INDEX_SCAN, "C"@"SEL$5", ORDER_FG_ITEM_IX3, SCALE_ROWS=0.2507281101)

It’s the first two lines and whatever alias F refers to is our problem area. The OPT_ESTIMATE hint tells the optimizer to decrease it’s estimate by a factor of 8.883203639e-06. So the optimizer has vastly overestimated the rows that will be returned by the index.

Second, STA Profiles are sometimes capable of producing better plans. This is primarily due to the fact that STA can take as long as you give it to analyze a statement, making sure that all the optimizer’s calculations are correct. It does this by running various pieces of the statement and checking that the number of rows the optimizer has estimated are actually correct. Obviously this can take a while on complex statements, much longer than the optimizer is allowed when parsing a statement. But as I’ve already shown, the SQL Profiles that get created to enable those better plans have a pretty good chance of going sour on us over time.

Which leads me to the point of this post. We can have our cake and eat it too! We can create the SQL Profile as recommended by STA and then “lock” the plan into place by converting the OPT_ESTIMATE hints to directive type hints. I put the word “lock” in quotes because there is really no such thing as “locking” a plan. It’s just that using directive hints as opposed to OPT_ESTIMATE hints, significantly lowers the probability of the plan changing in the future. So how do we make this conversion. Well I have a script for that called lock_STA_profile.sql. Here’s an example showing how it works.

 
SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROF_6kymwy3guu5uq_1388734953  DEFAULT         ENABLED  select 1                                                               YES
PROFILE_cnpx9s9na938m_MANUAL   DEFAULT         ENABLED  select /*+ opt_param('statistics_level','all') */ * from kso.skew wher NO
PROF_79m8gs9wz3ndj_3723858078  DEFAULT         ENABLED  /* SQL Analyze(252,1) */ select avg(pk_col) from kso.skew              NO
PROFILE_9ywuaagwscbj7_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_arcvrg5na75sw_3723858078  DEFAULT         ENABLED  select /*+ index(skew@sel$1 skew_col1) */ avg(pk_col) from kso.skew wh NO
SYS_SQLPROF_01274114fc2b0006   DEFAULT         ENABLED  select i.table_owner, i.table_name, i.index_name, FUNCIDX_STATUS, colu NO
SYS_SQLPROF_0127d10ffaa60000   DEFAULT         ENABLED  select table_owner||'.'||table_name tname , index_name, index_type, st NO
SYS_SQLPROF_01281e513ace0000   DEFAULT         ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO
coe_abwg9nwg8prsj_3723858078   DEFAULT         ENABLED                                                                         NO
PROF_84q0zxfzn5u6s_2650913906  TEST            ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_0pvj94afp6faw_FULL        DEFAULT         ENABLED  select /* test 1 hint */ avg(pk_col) from kso.skew a where col1 = 2222 NO
PROF_875qbqc2gw2qz_4201340344  DEFAULT         ENABLED  select /* NOT IN */ department_name                                    NO
PROF_09gdkwq1bs48h_167097056   DEFAULT         ENABLED  select /*+ index (skew skew_col3_col2_col1) */ count(*) from kso.skew  NO
PROFILE_4cp821ufcwvgc_moved    DEFAULT         ENABLED  select count(*) from kso.skew where col3 = '01-jan-10'                 NO
PROF_8wvgj0n4kh6dx_2650913906  DEFAULT         ENABLED  select avg(pk_col) from kso.skew a where col1 = 333333                 NO
PROFILE_g737q1pfmbvjj_moved    DEFAULT         ENABLED  select /*+ full (skew) */ avg(pk_col) from kso.skew where col1 = 13613 NO
PROFILE_cvdnr0b8dcxzz_MANUAL   DEFAULT         ENABLED  select /* aasdas */ avg(pk_col) from kso.skew where col1 = 136133      NO
PROF_719syuvrm29tq_931251584   DEFAULT         ENABLED  SELECT IOBJID, IDOBJID, INAME, IOWNER, IOWNERID, ISPACE, ITSNO, IFILEN NO
PROF_g4gp07gt2z920_105323984   DEFAULT         ENABLED  update sys.scheduler$_job set  last_start_date = :1, running_instance  NO
 
25 rows selected.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01281e513ace0000
 
HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default)
FIRST_ROWS(1)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)
 
5 rows selected.
 
SYS@LAB112> @find_sql
Enter value for sql_text: SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORD%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH EXECS AVG_ETIME  AVG_LIO SQL_TEXT
------------- ------ ---------- ----- --------- -------- --------------------------------------------------
bqfx5q2jas08u      0 2496534803    86       .00       12 SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE
                                                         (T) ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_A
                                                         DVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR
                                                         _PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P
                                                         2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMP
                                                         LETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND
                                                         T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID
                                                         AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETE
                                                         R_VALUE = SYS_CONTEXT('USERENV','INSTANCE') AND T.
                                                         TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_I
                                                         D' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 ) ORDER BY
                                                          T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1
 
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: bqfx5q2jas08u
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bqfx5q2jas08u, child number 0
-------------------------------------
SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */
T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC)
T, DBA_ADVISOR_PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE
T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START
>= (SYSDATE - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID
AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE =
SYS_CONTEXT('USERENV','INSTANCE') AND T.TASK_ID = P2.TASK_ID AND
P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 )
ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1
 
Plan hash value: 2496534803
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |       |       |     9 (100)|          |
|*  1 |  COUNT STOPKEY                     |                        |       |       |            |          |
|   2 |   VIEW                             |                        |     2 |    26 |     9   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                    |                        |       |       |            |          |
|   4 |     NESTED LOOPS                   |                        |     2 |   240 |     9   (0)| 00:00:01 |
|*  5 |      FILTER                        |                        |       |       |            |          |
|   6 |       NESTED LOOPS OUTER           |                        |     2 |   188 |     7   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                        |     2 |   126 |     5   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WRI$_ADV_TASKS         |     2 |    74 |     3   (0)| 00:00:01 |
|   9 |          INDEX FULL SCAN DESCENDING| WRI$_ADV_TASKS_PK      |   822 |       |     2   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| WRI$_ADV_PARAMETERS    |     1 |    26 |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | WRI$_ADV_PARAMETERS_PK |     1 |       |     0   (0)|          |
|* 12 |        TABLE ACCESS BY INDEX ROWID | WRI$_ADV_EXECUTIONS    |     1 |    31 |     1   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | WRI$_ADV_EXECS_PK      |     1 |       |     0   (0)|          |
|* 14 |      INDEX UNIQUE SCAN             | WRI$_ADV_PARAMETERS_PK |     1 |       |     0   (0)|          |
|* 15 |     TABLE ACCESS BY INDEX ROWID    | WRI$_ADV_PARAMETERS    |     1 |    26 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   5 - filter((DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTER
              RUPTED',5,'CANCELLED',6,'FATAL ERROR')='COMPLETED' AND
              NVL("E"."EXEC_START","A"."EXEC_START")>=SYSDATE@!-1))
   8 - filter(("A"."ADVISOR_NAME"='ADDM' AND "A"."HOW_CREATED"='AUTO' AND
              BITAND("A"."PROPERTY",6)=4))
  10 - filter("A"."VALUE"=TO_CHAR(:B1))
  11 - access("A"."ID"="A"."TASK_ID" AND "A"."NAME"='DB_ID')
  12 - filter("A"."ADVISOR_ID"="E"."ADVISOR_ID")
  13 - access("A"."ID"="E"."TASK_ID" AND "A"."LAST_EXEC_NAME"="E"."NAME")
  14 - access("A"."ID"="A"."TASK_ID" AND "A"."NAME"='INSTANCE')
  15 - filter("A"."VALUE"=SYS_CONTEXT('USERENV','INSTANCE'))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL profile SYS_SQLPROF_01281e513ace0000 used for this statement
 
 
57 rows selected.
 
SYS@LAB112> @lock_STA_profile
Enter value for sql_id: bqfx5q2jas08u
Enter value for child_no (0): 0
Enter value for new_profile_name (PROF_sqlid_planhash): 
Enter value for force_matching (FALSE): 
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: %bqfx5q2jas08u%
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
PROF_bqfx5q2jas08u_2496534803  DEFAULT         ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROF_bqfx5q2jas08u_2496534803
 
HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
FIRST_ROWS(1)
NO_PARALLEL
OUTLINE_LEAF(@"SEL$86A1760A")
MERGE(@"SEL$5")
MERGE(@"SEL$532C0C35")
MERGE(@"SEL$6")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$532C0C35")
MERGE(@"SEL$4")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$58B2FD6B")
ELIMINATE_OBY(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "TASK_LIST"@"SEL$1")
INDEX_DESC(@"SEL$86A1760A" "A"@"SEL$4" ("WRI$_ADV_TASKS"."ID"))
INDEX_RS_ASC(@"SEL$86A1760A" "A"@"SEL$6" ("WRI$_ADV_PARAMETERS"."TASK_ID" "WRI$_ADV_PARAMETERS"."NAME"))
INDEX_RS_ASC(@"SEL$86A1760A" "E"@"SEL$4" ("WRI$_ADV_EXECUTIONS"."TASK_ID" "WRI$_ADV_EXECUTIONS"."NAME"))
INDEX(@"SEL$86A1760A" "A"@"SEL$5" ("WRI$_ADV_PARAMETERS"."TASK_ID" "WRI$_ADV_PARAMETERS"."NAME"))
LEADING(@"SEL$86A1760A" "A"@"SEL$4" "A"@"SEL$6" "E"@"SEL$4" "A"@"SEL$5")
USE_NL(@"SEL$86A1760A" "A"@"SEL$6")
USE_NL(@"SEL$86A1760A" "E"@"SEL$4")
USE_NL(@"SEL$86A1760A" "A"@"SEL$5")
NLJ_BATCHING(@"SEL$86A1760A" "A"@"SEL$5")
 
29 rows selected.
 
SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: SYS%
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
SYS_SQLPROF_01274114fc2b0006   DEFAULT         ENABLED  select i.table_owner, i.table_name, i.index_name, FUNCIDX_STATUS, colu NO
SYS_SQLPROF_0127d10ffaa60000   DEFAULT         ENABLED  select table_owner||'.'||table_name tname , index_name, index_type, st NO
SYS_SQLPROF_01281e513ace0000   SAVED           ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO
 
3 rows selected.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01281e513ace0000
 
HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default)
FIRST_ROWS(1)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)
 
5 rows selected.
 
SYS@LAB112>

So in this example I listed all the SQL Profiles in existence on the system (using sql_profile.sql). Then I showed the hints associated with STA Profile, SYS_SQLPROF_01281e513ace0000 with sql_profile_hints.sql. Then I located the sql statement in v$sql using the find_sql.sql script. Then I used dbms_xplan (via the dplan.sql script) to show the plan for the statement (proving that it was using the STA Profile). Then I used the lock_STA_profile.sql script to create a directive hint based Profile in place of the OPT_ESTIMATE hint based Profile. Then I showed the hints for the new SQL Profile. Note that the original STA Profile is not dropped, but rather moved to the SAVED category, so you can still look at its hints as I have done at the end of this example.

So that’s it. This is a complex topic and I have blogged about it before on numerous occasions. You may want to look back at this post, Oracle Support Sanctions Manually Created SQL Profiles, to get a better feel for where the hints came from that are used to replace the OPT_ESTIMATE hints. By the way, Jonathan Lewis and Tom Kyte have also written about this feature. (I trust you can find them via Google)

Also, I have written a chapter on Plan Stability in the upcoming Apress book, Pro Oracle SQL. The chapter is 65 or so pages long and it covers SQL Profiles in depth, so if you are hungry for more info on this topic, I highly recommend it. ;)

You can pre-order the book here: Pro Oracle SQL (if you are so inclined)

It should be released in a few weeks.