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:

  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.

> !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@SEL$1 )
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.

Leave a Reply