Kerry Osborne's Oracle Blog

Roughly 10 years ago, Oracle introduced a way to lock down execution plans. This was a response to the inherent unpredictability of the early cost based optimizer. The Oracle marketing machine even gave the feature the name “Plan Stability”. It appeared to be a good approach, basically allowing a DBA to lock an existing plan for a given statement. The mechanism employed to accomplish this was to create hints that didn’t allow the optimizer much (if any) flexibility to come up with an alternative plan. These hints are stored in the database in a structure called an OUTLINE or sometimes a STORED OUTLINE. The optimizer could then apply these hints behind the scenes whenever a SQL statement that matched was executed. By the way, “matching” basically means that the text of the statement matches. Originally outlines had to match character for character just like the normal rules for sharing SQL statements, but for some reason, Oracle later decided that the matching algorithm should be somewhat relaxed as compared to Oracle’s standard. What that means is that in 10gR2 by default whitespace is collapsed and differences in case are ignored. So (at least as far as outlines are concerned)  “select * from dual” is the same as “SELECT     *       FROM DuAl”. You’ll still get two different statements in the shared pool but they will use the same outline, if one exists.

With 9i, Oracle started to enhance this feature by adding the ability to edit the outlines themselves, but they never really completed the job. They pretty much quit doing anything with the feature after 9i. In fact, the script that creates the DBMS_OUTLN package ($ORACLE_HOME/rdbms/admin/dbmsol.sql) has not been updated since early in 2004 (with the exception of a tweak to keep it working in 11g). Anyway, I think it is a great feature with two primary uses.

• First, it can be used to freeze a plan for a statement. This is especially helpful in situations where bind variable peeking is causing Oracle to alternate between a couple of plans.
• Second, it can be very helpful when dealing with an application where the code can not be modified. Outlines provide a means of altering the execution plan for a statement without changing the code or making changes to the basic database configuration.

Lot’s of people have written about outlines, so I don’t want to just repeat information that is already available. But there doesn’t seem to be a single spot that pulls together all (at least what I consider to be all) the important stuff. Also, most of the stuff I have seen about outlines was written for 8i or 9i. As this is being written, 11gR1 has been out for over a year (although it has still not been widely adopted), and 10gR2 is far and away the most prevalent version in production. So, here we go.

Outlines can be created two ways.

1. You can use the CREATE OUTLINE statement – which allows you to give your outline a name, but requires you to include the SQL statement as part of your CREATE OUTLINE statement. Therefore you can’t see what the execution plan is before creating the outline. Not very useful in my opinion.
2. You can use the CREATE_OUTLINE procedure in the DBMS_OUTLN package – which doesn’t allow you to give your outline a name, but does let you specify a specific child cursor of a specific SQL statement in the shared pool. This means you can check the execution plan before creating the outline and that you can be sure the statement exactly matches what is being sent from the application.

Here’s an example:

CREATE OUTLINE myoutline FOR CATEGORY temp ON select * from dual;

EXEC DBMS_OUTLN.CREATE_OUTLINE('82734682234',0,'DEFAULT');

Continue reading ‘Oracle Outlines – aka Plan Stability’ »

I posted earlier about the ability to flush a single SQL statement out of the shared pool in 11g (also back ported to 10.2.0.4 with a bit of extra work) here. If you are on an earlier release of Oracle though, you can accomplish the same thing by creating an outline on the statement using the DBMS_OUTLN.CREATE_OUTLINE procedure. I just discovered this recently, so let me know if I just missed this trick. Anyway, prior to noticing this affect of creating an outline, my best options to flush a statement were:

• flush the shared pool – not a very appealing option in a production environment (although I see almost the same affect frequently at sites that gathering stats every night).
• modify an object that the statement depends on – I usually would add a comment to one of the tables used by the statement. Unfortunately, all statements that use the table will be flushed, so this technique can also be a little hard on a production system, but it’s certainly better than flushing the whole shared pool. 

So I wrote a little script (like I always do) and called it flush_sql10.sql. There are a couple of things to be aware of with it.

1. I don’t like having to find the hash_value that the create_outline procedure uses, so I prompt for the sql_id and then let the script find the hash_value.
2. The create_outline procedure requires a child number, but the flushing affect is not limited to the specified child. All children for the specified statement will be flushed.
3. The script drops the outline after creating it (since the purpose is to flush the statement, not to create an outline). This part is a little dicey since the create_outline procedure does not return an identifier for the outline that gets created. Nor does it allow you set a name for the outline. So I coded it to drop the most recently created outline (which should be sufficient, since it would be highly unlikely that more than one person would be creating outlines at the same time). But wait, don’t answer yet, I also limited the drop to outlines created in the last 5 seconds. Bottom line, it is unlikely that an unintended outline would be accidentally dropped by the script. (you have however been forewarned!)
4. There’s no error checking. Any errors stop execution of the script and are passed back to the user. The most common error is to not give it a valid SQL_ID, CHILD_NO combination. In this case the create_outline procedure fails and the script exits with a “no data found” message.

