Home » RDBMS Server » Server Utilities » Trace File does not give all the bind variable values when multiple rows are inserted using 1 query (10G)
Trace File does not give all the bind variable values when multiple rows are inserted using 1 query [message #437064] Wed, 30 December 2009 15:54 Go to next message
catchmss
Messages: 5
Registered: December 2009
Junior Member
Hello,

We have an application (I believe it is built on OCI) which, under certain circumstances, inserts multiple rows using single statement. I'm trying to debug a particular issue by enabling Tracing at the oracle level. Problem is the trace file has values of bind variables only for the first record, though more than 1 record is inserted and I can verify that in database.

Has anyone faced this issue?
If you have any insights, please let me know.

Thanks in Advance
Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437068 is a reply to message #437064] Wed, 30 December 2009 20:07 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>If you have any insights, please let me know.
If you enable SQL_TRACE at level 12, the bind variables values will be contained in raw trace file.

GOOGLE for details on how exactly to implement this solution.
BTW, I am unaware of another way to determine actual bind values.

[Updated on: Wed, 30 December 2009 21:01]

Report message to a moderator

Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437111 is a reply to message #437064] Thu, 31 December 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_MONITOR to activate the trace with bind variables.

Regards
Michel
Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437165 is a reply to message #437111] Thu, 31 December 2009 08:15 Go to previous messageGo to next message
catchmss
Messages: 5
Registered: December 2009
Junior Member
Thanks BlackSwan and Micel Cadot for the reply.

BlackSwan, I apologize for not giving enough details.

Let me try to explain in detail what I have been doing:

My oracle version details are :

Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Operating System : Win XP SP2 (32 bit)

I'm running my application in the same system as oracle server.

I did the following steps to enable tracing:


    1.Launched my application which connected to database

    2.Logged into database using sqlplus as sys

    3.Got the Process ID of the my application's session using the following query.
    SELECT 	p.spid,	
    	s.osuser, 
    	s.machine, 
    	s.program, 
    	s.logon_time, 
    	s.status 
    FROM 	v$session s, 
    	v$process p 
    WHERE 	s.paddr = p.addr 
    AND 	s.username = <app User Name>;


    4.Then Enabled tracing using following commands:
    oradebug setospid <p.spid value from above query>
    oradebug event 10046 trace name context forever, level 12


    5.Did some operations in my application. These operations inserted bunch of records to the database. One of the insert statement inserted multiple records to one table. I need to debug these records at oracle level.

    6.Executed the following command in sqlplus to get the trace file name.
    oradebug tracefile name

    7.While looking at the trace file, I found the insert query. But it showed the bind variable values for only one record.

    8.Please look at the following trace file content:

    PARSING IN CURSOR #1 len=98 dep=0 uid=105 oct=2 lid=105 tim=9725620482 hv=2397566571 ad='31cc8f68'
    INSERT INTO MYTABLE (Col1,Col2,Col3,Col4,Col5) VALUES (:1,:2,:3,:4,:5)
    END OF STMT
    PARSE #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9725620478
    BINDS #1:
    kkscoacd
    Bind#0
    oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
    oacflg=01 fl2=1000000 frm=01 csi=178 siz=136 off=0
    kxsbbbfp=07fdcf30 bln=32 avl=14 flg=05
    value="Col1Val"
    Bind#1
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=32
    kxsbbbfp=07fdcf50 bln=22 avl=03 flg=01
    value=23
    Bind#2
    oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
    oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=56
    kxsbbbfp=07fdcf68 bln=32 avl=14 flg=01
    value="Col3Val"
    Bind#3
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=88
    kxsbbbfp=07fdcf88 bln=22 avl=03 flg=01
    value=291
    Bind#4
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=112
    kxsbbbfp=07fdcfa0 bln=22 avl=02 flg=01
    value=1
    WAIT #1: nam='db file sequential read' ela= 3522 file#=8 block#=21856 blocks=1 obj#=230252 tim=9725624584
    WAIT #1: nam='db file sequential read' ela= 9077 file#=8 block#=21240 blocks=1 obj#=230252 tim=9725633739
    WAIT #1: nam='db file scattered read' ela= 7181 file#=8 block#=21849 blocks=7 obj#=230252 tim=9725641051
    WAIT #1: nam='db file sequential read' ela= 2913 file#=8 block#=22445 blocks=1 obj#=230253 tim=9725644034
    WAIT #1: nam='db file scattered read' ela= 3993 file#=8 block#=22461 blocks=4 obj#=230253 tim=9725648134
    WAIT #1: nam='db file sequential read' ela= 9021 file#=8 block#=17237 blocks=1 obj#=230253 tim=9725657237
    WAIT #1: nam='db file sequential read' ela= 10025 file#=8 block#=17279 blocks=1 obj#=230253 tim=9725667366
    EXEC #1:c=0,e=46858,p=16,cr=1,cu=21,mis=0,r=4,dep=0,og=1,tim=9725667439
    WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=230253 tim=9725667503
    WAIT #1: nam='SQL*Net message from client' ela= 489 driver id=1413697536 #bytes=1 p3=0 obj#=230253 tim=9725668029
    =====================


    From the EXEC #1 line, we can see that number of rows processed are 4 (r=4). But the trace file does not have the remaining row details.



I'm already using trace level 12 and I believe DBMS_SUPPORT also eventually doing the same as what I was doing.

Let me know if you need any more information

[Updated on: Thu, 31 December 2009 08:21]

Report message to a moderator

Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437169 is a reply to message #437165] Thu, 31 December 2009 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle only gives you the bind variables at the time of parse, when it reuses the same sql entry in memory it does not parse the statement and so does not record the bind variables.

If you want all values you have to add a trigger on your table or enter to fine-grained auditing world.

Regards
Michel
Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437170 is a reply to message #437169] Thu, 31 December 2009 11:47 Go to previous messageGo to next message
catchmss
Messages: 5
Registered: December 2009
Junior Member
Michel Cadot wrote on Thu, 31 December 2009 10:49
Oracle only gives you the bind variables at the time of parse, when it reuses the same sql entry in memory it does not parse the statement and so does not record the bind variables.

If you want all values you have to add a trigger on your table or enter to fine-grained auditing world.

Regards
Michel


Michel,

Thanks for the helpful information.
icon6.gif  Re: Trace File does not give all the bind variable values when multiple rows are inserted using 1 qu [message #437473 is a reply to message #437165] Mon, 04 January 2010 10:48 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

Perhaps another option would be to set the environment variable AUDIT_TRAIL = db, extended; and use the AUDIT statement.
Razz
Previous Topic: import from split
Next Topic: Import - indexes
Goto Forum:
  


Current Time: Thu Sep 29 17:41:33 CDT 2016

Total time taken to generate the page: 0.09930 seconds