Kerry Osborne's Oracle Blog

I modified my create_1_hint_sql_profile.sql script (which I blogged about here: Single Hint Profiles) to allow any arbitrary text sting including quote characters. This is a script that I use fairly often to apply a hint to a single SQL statement that is executing in a production system where we can’t touch the code for some reason. For example, it’s sometimes useful to add a MONITOR hint or a GATHER_PLAN_STATISTICS hint to a statement that’s behaving badly so we can get more information about what the optimizer is thinking. I recently updated the script to allow special characters in the hint syntax. This feature is useful when you want to add something like an OPT_PARAM hint that takes quoted arguments. The change makes use of the q-Quote feature which I blogged about here: q-Quote. (the original version just barfed on quotes being input as part of the hint)

Here’s an example of how to use it:

SYS@SANDBOX1> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.00
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;

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

1 row selected.

Elapsed: 00:00:28.08
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg      0 2684249835      1      28.07      0 No             .00 select avg(pk_col) from kso.skew3 where col1 < 0

1 row selected.

Elapsed: 00:00:00.02
SYS@SANDBOX1> @create_1_hint_sql_profile.sql
Enter value for sql_id: a6j7wgqf84jvg
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint_text: opt_param('cell_offload_processing' 'true')

Profile PROFILE_a6j7wgqf84jvg_MANUAL created.

Elapsed: 00:00:00.07
SYS@SANDBOX1> @sql_profile_hints
Enter value for profile_name: PROFILE_a6j7wgqf84jvg_MANUAL

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
opt_param('cell_offload_processing' 'true')

1 rows selected.

Elapsed: 00:00:00.04
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;

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

1 row selected.

Elapsed: 00:00:05.11
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg      0 2684249835      1      28.07      0 No             .00 select avg(pk_col) from kso.skew3 where col1 < 0
a6j7wgqf84jvg      1 2684249835      1       5.10      0 Yes          99.99 select avg(pk_col) from kso.skew3 where col1 < 0

In the example I turned off cell offload processing with the ALTER SESSION and ran a SQL statement that took 28 seconds. Then I used my fsx.sql script to verify that the statement was not offloaded and to find the SQL_ID. Next I created a 1 hint Profile with an OPT_PARAM hint that set the cell_offload_processing parameter back to TRUE using the new version of my create_1_hint_sql_profile.sql script. Next I used my sql_profile_hints.sql script to verify the text of the hint that was added to the Profile. It looked good including the quotes. When I executed the statement a second time it ran in 5 seconds. I then used fsx.sql again to see that the statement was offloaded for the second execution (child 1).

I ran across a great post by Marcin Przepiorowski (How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines) thanks to Karl Arao‘s tweet. Marcin posted a bit of code for calculating a SQL_ID using the text of a SQL statement (based on some research by Tanel Poder). I was aware of Tanel’s research but had not taken the time to try to figure out how to code it. The context of Marcin’s post was based on wanting to relate a Baseline to a SQL_ID. This is something I’ve wanted to do in the past so I was quite interested in the post. As a bonus Marcin also demonstrated a technique to pull the PLAN_HASH_VALUE associated with a Baseline. Of course I can never leave well enough alone and so I had to re-arrange Marcin’s code a little bit to suite my own purposes. So I created a function that returns either the PLAN_HASH_VALUE or the SQL_ID of a Baseline. Here’s the code to create the function: create_baseline_info.sql and a script that uses it: baselines2.sql

Here’s an example of how to use them:

Continue reading ‘Baselines and SQL_ID’ »

I think I made a mistake. I recently set up a twitter account @KerryOracleGuy (of course plain old @KerryOsborne was already taken). I only signed up because I wanted to see what @TanelPoder was saying. I wasn’t planning on tweeting at all when I signed up. I just wanted to see what Tanel was saying. Well the first day a bunch of people signed up to follow me. The whole thing seems a little strange. Although it is pretty easy to keep up with what people are doing. So I guess I’m going to give it a try and see if I like it. I have tweeted a few times and I think I am starting to get the hang of it (it’s hard to teach an old dog new tricks). I have figured out how to use tiny url’s now. I think I need to figure out how to tweet pictures as well. I’ve heard there is a Twit Pic for doing that. If I use it though, I guess that would make me a “Twit Pic-er”, which I don’t much like the sound of. So maybe I’ll stick to text based tweets for now.

I ran into an interesting issue last week having to do with plan stability. The problem description went something like this:

“I have a statement that runs relatively quickly the first time I run it (around 12 seconds). Subsequent executions always run much slower, usually around 20 or 30 minutes. If I flush the shared pool and run it again elapsed time is back to 12 seconds or so.”

The query looked a little like this:

Continue reading ‘Cardinality Feedback’ »

Karen Morton, Cary MIllsap and I will be participating in a on-line panel discussion about Oracle Performance on July 28th. Since we all worked together in the past we thought it would be a fun to listen to each other answer questions. Embarcadero is sponsoring this event and invited us to participate. Here’s a graphic from the mailer they sent out.

I only point it out because Cary and Karen look like they are posing for a picture, while I, as usual, look like someone just poured a drink down my pants. That’s normal though. I’ve been told I have a great face for radio.

You can sign up here: Register Now!