Extended SQL trace is one of the oldest and most complete methods for capturing the values of bind variables used in SQL statements. You can enable extended SQL trace in your own session with the following statement:
ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;
You can enable extended SQL trace in another user’s session using one of the following procedure calls:
SYS.dbms_system.set_ev (sid, serial#, 10046, 4, ”)
SYS.dbms_support.start_trace_in_session (sid, serial#, waits=>FALSE, binds=>TRUE)
SYS.dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE)
The dbms_monitor package is new in Oracle 10g. Any of these will cause Oracle to write a trace file on the database server in the directory specified by the user_dump_dest instance parameter. The trace file will contain text like:
PARSING IN CURSOR #1 len=116 dep=0 uid=77 oct=3 lid=77 tim=4528731877418 hv=3407
047714 ad=’b8cd9050′
SELECT e.*
FROM emp e,
dept d
WHERE e.deptno = d.deptno AND
d.deptno = :l_deptno AND
e.sal > :l_sal
END OF STMT
PARSE #1:c=0,e=1644,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=4528731877382
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
size=48 offset=0 bfp=800003fb800697f0 bln=22 avl=02 flg=05 value=10
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
size=0 offset=24 bfp=800003fb80069808 bln=22 avl=02 flg=01 value=300
EXEC #1:c=0,e=810,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4528731879874
FETCH #1:c=10000,e=14607,p=3,cr=6,cu=0,mis=0,r=1,dep=0,og=4,tim=4528731894755
FETCH #1:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=4,tim=4528731898231
In the above example, “BINDS #1″ indicates values bound into the bind variables of the statement in cursor #1. (See the previous “PARSING IN CURSOR #1″ entry for the text of the statement in cursor #1.) Below “BINDS #1″, the “bind 0″ entry corresponds to the first bind variable in the statement i.e. :l_deptno. Similarly, “bind 1″ corresponds to the second bind variable, :l_sal. The last item on the “bind 0″ and “bind 1″ entries shows the value of the bind variable at the time of binding. If a statement is executed multiple times in a traced session, there will be additional “BINDS” entries as new values are bound.
Beginning in Oracle 10g, it is also possible to monitor bind variable values in your own session or other sessions witho
ut performing an extended SQL trace. A new v$ view called v$sql_bind_capture shows bind variable data across all sessions. There are a few important restrictions on v$sql_bind_capture: Only simple-type bind variables that appear in the WHERE a nd HAVING clauses are captured. (Thus it is not possible to obtain the values used in INSERT statements or SET clauses of UPDATE statements, or LOBs or LONGs.) Also, to reduce overhead, bind variable information is captured no more frequently than once every 15 minutes for a given cursor. Finally, this view is only populated when the statistics_level parameter is set to TYPICAL or ALL.
For example, we can retrieve bind variable information for all statements parsed by us with a query such as:
SELECT b.name, b.value_string, sq.sql_text
FROM v$sql_bind_capture b, v$sql sq, v$session s
WHERE sq.sql_id = b.sql_id
AND sq.address = b.address
AND sq.child_address = b.child_address
AND sq.parsing_user_id = s.user#
AND s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;
This might produce results such as:
NAME VALUE_STRING SQL_TEXT
———– —————– ———————————————————-
:L_DEPTNO 10 SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno…
:L_SAL 300 SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno…
Even though the data collected in v$sql_bind_capture is only sampled periodically, it can still help DBAs track down performance issues in code they are not familiar with. For example, since v$sql_bind_capture indicates the data type of thebind variables, it is possible to use the view to discover statements that have performance problems due to implicit type conversions. Recently we were asked by a development group why the following statement would not use an index on the image_no column:
SELECT * FROM images WHERE image_no = :B1;
By comparing the data type of the bind variable against the data type of the image_no column, it was straightforward to show a type conversion was taking place that was defeating the index. When the developer changed the bind variable datatype from numeric to character, the query began using the index.
Extended SQL trace allows you to see all bind variable values for all executions of SQL statements captured in the tracefile. Beginning in Oracle 10g, the v$sql_bind_capture view allows you to see sampled bind variable information for all sessions without having to use tracing. Both tools can be extremely useful to the Oracle DBA.
