Martins Blog

Trying to explain complex things in simple terms

Explain plan doesn’t peek at variables

Posted by Martin Bach on December 7, 2009

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.

One Response to “Explain plan doesn’t peek at variables”

  1. Leonid Nossov said

    The explanation is very easy. Explain Plan causes only parsing. It is the reason, why you don’t observe bind peeking.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: