Kerry Osborne's Oracle Blog

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.

So first I thought I would show a few examples of statements with the same sql_id and plan_hash_value that have other plan differences (in the predicate section). To do this I used a method proposed by Randolf Geist a few years back in his 2nd post on the topic which covered Alternative Ways to Calculate a PLAN_HASH_VALUE In that post, Randolf shows several ways to compute a hash value on any or all of the columns in the v$sql_plan table. I wrote a simple script around one of the those methods (find_pred_mismatch.sql), and as you might guess from the name, I limited this version to not include all the columns in v$sql_plan, but to only identify statements with mismatched predicates. To be more explicit, the script will locate statements in the shared_pool that have multiple child cursors, where there are more than one set of predicates to go with a single plan_hash_value. Here’s an example:

SYS@DEMO1> @find_pred_mismatch

Type created.


SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER   THE_HASH ARE_H
------------- --------------- ------------ ---------- -----
063m5s0cvrr19      1502175119            0 2709091620 DIFF!
093fgfvygm51m      3114044936            0 3689661040 DIFF!
0cn2wm9d7zq8d      1540383338            0 3746559344 DIFF!
0pt4jfmq9f1q0      3078496799            0 1309675335 DIFF!
155cwuv2pfp1d       768389713            0 2982291916 DIFF!
18c2yb5aj919t      1032442798            0 1714458756 DIFF!
1n9crga6mbw2x      4174841998            0 2752042257 DIFF!
1ytxrt5qp9qdd      2707146560            0 3757837347 DIFF!
23buxzfxyp1vy      3143617369            0 2089881285 DIFF!
23nad9x295gkf       891847141            0 4056778555 DIFF!
24zvjzuyrxh3w      1877711096            0 1680905434 DIFF!
28n17ru48jnh5      1665111388            0 3584687734 DIFF!
2j0fw19fph49j      1337823903            0 2431841938 DIFF!
2kd6nusgzc3uw      3151266570            0 3024843785 DIFF!
2rpwgryn7pxz5      3329544515            0  452505826 DIFF!
35nhk48nxwc0v      2553960494            0  117262982 DIFF!
3bc73t2h9mwxc      1420457580            0 1226225583 DIFF!
3gputsqv4u1j3      3161304745            0 2252819340 DIFF!
3zauy2zqryrsx      1420457580            0 1128973296 DIFF!
42q1qby3huf2c      3069437562            0 4008632079 DIFF!
47mm81hm9sggy      1836210496            0 1554180227 DIFF!
4g46czps3t55u      2714703948            0 4063401817 DIFF!
4n2gca427719q      1360319091            0 4013571180 DIFF!
4tpsnbkt1dm5j      2960949352            0 3341004783 DIFF!
5dyhfnkzta2zm      3767331201            0 4238766232 DIFF!
5h91zx386wbht       293199272            0  949799344 DIFF!
5s34t44u10q4g      2693539438            0  839739072 DIFF!
5uw1u291s3m0k       219265157            0  642280427 DIFF!
61tn3mam0vq0b      2012170170            0 2048362545 DIFF!
63t3ufgq37m0c      1155609947            0  844291465 DIFF!
69k5bhm12sz98      3091659676            0  356777601 DIFF!
6cp74g22fzahf        76968983            0 1617454724 DIFF!
6wm3n4d7bnddg      1772758172            0 1148123313 DIFF!
78kp0fcyxavzb      2960949352            0 1085639264 DIFF!
7ah4afrggrw5c      4213028598            0 4285032606 DIFF!
7g4rxwbvhdh3q      3170022080            0 2083442940 DIFF!
7hspvruktu52b      4016032974            0 2538340188 DIFF!
84p3g5b5bsfvn       681044650            0 3826083810 DIFF!
86521pa77y28j      3760090177            0 3887843475 DIFF!
8ak9gkw2mjhvr      1526940012            0 2946674232 DIFF!
8p9z2ztb272bm       408663731            0 3293625021 DIFF!
aca4xvmz0rzup       427901911            0 4215668999 DIFF!
akh9zqqkx3wj7      2306922995            0 2084689096 DIFF!
akx4284f2vjnv      3948068913            0 2662025793 DIFF!
amycufzt6uq5f      3283312188            0 1896511712 DIFF!
atnkqhrp3t7xa      2196914545            0   26873046 DIFF!
aw2x7hh2a9ag0      1148557212            0  719001678 DIFF!
b41wak2bb7atw       108532975            0 1699960507 DIFF!
bhvyz9bgyrhb2      1134671139            0 2402404248 DIFF!
c8gnrhxma4tas      4024720576            0 2473084105 DIFF!
cc7vvmrsxzyq1      1849127868            0 1912933403 DIFF!
cjtaqp92v10bn       922118807            0 2313573387 DIFF!
ckfgcyv05xptf      2869192503            0 3932622422 DIFF!
cw860p03hy5ff      1502175119            0 2915988156 DIFF!
cyw0c6qyrvsdd       192117504            0 2551186960 DIFF!
d53nc7j6n1057      1356236608            0  582788179 DIFF!
dyj1ssw8jw54f      1836210496            0   66902761 DIFF!
fkjkrv5ycz96u      2247257083            0 1809299677 DIFF!
gdn3ysuyssf82      4024720576            0 2473084105 DIFF!
gwbdd5m45ugpm      3180770434            0  235716193 DIFF!

60 rows selected.

SYS@DEMO1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));
Enter value for sql_id: 24zvjzuyrxh3w
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  24zvjzuyrxh3w, child number 0
-------------------------------------
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2  AND
model=:3

Plan hash value: 1877711096

--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS STORAGE FULL| METAXSL$ |     3 |    99 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - storage(("XMLTAG"=:1 AND "TRANSFORM"=:2 AND "MODEL"=:3))
       filter(("XMLTAG"=:1 AND "TRANSFORM"=:2 AND "MODEL"=:3))

SQL_ID  24zvjzuyrxh3w, child number 1
-------------------------------------
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2  AND
model=:3

Plan hash value: 1877711096

----------------------------------------------
| Id  | Operation                 | Name     |
----------------------------------------------
|   0 | SELECT STATEMENT          |          |
|*  1 |  TABLE ACCESS STORAGE FULL| METAXSL$ |
----------------------------------------------

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

   1 - storage(("MODEL"=:3 AND "TRANSFORM"=:2 AND "XMLTAG"=:1))
       filter(("MODEL"=:3 AND "TRANSFORM"=:2 AND "XMLTAG"=:1))

Note
-----
   - rule based optimizer used (consider using cbo)


44 rows selected.

Continue reading ‘SQL Gone Bad – But Plan Not Changed? – Part 2’ »

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.

As a quick diversion, you probably already know that the plan_hash_value is calculated based on partial information about the plan. Arguably it’s the most important parts, but there are some important parts of the plan that are not included (namely the stuff that shows up in the predicate section of the plan). Randolf Geist explained which parts of the plan are used in calculating the plan_hash_value well in a post on How PLAN_HASH_VALUES Are Calculated several years ago. His summary was this:

So in summary the following conclusions can be made:

– The same PLAN_HASH_VALUE is merely an indicator that the same operations on objects of the same name are performed in the same order.

– It tells nothing about the similarity of the expected runtime performance of the execution plan, due to various reasons as demonstrated. The most significant information that is not covered by the PLAN_HASH_VALUE are the filter and access predicates, but there are other attributes, too, that are not part of the hash value calculation.

The production issue got me thinking about several things:

Could I come up with a simple test case to show a severe performance degradation between executions of a statement with the same plan_hash_value because of a change in the predicates? (the answer is it’s pretty easy actually)

What forensics could be used to determine when this situation has occurred?

How do you fix the problem?

Continue reading ‘SQL Gone Bad – But Plan Not Changed? – Part 1’ »

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:

This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.

But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:

1. run a statement that uses an index and check the plan
2. create a Baseline on the statement using the index (using my create_baseline.sql script)
3. check the hints stored with the baseline (using my baselines_hints.sql script)
4. run the statement again and check the real plan to see that the Baseline was used
5. use the display_sql_plan_baseline function to show the Baseline plan
6. make the index invisible (thus rendering the Baseline plan non-reproducible)
7. execute the statement again and check the real plan
8. use the display_sql_plan_baseline function to show the Baseline plan

So here’s the test:

Continue reading ‘Displaying SQL Baseline Plans’ »

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. The change makes use of the q-Quote feature which I blogged about here: q-Quote. (the original version just barfed on quotes being input as part of the hint)

Here’s an example of how to use it:

SYS@SANDBOX1> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.00
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;

AVG(PK_COL)
-----------
  1849142.5

1 row selected.

Elapsed: 00:00:28.08
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg      0 2684249835      1      28.07      0 No             .00 select avg(pk_col) from kso.skew3 where col1 < 0

1 row selected.

Elapsed: 00:00:00.02
SYS@SANDBOX1> @create_1_hint_sql_profile.sql
Enter value for sql_id: a6j7wgqf84jvg
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint_text: opt_param('cell_offload_processing' 'true')

Profile PROFILE_a6j7wgqf84jvg_MANUAL created.

Elapsed: 00:00:00.07
SYS@SANDBOX1> @sql_profile_hints
Enter value for profile_name: PROFILE_a6j7wgqf84jvg_MANUAL

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
opt_param('cell_offload_processing' 'true')

1 rows selected.

Elapsed: 00:00:00.04
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;

AVG(PK_COL)
-----------
  1849142.5

1 row selected.

Elapsed: 00:00:05.11
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg      0 2684249835      1      28.07      0 No             .00 select avg(pk_col) from kso.skew3 where col1 < 0
a6j7wgqf84jvg      1 2684249835      1       5.10      0 Yes          99.99 select avg(pk_col) from kso.skew3 where col1 < 0

In the example I turned off cell offload processing with the ALTER SESSION and ran a SQL statement that took 28 seconds. Then I used my fsx.sql script to verify that the statement was not offloaded and to find the SQL_ID. Next I created a 1 hint Profile with an OPT_PARAM hint that set the cell_offload_processing parameter back to TRUE using the new version of my create_1_hint_sql_profile.sql script. Next I used my sql_profile_hints.sql script to verify the text of the hint that was added to the Profile. It looked good including the quotes. When I executed the statement a second time it ran in 5 seconds. I then used fsx.sql again to see that the statement was offloaded for the second execution (child 1).

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:

Continue reading ‘Cardinality Feedback’ »

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

Continue reading ‘Licensing Requirements for SQL Profiles’ »

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):
Continue reading ‘SQL Profiles Disable Automatic Dynamic Sampling’ »

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.

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.083144561)
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_profiles.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.

I got an email a few days ago asking if I would provide the scripts from my Hotsos Symposium 2010 presentations. I didn’t even realize the presentations had been posted anywhere but I managed to find them on my company’s website. So anyway, I decided to go ahead and post a link to the PDF’s and the scripts here as well. So click on the pretty pictures to get the PDFs and the cleverly titled text links to get the accompanying zip files with the scripts.

Controlling Execution Plans Zip File

My Favorite Scripts 2010 Zip File

Oh and Bob Sneed as “Disco Duck” (Thanks Marco)