Adding Hints to Existing SQL Profiles (10g)
By Jon Adams, Sr. Technical Consultant and Jack Augustin, Sr. Technical Consultant
The SQL Tuning Advisor was introduced in Oracle 10g. Its purpose is to take a poorly-executing SQL statement and perform an examination process looking at object optimizer statistics, index usage, and other aspects of the query to determine how to make it perform more efficiently. The result of executing this tool often results in a SQL profile being recommended on the SQL statement.
Simply put, a SQL Profile is a list of hints that will influence the execution of the SQL statement that it is attached to. Unfortunately, SQL profiles do not prevent the optimizer from choosing a different plan for several reasons including bind variable peeking, out-of-date or insufficient segment statistics or histograms, configuration settings (index cost, optimizer, etc.), or simply that Oracle thinks another plan is better. There is a way to add hints to existing SQL profiles to ignore features like bind variable peeking. Altering an existing SQL Profile is not officially supported by Oracle, as the SQL Profile itself is only supposed to be created by the SQL Tuning Advisor, so caution and testing is highly recommended.
For our test case, I will take a query that had nine different plans associated with it, observed by querying the AWR (the output has been compressed for sanity’s sake):
SQL_ID 48k8mu4mt68pw -------------------- Plan hash value: 1327752597 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 58814 (100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 27 | 34911 | 58814 (1)| 00:11:46 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 23 | 29624 | 58385 (1)| 00:11:41 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 15 (0)| 00:00:01 | | 9 | NESTED LOOPS SEMI | | 1 | 206 | 8 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 194 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 06/24/10 22:00:00 2 - :B3 (DATE): 06/25/10 22:00:00 3 - :B2 (NUMBER): 50 4 - :B1 (VARCHAR2(30), CSID=31): (null) Note ----- - SQL profile "PROFILE_48k8mu4mt68pw" used for this statement Plan hash value: 1430530385 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 151K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 23 | 29739 | 151K (1)| 00:30:17 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 19 | 24472 | 150K (1)| 00:30:12 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 16548 (1)| 00:03:19 | | 9 | NESTED LOOPS | | 1 | 206 | 16541 (1)| 00:03:19 | | 10 | NESTED LOOPS | | 1 | 144 | 16540 (1)| 00:03:19 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/12/09 22:00:00 2 - :B3 (DATE): 11/13/09 22:00:00 3 - :B2 (NUMBER): 9 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 1791470508 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 364K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 47 | 60771 | 364K (1)| 01:12:54 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 43 | 55384 | 364K (1)| 01:12:49 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 48383 (1)| 00:09:41 | | 9 | NESTED LOOPS | | 1 | 206 | 48375 (1)| 00:09:41 | | 10 | NESTED LOOPS | | 1 | 144 | 48374 (1)| 00:09:41 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/27/09 22:00:00 2 - :B3 (DATE): 11/28/09 22:00:00 3 - :B2 (NUMBER): 52 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 1891839131 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 151K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 24 | 31032 | 151K (1)| 00:30:23 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 20 | 25760 | 151K (1)| 00:30:18 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 16979 (1)| 00:03:24 | | 9 | NESTED LOOPS | | 1 | 206 | 16971 (1)| 00:03:24 | | 10 | NESTED LOOPS | | 1 | 144 | 16970 (1)| 00:03:24 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/17/09 22:00:00 2 - :B3 (DATE): 11/18/09 22:00:00 3 - :B2 (NUMBER): 53 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 2074647832 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 366K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 47 | 60771 | 366K (1)| 01:13:24 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 43 | 55384 | 366K (1)| 01:13:19 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 36726 (1)| 00:07:21 | | 9 | NESTED LOOPS | | 1 | 206 | 36718 (1)| 00:07:21 | | 10 | NESTED LOOPS | | 1 | 144 | 36717 (1)| 00:07:21 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/06/09 22:00:00 2 - :B3 (DATE): 11/07/09 22:00:00 3 - :B2 (NUMBER): 29 4 - :B1 (VARCHAR2(30), CSID=31): '029465681' Plan hash value: 2164124035 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 107K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 23 | 29739 | 107K (1)| 00:21:30 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 19 | 24472 | 107K (1)| 00:21:25 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 16548 (1)| 00:03:19 | | 9 | NESTED LOOPS | | 1 | 206 | 16541 (1)| 00:03:19 | | 10 | NESTED LOOPS | | 1 | 144 | 16540 (1)| 00:03:19 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/12/09 22:00:00 2 - :B3 (DATE): 11/13/09 22:00:00 3 - :B2 (NUMBER): 897 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 3121899935 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 88167 (100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 22 | 28446 | 88167 (1)| 00:17:39 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 18 | 23184 | 87754 (1)| 00:17:34 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 14 (0)| 00:00:01 | | 9 | NESTED LOOPS SEMI | | 1 | 206 | 7 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 194 | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/25/09 22:00:00 2 - :B3 (DATE): 11/26/09 22:00:00 3 - :B2 (NUMBER): 7 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 3583328698 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 149K(100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 22 | 28446 | 149K (1)| 00:29:52 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 18 | 23184 | 143K (1)| 00:28:44 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 18014 (1)| 00:03:37 | | 9 | NESTED LOOPS | | 1 | 206 | 18007 (1)| 00:03:37 | | 10 | NESTED LOOPS | | 1 | 144 | 18006 (1)| 00:03:37 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/14/09 22:00:00 2 - :B3 (DATE): 11/15/09 22:00:00 3 - :B2 (NUMBER): 32 4 - :B1 (VARCHAR2(30), CSID=31): (null) Plan hash value: 3860900905 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 42769 (100)| | | 1 | FILTER | | | | | | | 2 | VIEW | CO_GL_TENDER_V | 20 | 25860 | 42769 (1)| 00:08:34 | | 3 | UNION-ALL | | | | | | | 4 | FILTER | | | | | | | 5 | VIEW | CO_GL_TENDER_V_1 | 16 | 20608 | 42358 (1)| 00:08:29 | | 6 | UNION-ALL | | | | | | | 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 1 | 223 | 14 (0)| 00:00:01 | | 9 | NESTED LOOPS SEMI | | 1 | 206 | 7 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 194 | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :B4 (DATE): 11/15/09 22:00:00 2 - :B3 (DATE): 11/16/09 22:00:00 3 - :B2 (NUMBER): 897 4 - :B1 (VARCHAR2(30), CSID=31): (null) |
Only the first plan listed makes use of the SQL Profile associated with this statement. Since only the bind variables are changing when this statement is executed, it was theorized that either bind variable peeking or issues with column cardinality were the root cause. Since this was an Oracle 10g database, bind variable peeking seemed to be a more likely cause, so we will focus on adding a hint to the current profile to disable bind variable peeking.
Being cautious about this process, we first want to back up the SQL Profile we are going to work with in the event that we wish to go back to the original profile should our added hint cause unnecessary side effects. We will use a portion of the process that is recommended for migrating SQL Profiles between databases. The full process is covered in Oracle Support note 457531.1 for 10g databases.
First, we need to create a table in the schema where the SQL code executes that is associated with the SQL Profile we are interested in.
exec dbms_sqltune.create_stgtab_sqlprof (table_name=>’PRFBKP’, schema_name=>’SCOTT’);
Next, we ‘pack’ the table with the SQL Profile that we are going to modify (as the table owner):
exec dbms_sqltune.pack_stgtab_sqlprof (staging_table_name=>’PRFBKP’, profile_name=>’SQL_PROFILE_NAME’);
Note – Recovering the SQL Profile from this staging table will be covered later in this post.
Now we need to get the SQL Profile information we will need to add the hint we have in mind.
Get the signature of the SQL Profile:
set lines 200
col signature format a50
select name, to_char(signature,’9999999999999999999999′) signature
from dba_sql_profiles
where name like ‘&profile_name’;
Sample Output:
NAME SIGNATURE
------------------------------ --------------------------------------------------
PROFILE_48k8mu4mt68pw 13904227686034781239
Using this information, we can display a (partial) list of hints associated with this profile:
select ATTR#,ATTR_VAL
from sqlprof$attr
where SIGNATURE='&signature' order by 1;
Sample Output:
1 IGNORE_OPTIM_EMBEDDED_HINTS
2 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
3 OPT_PARAM('optimizer_index_cost_adj' 80)
4 OPT_PARAM('optimizer_index_caching' 60)
5 ALL_ROWS
6 OUTLINE_LEAF(@"SEL$9384AC1F")
7 OUTLINE_LEAF(@"SEL$42078B5E")
8 OUTLINE_LEAF(@"SET$4D926EBB")
9 PUSH_PRED(@"SEL$43469DE2" <a href="mailto:"D"@"SEL$4">"D"@"SEL$4</a>" 7)
10 OUTLINE_LEAF(@"SEL$43469DE2")
11 UNNEST(@"SEL$7")
12 OUTLINE_LEAF(@"SEL$10")
13 OUTLINE_LEAF(@"SEL$11")
14 OUTLINE_LEAF(@"SET$4")
15 OUTLINE_LEAF(@"SEL$2B7237FB")
16 ELIMINATE_OUTER_JOIN(@"SEL$7183ADFB")
17 OUTLINE_LEAF(@"SEL$F5405A99")
18 OUTLINE_LEAF(@"SEL$7B63D8C4")
19 OUTLINE_LEAF(@"SET$AC71949D")
...
200 INDEX_RS_ASC(@"SEL$7C94CDEB_5" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>" ("ORDER_TENDER"."ORDER_DATE"))
201 INDEX(@"SEL$7C94CDEB_5" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>" ("ORDER_TENDER_DTL"."ORDER_ID" "ORDER_TENDER_DTL"."ORDER_DATE" "ORDER_TENDER_DTL"."ORDER_TENDER_LINE_NO"))
202 INDEX_RS_ASC(@"SEL$7C94CDEB_6" <a href="mailto:"B"@"SEL$7C94CDEB_6">"B"@"SEL$7C94CDEB_6</a>" ("ORDER_TENDER"."ORDER_ID" "ORDER_TENDER"."ORDER_DATE" "ORDER_TENDER"."ORDER_TENDER_LINE_NO"))
203 INDEX_RS_ASC(@"SEL$7C94CDEB_6" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>" ("ORDER_HDR"."ORDER_ID" "ORDER_HDR"."ORDER_DATE"))
204 INDEX(@"SEL$7C94CDEB_6" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>" ("ORDER_STATUS_TYPE"."ORDER_STATUS" "ORDER_STATUS_TYPE"."ORDER_STATUS_TYPE_ID"))
205 INDEX(@"SEL$7C94CDEB_6" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>" ("ORDER_TENDER_DTL"."ORDER_ID" "ORDER_TENDER_DTL"."ORDER_DATE" "ORDER_TENDER_DTL"."ORDER_TENDER_LINE_NO"))
206 LEADING(@"SEL$7C94CDEB_5" <a href="mailto:"C"@"SEL$24">"C"@"SEL$24</a>" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$25">"ORDER_STATUS_TYPE"@"SEL$25</a>" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>")
207 LEADING(@"SEL$7C94CDEB_6" <a href="mailto:"B"@"SEL$7C94CDEB_6">"B"@"SEL$7C94CDEB_6</a>" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>")
208 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$25">"ORDER_STATUS_TYPE"@"SEL$25</a>")
209 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>")
210 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>")
211 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>")
212 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>")
213 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>" |
Note the first hint in this list (IGNORE_OPTIM_EMBEDDED_HINTS). This hint directs the optimizer to ignore any hints embedded in the query syntax itself. This makes sense if the SQL Profile’s directives are going to take precedent over any other directives. This also ensures that there is no conflict between embedded directives and the ones in the SQL Profile.
This particular profile has a lot of hints associated with it. Notice that the direct optimizer hints (OPT_PARAM prefix) are listed near the top. If there were no pre-existing OPT_PARAM hints in the profile, any new OPT_PARAM hints should be placed after the first two hints in the listing. It is not a good idea to place hints before the first two dealing with ignoring any embedded hints and version features enablement. The hint we are going to add will be placed at the end of the listing of existing OPT_PARAM hints at line number 5. Since there is already a hint at that location, we will need to increment the list by one, starting at the line number where we want our new hint to go. This next update statement will perform that function:
update sqlprof$attr set ATTR#=ATTR#+1
where Signature=’&signature’
and ATTR#>= &line_number_for_new_hint;
Now that we have made a place for the new hint, we need to insert it directly into
the table. The following insert statement would be used in this situation (notice the
two single-quotation marks enclosing the hint name and value):
insert into sqlprof$attr
values (‘&signature’,'DEFAULT’,&line_number_for_new_hint,’OPT_PARAM(”_optim_peek_user_binds” ”false”)’);
commit;
Let’s check the listing again to make sure the hint is now embedded in the profile:
select ATTR#,ATTR_VAL
from sqlprof$attr
where SIGNATURE='&signature'
order by 1;
1 IGNORE_OPTIM_EMBEDDED_HINTS
2 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
3 OPT_PARAM('optimizer_index_cost_adj' 80)
4 OPT_PARAM('optimizer_index_caching' 60)
5 OPT_PARAM('_optim_peek_user_binds' 'false')
6 ALL_ROWS
7 OUTLINE_LEAF(@"SEL$9384AC1F")
8 OUTLINE_LEAF(@"SEL$42078B5E")
9 OUTLINE_LEAF(@"SET$4D926EBB")
10 PUSH_PRED(@"SEL$43469DE2" <a href="mailto:"D"@"SEL$4">"D"@"SEL$4</a>" 7)
11 OUTLINE_LEAF(@"SEL$43469DE2")
12 UNNEST(@"SEL$7")
13 OUTLINE_LEAF(@"SEL$10")
14 OUTLINE_LEAF(@"SEL$11")
15 OUTLINE_LEAF(@"SET$4")
16 OUTLINE_LEAF(@"SEL$2B7237FB")
17 ELIMINATE_OUTER_JOIN(@"SEL$7183ADFB")
18 OUTLINE_LEAF(@"SEL$F5405A99")
19 OUTLINE_LEAF(@"SEL$7B63D8C4")
20 OUTLINE_LEAF(@"SET$AC71949D")
...
200 INDEX_RS_ASC(@"SEL$7C94CDEB_5" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>" ("ORDER_TENDER"."ORDER_DATE"))
201 INDEX(@"SEL$7C94CDEB_5" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>" ("ORDER_TENDER_DTL"."ORDER_ID" "ORDER_TENDER_DTL"."ORDER_DATE" "ORDER_TENDER_DTL"."ORDER_TENDER_LINE_NO"))
202 INDEX_RS_ASC(@"SEL$7C94CDEB_6" <a href="mailto:"B"@"SEL$7C94CDEB_6">"B"@"SEL$7C94CDEB_6</a>" ("ORDER_TENDER"."ORDER_ID" "ORDER_TENDER"."ORDER_DATE" "ORDER_TENDER"."ORDER_TENDER_LINE_NO"))
203 INDEX_RS_ASC(@"SEL$7C94CDEB_6" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>" ("ORDER_HDR"."ORDER_ID" "ORDER_HDR"."ORDER_DATE"))
204 INDEX(@"SEL$7C94CDEB_6" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>" ("ORDER_STATUS_TYPE"."ORDER_STATUS" "ORDER_STATUS_TYPE"."ORDER_STATUS_TYPE_ID"))
205 INDEX(@"SEL$7C94CDEB_6" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>" ("ORDER_TENDER_DTL"."ORDER_ID" "ORDER_TENDER_DTL"."ORDER_DATE" "ORDER_TENDER_DTL"."ORDER_TENDER_LINE_NO"))
206 LEADING(@"SEL$7C94CDEB_5" <a href="mailto:"C"@"SEL$24">"C"@"SEL$24</a>" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$25">"ORDER_STATUS_TYPE"@"SEL$25</a>" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>")
207 LEADING(@"SEL$7C94CDEB_6" <a href="mailto:"B"@"SEL$7C94CDEB_6">"B"@"SEL$7C94CDEB_6</a>" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>")
208 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$25">"ORDER_STATUS_TYPE"@"SEL$25</a>")
209 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"B"@"SEL$24">"B"@"SEL$24</a>")
210 USE_NL(@"SEL$7C94CDEB_5" <a href="mailto:"B1"@"SEL$24">"B1"@"SEL$24</a>")
211 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"C"@"SEL$7C94CDEB_6">"C"@"SEL$7C94CDEB_6</a>")
212 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6">"ORDER_STATUS_TYPE"@"SEL$7C94CDEB_6</a>")
213 USE_NL(@"SEL$7C94CDEB_6" <a href="mailto:"B1"@"SEL$7C94CDEB_6">"B1"@"SEL$7C94CDEB_6</a>") |
The new hint is now at line 5.
After the SQL Profile is modified, it would be a good idea to flush all the old execution plans from the shared pool with the following routine:
set serveroutput on
set pagesize 9999
set linesize 155
var name varchar2(50)
accept sql_id -
prompt ‘Enter value for sql_id: ‘
BEGIN
select address||’,'||hash_value into :name
from v$sqlarea
where sql_id like ‘&&sql_id’;
dbms_shared_pool.purge(:name,’C',1);
END;
/
In the event that the hint has no affect, or does more harm than good, we can recover the old SQL Profile from the staging table we backed it up to earlier.
We need to drop the SQL Profile we modified first. Use this command:
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => ‘&SQL_PROFILE_NAME’,
ignore => TRUE);
END;
/
Now, simply ‘unpack’ the SQL Profile backup table:
exec dbms_sqltune.unpack_stgtab_sqlprof (replace=>TRUE, staging_table_name=>’ PRFBKP’);
Once you have verified that the SQL Profile has returned, you can drop the SQL Profile backup table.
Jon Adams is a principal consultant, 9i-11g OCP, and sometime blogger with Enkitec in Irving, TX.
