SQL Tuning Advisor Profiles
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.
===============
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.
I agree with your points. I was recently horrified when a recent STA job had recommended a plan with about 20 cardinality hints. No way I would consider implementing that suggestion as it would quickly be wrong.
It did give me a direction to start looking and further analysis with GATHER_PLAN_STATISTICS showed me exactly where Estimated Rows and Actual Rows were getting hosed.
But sometimes a simple switch between ALL_ROWS and FIRST_ROWS will be something that is generic enough to feel safe implementing.
Kerry,
Thanks for following up that topic.
Interesting result – but there’s an anomaly that I’d want to look into.
Note that your output shows the ALL_ROWS hint three times; that’s suspect. Then near the end of the output you’ve got:
OPT_ESTIMATE(@”SEL$5DA710D3″, TABLE, “D”@”SEL$1″, SCALE_ROWS=11.39782103)
OPT_ESTIMATE(@”SEL$5DA710D3”, TABLE, “D”@”SEL$1”, SCALE_ROWS=11.95241429)
It’s the same transformed query block referencing the same alias twice with different scale factors. Again, that’s suspect; and it happens at many points in the hint set.
I suspect that you’ve managed to report two profiles as one – which doesn’t preclude the possibility that there is a faked set that holds the ‘outline’ type hints and your generated set is just ‘stats’ hints.
Regards
Jonathan Lewis
Excellent observation. I always just figured they didn’t do a very good job of programming, but now that I think about it a little, it seems unlikely that the programmers would not catch multiple all_rows hints in the same profile, much less multiple opt_estimates on the same operation (which I hadn’t noticed). I’ll have a look at that specific example and let you know what I find.
Kerry
Jonathan,
Well rats! I hate it when I am wrong! My script was combining hints for multiple Profiles (if there happened to be more than one Profile on the same statement, which is possible if you use multiple categories – a bit unusual but very possible). This was exactly the case in my test. Brilliant piece of deduction on your part by the way, without actually seeing the system – especially since I hacked up the hints to protect the table and column names. At any rate, I’m back to where I was before with a Profile of unknown origin that has a mixture of opt_estimate and other hints, but no proof that it was generated by the Tuning Advisor. It’s possible someone manually added hints or changed the name of the profile, although I can’t get anyone to fess up to that. So still a bit of a mystery to unravel there – another post I’m sure.
At any rate, I’ve updated my sql_profile_hints script to add the additional join (on category). Thanks for taking the time to look carefully at this post and comment on it.
Kerry
[…] SQL Profiles and manual sql profile (comments) Kerry Osborne-Single Hint SQL Profiles Kerry Osborne-SQL Tuning Advisor Profiles 22-How to do pivoting pre-11G? Marc Billette-Easy Pivot Query Result in pre-11g Oracle Comments […]
[…] listed anywhere in the report, and require some extra work to uncover. Kerry Osborne has blogged about this issue before, but his post details profiles after they’re accepted; I want to see […]