Home » RDBMS Server » Server Administration » trace bind variable for insert statement not working (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
trace bind variable for insert statement not working [message #645944] Wed, 16 December 2015 10:55 Go to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member
Hi,

I am trying to trace bind variables value for an insert statement and it's not working

Here is my test code :

1- Trace bind variable value for a select statement (this one work fine)

create table sag.test (a varchar2(2000));

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';

declare
b varchar2(1) := '1';
res varchar2(1);
begin
select max(a) into res from test where test.a = b;
end;

-- to check the bind variable value

SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = 'czh677ju2qpak';

-- the value_string is not null and set to the variable value passed to the statement

But

2- Trace bind variable value for an insert statement (this one is not working for me)

create table sag.test (a varchar2(2000));

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';

declare
a varchar2(1) := '1';
begin
insert into test values (a);
end;

-- to check the bind variable value

SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = '9tckxpvzmsq2d';

the value_string is null !!!


Any idea ?

Thanks for your help.
/forum/fa/12946/0/
-- moderator edit: image inserted into message body by bb

[Updated on: Wed, 16 December 2015 11:07] by Moderator

Report message to a moderator

Re: trace bind variable for insert statement not working [message #645945 is a reply to message #645944] Wed, 16 December 2015 10:59 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
You are enabling trace in a way that dates back to the last century. Don't do any of that!

Use dbms_monitor.session_trace_enable with binds=>true instead.

Re: trace bind variable for insert statement not working [message #645946 is a reply to message #645945] Wed, 16 December 2015 11:00 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Sorry, I forgot to do the Moderator bit:

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Re: trace bind variable for insert statement not working [message #645971 is a reply to message #645946] Thu, 17 December 2015 04:10 Go to previous messageGo to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member
Thanks Watson for the welcome.

I tried that one too and it's not working too

execute DBMS_MONITOR.SESSION_TRACE_ENABLE(7, 3800, binds=>true);

completed

The trace is enabled

select user, sid,serial#,sql_trace
from sys.v_$session
where sid =7;

SQL_TRACE = ENABLED

But the bind variable value still null

SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = '9tckxpvzmsq2d';


Another point :

select * from V$SQL where module = 'SQL Developer'

The [ declare a varchar2(1) := '1'; begin insert into test values (a); end; ] is being caught with SQL_ID = d74ft9thpdk85

This one do exist in v$sqlarea

select * from v$sqlarea where sql_id = 'd74ft9thpdk85';

but don't exist in v$sql_bind_capture


I tried to create a stored procedure

create or replace procedure test_bind_var_val_insert_stm(param in test.a%TYPE)
is
begin
insert into test(a) values (param);
commit;
END;

Then I call it in my test after having activated the trace

declare
parm varchar2(1) := '1';
res varchar2(1);
begin
test_bind_var_val_insert_stm(parm);
end;

and I have the same result

no id_sql in v$sql_bind_capture for the stored procedure

and the bind variable is null for the statement INSERT INTO TEST(A) VALUES (:B1 )

I am confused !!!

I am not seeing the problem


Thanks for your help









[Updated on: Thu, 17 December 2015 04:19]

Report message to a moderator

Re: trace bind variable for insert statement not working [message #645972 is a reply to message #645971] Thu, 17 December 2015 04:16 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
You need to look at the trace file, with tkprof.

And next time you post, please use [code] tags as described in the links I gave you.
Re: trace bind variable for insert statement not working [message #645973 is a reply to message #645971] Thu, 17 December 2015 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried looking in the actual trace file for the bind variables?
Re: trace bind variable for insert statement not working [message #645976 is a reply to message #645973] Thu, 17 December 2015 04:41 Go to previous messageGo to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member

Yes I generated trace file with bind variable, but tkprof rapport don't display the bind variables values and I can't get the bind value from the trace file (.trc)

I have a insert with 65 bind variable, How to upload the trace file so you can see what's going on ? thanks

INSERT INTO T_MY_TABLE (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32,COL33, COL33, COL34, COL35, COL36, COL37, COL38, COL39,COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47,COL48,COL49,COL50,COL51, COL52, COL53, COL54,COL55,COL56, COL57,COL58,COL59, COL60,COL61,COL62,COL63,COL64, COL65) VALUES (:B65 , :B64 , :B63 , :B62 , :B61 , :B60 , :B59 , :B58 , :B57 , :B56 , :B55 , :B54 , EMPTY_BLOB(), :B53 , :B52 , :B51 , :B50 , :B49 , :B48 , :B47 , :B46 , :B45 , :B44 , :B43 , :B42 , DES.OBF(:B41 ), :B40 , DES.OBF(:B39 ), :B38 , :B37 , :B36 , :B35 ,:B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , DES.OBF(:B27 ), :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 ,:B18 ,:B17 ,:B16 ,:B15 ,:B14 , :B13 , DES.OBF(:B12 ),:B11 ,:B10 , :B9 ,:B8 ,:B7 , :B6 ,:B5 ,:B4 ,:B3 , :B2 , :B1 )

Re: trace bind variable for insert statement not working [message #645978 is a reply to message #645976] Thu, 17 December 2015 05:02 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
My memory is failing. Not tkprof, the Trace Analyzer Tool:
TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)

Or of course you can see the binds in the raw trace file. There are probably third party tools out there as well.
Re: trace bind variable for insert statement not working [message #645981 is a reply to message #645978] Thu, 17 December 2015 05:27 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
The binds will be listed after the insert statement in the trace file.
Re: trace bind variable for insert statement not working [message #645982 is a reply to message #645978] Thu, 17 December 2015 05:31 Go to previous messageGo to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member

I did found the bind variables values in the trace file (.trc) though the display is not great.

Can I install Trace Analyzer Tool if I have just the oracle client (not the server) ?

Any idea why the select request to get these bind variables values return null given that this values do appear in the trace file, I am not looking in the right views ?!!!

Thanks

Re: trace bind variable for insert statement not working [message #645984 is a reply to message #645982] Thu, 17 December 2015 05:40 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the docs say that view won't capture values if the STATISTICS_LEVEL initialization parameter is set to BASIC. So what's yours set to?
Re: trace bind variable for insert statement not working [message #645997 is a reply to message #645984] Thu, 17 December 2015 07:23 Go to previous messageGo to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member
TYPICAL


SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM   v$statistics_level
ORDER BY statistics_name;


Active Session History	        ENABLED	 ENABLED	TYPICAL	NO
Adaptive Thresholds Enabled  	ENABLED	 ENABLED	TYPICAL	NO
Automated Maintenance Tasks	ENABLED	 ENABLED	TYPICAL	NO
Automatic DBOP Monitoring	ENABLED	 ENABLED	TYPICAL	YES
Bind Data Capture	        ENABLED	 ENABLED	TYPICAL	NO
Buffer Cache Advice	        ENABLED	 ENABLED	TYPICAL	NO
Global Cache CPU Statistics	DISABLED DISABLED	ALL	NO
Global Cache Statistics	        ENABLED	 ENABLED	TYPICAL	NO
Longops Statistics	        ENABLED	 ENABLED	TYPICAL	NO
Modification Monitoring	        ENABLED	 ENABLED	TYPICAL	NO
MTTR Advice	                ENABLED	 ENABLED	TYPICAL	NO
OLAP row load time precision	DISABLED DISABLED	TYPICAL	YES
PGA Advice	                ENABLED	 ENABLED	TYPICAL	NO
Plan Execution Sampling	        ENABLED	 ENABLED	TYPICAL	YES
Plan Execution Statistics	DISABLED DISABLED	ALL	YES
Segment Level Statistics	ENABLED	 ENABLED	TYPICAL	NO
Shared Pool Advice	        ENABLED	 ENABLED	TYPICAL	NO
SQL Monitoring	                ENABLED	 ENABLED	TYPICAL	 YES
Streams Pool Advice	        ENABLED	 ENABLED	TYPICAL	NO
Threshold-based Alerts	        ENABLED	 ENABLED	TYPICAL	NO
Time Model Events 	        ENABLED	 ENABLED	TYPICAL	YES
Timed OS Statistics	        DISABLED DISABLED	ALL	YES
Timed Statistics	        ENABLED	 ENABLED	TYPICAL	YES
Ultrafast Latch Statistics	ENABLED	 ENABLED	TYPICAL	NO
Undo Advisor,Alerts and Ramp up ENABLED	 ENABLED	TYPICAL	NO
V$IOSTAT_* statistics	        ENABLED	 ENABLED	TYPICAL	NO


[Updated on: Thu, 17 December 2015 07:25]

Report message to a moderator

Re: trace bind variable for insert statement not working [message #646001 is a reply to message #645997] Thu, 17 December 2015 09:41 Go to previous messageGo to next message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member
As you can see the Bind Data Capture is set to TYPICAL

Bind Data Capture ENABLED ENABLED TYPICAL NO

Do you see any problem in my config ?

Re: trace bind variable for insert statement not working [message #646002 is a reply to message #645944] Thu, 17 December 2015 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+bind+variable+values+trace
Re: trace bind variable for insert statement not working [message #646008 is a reply to message #646002] Thu, 17 December 2015 11:12 Go to previous message
acherifi
Messages: 7
Registered: December 2015
Location: PARIS
Junior Member
Hi BlackSwan

I do did a research to get this "result", I have already got the bind variables values in the trace file but when I request the oracle views (expecting to get the same result) I am getting the bind variables values set to null and I don't get why !!

thanks
Previous Topic: UTF8 or conversion to Best Practice?
Next Topic: How many Oracle instance on Dual Core System
Goto Forum:
  


Current Time: Tue Oct 16 02:32:36 CDT 2018