Archive for the ‘Plan Stability’ Category.

« Previous Entries

SQL Tuning Advisor Profiles

February 3, 2010, 11:05 am

Warning! While the details of the other points in this post remain valid, the main conclusion of the post (i.e. that the Tuning Advisor can create Profiles with hints other than the “statistics modification” type, opt_estimate for example) is not valid. At this point I have no proof that the Tuning Advisor creates Profiles with the more directive type hints. The conclusion was based on a faulty script (sql_profile_hints) which in certain situations combined hints from multiple Profiles. (The script has been modified now)

Be sure and see the comments from Jonathan and my response for further information. I will leave the remainder of the post unchanged for reference.

============================================================

This is the 2nd post in response to a dialog with Jonathan Lewis regarding SQL Profiles. Here’s a link to the 1st post and a link to the original dialog.

Just a quick recap: Jonathan had pointed out that I was using SQL Profiles in a manner that was not intended by the developers (with which I had to agree). SQL Profiles are created by the SQL Tuning Advisor (STA) primarily to address inaccurate calculations by the optimizer. The first post dealt with the question of whether SQL Profiles were capable of dealing with any/all hints or just the ones most commonly used by STA generated Profiles. This post focuses on a second question, namely whether STA generated Profiles ever had any thing other than the “statistics modification” type hints (opt_estimate, column_stats, table_stats, index_stats, etc…).

So in an effort to answer that question, I did a little looking around on some systems that had implemented a fair number of STA generated Profiles. In order to speed my “looking around”, I wrote a script to do a simple grouping of hints (sql_profile_distinct_hints.sql). <- got to use a little regex in that one ;)

You'll also need my sql_profile_hints.sql script if you want to follow along in your hymnal.

Also note that STA creates Profiles that are named with this format: SYS_SQLPROF_xxxxxxx, so we’ll only look at Profiles that are named in that fashion.

?View Code NONE
===============
System 1
===============
 
SQL> set echo on
SQL> @sql_profiles
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text',sql_text)
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text: 
Enter value for name: SYS_SQLPROF%
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ----------------------------------------------------------------------
SYS_SQLPROF_014715f1c62ec002   DEFAULT         ENABLED  SELECT /*+ OPAQUE_TRANSFORM */ ...
SYS_SQLPROF_0148f3819bdb0003   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3819bd1c002   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3819be38004   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3b92f920008   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3b92f5c4005   DEFAULT         ENABLED  DELETE ...
SYS_SQLPROF_0148f3b92f848007   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014914d83003400b   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014914d8682e800c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491c90b1dd0010   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491dfb1c85c012   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014913e28eec0006   DEFAULT         DISABLED SELECT ... 
SYS_SQLPROF_0148f3b92fa04009   DEFAULT         DISABLED SELECT ...
SYS_SQLPROF_01491ccd37348011   DEFAULT         DISABLED SELECT ...
 
14 rows selected.
 
