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_vars2.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):

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?’ »

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

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
--------------------------------------------------------------------------------
<other_xml><info type="db_version">10.2.0.3</info><info type="parse_schema"><![C
DATA["SYS"]]></info><info type="plan_hash">4025366187</info><outline_data><hint>
<![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_
ENABLE('10.2.0.3')]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint
><![CDATA[INDEX(@"SEL$1" "RULE_SET_ROP$"@"SEL$1" ("RULE_SET_ROP$"."RS_OBJ#" "RUL
E_SET_ROP$"."EC_OBJ#" "RULE_SET_ROP$"."RULE_ID" "RULE_SET_ROP$"."RULE_OR_PIECE"
"RULE_SET_ROP$"."ROP_ID" "RULE_SET_ROP$"."EVAL_ID"))]]></hint></outline_data></o
ther_xml>
 
 
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.

Karen Morton just posted a great paper on her blog about statistics gathering. The paper is titled “Managing Statistics for Optimal Query Performance“. I was excited to see it because I think gathering stats is one of the most important and least well understood aspects of managing an Oracle environment. I must admit that I was expecting a recommended method or framework for gathering stats, but actually the paper is really more about how the statistics are used along with general guidelines for gathering them, rather than a direct recommendation on how to gather them. Nevertheless, it is one of the best papers I’ve seen on the subject. She’s going to present the paper at the Hotsos Symposium to be held in Dallas the week of March 9th. I’m going to be there and am really looking forward to hearing what she has to say on the subject.

By the way, I can’t recommend this conference highly enough. If you really want to understand how Oracle works, this is the place to be. You should know that I am not generally a fan of formal training classes. I have often been disappointed because I felt like my time would have been better spent researching the subject matter myself. On the other hand, I have found a lot of value in working closely on a project with someone who knows the subject matter well, kind of like a mentor. But generally speaking, the formal classes have been less satisfying, except in the rare case where you get the great instructor that wrote the class. This symposium format on the other hand allows you to listen to a collection of really knowledgeable Oracle people packed into a short period of time. I have been to the Hotsos Symposiums for several years in a row and I always come away with pages of notes on things I want to investigate further. And the participants are, generally speaking, a collection of very bright Oracle people. So even the conversations between the presentations are often very interesting. Finally, they run two presentations at a time which allows you to pick the presentation that is most interesting. I have often found it hard to choose (don’t tell anyone, but I have on more than one occasion listened to the first half of one and then the second half of the other). So like I said, I find it to be a very productive few days.

But I digress, Karen’s paper is pretty long (24 pages) but it covers a vast amount of ground. There are a number of one liners that could be expanded into full papers. In the paper she discusses a number of topics including dealing with short comings of the optimizer in 10g. One of those issues is bind variable peeking (probably my least favorite optimizer feature, quirk, bug, … what ever you want to call it). I must say that I think it has caused far more problems than it solved, and I frankly don’t know what they were thinking when they put that feature in. I wrote a little about a way to get around it using outlines here. By the way, this reminds me of a cartoon I drew 20 years ago that looked very similar to this one (that I lifted off of Steve Karam’s blog)

Of course as Karen points out, the right way to deal with bind variable peeking issues it is to understand your data and use literals where they are appropriate, keeping in mind the number of additional statements that will need to be parsed and dealt with in the shared pool. She also points out that code could be written to selectively use literals for specific values, giving you a mix of literals and bind variables for the same statement. This approach should allow you to minimize the impact on the shared pool while still providing the optimizer with the data it needs to make good decisions (this is a great idea but I’ve never seen anyone actually do it). And of course she points out that 11g has a much better mechanism for dealing with this whole issue.

Another idea that really got me thinking was the use of dynamic sampling . Karen clearly shows one of the advantages of dynamic sampling in the case of correlated predicates (i.e. the optimizer assumes a query where car_model = ‘Mustang’ and car_make = ‘Ford’ are independent, when clearly they are not). She shows how dynamic sampling can be very useful in conjunction with normal statistics in this situation. (rats, now I’m going to have to go play around with that a bit - so much to do, so little time)

Finally, she discusses some of the statistics gathering options and differences in 9i, 10g, and 11g. The automatic creation of histograms is one of the main differences between 9i and 10g and she discusses this issue, but doesn’t go into to much detail on it. I must admit that I think 10g’s default setup does a very poor job when it comes to histograms. This is the one area I would have liked to see address a little more fully, but at 24 pages already I can understand why she had to draw the line somewhere. Anyway, by default 10g creates histograms on columns based on several factors including their use in where clauses. Unfortunately, histograms often get created on columns where their usefulness is questionable at best and they regularly get created with a very small sample sizes. The small sample sizes often result in significant inaccuracies. I personally think that allowing the gather stats job to automatically create histograms in 10g is really bad idea.

Anyway, this is a paper that is well worth reading in my opinion. Typical Hotsos Symposium fare!