How to Lock SQL Profiles Generated by SQL Tuning Advisor
I’ve mentioned (many times) that I think SQL Profiles that are generated by the SQL Tuning Advisor (STA) tend to sour over time.
After seeing it happen at a few sites I began to wonder why. So first a few facts about the SQL Profiles that STA generates:
- They are simply a set hints that get applied to statements behind the scenes during parsing
- They consist mainly of OPT_ESTIMATE hints which modify optimizer calculations
- They also may contain direct statistics modification hints (COLUMN_STATS, TABLE_STATS)
- They usually contain a OPTIMIZER_FEATURES_ENABLED hint
- They very occasionally contain other environment type hints (FIRST_ROWS, etc…)
- They do not contain directive hints (FULL, INDEX, NESTED_LOOP, etc..)
- The names of STA profiles start with SYS_SQLPROF
- 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.
Sweet pic and sweet topic… awesome together 🙂
Kerry,
This is a very pertinent topic right now.. I have some profiles that we run in our environments that appear to be going stale. We use the sqlt package provided that creates the COE_{sql_id}_{plans_hash_value}.sql script.. I noticed you have included one of these in your example.
When going throught the hints, there is no OPT_ESTIMATE. Why would they go stale ?
One of the very interesting things I noticed about profiles, is that we use GTT (global temporary tables) with dynamic sampling. We join to the same GTT multiple times in the same query. When I look at the plan that utilizes a profile, the cardinality of the first FTS for the GTT is 1.. All the other FTS’s for that same GTT have a cardinality of 8168. This is usually OK, and the profile provides the plan I want (even though the cardinality is way off).
It appears at some point, the profile goes stale enough that the GTT cardinality of 8168 drives FTS on other tables.
Is any of this type of behaviour covered in your new book ?
I would also be interested in your thoughts on Plan baselines. I haven’t had much luck transferring them between environments with different parsing_schema names. That’s why we have stuck with profiles.
Update 12/3/10
Kerry,
I found my answer to the question I had.. I am using a query with a view.. the query didn’t change, but the underlying view did.. Some tables were re-arranged in their order (causing the sel$ numbers to change). In addition, we have some GTT’s that dynamically sample.. The combination of all this is the perfect storm to kill the performance of the profile. I will document all this on my website. I would still be interested if your book covers some of these pitfalls
1) underlying views and how profiles recognize changes (or doesn’t recognize)
2) GTT’s dynamically sampling and how this affects the cardinality in a profile.
I just bought the electronic version of the book from Apress website last night and it’s really excellent stuff, good job 😉
Happy to understand more the way the sql profiles are working 🙂
Bryan,
Profiles are matched up to SQL statements via the text of the statement (actually a signature is calculated). This is very similar to how SQL statements are shared in the library cache except that the matching rules are slightly relaxed (i.e. case is ignored and multiple contiguous spaces are collapsed). So if an underlying view changes, the profile will have no idea and as you experienced, the hints will be blindly applied. Whatever plan the optimizer comes up with using those hints is the plan you get. Profiles have no idea what plan you started with or were attempting to recreate. Baselines on the other hand store a version of the plan_hash_value (although using a different calculation than the one that’s used to produce the plan_hash_value in v$sql_plan). So baselines know whether they were able to reproduce the original plan or not. Therefore, if the optimizer applies all the hints and doesn’t come up with the original plan, it throws the hints out and re-optimizes the statement without the hints. (it doesn’t actually happen in that order, but that’s basically what happens)
I’m not sure about your issue with the different cardinality estimates of the GTT in a statement, but I don’t believe this is related to the fact that there was a Profile on the statement (unless of course the profile had a cardinality or opt_estimate hint – which I doubt – based on you comment about using sqlt to generate it).
I’m not sure I understand your comment about transferring baselines between “environments with different parsing schema names”. Baselines are just like profiles in matching the SQL text. Obviously if the underlying objects are different and/or have different stats, the plans may not be re-created. If you’re talking about moving from a test/dev environment to prod – I would not expect this to be very successful. When I’m trying to fix a statement in a production system, I generally would make a copy of the statement (with a comment to make if have a different signature). Once I get it to do what I want via hints, alter sessions, etc… I create a profile on it and move the profile onto the production statement. As long as the query blocks don’t change due to the manipulation, this works pretty well. I have documented these techniques on other blog posts as well as in the book.
Speaking of the book, the chapter title is “Plan Stability and Control”. Profiles and Baselines are covered in detail along with techniques/scripts for using them and my thoughts on what causes plan instability in the first place and how to identify it.
Glad to hear you resolved your issue. Hope this additional info is helpful.
Kerry
Thanks..
Here is everything I found out http://bryangrenn.blogspot.com/2010/12/sql-profiles.html
As far as baselines, the issue is that they have a parsing_schema_name as part of the baseline.. All our database use different login ids (ie. Imp_user,prod_user), so any baselines I capture can’t be moved between environments without creating he userid’s everywhere. Baselines are definitely a lot less portable than Profiles.
Lastly with ADG you can’t capture a baseline.. In order to create a baseline you need a sql_tuning_set.. You can’t create one in an ADG so you are dead.
I keep telling everyone that Baselines are great, but they have a long way to go to make them really useful like profiles.
We had long discussions at my company (baselines VS profiles) when we upgraded to 11.2, and profiles won for the above mentioned reasons.
Kerry,
I’m continually impressed, but not surprised, at your blog content! This is an excellent post. I wrestle with plan stability too much and tips like this are ever so helpful. Thanks!
Bryan,
I have not worked with ADG, so I’m not sure how they deal with Baselines. I would expect though that you could create a Baseline on the database, although they may not be automatically propagate from the source. But this is just a guess.
Here are a couple of of follow up comments on Profiles and Baselines:
1. I agree that Profiles are more flexible than Baselines. The reason I say this is because of the dbms_sqltune.import_sql_profile procedure which allows any arbitrary set of hints to be used to create a sql profile on any statement. This is what sqlt uses to create Profiles by the way. The second thing they have that Baselines don’t is the force_matching flag which allows them to be applied to many statements by ignoring literals.
2. I haven’t found Baselines all that difficult to deal with. They do not require SQL Tuning Sets to create. Check out the dbms_spm.load_plans_from_cursor_cache procedure. It will create a Baseline on any statement in the shared pool. You can search my blog for the create_baseline.sql script for an example of using that procedure.
3. Baselines may be created on statements that have SQL Profiles on them. Oddly enough, in my testing it appears that in cases with statements having both a SQL Profile and a Baseline, that the optimizer attempts to apply the hints from both the Profile and the Baseline. But in general, you should be able to create a Baseline on top of a statement using a Profile with dbms_spm.load_plans_from_cursor_cache, drop the Profile, and leave the Baseline in place. The Baseline should have the same set of hints that the Profile had and should produce the same plan.
4. I generally consider Profiles and Baselines (and Outlines for that matter) as temporary solutions, as opposed to something that I would come up with in development and move into production. If hints are required to get the plan you want in development, I would prefer to put the hints in the SQL statement where everyone can see them as opposed to hiding them in one of these hint based constructs.
Thanks Kevin,
I love you too man!
Kerry
Very nice post Kerry (as usual …;) ),
I am a bit suprised by you statement:
”
Oddly enough, in my testing it appears that in cases with statements having both a SQL Profile and a Baseline, that the optimizer attempts to apply the hints from both the Profile and the Baseline.
”
In my experience, if a statement that has SQL Profile and Baseline on it, the Baseline execution plan will be used (and not a mix of SQL Profile and Baseline plan).
Could you please give an example when Oracle is using a mix of SQL Profile and Baseline (I suppose baselines doesn’t have a status FIXED).
Best Regards
Milen
Milen,
I have a post half way written on that topic but it’s been sitting for months (in fact I have more half written unpublished posts than I have published ones – it’s a great place to keep ideas). Anyway, this is really more of an interesting tidbit than anything really useful I think. I did write some about it already on this post though:
GATHER_PLAN_STATISTICS
Scroll down to the last example and it shows a case where I had a statement with a baseline and added a SQL Profile with a single GATHER_PLAN_STATISTICS hint. The xplan output showed both the Profile and the Baseline in the notes section of the plan and also showed the additional statistics that the GATHER_PLAN_STATISTICS provide. So that should give you some ideas for additional experimenting.
Update: I went ahead and published the draft post on this issue here:
Interaction Between Baseline and SQL Profiles
Inter
Kerry
Hello kerry,
Can you please provide the link to your scripts so that we can download and use them. I tried but its not allowing me because of lack of credentials.
The link I tried is http://kerryosborne.oracle-guy.com/scripts/
please help me in downloading scripts
There is no central location. All scripts should be linked from individual posts. You can use the search box at top right of the page to search for a specific script by name. I need to organize a central location but have not had time. Let me know if there is a specific script you cannot locate.
Kerry
I would like to stabilize the execution plan associated to a nonmergeable view, i.e. I would like Oracle follows always the same optimal sub-plan (related to this view) for every query referencing this view. My view is very complicated, so I can not use hints. Is it possible with some trick?
Roberto
There is no mechanism to lock a plan. The hint based mechanisms provided by Outlines, SQL Profiles or Baselines are the best options we have at this point, but they are not guaranteed.
Kerry
Outlines, SQL Profiles are ok, but work only for the “main” query. I need to fix the execution plan of the query defining the view. Nothing in addition to hints?
Roberto,
I am hesitant to get off in the weeds here because I don’t want to get you running down the path of trying to hint stuff if there is some underlying issue that needs to be addressed. But views can certainly contain hints in their definition. I don’t like to do that because the hints are hidden from casual observation. You can also put hints in the “main” query that can apply inside the view. The full hint syntax includes a place to put the query block name and each object can be fully qualified as to where in the statement it is. Like so:
FULL(@”SEL$1″ “SKEW3″@”SEL$1″)
In this hint the query block name is SEL$1 (it’s the default name given to the first query block in a select). So that’s where the hint is applied, and the SKEW3 object is the one defined in that same QB.
Here’s another example showing how to apply a hint that is non-object specific at a specific place in the statement.
PUSH_SUBQ(@”SEL$2”)
Tells the optimizer you want the PUSH_SUBQ hint applied in the second query block.
Again – this is pretty far off in the weeds, and there may be other much more appropriate ways to deal with a query that is not doing what it should. Rewriting the query to directly access the underlying tables might be an option, fixing incorrect stats could be another option, it really depends on what’s causing the optimizer to pick a bad plan. Hints should be used when the optimizer will never get it right due to limitations in the optimizer code or situations where it changes it’s mind frequently due to bind variable peeking or some other issue. Of course SQL Profiles and Baselines are just collections of hints so these techniques can be used within those constructs as well.
I’d highly recommend reading the Plan Stability chapter in Karen Morton’s “Pro Oracle SQL” book for more detailed information on the topic. There is quite a bit of info on this blog as well but it’s spread out across many posts.
I understand your hesitation to recommending hints, but I think is the only solution in my case… I wanted to avoid using complex hints with query blocks!
I will use tuning pack on the query defining the view, then I’ll take the hints of the SQL profile and will rewrite the query with those hints.
I will try to do it.
Thank you very much for your time,
Roberto
I did how annunced: took the hints generated by the Oracle tuning advisor and put them into the query statement.
Here it the results:
if I execute the query with hints works:
select /*+ hint1 hint2 … */ … from tab1, tab2, …; => it follows the exact execution plan
but if I define a view with the same SQL:
create vies v1 as select /*+ hint1 hint2 … */ … from tab1, tab2, …;
select * from v1; => it does NOT follow more the exact execution plan
Then, if I use the WITH clausole (subquery factoring) it works again:
with v1 as ( select /*+ hint1 hint2 … */ … from tab1, tab2, … ) select * from v1; => it follows the exact execution plan
Why doesn’t the view work?
Roberto
Not enough info to answer you concisely, but most likely it is related QB names.
Hi Kerry!
Great post! You are awsome!
One question: is it possible to create outline from the sql*profile to aslo’lock’ the explain plan (10g)?
Thanks,LT