A developer ask me if i could give him the values of the bind variable.
There are several ways do this. In 11gr2 there is a way to capture the values of bind variable in currently running SQL.
You must have the enterprise edtion of Oracle 11gr2 and have the lisences for diagnostics & tuning packs.
If not i will show you other ways to get the values.
But here is an example of getting the values using oracle's real time monitoring
SQL>var id number
SQL>var naam varchar2(50)
SQL>exec :id :=71757;
SQL>exec :naam :='WRH$_SQLSTAT_PK';
Now run the query
SELECT
2 COUNT(*)
3 FROM
3 gda001.x a
5 , dba_objects b
6 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) c
7 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) d
8 where
9 a.object_id = b.object_id
10 and b.object_id < :id
11 and a.object_name != :naam
12 /
Now run the following statement while the above query is running
SQL> select xmltype(binds_xml) from v$sql_monitor where sid = 18 and status = 'EXECUTING';
The output will look like this
<binds>
<bind name=":SYS_B_1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":SYS_B_3" pos="4" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":ID" pos="5" dty="2" dtystr="NUMBER" maxlen="22" len="4">71757</bind>
<bind name=":NAAM" pos="6" dty="1" dtystr="VARCHAR2(2000)" maxlen="2000" csid="873" len="15">WRH$_SQLSTAT_PK</bind>
</binds>
And there you have it the values of the bind variables ID value 71757 and NAAM value wrh$_sqlstat_pk.
But what if you don't have the diagnostics & tuning packs or you use standard edition.
Well than you can use oradebug.
I use the same statement is above.
The open a sql session. You must have os process id (spid) for the session you use. In my case 12072
SQL> oradebug setospid 12072
Oracle pid: 31, Unix process pid: 12072, image: oracle@ota5070 (TNS V1-V3)
SQL> oradebug dump errorstack 3
Statement processed.
Now go to the place where you have set the udump and open the trace file.
Trace file /oracle/home/diag/rdbms/tst451p/TST451P1/trace/TST451P1_ora_12071.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/home/11.2.0.3.0
System name: Linux
Node name: ota5070
Release: 2.6.18-308.8.1.el5
Version: #1 SMP Fri May 4 16:43:02 EDT 2012
Machine: x86_64
VM name: VMWare Version: 6
Instance name: TST451P1
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 22336, image: oracle@ota5070 (TNS V1-V3)
*** 2012-07-31 10:55:05.199
*** SESSION ID:(277.12763) 2012-07-31 10:55:05.199
*** CLIENT ID:() 2012-07-31 10:55:05.199
*** SERVICE NAME:(SYS$USERS) 2012-07-31 10:55:05.199
*** MODULE NAME:(SQL*Plus) 2012-07-31 10:55:05.199
*** ACTION NAME:() 2012-07-31 10:55:05.199
Received ORADEBUG command (#1) 'dump errorstack 3' from process 'Unix process pid: 12072, image: <none>'
To locate the values of the bind variables search for Session Cursor Dump
----- Session Cursor Dump -----
Current cursor: 4, pgadep=0
Open cursors(pls, sys, hwm, max): 3(0, 2, 64, 1000)
NULL=2 SYNTAX=0 PARSE=0 BOUND=0 FETCH=1 ROW=0
Cached frame pages(total, free):
4k(14, 3), 8k(1, 1), 16k(1, 0), 32k(2, 2)
----- Current Cursor -----
xsc=0x2b30d3938e38 ctx=0x74469b88 pgactx=0x74469b88 ctxcbk=(nil) ctxqbc=(nil) ctxrws=0x7f56c158
----- Explain Plan Dump -----
----- Compact Format (Stream) -----
You can go down in the file from this point on and after the explain plan you find
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : GDA001
plan_hash : 1182576250
plan_hash_2 : 2708775469
outline : SYS_OUTLINE_12073016024169950
Peeked Binds
============
Bind variable information
position=5
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=71757 Bind variable information
position=6
datatype(code)=1
datatype(string)=VARCHAR2(2000)
char set id=873
char format=1
max length=2000
value=WRH$_SQLSTAT_PK Outline Data:
/*+
BEGIN_OUTLINE_DATA
And in red you see the values of the bind variables.
a third way is being around since Oracle 10G and that is view called
$sql_bind_capture
Now to find the bind variables of the same query
SQL>select sql_id, .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='7z5czth4q5g4p'
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT :SYS_B_1 1000000
COUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :SYS_B_3 1000000
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :ID 71757
OUNT(*) FROM gda001.x e
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :NAAM WRH$_SQLSTAT_PK
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
As this is a dynamic view after 30 minutes the data is not available anymore. if you want to see the data from longer ago you can use the view DBA_HIST_SQLBIND.
You need the sql_id and/of snapshot id. This means of course that this only works if you got
the diagnostics & tuning packs.
SQL>SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND where sql_id='7z5czth4q5g4p'
NAME POSITION DATATYPE_STRING VALUE_STRING
------------------------------ ---------- --------------- ------------------------------
:SYS_B_0 1 VARCHAR2(32)
:SYS_B_1 2 NUMBER 1000000
:SYS_B_2 3 VARCHAR2(32)
:SYS_B_3 4 NUMBER 1000000
:ID 5 NUMBER 71757
:NAAM 6 VARCHAR2(32) WRH$_SQLSTAT_PK
6 rows selected.
And there you have them.
Thats it
No comments:
Post a Comment