Grepping the actual bind values of sql through v$sql_bind_capture


Programmatically or intercepted by forced cursor sharing, sql where clause actual values shall always be bound to variable placeholders and never / no longer be literally coded. You know Tom Kyte talking that story on each and every occasion, pointing out that so many sql areas got stuck in size and performance because of literal values and the resulting multitude of variants of sql statements. On the other hand, people complain about the sporadic issue that the optimizer takes wrong decisions due to one-time probing of actual values in a given session, a nightmare in 10g, much improved by adaptive cursor sharing throughout 12c meanwhile. Well, I do not want to dive into this again, what follows is just a commented recipe sql statement to inspect what actual bind values have had been in action for a (potentially) sub-optimal sql execution. Please note, that the STATISTICS_LEVEL initialization parameter takes to be greater than BASIC to have the statement deliver any data.

The statement is neither complex nor long-running, just using two perfomance views v$sql_bind_capture and v$sqlarea, resp. While v$sql_bind_capture provides for the bind names, values, capture state and time, v$sqlarea offers different ways to approach the subject, by schema or sql-id or module etc, and not at least, the raw text of the statement in question.

select * from (
  select a.PARSING_SCHEMA_NAME as schema, a.module, a.action,
    b.sql_id, b.hash_value as sql_hash, b.child_number as sql_chld,
    b.name as par_name, b.position as par_pos, b.dup_position as par_pos_dup,
    b.datatype_string as par_type, b.was_captured as cap_yn,
    b.last_captured as cap_ts, b.value_string cap_val,
    trim(regexp_replace(a.sql_text, '[[:space:]]{2,}', ' ')) as sql_stmt
  from v$sql_bind_capture b, v$sqlarea a
    where b.hash_value = a.hash_value and b.address = a.address
) where cap_yn != 'NO' -- and cap_ts is null
    and schema = 'AUTUMN'
--    and sql_id = '7sw5hybg6nb5d'
--    and sql_hash = '3731500205'
order by 1,4,5
;

Comments:

  • v$sql_bind_capture has a column was_captured in ('YES', 'NO') that invalidates any values in last_captured and value_string for a value of 'NO'. Since actual bind values in value_string is the matter of the inspection, one may limit the result set of the statement to cap_yn != 'NO' as above.
  • Continuing on this, at times, last_captured is null although was_captured = 'YES'. Examination shows that this applies to bind placeholders in the select ... or in the order by ... sections of the sql raw text.

    On order by ..., the actual values may even relate to the column ordinal names used.
  • Since v$sqlarea holds the raw sql text in sql_text, on may also spy literal bindings as in this reco-manager select, where only one value binding is explicitely employed (:b1) and two are generated (:b2, :b3).
    SELECT NVL(MAX(NEXT_CHANGE#),0)
    FROM V$ARCHIVED_LOG L, V$ARCHIVE_DEST D, V$DATABASE_INCARNATION I
      WHERE D.TARGET IN ('REMOTE', 'STANDBY') AND (D.VALID_NOW = 'YES'
          OR D.VALID_NOW = 'UNKNOWN' OR D.VALID_NOW = 'INACTIVE')
        AND (:B4 = :B2 OR D.STATUS != 'DEFERRED') AND D.STATUS != 'INACTIVE'
        AND D.STATUS != 'ALTERNATE' AND (:B3 = :B2 OR D.BINDING = 'MANDATORY')
        AND D.DEPENDENCY = 'NONE' AND D.DEST_ID = L.DEST_ID
        AND L.STANDBY_DEST = 'YES' AND L.RESETLOGS_TIME = I.RESETLOGS_TIME
        AND L.RESETLOGS_CHANGE# = I.RESETLOGS_CHANGE# AND I.STATUS != 'ORPHAN'
        AND L.APPLIED = 'YES' AND L.DEST_ID = :B1
    ;
    

  • The recipe statement allows restrictions by schema, some app-manager called in, or sql_id, you spotted suspicious data in etp-manager. However, if you are on the hunt for misbehaving statements, v$sqlarea offers a wealth of application- or performance-related columns, just as parse_calls or buffer_gets, to get a top-n-result according to your point of view.
  • trim(regexp_replace(a.sql_text, '[[:space:]]{2,}', ' ')) just eats up any superfluous whitespace for easy readability.

That said, v$sql_bind_capture might, from some perspective, seem to provide a bind variable value logger. In fact, it does not. Documentation asserts that captured bind values only involve:

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…

So what’s the bang now, whatsoever? It’s in performance tuning again, not in logging (I know, lately this guy came up asking you to trace the bindings of this old clumsy legacy middleware application to clarify what it is doing actually ;-). Be affirmed that, however, v$sql_bind_capture will register and deliver the important bind variable values for optimizer execution plan construction. In relation to the adaptive pathway, that the optimizer goes from bind-sensitive to bind-aware cursors, it will register the very bind values that produce new child cursors and execution plans. And this is exactly what we want to trace along the adaptive cusor sharing progress.

For example, retrieving a count of objects according to a name pattern of, say, 'LOG%', 'UET$', 'DUAL', 'RES$', 'I%'

select count(*) as cnt from all_objects where object_name like :name
;

will sooner or later produce bind-sensitive and bind-aware child cursors, three here,

select sql_id, child_number, is_bind_sensitive, is_bind_aware, sql_text
from v$sql a where a.sql_text like '%cnt%all_objects%where%object_name%'
;
a1yvtbruzjsqv	0	Y	N	select count(*) as cnt from all_objects where object_name like :name
a1yvtbruzjsqv	1	Y	Y	select count(*) as cnt from all_objects where object_name like :name
a1yvtbruzjsqv	2	Y	Y	select count(*) as cnt from all_objects where object_name like :name

with their corresponding entries in v$sql_bind_capture,

select * from (
  select b.sql_id, b.hash_value as sql_hash, b.child_number as sql_chld,
    b.name as par_name, b.position as par_pos,
    b.dup_position as par_pos_dup, b.datatype_string as par_type,
    b.was_captured as cap_yn, b.last_captured as cap_ts, b.value_string cap_val,
    trim(regexp_replace(a.sql_text, '[[:space:]]{2,}', ' ')) as sql_stmt
  from v$sql_bind_capture b, v$sqlarea a
    where b.hash_value = a.hash_value and b.address = a.address
) where cap_yn != 'NO' -- and cap_ts is null
    and sql_stmt like '%cnt%all$_objects%where%object$_name%' escape '$'
;
a1yvtbruzjsqv	4126728923	2	:NAME	1		VARCHAR2(32)	YES	01.11.2017 12:10:09	I%
a1yvtbruzjsqv	4126728923	0	:NAME	1		VARCHAR2(32)	YES	01.11.2017 12:02:23	LOG%
a1yvtbruzjsqv	4126728923	1	:NAME	1		VARCHAR2(32)	YES	01.11.2017 12:09:48	RES$

child cursor 0 for a selectivity comparable to 'LOG%', child cursor 1 for 'RES$' and so forth. Child cursors 1 and 2 are already fixed bind-aware, child cursor 0 only bind-sensitive and monitored for yet unseen bind values to set up new bind-aware siblings.

Enjoy Peter

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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