SQL> 
SQL> @sql_profile_distinct_hints
SQL> set lines 155
SQL> col hint for a50 trunc
SQL> select hint, count(*) from (
  2  select regexp_replace(attr_val,'\(.*$') hint -- eliminate from ( to end of line
  3  from dba_sql_profiles p, sqlprof$attr h
  4  where p.signature = h.signature
  5  and name like ('&profile_name')
  6  )
  7  group by hint
  8  order by hint
  9  /
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
 
7 rows selected.
 
===============
System 2
===============
 
SQL> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ---------------------------------------------------------------------- 
SYS_SQLPROF_0149380562710001   DEFAULT         ENABLED  SELECT DISTINCT ...
SYS_SQLPROF_01494247f1d7c007   DEFAULT         ENABLED        INSERT INTO ...
SYS_SQLPROF_01494247f254c008   DEFAULT         ENABLED        INSERT INTO ...
SYS_SQLPROF_01494db7d72f0000   DEFAULT         ENABLED        INSERT INTO ...
PROFILE_1zywf8dtd9a6m          DEFAULT         ENABLED  select ...
PROFILE_as2u1cbquf6t3_moved    DEFAULT         ENABLED  SELECT ...
PROFILE_ay0yn9xczhqk2          DEFAULT         ENABLED  select ...
PROFILE_402tfpm6851du_moved    DEFAULT         ENABLED  SELECT /*+ORDERED ...
PROFILE_0bbnbjt6r17zg          DEFAULT         ENABLED  select /* test 1qkytgsyhfrn6_11.sql */ ...
PROFILE_7kj5cc04njm64          DEFAULT         ENABLED  select /* test 1qkytgsyhfrn6_4a.sql */ ...
PROFILE_1qkytgsyhfrn6_moved    DEFAULT         ENABLED  SELECT /*+ORDERED ...
PROFILE_8xva1napnu034_moved    DEFAULT         ENABLED  SELECT /*+ORDERED USE_NL ...
PROFILE_6njq8vtawcq1m          DEFAULT         ENABLED  DELETE FROM ...
 
13 rows selected.
 
SQL> 
SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%
 
HINT                                                 COUNT(*)
-------------------------------------------------- ----------
COLUMN_STATS                                                3
IGNORE_OPTIM_EMBEDDED_HINTS                                 3
INDEX_STATS                                                 2
OPTIMIZER_FEATURES_ENABLE                                   4
OPT_ESTIMATE                                              416
TABLE_STATS                                                 1
 
6 rows selected.
 
===============
System 3
===============
 
 
SQL> @sql_profiles
Enter value for sql_text:  
Enter value for name: SYS%
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
SYS_SQLPROF_0146d1cc64d5c001   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1ce58150002   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1cfd2488003   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1e45f1f0005   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146f4d7bee40007   DEFAULT         ENABLED  select ...
SYS_SQLPROF_014738be1cb64000   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0148f25055a28004   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f2528b754005   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0148f252e7e84007   DEFAULT         ENABLED  SELECT DISTINCT ...
SYS_SQLPROF_0148f2553429800a   DEFAULT         ENABLED  SELECT /*+ FULL (...
SYS_SQLPROF_0248f26958210000   DEFAULT         ENABLED  select * from ...
SYS_SQLPROF_0148f37413ef000c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148fcc1fa89c001   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014909a2d7bc4002   DEFAULT         ENABLED  select ...
SYS_SQLPROF_01491c971e1e4003   DEFAULT         ENABLED  select ...
SYS_SQLPROF_01491ce0f3c1800b   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491ce50f9e800c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491f66feb0400d   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_02492723d3778000   SAVED           DISABLED select ...
SYS_SQLPROF_01491ce04c38c008   DEFAULT         DISABLED SELECT ...
 
20 rows selected.
 
SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%
 
HINT                                                 COUNT(*)
-------------------------------------------------- ----------
ALL_ROWS                                                    3
COLUMN_STATS                                               12
FIRST_ROWS                                                  1
FULL                                                        3
IGNORE_OPTIM_EMBEDDED_HINTS                                 2
INDEX                                                       2
INDEX_RS_ASC                                                2
INDEX_STATS                                                12
LEADING                                                     1
OPTIMIZER_FEATURES_ENABLE                                   6
OPT_ESTIMATE                                              212
OPT_PARAM                                                   2
OUTLINE                                                     2
OUTLINE_LEAF                                                1
SWAP_JOIN_INPUTS                                            1
TABLE_STATS                                                 4
UNNEST                                                      1
USE_HASH                                                    2
USE_NL                                                      4
 
19 rows selected.
 
SQL> -- different
SQL> -- After a little looking I found that one Profile had all the non standard hints
SQL>
SQL> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_02492723d3778000
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
ALL_ROWS
ALL_ROWS
FULL(@"SEL$5DA710D3" "A"@"SEL$1")
FULL(@"SEL$5DA710D3" "B"@"SEL$1")
FULL(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
IGNORE_OPTIM_EMBEDDED_HINTS
INDEX(@"SEL$5DA710D3" "D"@"SEL$1" ("TAB_XXX3"."STRATEGY_ID" "TAB_XXX3"."FF_STATUS"))
INDEX(@"SEL$5DA710D3" "F"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2" "TAB_XXX2"."COL3" "TAB_XXX2"."COL4"))
INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("TAB_XXX4"."COL1" "TAB_XXX4"."COL2" "TAB_XXX4"."COL3"))
LEADING(@"SEL$5DA710D3" "A"@"SEL$1" "B"@"SEL$1" "TAB_XXX1"@"SEL$2" "F"@"SEL$1" "C"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
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, ("D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1.308307653)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=135.982493)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=190.2944942)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=26.52093258)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=259.4309108)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=862.935946
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=11.39782103)
OPT_PARAM('optimizer_index_caching' 60)
OPT_PARAM('optimizer_index_cost_adj' 80)
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
UNNEST(@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "B"@"SEL$1")
USE_HASH(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "C"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "D"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "E"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "F"@"SEL$1")
 
39 rows selected.
 
SQL> -- So did someone create a manual Profile and name it SYS_SQLPROF_xxxxx ?
SQL> -- Seems unlikely
SQL> -- Let's try to reproduce this behavior (it's on sql_id: 425p8m2qdxdtm)
SQL>
SQL> @create_tuning_task
Task_Name: KSO TEST 425p8m2qdxdtm
Enter value for sql_id: 425p8m2qdxdtm
Enter value for time_limit: 3000
 
PL/SQL procedure successfully completed.
 
 
RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : KSO TEST 425p8m2qdxdtm
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 3000
Completion Status                 : COMPLETED
Started at                        : 01/26/2010 15:25:08
Completed at                      : 01/26/2010 15:35:05
Number of SQL Profile Findings    : 1
 
-------------------------------------------------------------------------------
Schema Name: SCHEMA_XXX
SQL ID     : 425p8m2qdxdtm
SQL Text   : select ...
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
 
  Recommendation (estimated benefit: 73.58%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'KSO TEST
            425p8m2qdxdtm', replace => TRUE);
 
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "PROFILE_425p8m2qdxdtm" exists for this statement and was
  ignored during the tuning process.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3884938694
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   1 |  HASH UNIQUE                    |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   2 |   NESTED LOOPS                  |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   3 |    NESTED LOOPS                 |                          |     1 |   198 |       |   171K  (2)| 00:34:18 |
|   4 |     NESTED LOOPS                |                          |     7 |  1092 |       |   171K  (2)| 00:34:18 |
|   5 |      NESTED LOOPS               |                          |    69 |  9039 |       |   171K  (2)| 00:34:16 |
|*  6 |       HASH JOIN RIGHT ANTI      |                          | 11209 |   996K|  5912K|   171K  (2)| 00:34:16 |
|...
 
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(TAB_XXX1.COL1)
   ...
 
2- Using SQL Profile
--------------------
Plan hash value: 3101771407
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     1 |   207 |       | 45293   (2)| 00:09:04 |
|   1 |  HASH UNIQUE                      |                          |     1 |   207 |       | 45293   (2)| 00:09:04 |
|   2 |   NESTED LOOPS ANTI               |                          |     1 |   207 |       | 45292   (2)| 00:09:04 |
|   3 |    NESTED LOOPS                   |                          |     1 |   170 |       | 45290   (2)| 00:09:04 |
|   4 |     NESTED LOOPS                  |                          |   122 | 15860 |       | 45289   (2)| 00:09:04 |
|*  5 |      HASH JOIN                    |                          |  2868 |   277K|  4480K| 40696   (2)| 00:08:09 |
|*  6 |       HASH JOIN                   |                          | 66442 |  3698K|    22M| 36124   (2)| 00:07:14 |
| ...
 
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access(TAB_XXX1.COL1)
   ...
 
-------------------------------------------------------------------------------
 
 
SQL> set echo on
SQL> set echo off
SQL> !cat accept_sql_profile.sql
exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');
 
SQL> @accept_sql_profile
Enter value for task_name: KSO TEST 425p8m2qdxdtm
Enter value for category: TEST
 
PL/SQL procedure successfully completed.
 
SQL>  select name, category, status, sql_text
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text',sql_text)
  4  and name like nvl('&name',name)
  5  and categeory like 'TEST'
  6* order by last_modified;
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ---------------------------------------------------------------------- 
SYS_SQLPROF_01499b27a4ea4000   TEST            ENABLED  select ...
 
 
SQL> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01499b27a4ea4000
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
ALL_ROWS
ALL_ROWS
ALL_ROWS
FULL(@"SEL$5DA710D3" "A"@"SEL$1")
FULL(@"SEL$5DA710D3" "B"@"SEL$1")
FULL(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
IGNORE_OPTIM_EMBEDDED_HINTS
INDEX(@"SEL$5DA710D3" "D"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2"))
INDEX(@"SEL$5DA710D3" "F"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2" "TAB_XXX2"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
LEADING(@"SEL$5DA710D3" "A"@"SEL$1" "B"@"SEL$1" "TAB_XXX1"@"SEL$2" "F"@"SEL$1" "C"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", TAB_XXX3_IX1, SCALE_ROWS=6.531081416e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "A"@"SEL$1", TAB_XXX2_IX3, SCALE_ROWS=1.481282096e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "A"@"SEL$1", TAB_XXX2_IX7, SCALE_ROWS=1.481282096e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", TAB_XXX_IX1, SCALE_ROWS=6.531081416e-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, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=8.321869785)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1.308307653)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=22.98505769)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=4.872324946)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=135.982493)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=188.6083111)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=190.2944942)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=26.52093258)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=40.52912454)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=259.4309108)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=41.60863798)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1102.68181
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=862.935946
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=11.39782103)
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=11.95241429)
OPT_PARAM('optimizer_index_caching' 61)
OPT_PARAM('optimizer_index_cost_adj' 79)
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
UNNEST(@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "B"@"SEL$1")
USE_HASH(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "C"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "D"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "E"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "F"@"SEL$1")
 
52 rows selected.
 
SQL> -- so STA really did create a Profile with all these hints, interesting...

A couple of things worth noting:

OPT_ESTIMATE was far and away the most popular hint. This is followed by the occasional XXX_STATS hints. The OPTIMIZER_FEATURES_ENABLE hint shows up in almost all the STA Profiles. Also, the IGNORE_OPTIM_EMBEDDED_HINTS shows up fairly regularly, but not always (not sure why it’s there sometimes and not others - I thought it might show up only when there are actually embedded hints in the SQL text, but that is not the case). Occasionally, an ALL_ROWS or FIRST_ROWS hint will show up. And finally, in rare cases, a full blown Profile with many directives will show up as in the one case I was able to reproduce. I really have no idea when/why this kicks in. But it doesn’t seem to happen very often. However, it does give a strong clue that most, if not all, valid hints can be applied by SQL Profiles.

And a couple of editorial points:

1. I don’t really like STA generated Profiles as a long term solution. Since they primarily apply fudge factors (via the opt_estimate hint) to the optimizer calculations (which are based in large part on statistics), they tend to go bad fairly quickly as the statistics change.

2. I think they are very useful though for finding where the optimizer has gone wrong. Anywhere there is an opt_estimate hint with a really large (or small) scaling factor, the optimizer had some kind of problem. This could be due to bad stats, or just a short coming in the optimizer itself. But it can be a very good pointer to why/where the optimizer is not doing a good job.

3. There is no reason you can’t use an STA Profile to get a decent plan and then do something else to make it more permanent - i.e. create an Outline or Baseline or even another Profile with all the hints necessary to reproduce the plan. This is of course if your intent is to “lock” the plan.

4. If you have a short coming in the optimizer (such as 10g’s lack of knowledge about correlated columns) then the opt_estimate hint in the STA Profiles may be a viable long term solution. Of course, this implies that you know enough about how the calculations are done to be sure that a certain calculation will always be off by a calculatable (did I just make that word up?) factor.

As always, your comments are welcomed.

Category: 10gR2, Oracle, Plan Stability  |  4 Comments

Single Hint SQL Profiles

February 1, 2010, 4:25 pm

Seems like all I ever write about these days is SQL Profiles. I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to “Magic” that DBAs get to do. By the way, software developers get to create “Magic” all the time. They have the ability to create something out of nothing. I think it’s one of the coolest jobs ever. Of course a lot of art is like that too. But painters and sculptors start with some sort of raw material - programmers don’t. Musicians don’t really use raw material either, but music is much less tangible than software. Maybe one of these days I’ll write a little on the similarities between musicians and software developers, but right now I’m way off in the weeds. Back to the subject at hand.

A few weeks ago Jonathan Lewis called me to task on a couple of posts regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing a particular execution plan the way Outlines are. There is after all, no documented way (that I’m aware of) to directly create a SQL Profile on a statement. I had to agree with him that I was using them in a way that was not necessarily intended. Outlines were designed to lock execution plans, SQL Profiles were designed to overcome shortcomings in the optimizer with regards to statistics. Tom Kyte described SQL Profiles like this:

So, a SQL profile is sort of like gathering statistics on A QUERY - which involves many
tables, columns and the like….

In fact - it is just like gathering statistics for a query, it stores additional
information in the dictionary which the optimizer uses at optimization time to determine
the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving
the optimizer yet more bits of information it can use to get the right plan.

Tom is referring to the documented way of creating a SQL Profile which is to use the SQL Tuning Advisor. The Tuning Advisor verifies the optimizer’s calculations and can create a SQL Profile that corrects the calculations, if they are found to be in error. The corrections most often come in the form of OPT_ESTIMATE hints which apply a scaling factor at various places in the optimizer’s calculations. But take note that the underlying mechanism of applying these corrections is hints. So a SQL Profile is ultimately a mechanism for applying a set of stored hints to a SQL statement (or set of statements) behind the scenes. And whether it was intended by the developers or not, this gives us a tremendously powerful tool. With this tool we can influence (and often times control) execution plans for statements coming from application code that is difficult or impossible to change.

By the way, all this discussion of SQL Profiles and whether they should be used as a generic mechanism for affecting plans without touching a SQL statement is probably going to be fairly short lived. SQL Baselines (introduced in 11g) are the latest revision of the “behind the scenes hint application” idea. They will most likely make SQL Profiles a less attractive option in the future. Fortunately, it is a simple matter to convert a SQL Profile into a Baseline. And SQL Profiles still work fine in 11g as well (so do Outlines for that matter), but on the off chance that Oracle decides to do away with SQL Profiles (or alter their behavior) in some future release, it’s comforting to know that we are not headed down a dead end street.

Back to the subject at hand. Jonathan expressed concern and doubt on a couple of points:

  1. That Profiles really were a generic mechanism to apply any hint behind the scenes. (i.e. that they could be used to apply any arbitrary hint, not just the hints created by the Tuning Advisor - like opt_estimate).
  2. Whether Profiles created by the Tuning Advisor ever had hints other than “Change the Optimizer Calculations” type hints (i.e. opt_estimate, index_stats, table_stats, etc…).

I think we finally agreed that they can be used to apply hints in a generic fashion, even if that was not the intention of the developers. Nevertheless, here is a simple test case to demonstrate that they can be used for that purpose. I created a little script to create a SQL Profile with a single manually typed hint called create_1_hint_sql_profile.sql. Please note that the syntax can be quite finicky with regards to query block names and aliases.

?View Code NONE
> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 25 15:23:02 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> @flush_pool
 
System altered.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
SYS@LAB112> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .05          190 select avg(pk_col) from kso.skew where col1 = 136133
 
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.
 
SYS@LAB112> -- let's create a 1 hint profile to force a full table scan
SYS@LAB112> 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full(skew)
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profiles   
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           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_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew 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
PROFILE_84q0zxfzn5u6s_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
 
7 rows selected.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full(skew)
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
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 3723858078          1           .01           86 select avg(pk_col) from kso.skew where col1 = 136133
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0
 
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)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement
 
 
24 rows selected.
 
SYS@LAB112> -- didn't work - i.e. Profile got created and used, but the hint was ignored
SYS@LAB112> -- oh yeah, Query Block needed
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> -- must reload SQL statement for create_1_hint_sql_profile to work
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full( SKEW@SEL1$ )
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full( SKEW@SEL$1 )
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
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_MANUAL used for this statement
 
 
23 rows selected.
 
SYS@LAB112> -- so that worked

Note that the syntax can be tricky requiring correct Query Block name (and aliases if there are any). SEL$1 is the default name for the query block of a simple select. (DEL$1 for deletes, UPD$1 for updates) As you can see from the example, the FULL hint was silently ignored without the QB name.

Since this post has gotten a little long, I’ll save my response to Johnathan’s second concern for the next post. Please let me know if you have any questions.

Category: DB Tips, How Our Brains Work, Oracle, Plan Stability, Tuning  |  8 Comments

Fixing Bad Index Hints in SQL Profiles (automatically)

November 18, 2009, 10:30 am

I’ve written before on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to “lock” execution plans. That is to allow the optimizer as little flexibility as possible. With 10g and 11g, it appears the goal has swung away from the “locking” concept and towards allowing the optimizer more flexibility. I’ll show you an example of what I mean in a minute, but I must say that I find this decision to be irritating at best. It seems to me that the whole reason for implementing one of these objects is to try to keep the optimizer from changing its mind. After all, it was originally called “Plan Stability” by the Oracle marketing guys.

One of the main offenders in this regard is the use of a new format available for index hints as of 10g. Prior to 10g, the index looked basically like this:

INDEX(TABLE_NAME INDEX_NAME)

Translation: If possible, use this index on this object.

As of 10gR2, there is a new possible format which appears to be used (at least most of the time) when Outlines/Profiles/Baselines are created. The new format looks like this:

INDEX(TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …)

Translation: If possible, use any available index on any of these columns.

As you can see, this format leaves a lot more to the optimizer’s discretion than the older format. (Note: the older format is still valid)

In my previous post I published a script for changing an individual SQL Profile hint, but recently I had a situation where there was a SQL Profile that had 20+ index hints where the statement was suffering from plan instability, despite the fact that it was using a SQL Profile. Rather than manually look up the correct index names and change the hints one by one (a very error prone proposition), I decided to write a script that would automatically change all the INDEX hints from the “non-specific column oriented” format to the “specific index name” format.

But before I give you the script - a little history. Here are Outline hints for the same statement from 9.2.0.8, 10.1.0.?, 10.2.0.4, and 11.2.0.1:

Continue reading ‘Fixing Bad Index Hints in SQL Profiles (automatically)’ »

Category: 10gR2, 11gR1, 11gR2, Oracle, Plan Stability, Tuning  |  6 Comments

How to Attach a SQL Profile to a Different Statement - Take 2

October 13, 2009, 10:00 am

I posted on this topic a while back (How to Attach a SQL Profile to a Different Statement - Take 1), but wasn’t really happy with my solution. So here’s another shot at it. The basic idea is to be able to create a profile on one statement using hints or whatever other tricks you can come up with, and then attach the profile to a production statement on which you cannot modify the code. The basic steps of this technique are as follows:

The main reason I became dissatisfied with my previous approach was that it’s often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. My previous approach created the profile and moved it all in one step. So there was no chance to modify the Profile before it was attached to the production statement. Anyways, here’s an example (note the examples are on a 10.2.0.4 database, but all the SQL should work on 11g as well):
Continue reading ‘How to Attach a SQL Profile to a Different Statement - Take 2’ »

Category: 10gR2, 11gR1, 11gR2, Oracle, Plan Stability  |  14 Comments

Why Isn’t Oracle Using My Outline / Profile / Baseline?

July 31, 2009, 9:39 am

I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right? Here’s a bit of an email I sent with my thoughts on the issue during a conversation on the Oracle-L list.


First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.

If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.

First, if it’s a profile generated by SQL Tuning Advisor it’s quite possible that it will have one or more OPT_ESTIMATE hints - which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.

The second common possibility is that Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on. For example:

INDEX_RS_ASC(@”SEL$1″ “TEST_TABLE”@”SEL$1″ (”TEST_TABLE”.”COL2″ “TEST_TABLE”.”TEST_TABLE_ID”))

This hint does not specify an index, but rather columns to use an index on. I’m not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a statement will switch plans even though it’s using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an Outline or Profile with, enable it, and have the new child use a plan that’s different from the plan of the child you used to create it with. Very frustrating.

So as I said in the email, the most likely cause for SQL Profiles not working is the non-specific form of index hints that are being used. However, as I was doing some research for this post, I found a couple situations where SQL Profiles just flat don’t work as advertised. I’ll show you an example, but first here are a few scripts that I will use (I sometimes don’t set “echo on” because it makes the examples so messy - so just hit the links to view the scripts):

sql_hints.sql - shows the hints stored in the v$sql_plan.other_xml column
sql_profile_hints.sql - shows the hints associated with a SQL Profile
sql_profile_hints11.sql - shows the hints associated with a SQL Profile (for 11g)
create_sql_profile.sql - creates a profile for a statement in the shared pool (wrapper for rg_sqlprof1.sql)
rg_sqlprof1.sql - does the real work to create a profile for a statement in the shared pool
fix_sql_profile_hint.sql - replaces a hint in a profile with whatever you want

Here’s the set up: a simple single table query that wants to do a full table scan and a second version that uses a hint to force an index. A profile created on the hinted statement should keep it from changing, but instead, it changes the plan back to a full table scan. Here’s the example:

Continue reading ‘Why Isn’t Oracle Using My Outline / Profile / Baseline?’ »

Category: 10gR2, 11gR1, Oracle, Plan Stability, Tuning  |  19 Comments
« Previous Entries