Category Archives: Performance

Explain plan doesn’t peek at variables

I was trying to experiment with the 11g SQL Plan Management feature when stumbing across this. You will most likely go “ohh-of course it doesn’t” but I didn’t know it for sure until now. It’s another reason the expected explain plan differs from the actual explain plan, as discussed on the oracle-l mailing list recently.

My test setup is 11.2.0.1 on RHEL 5.2 32bit, running para-virtualised on my laptop; your system might be different but I assume the general principle is still the same. So here we go:

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> var i number;
SQL> exec :i := 100

PL/SQL procedure successfully completed.

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select id from test2 where id = :i;

Explained.

In the trace file (which again changed quite a lot from 10.2 I use in production), you find the bind variable section:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
 oacflg=00 fl2=1010000 frm=01 csi=46 siz=32 off=0
 kxsbbbfp=00e62024  bln=32  avl=00  flg=05

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=TO_NUMBER(:B1)
kkoqbc: optimizing query block SEL$1 (#0)

You will notice the absence of the value of the bind variable. When I did my testing I randomly executed quieries with the explan plan option and without so this hasn’t become apparent at first. Re-executing the same statement without “explan plan” prefix yields the following result:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=007cca70  bln=22  avl=02  flg=05
 value=10

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)

Here we have the value in the relevant column. By the way, finding your trace file has become trivial in 11.1 and later:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3291.trc

Right, I agree this isn’t shaking the Oracle world but it cost me half an hour when developing my test case so I thought I’d share this with you. Bear in mind that your actual execution plan most likely will be different from the explain plan (with bind variables) so testing ACS and other new features will need to make use of dbms_xplan.display_cursor to fetch the plan from the v$ views.

Selectively purging the shared pool

Another very useful thread has developed on the oracle-l mailing list. On a side note, this is really _the_ list to be subscribed to-there are so many experienced Oracle DBAs out that it’s pure joy.

But I digress, back to the problem I often face when performance troubleshooting systems is to purge a SQL statement from the shared pool. Most often, bind variable peeking in conjunction with an empty partition (or otherwise inappropriate stats) caused the optimiser to choose a really inadequate plan for subsequent executions of the same SQL ID.

Oracle 11.1 extended DBMS_SHARED_POOL to include a “purge” function which can be used to manage the shared pool. It’s actually a lot more powerful than just purging SQL plans but that’s for another blog entry :) Anyway, if you are interested, additional documentation can be found in metalink notes 457309.1 and 751876.1. If anyone can shed more light on the various heaps you can pass as an argument, please comment here!

The new version of the package has been back ported and included in the 10.2.0.4 patch set (where it is event protected) and can also be downloaded for 10.2.0.2 and 10.2.0.3-search for patch 5614566 on metalink.

Prior to this, I usually used the “comment on table <owner>.<table_name> is ‘<some comment>’ to force an invalidation of the statement in v$sql. Next time the statement is executed you’d hope that you have escaped the bind variable peeking disaster.

Kerry Osborne, a great source for inspiration has posted an easy way to using it. The following is shamelessly taken from the mailing list, I won’t take any credit for it (but I’ll use it whenever I can!)

Beginning the quote, slightly amended to better fit into context:

Heap 0 flushes the whole statement, heap 6 flushes the plans (although I’ve never had a reason to flush just the plans. By the way, when heap=6, plans for all children are flushed.

The backport to 10.2.0.4 seems to work fine as well, but it doesn’t appear to work in versions prior to 10.2.0.4 (10.2.0.3 for example).

Here’s the script I use (because I don’t like the funky “address,hash_value” format that the purge procedure requires:

-- flush_sql.sql
DECLARE
 name varchar2(50);
 version varchar2(3);
BEGIN
 select regexp_replace(version,'\..*') into version from v$instance;

 if version = '10' then
 execute immediate
 q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
 end if;

 select address||','||hash_value into name
 from v$sqlarea
 where sql_id like '&sql_id';

 dbms_shared_pool.purge(name,'C',1);

END;
/

If you want to have the flexibility to select the heap – change the call to purge like this:

dbms_shared_pool.purge(name,’C’,power(2,&heap));

Then just put in 0 or 6 when prompted for the heap.

End qoute. Credit where credit is due:

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com <http://kerryosborne.oracle-guy.com&gt;