v$sql_bind_capture – is it buggy?
zigz...@yahoo.com
V$sql_bind_capture to find values of bind vars used in my query.
When I look at the description of v$sql_bind_capture:
SQL> desc v$sql_bind_capture;
Name Null? Type
----------------------------------------- --------
----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
CHILD_ADDRESS RAW(4)
CHILD_NUMBER NUMBER
NAME VARCHAR2(30)
POSITION NUMBER
DUP_POSITION NUMBER
DATATYPE NUMBER
DATATYPE_STRING VARCHAR2(15)
CHARACTER_SID NUMBER
PRECISION NUMBER
SCALE NUMBER
MAX_LENGTH NUMBER
WAS_CAPTURED VARCHAR2(3)
LAST_CAPTURED DATE
VALUE_STRING VARCHAR2(4000)
VALUE_ANYDATA SYS.ANYDATA
It has a column was_captured. My query has 206 bind vars, I see it
only has value YES for this field for ~40 bind vars. Rest of the
variables. Its value is NO, as a result VALUE_STRING is not shown
(i.e., null).
Why is not capturing all the bind variables. Where can I get values of
all bind variables? I do not want to do sql trace with bind variables,
I was hoping some v$ provides me all the values.
ora...@msn.com
No, it isn't. I would take the time to read up on the view rather
than speculating on whether or not your lack of knowledge makes this
object 'buggy' and waiting for someone to respond to your question:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2114.htm#REFRN30310
From the documentation:
"Bind data
One of the bind values used for the bind variable during a past
execution of its associated SQL statement. Bind values are not always
captured for this view. Bind values are displayed by this view only
when the type of the bind variable is simple (this excludes LONG, LOB,
and ADT datatypes) and when the bind variable is used in the WHERE or
HAVING clauses of the SQL statement."
Bind capture is also disabled when STATISTICS_LEVEL = BASIC.
Funny how you have the time to wait for someone ELSE to read the
documentation and report back to you.
David Fitzjarrell
DA Morgan
Worse than that. The only reason the posting was made here is that the
OP didn't like the answers in the OTN forums where, I should point out,
I pointed to the fact that any query with 206 bind variables has a
much larger issue than whether the values are being capture.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
ora...@msn.com
>
> > From the documentation:
>
> > "Bind data
>
> > One of the bind values used for the bind variable during a past
> > execution of its associated SQL statement. Bind values are not always
> > captured for this view. Bind values are displayed by this view only
> > when the type of the bind variable is simple (this excludes LONG, LOB,
> > and ADT datatypes) and when the bind variable is used in the WHERE or
> > HAVING clauses of the SQL statement."
>
> > Bind capture is also disabled when STATISTICS_LEVEL = BASIC.
>
> > Funny how you have the time to wait for someone ELSE to read the
> > documentation and report back to you.
>
> > David Fitzjarrell
>
> Worse than that. The only reason the posting was made here is that the
> OP didn't like the answers in the OTN forums where, I should point out,
> I pointed to the fact that any query with 206 bind variables has a
> much larger issue than whether the values are being capture.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
I guess he wants the query to ... ummm ... 'scale' ...
Of course with 206 bind variables that would tip the scales pretty
quickly.
David Fitzjarrell
Yong Huang
Hi,
In addition to the limitation listed in the Reference manual, bind
data capture is controlled by other factors. Either statistics_level
needs to be typical (or all) or _cursor_bind_capture_area_size has to
be manually set to non-zero (it becomes zero if you lower
statistics_level). If it's not the case no values are captured at all,
it's also possible _cursor_bind_capture_area_size needs to be
increased. Bind data capture happens during a cursor hard parse, a
soft parse that creates a new child cursor, or if the last capture was
_cursor_bind_capture_interval seconds or longer ago, and bind
variables in the select list are ignored. There may be other factors I
haven't figured out that cause the bind data capture to not happen.
Yong Huang
zigz...@yahoo.com
>
> From the documentation:
>
> "Bind data
>
> One of the bind values used for the bind variable during a past
> execution of its associated SQL statement. Bind values are not always
> captured for this view. Bind values are displayed by this view only
> when the type of the bind variable is simple (this excludes LONG, LOB,
> and ADT datatypes) and when the bind variable is used in the WHERE or
> HAVING clauses of the SQL statement."
>
> Bind capture is also disabled when STATISTICS_LEVEL = BASIC.
>
> Funny how you have the time to wait for someone ELSE to read the
> documentation and report back to you.
>
>
> - Show quoted text -
supect reason is there is a limitaion of how much data can be captured
with bind vars. _cursor_bind_capture_interval (default value of this
hidden parameter is 400, so when one has too many bind vars, they are
not captured). I do not see this documented in manauals.
zigz...@yahoo.com
>
> - Show quoted text -
Young:
Most useful answer. Thanks.
Frank van Bortel
-
> with bind vars. _cursor_bind_capture_interval (default value of this
> hidden parameter is 400, so when one has too many bind vars, they are
> not captured). I do not see this documented in manauals.
hidden - not documented.
1 + 1 = 2, eh?
--
Regards,
Frank van Bortel
