среда, 19 сентября 2012 г.

How to viewing Oracle bind variable values?

  View V$SQL_BIND_CAPTURE has been introduced to report information on bind variables used by SQL cursors.

  Following query uses the sql_id address that should be specified for each unique SQL statement:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col KILL_SERIAL_SID format a52
col sql_text format a50 wrap
col name format a15 wrap
col datatype_string format a30
col value_string format a30 wrap

SELECT
   ss.serial#,
   ss.sid,
   --ALTER SYSTEM KILL SESSION '573,31517' IMMEDIATE
   'ALTER SYSTEM KILL SESSION ''' || ss.sid || ',' || ss.serial# || ''' IMMEDIATE;' kill_serial_sid,   
   b.name,
   b.position,
   b.datatype_string,
   b.value_string,
   a.sql_text
FROM
  v$sql_bind_capture b,
  v$sqlarea          a,
  v$sql s,
  v$session ss
WHERE 1 = 1
AND s.sql_id = a.sql_id   
AND b.sql_id = a.sql_id
-- Join v$session to v$sqlarea 
--AND ss.sql_address = a.address 
--AND ss.sql_hash_value = a.hash_value
--
-- Join v$session to v$sql 
--AND ss.SQL_ADDRESS = s.ADDRESS
-- joining on SQL_ID is much more efficient
AND ss.sql_id = s.sql_id
-- To capture only active session with specified SQL_ID
--AND ss.STATUS = 'ACTIVE'
AND b.sql_id = '&sql_id' --'4453zzgmqssxr'
;

You can get the SQL ID from the v$sql view:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col sql_text format a130 wrap
col bind_name format a35 wrap
col bind_string format a50 wrap

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='&sql_id' --'f8pavn1bvsj7t'
;

How to viewing in SQL Monitor of Enterprise Manager 11g
   Offline Analysis of Long Running SQL using SQL Monitoring Active Reports

Using 10046 trace for tracing bind variable values
  You can run a 10046 level 4 trace to debug code with bind variables and see the bind variable values as it steps through the code:

EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,4,'''')

Bind Capture History

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col name format a50 wrap
col value_string format a50 wrap

VARIABLE sql_id VARCHAR2(50)
EXEC :sql_id := '4453zzgmqssxr'

select snap_id, name, position, value_string, last_captured
  from dba_hist_sqlbind
 where sql_id = :sql_id
 order by snap_id desc;