Here’s an example:

> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 12 08:31:08 2008

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> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO INVALIDATIONS    SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------- -----------------------------------------------------
0gza16w5ka67q      0 1528838287          1           .01          249             0 SELECT   count(*)    FROM kso.skew where pk_col < 10
688rj6tv1bav0      0 3723858078          1          2.15       37,270             1 select avg(pk_col) from kso.skew where col1 = 1
688rj6tv1bav0      1  568322376          1          5.09      173,731             1 select avg(pk_col) from kso.skew where col1 = 1
7cbu7dgt0vh6y      0 1528838287          1           .00          226             0 select count(*) from kso.skew where pk_col < 10

SQL> @flush_sql_ol

Session altered.

Enter value for sql_id: 688rj6tv1bav0
Enter value for child_number: 1

SQL Statement 688rj6tv1bav0 flushed.
(Note also that outline SYS_OUTLINE_08121120170934217 was dropped.)

PL/SQL procedure successfully completed.

SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO INVALIDATIONS    SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------- -----------------------------------------------------
0gza16w5ka67q      0 1528838287          1           .01          249             0 SELECT   count(*)    FROM kso.skew where pk_col < 10
7cbu7dgt0vh6y      0 1528838287          1           .00          226             0 select count(*) from kso.skew where pk_col < 10

The best Oracle conference in the galaxy.

I’ve attended the last 4 years in a row and I have to say that it’s been far and away the best conference or training event that I’ve ever participated in. Add to that the extremely high quality of the people in attendance and you get an awesome event. 

The 2009 Symposium will be held in Dallas (as always), March 8-12. Chris Date will be giving the key note address. He is one of the founding fathers of Relational Databases and I am very interested to hear what he has to say. 

I must say I am extremely honored to be able to present at this years symposium, and humbled to be in the company of the other presenters. Here’s a link to the speakers page for this years event. And here’s a link to the sign up page.

Hope to see you there!

Oracle uses the BITAND function heavily (over 1000 times in the catalog.sql script alone as of 10.2.0.4). It’s used to determine whether a particular bit is set or not.

Here’s a link to 11gR1 documentation on the BITAND function. The function takes 2 arguments and performs these basic steps:

1. Converts the 2 arguements to binary (n-bit two’s complement binary integer value)
2. Performs a standard bitwise AND operation on the two strings
3. Converts the binary result back to decimal

So what’s a standard bitwise AND operation actually do?

Well it basically does a logical AND of two bit strings. If the values in any position are both 1’s, then the result will have a 1 in that position, otherwise the result will have a 0 in that position. Here’s a link to the Wikipedia entry on bitwise operations in case you want more information.

Here’s an example:

   
    0101
AND 1001
  = 0001

Continue reading ‘Oracle BITAND function (bitwise AND)’ »

There is a fair amount of information available on this topic already, but I thought I’d add my two cents worth here.

So why might you need to do this in the first place? Well generally it’s because some parameter has been set that someone later decides was a mistake. Or, as in my case, because Oracle comes out with a new way to handle a parameter making it attractive to let Oracle automatically handle it. db_file_multiblock_read_count is just such a parameter, but I’ll have to save that for another post. Anyway, there are a couple of key points to keep in mind:
 

1. Oracle does a lot of things automatically (if we don’t prevent it)
2. Some of the automatic things are good
3. Setting a parameter back to it’s default value IS NOT THE SAME as unsetting it

 
But first a few house keeping basics. Oracle has a view called V$PARAMETER which lists all the non-hidden parameters, their current values and a few fields which indicate whether the current value has been altered from the default value (ISMODIFIED and ISDEFAULT in particular). The view is based on X$KPPI and X$KSPPV. These X$ views contain the so called hidden parameters (those beginning with “_”) as well as the regular init.ora / spfile parameters, although the V$PARAMETER view doesn’t expose the hidden ones. There is a metalink note with all the fixed view definitions by the way (220021.1). But it’s easier just to pull the definition from V$FIXED_VIEW_DEFINITION (fixed_view_def.sql) and you’ll be sure you have the version that you’re actually running. Here is a link to the 11gR1 doc on the V$PARAMETER view.

One of the things that has always bugged me about the V$PARAMETER view is that the ISDEFAULT column is not updated when a parameter is dynamically changed. There’s another column, ISMODIFIED, that is changed when a parameter is changed with an alter system or alter session command. But it’s a pain to have to look at two columns. So I wrote this little script (parms.sql) to make it a little easier to deal with. It also displays hidden parameters if you ask for them. It’s based on the 10.2.0.4 definition of V$PARAMETER but it works in 9i and 11gR1 as well.

Continue reading ‘Reset Oracle init.ora / spfile parameters’ »