Single Hint SQL Profiles
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:
- 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).
- 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.
> !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.
Balakrishna:
Hi,
You got a very good scripts written for a daily use , is that possible to share all your scripts by any chance ?
Regards
Bala
February 1, 2010, 10:26 pmosborne:
Thanks. I’m working on a scripts page. But for now, most of the scripts in the posts are on the site.
You should be able to find them by searching on the site. Alternativey, many of them are stored in the following location:
http://kerryosborne.oracle-guy.com/scripts/
While browsing that directory is not allowed, you can try putting in the directory followed by the explicit file name you are looking for. Like so:
http://kerryosborne.oracle-guy.com/scripts/find_sql.sql
Kerry
February 1, 2010, 11:26 pmjoel garry:
The raw material programmers start with is the language (or language generating tool) they use.
Some limited browsing of the scripts directory is available.
February 2, 2010, 1:19 pmosborne:
Joel,
On the limited browsing:
Well what do you know. Google is amazing! Thanks for sharing.
On the raw materials:
This is really an interesting topic (to me anyway). I would like to spend some time writing about it. Anyway, I don’t think of the language as a raw material, like clay or stone or wood or paint. I think of it as a tool like a chisel or a paint brush. Using these tools (which are not consumed in the process) a thought can be transformed into something real and tangible. Music is very similar in that a tool (instrument) is used, and basically a thought can be transformed into something at least semi tangible. It’s the closest thing to magic I can think of. Not to dismiss the fact that we are all standing on the shoulders of countless men and women that went before us. Without the advanced tools we have at our disposal, it would be very difficult to do what we do. Here’s to the pioneers!
Kerry
February 2, 2010, 2:27 pmKerry Osborne’s Oracle Blog » Blog Archive SQL Tuning Advisor Profiles - Kerry Osborne’s Oracle Blog:
[...] Oracle Whitepapers / Presentations « Single Hint SQL Profiles [...]
February 3, 2010, 11:05 amjoel garry:
I for one look forward to your thoughts on these matters. I remember talking to people in the early ’80s, remarking on the apparent links between programming and music and maybe even religious studies. We all knew people who could barely write but were seemingly excellent programmers and/or musicians. Even now, people who can do our stuff well, and write or present well, are a rare treat. Some (human) language theories posit that thought is limited, or enhanced, by language.
odd tangent: hitting next blog link a few times from Noon’s blog landed me an interesting useless music tidbit, I know not why (date posted, maybe): http://bertnews.blogspot.com/
One of my current tasks is to write a front end for some data entry, to move the effort from one group closer to the group that generates the data (and gets some of it bar-codable). So I’m taking the data entry program and hacking away, I actually did think of it like molding clay before you said anything about it.
February 3, 2010, 5:32 pmosborne:
Thanks Joel,
I’ve got it on my list to write about the similarities between musicians and programmers. It’s of interest to me because I am also a musician and have known a relatively large number of people that were accomplished programmers as well as accomplished musicians.
Interesting link. I feel for the guy. Sadly I traded off my 62 Gibson SG in the late 70’s for a piece of %$#& Ovation acoustic. I’ve made a couple of horrendous decisions in the past that cost me dearly. But honestly, the decision to trade that guitar is the only one I think back on and wish I could undo.
Interesting that you think about your current project as molding clay. I find I often start out with that frame of mind on a project, but that often shifts to more of a sculpture metaphor. (i.e. “I just chip away everything that doesn’t look like a horse.”) I had a friend that told me he viewed it as if he was going into battle (and spent several minutes describing what he meant, in excruciating detail).
Thanks again for the comments.
Kerry
February 4, 2010, 1:57 pmLog Buffer #177: a Carnival of the Vanities for DBAs | The Pythian Blog:
[...] closest thing to ‘Magic’ that DBAs get to do.” Here’s Kerry’s post on single-hint SQL profiles, inspired by a discussion with Jonathan [...]
February 5, 2010, 1:24 pmBlogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle:
[...] 21-SQL Tuning Advisor generated 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 [...]
March 5, 2010, 5:00 am