Kerry Osborne's Oracle Blog

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