Oraclue

Oracle internals, debugging and undocumented features

Cursor high_version count Part 2

high_version counts caused by unsafe binds?   Oracle event 10046 can be used to determine if binds are unsafe. 

From the trace file  part that represent binds:

BINDS #3:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aaaac0d8cf0  bln=22  avl=02  flg=09
  value=100

First field of interest oacdty represent Oracle internal data type.

Query to get list of internal oracle data types:

SELECT t.typecode,o.name
FROM sys.type$ t, sys.obj$ o
WHERE BITAND (t.properties, 16) = 16
AND t.toid = o.oid$
ORDER BY t.typecode

We are looking for  flag to show if variable is unsafe.

It is flag fl2.I believe oacfl2 in previous releases.

From oracle ( removed from  the original metalink note but I found on Google ) 

#define UACFBLTR 0×00000100 /* Bind was generated by LiTeRal replacement */
#define UACFUNSL 0×00000200 /* UNSafe Literal */
#define UACFNDTL 0×00000400 /* Non-DaTa LiteRal */

A query that should be sharing bind variables is showing them as unsafe. 

 

In the case that binds are unsafe flag fl2=300.  

BINDS #3:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0300frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aaaac0d8cf0  bln=22  avl=02  flg=09
  value=100

One of the reasons for a literals to be marked unsafe is CBO using it for a decision where column involved has as histogram statistics.

Also we can use event 10270 ( debug shared cursor ) to get more information.

Advertisements

2 responses to “Cursor high_version count Part 2

  1. coskan March 20, 2009 at 12:11 pm

    How did we understand 300 is unsafe when the old metalink note says it should be 200 ?

    you quoted

    #define UACFUNSL 0×00000200 /* UNSafe Literal */

    you said
    In the case that binds are unsafe flag fl2=300.

    I am lost 🙂

  2. oraclue March 20, 2009 at 2:35 pm

    In 9i oacfl2=500

    As of 10g

    oacflg=10 fl2=0300 => indicates that the literal passed is unsafe.

    Check note: 731468.1

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: