bind_data in v$sql
- From: "John Darrah" <darrah.john@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 9 Feb 2007 13:05:11 -0700
A while back I saw a post asking about the bind_data column in v$sql. The
poster wanted to know how to interpret the RAW data in the column. At first
I didn't see the point since there is also a V$sql_bind_capture view that
has all the columns in plain text but I was curious about the structure of
the column and started digging into it. I quickly stumbled on a procedure
that translates this raw string called dbms_sqltune.extract_binds which made
manually translating this data even less necessary but at that point I was
having fun pulling the data out.
Here is what I have so far.
select /* unit_test_1_parse */ * from alpha where val = m_bind;
m_bind is defined as varchar2(1) := 'A'
Bind data string:
BEDA0A20020045C909F0000101F0012003690141
First part
BEDA0A200200 this is currently unknown but we do see patterns
*BEDA0<--*A200200 was the same across all statements selected. It is also
the same from AIX to linux and 10.2.0.2 to 10.2.0.3
BEDA0-->*A2002<--*00 appears to be related to the version of Oracle it was 2
in version 10.2.0.2 and 3 in version 10.2.0.3, in other words A = 10 2 = 2
00 = 0 2 = 2
BEDA0A2002-->*00 *Separator??
BEDA0A200200-->*45C909F0<--*000101F0012003690141 this is the capture date:
45C909F0 = 1170803184 (in decimal) converts to "Tuesday, February 06, 2007
4:06:24 PM"
BEDA0A20020045C909F0-->*0001<--*01F0012003690141 this number is the total
number of vars in the field (it could also be then number of total bind
variables in the query regardless of whether or not they could fit in the
RAW) *Note, this gives a hard limit of 64K is this the hard limit on binds
altogether?
BEDA0A20020045C909F0000101F0-->*01<--*2003690141 data type
BEDA0A20020045C909F0000101F001-->*20<--*03690141 this is the max length of
the bind var 20 = 32 in decimal
BEDA0A20020045C909F0000101F00120-->*0369<--*0141* *this is the character set
id hex for 873
BEDA0A20020045C909F0000101F001200369-->*0<--*141 unknown maybe a separator?
BEDA0A20020045C909F0000101F0012003690-->*141 *the actual value 141 = "A"
The part I haven't been able to dissect is 01F0
I think the "0"s might be separators and the 1F is a bitmap of some kind (it
changes depending on the data type) but that is only a guess.
Here is the information I know is in this RAW string because it it produced
by the dbms_sqltune.extract_binds procedure:
For each bind the following information is present:
NAME -- This appears not to be captured
POSITION -- I don't think this is explicitly stored but determined from the
total number of binds present in the string.
DUP_POSITION -- don't know how this is stored
DATATYPE *
Character Set ID *
PRECISION
SCALE
MAX LENGTH *
LAST CAPTURED *
VALUE *
I've determined how the columns marked with a "*" are stored. Does anyone
know how to interpret the rest of this string? Also, does anyone know if
the binds that were peeked during a hard parse are stored anywhere? It
appears that the bind_data string gets refreshed periodically by oracle,
overwriting the originals.
Thanks,
John
Other related posts:
