Kerry Osborne's Oracle Blog

I posted on this topic a while back (How to Attach a SQL Profile to a Different Statement – Take 1), but wasn’t really happy with my solution. So here’s another shot at it. The basic idea is to be able to create a profile on one statement using hints or whatever other tricks you can come up with, and then attach the profile to a production statement on which you cannot modify the code. The basic steps of this technique are as follows:

• make a copy of the statement – (I use my build_bind_vars.sql (or build_bind_vars_awr.sql) script)
• do whatever you want to make it get the plan you want including adding hints
• create a Profile on the new statement – (I use my create_sql_profile.sql (rg_sqlprof1.sql) script)
• fix the Profile to eliminate any bad index hints – (I use my fix_sql_profile_hint.sql script)
• move the Profile over to the original statement – (I use my move_sql_profile.sql script)

The main reason I became dissatisfied with my previous approach was that it’s often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. My previous approach created the profile and moved it all in one step. So there was no chance to modify the Profile before it was attached to the production statement. Anyways, here’s an example (note the examples are on a 10.2.0.4 database, but all the SQL should work on 11g as well):
Continue reading ‘How to Attach a SQL Profile to a Different Statement – Take 2’ »

In the “Learn Something New Every Day” category, I ran across this new feature (to me) of 10g today (on Eddie Awad’s Blog). I was trying to write a little script to help me create a rewrite rule using DBMS_ADVANCED_REWRITE and I wanted to be able to pass a string that had quotes embedded in it (i.e. a SQL statement). Of course you all know that quotes can be embedded in a string by doubling them like so:

SQL> select '''This is Kerry''s stuff.''' string from dual;

STRING
------------------------
'This is Kerry's stuff.'

Or perhaps using the chr function like so:

SQL> select chr(39)||'This is Kerry'||chr(39)||'s stuff.'||chr(39) string from dual;

STRING
------------------------
'This is Kerry's stuff.'

The new way is much more straight forward, especially if the goal is to paste in a long SQL statement with potentially lot’s of quotes. It looks like this:

SQL> select q'[What ever stuff you'd like to see, just as you'd like to see it.]' string from dual;

STRING
----------------------------------------------------------------
What ever stuff you'd like to see, just as you'd like to see it.

Not bad, huh? Much easier to read – but that’s not all. Suppose you want to prompt the user for a string that contains quotes.

SQL> select '&string' into :x from dual;
Enter value for string: this is text

'THISISTEXT'
------------
this is text

SQL> /
Enter value for string: where x = 'Y'
select 'where x = 'Y'' into :x from dual
                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

So it sees the first quote in the string and thinks it’s at the end of the string. To get around this you could paste your string into a text editor, replace every quote with two quotes (or with ‘||chr(39)||’), and then grab the whole new string and paste it back into your script. But it’s pretty messy. Obviously the Q-quote method makes the whole thing much simpler.

By way of example, here’s the little script I was working on:

SQL> !cat create_rewrite.sql

exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence ( -
name => '&rewrite_name', -
source_stmt => q'[&from_sql_statement]', -
destination_stmt => q'[&to_sql_statement]', -
validate => FALSE, -
rewrite_mode => 'TEXT_MATCH' -
);

SQL> @create_rewrite
Enter value for rewrite_name: DODA
Enter value for from_sql_statement: select * from sapsr3.fkkvk where vkona <> 'Y'
Enter value for to_sql_statement: select * from sapsr3.fkkvk where vkona = 'N'

PL/SQL procedure successfully completed.

The syntax is really simple: Just select q'[ whatever_you_want_here_including_quotes ]’ and that’s it.
Of course there are a few options (such as using your choice of delimiters). Here’s a link to the docs if you want
further details.

Dan Morgan’s Oracle Library has a good example as well here.

I like using the square brackets as the delimiters because I don’t usually see them in strings (and it looks kind of like a regular expression) ;).

I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right? Here’s a bit of an email I sent with my thoughts on the issue during a conversation on the Oracle-L list.

First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.

If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.

First, if it’s a profile generated by SQL Tuning Advisor it’s quite possible that it will have one or more OPT_ESTIMATE hints – which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.

The second common possibility is that Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on. For example:

INDEX_RS_ASC(@”SEL$1″ “TEST_TABLE”@”SEL$1” (“TEST_TABLE”.”COL2″ “TEST_TABLE”.”TEST_TABLE_ID”))

This hint does not specify an index, but rather columns to use an index on. I’m not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a statement will switch plans even though it’s using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an Outline or Profile with, enable it, and have the new child use a plan that’s different from the plan of the child you used to create it with. Very frustrating.

So as I said in the email, the most likely cause for SQL Profiles not working is the non-specific form of index hints that are being used. However, as I was doing some research for this post, I found a couple situations where SQL Profiles just flat don’t work as advertised. I’ll show you an example, but first here are a few scripts that I will use (I sometimes don’t set “echo on” because it makes the examples so messy – so just hit the links to view the scripts):

Updated: 6/14/13
Note that I have rewritten the create_sql_profile.sql script so that it is not dependent on the rg_sqlprof1 script and to add additional functionality. Please use the newer one. Also I have rewritten the sql_profile_hints.sql script so that it works with 10g and 11g. I have disabled the links to the old versions in the list below.

sql_hints.sql – shows the hints stored in the v$sql_plan.other_xml column
sql_profile_hints.sql – shows the hints associated with a SQL Profile
sql_profile_hints11.sql – shows the hints associated with a SQL Profile (for 11g)
create_sql_profile.sql – creates a profile for a statement in the shared pool (wrapper for rg_sqlprof1.sql)
rg_sqlprof1.sql – does the real work to create a profile for a statement in the shared pool
fix_sql_profile_hint.sql – replaces a hint in a profile with whatever you want

Here’s the set up: a simple single table query that wants to do a full table scan and a second version that uses a hint to force an index. A profile created on the hinted statement should keep it from changing, but instead, it changes the plan back to a full table scan. Here’s the example:

Continue reading ‘Why Isn’t Oracle Using My Outline / Profile / Baseline?’ »

One of the old tricks with Outlines was to switch hints between two statements. This allowed hints to be applied to a statement to influence the plan that the optimizer chose. The original statement (without the hints) could then be made to behave by swapping the Outlines. It was always a questionable approach in my mind. However, DBMS_SQLTUNE has a built-in procedure to import hints into a SQL Profile which makes it feel a little less risky. So here’s a quick little script to do the old “Outline Switcheroo Trick”, but with SQL Profiles. It’s based on some work done a few months back by myself and Randolf Geist. Here’s a couple of posts to look at for background info:

• Mine
• Randolf’s

This iteration has a couple of new scripts:

create_sql_profile2.sql – creates a profile for one statement based on hints from another (wrapper for rg_profile_hints3.sql)
rg_sqlprof3.sql – modified version of Randolf’s original script, pulls hints from v$sql_plan
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g

NOTE: I have posted an update to this approach here:

How to Attach a SQL Profile to a Different Statement – Take 2

You may want to skip the example below and refer to it instead (don’t miss the caveats at the bottom of this post though).

Here’s an example:

> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:38:22 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @flush_pool

System altered.

Elapsed: 00:00:00.31
SQL> set echo on
SQL> set timing on
SQL> @avgskewi
SQL> select avg(pk_col) from kso.skew
  2  where col1 = 23489
  3  /

AVG(PK_COL)
-----------


Elapsed: 00:00:00.01
SQL> @avgskewi_hint
SQL> select /*+ full(skew) */ avg(pk_col) from kso.skew
  2  where col1 = 23489
  3  /

AVG(PK_COL)
-----------


Elapsed: 00:00:11.23
SQL> set timing off
SQL> @find_sql
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs,
  2  (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
  3  buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
  4  sql_text
  5  from v$sql s
  6  where upper(sql_text) like upper(nvl('&sql_text',sql_text))
  7  and sql_text not like '%from v$sql where sql_text like nvl(%'
  8  and sql_id like nvl('&sql_id',sql_id)
  9  order by 1, 2, 3
 10  /
Enter value for sql_text: %skew%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7s0b9ygcrj77u      0 3723858078          1           .01          134 select avg(pk_col) from kso.skew where col1 = 23489
9r9wq9xqsw6mu      0  568322376          1         10.97      173,731 select /*+ full(skew) */ avg(pk_col) from kso.skew where col
                                                                      1 = 23489

SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
  2  /
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489

Plan hash value: 3723858078

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    53 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    54 |   594 |    53   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    54 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COL1"=23489)


20 rows selected.

SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
  2  /
Enter value for sql_id: 9r9wq9xqsw6mu
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9r9wq9xqsw6mu, child number 0
-------------------------------------
select /*+ full(skew) */ avg(pk_col) from kso.skew where col1 = 23489

Plan hash value: 568322376

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 31719 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    54 |   594 | 31719  (37)| 00:00:43 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=23489)


19 rows selected.

SQL> -- so one statement hinted to use full table scan the other not hinted is using an index 
SQL> -- let's try creating a profile from the hinted version and putting it on none hinted statement
SQL> set echo off
SQL> @create_sql_profile2
Enter value for sql_id to generate profile from: 9r9wq9xqsw6mu
Enter value for child_no to generate profile from: 0
Enter value for sql_id to attach profile to: 7s0b9ygcrj77u
Enter value for child_no to attach profile to: 0
Enter value for category: 
Enter value for force_matching: 

PL/SQL procedure successfully completed.

SQL> @sql_profiles
Enter value for sql_text: %skew%
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_922pr090z0bvm          DEFAULT         ENABLED  select sql_id, dbms_lob.substr(sql_text,3999,1) sql_text from dba_hist NO
PROFILE_7s0b9ygcrj77u_attach   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO

SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_7s0b9ygcrj77u

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")

SQL> @flush_pool

System altered.

SQL> @avgskewi

AVG(PK_COL)
-----------


SQL> @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
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7s0b9ygcrj77u      0  568322376          1         10.29      174,071 select avg(pk_col) from kso.skew where col1 = 23489

SQL> @dplan
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489

Plan hash value: 568322376

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 31719 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    54 |   594 | 31719  (37)| 00:00:43 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=23489)

Note
-----
   - SQL profile "PROFILE_7s0b9ygcrj77u" used for this statement


23 rows selected.

SQL> -- so this worked, statement is now using a profile generated from hints on another statement

A couple of notes:

• Beware of table aliases as they can cause hints to fail.
• Beware of any structural changes as they can cause hints to fail.
• Beware of the non-specific format of the INDEX hint.
  (it leaves the optimizer with a lot of flexibility than you probably want it to have)
  (for more details, see this post: Why Isn’t Oracle Using My Outline / Profile / Baseline?

Your comments are always welcomed.

Here’s a nifty little script that one of my co-workers (Jack Augustin) wrote to speed up the process of building test scripts. I thought it was a great idea, but of course I can never leave well enough alone, so I had to modify it slightly to my own taste (which by the way, I hope you will do as well). Anyway, the reason for the script is that we often get requests to improve a SQL statement that uses bind variables (sometimes lots of them). It’s pretty common to build a test script that can be run in SQL*Plus by grabbing the sql text and the values of the bind variables from the shared pool. Building the script though can be very tedious if the statements are long and particularly if there are a lot of bind variables. So this script eliminates most of that tedious work by generating a first cut at a test script based on a sql_id. The test script defines the variables, sets the values of the variables, and then executes the sql statement. A little manual editing may still be necessary, but it saves a lot of time.

Jack’s original version used V$SQL_BIND_CAPTURE to get bind variable values. I changed it to use the OTHER_XML column of V$SQL_PLAN. There are a few differences in these approaches.

• v$sql_plan has the values used when the statement was parsed.
• v$sql_plan usually has more bind variables in cases where there are a large number of bind variables. This is due to the fact that v$sql_bind_capture is controlled by _bind_capture_area_size which defaults to a value that is quite a bit smaller than the area used by v$sql_plan (thus a smaller number of bind variables).
• v$sql_bind_capture has the data type, v$sql_plan does not. (it’s got a numeric code for the data type – 1 for varchar2, 2 for number, etc…)

I called the script build_bind_vars.sql.

Well I was feeling all good about my version since it worked better on statements with lots of bind variables. But last week I was working on a system where they had turned off bind variable peeking via the _OPTIM_PEEK_USER_BINDS parameter. Thus, no bind info in the OTHER_XML field. Rats! Back to V$SQL_BIND_CAPTURE. So I made a copy of the script but changed it to get the values from V$SQL_BIND_CAPTURE.

Naturally I called this one build_bind_vars2.sql.

Note: these scripts depend on a little function called display_raw which I got from Greg Rahn. Here’s a script to create the function: create_display_raw.sql

UPDATED 01-JAN-13:

I have used this script a lot over the last several years and have modified it quite a bit. Here’s a newer version that is a little more functional (I also shortened the name to bbv.sql as I tend to do with scripts I use fairly often): bbv.sql

Now here’s an example:

> !sql
sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 23 08:51:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @find_sql
Enter value for sql_text: select%:4%:5%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
3hcvdhu22mjqu      0 1890474001          1           .01           76 select box_id from rule_set_rop$ where rs_obj# = :1 and ec_o
                                                                      bj# = :2  and rule_id = :3 and rule_or_piece = :4 and rop_id
                                                                       = :5 order by eval_id

57antna43jh0a      0 3630768429          1           .04          252 select box_id, value from rule_set_iot$ where rs_obj# = :1 a
                                                                      nd ec_obj# = :2  and rule_id = :3 and rule_or_piece = :4 and
                                                                       rop_id <> :5 order by rop_id
...

SQL> @build_bind_vars
Enter SQL ID ==> 3hcvdhu22mjqu
Enter Child Number ==> 0

variable N1 NUMBER
variable N2 NUMBER
variable N3 NUMBER
variable N4 NUMBER
variable N5 NUMBER

begin

end;

/

select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ where rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and rule_or_piece = :N4
and rop_id = :N5 order by eval_id;

SQL> -- Oops, nothing in the section setting the variable values!
SQL> -- Let's see what's actually in the OTHER_XML field
SQL> SQL> select other_xml from v$sql_plan
  2  where sql_id = '3hcvdhu22mjqu'          
  3  and other_xml is not null
  4  /
 
OTHER_XML
--------------------------------------------------------------------------------
10.2.0.34025366187



1 row selected.

SQL> -- Ah, no bind data in the OTHER_XML column, we'll have to get values from v$sql_bind_capture
SQL> @build_bind_vars2
Enter SQL ID ==> 3hcvdhu22mjqu
Enter Child Number ==> 

variable N1 NUMBER
variable N2 NUMBER
variable N3 NUMBER
variable N4 NUMBER
variable N5 NUMBER

begin

:N1 := 8809;
:N2 := 8794;
:N3 := 0;
:N4 := 0;
:N5 := 0;

end;

/

select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ where rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and rule_or_piece = :N4
and rop_id = :N5 order by eval_id;

SQL> -- OK, let's try executing the script
SQL> @3hcvdhu22mjqu

PL/SQL procedure successfully completed.


no rows selected

SQL> -- Well it looks like it worked, let's see if we can find the test statement
SQL> @find_sql
Enter value for sql_text: %test 3hcvdhu22mjqu%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7ndfkkg214gd1      0 4025366187          1           .01           85 select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ wh
                                                                      ere rs_obj# = :N1 and ec_obj# = :N2  and rule_id = :N3 and r
                                                                      ule_or_piece = :N4 and rop_id = :N5 order by eval_id


1 row selected.

I have been using this script a lot recently. Seems like I could keep adding things to it, but it does the job for the most part. (I still need to edit the output file occasionally to clean up split words, etc…) Also, the data type mapping is not complete in build_bind_vars2.sql, it just has types 1 and 2. Most likely I won’t get around to adding other types until I actually need them. So anyway, there it is. Let me know if you have any ideas for improving it.