See bind variable value in Oracle using trace 10046
Turn on trace:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
Turn off trace: (once trace file were generated)
ALTER SYSTEM SET EVENTS '10046 trace name context off';
Get the sql_id
SELECT sql_text, sql_id
FROM V$SQLAREA
WHERE sql_text LIKE '%INSERT INTO GA_W_COUNTERS_HISTORY%'
AND module = 'JDBC Thin Client';
sql_id = cyzmshqj3a6h6
show parameter _diag_adr_trace_dest;
NAME VALUE
---------------------- -----------------------------------------
_diag_adr_trace_dest /software/oracle/diag/rdbms/igt/igt/trace
cd to trace directory
cd /software/oracle/diag/rdbms/igt/igt/trace
To search for a value toi the 15 bind variable:
find . -type f -name "*.trc" | xargs grep -l cyzmshqj3a6h6 | xargs grep Bind#15 -A 4 | grep value | sort -u | grep -v JAN
grep Bind#15 -A 4 : will fetch 4 rows after Bind#15, for example:
grep -v JAN : will filter out irrelevant parameters.
Bind#15
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=496
kxsbbbfp=7f4179755d48 bln=22 avl=02 flg=01
value=1
The output would be:
./igt_ora_10061.trc- value=1
./igt_ora_11508.trc- value=1
./igt_ora_1222.trc- value=1
./igt_ora_18480.trc- value=1
./igt_ora_19637.trc- value=1
./igt_ora_19659.trc- value=1
./igt_ora_19733.trc- value=1
./igt_ora_22060.trc- value=1
./igt_ora_2624.trc- value=1
./igt_ora_2777.trc- value=1
./igt_ora_4301.trc- value=1
./igt_ora_5119.trc- value=1
Sample output:
=====================
PARSING IN CURSOR #140173184644272 len=294 dep=0 uid=82 oct=2 lid=82 tim=3901074549204 hv=2721389062 ad='14ed60668' sqlid='cyzmshqj3a6h6'
INSERT INTO GA_W_COUNTERS_HISTORY (STATIC_ID , DYN1 , DYN2 , DYN3 , DYN4 , DYN5, DYN_S1, DYN_S2, DYN_S3 ,DYN_S4, DYN_S5, COUNTER_SUM , COUNTER_DELTA, TS_LAST_MODIFIED, NODE_ID, DAY, AFFILIATE_ID, SITE_ID) values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 )
END OF STMT
PARSE #140173184644272:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=3901074549203
BINDS #140173184644272:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=472 off=0
kxsbbbfp=7f7c97477c50 bln=22 avl=09 flg=05
value=1011500104010002
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24
kxsbbbfp=7f7c97477c68 bln=22 avl=01 flg=01
value=0
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=48
kxsbbbfp=7f7c97477c80 bln=22 avl=01 flg=01
value=0
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=72
kxsbbbfp=7f7c97477c98 bln=22 avl=01 flg=01
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=96
kxsbbbfp=7f7c97477cb0 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=120
kxsbbbfp=7f7c97477cc8 bln=22 avl=01 flg=01
value=0
Bind#6 #NULL - no value
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=144
kxsbbbfp=7f7c97477ce0 bln=32 avl=00 flg=01
Bind#7 #NULL - no value
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=176
kxsbbbfp=7f7c97477d00 bln=32 avl=00 flg=01
Bind#8 #NULL - no value
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=208
kxsbbbfp=7f7c97477d20 bln=32 avl=00 flg=01
Bind#9 #NULL - no value
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=240
kxsbbbfp=7f7c97477d40 bln=32 avl=00 flg=01
Bind#10 #NULL - no value
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=272
kxsbbbfp=7f7c97477d60 bln=32 avl=00 flg=01
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=304
kxsbbbfp=7f7c97477d80 bln=22 avl=03 flg=01
value=168
Bind#12
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=328
kxsbbbfp=7f7c97477d98 bln=22 avl=02 flg=01
value=2
Bind#13
oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
oacflg=03 fl2=9000000 frm=00 csi=00 siz=0 off=352
kxsbbbfp=7f7c97477db0 bln=11 avl=07 flg=01
value=01-FEB-24 11.45.00 AM
Bind#14
oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=368
kxsbbbfp=7f7c97477dc0 bln=32 avl=03 flg=01
value="108"
Bind#15
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=400
kxsbbbfp=7f7c97477de0 bln=22 avl=02 flg=01
value=1
Bind#16
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=424
kxsbbbfp=7f7c97477df8 bln=22 avl=02 flg=01
value=69
Bind#17
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=448
kxsbbbfp=7f7c97477e10 bln=22 avl=02 flg=01
value=1
=====================
PARSING IN CURSOR #140173090707784 len=173 dep=1 uid=0 oct=3 lid=0 tim=3901074550196 hv=3830682485 ad='11f235bb8' sqlid='3c32a4bk574vp'
select d.sumobj# from sys.sumdetail$ d, sys.sum$ s where d.sumobj# = s.obj# and bitand(d.flags, 2) = 2 and bitand(s.xpflags, 34359738368) = 34359738368 and d.detailobj# = :1
END OF STMT
PARSE #140173090707784:c=126,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1505437449,tim=3901074550195
BINDS #140173090707784:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f7c97473558 bln=22 avl=04 flg=05
value=25760
etc...
No comments:
Post a Comment