Solved

Oracle 10053 Trace

Posted on 2011-03-16
3
835 Views
Last Modified: 2012-05-11
I need to enable the 10053 trace for a sql_id via procedure.

Manually I can generate it as below:
alter session set events='10053 trace name context forever, level 1';
Run the sql I want to trace..
ALTER SESSION SET EVENTS '10053 trace name context off';

With the sql_id, I can get the sql text. But what about bind variables. How can I get the bind variables of the last run of the query. Is it possible?
If i get it, I can generate the sql statement and run it with the bind variables and get the trace.

0
Comment
Question by:sanpradeep
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:sanpradeep
ID: 35153797
I found V$SQL_BIND_CAPTURE.
But it is not refreshing this view fast..

Session 1:
 variable b1 number;
 exec :b1:=7902;
 select * from emp where empno = :b1;
SELECT NAME, VALUE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = 'fr63tdr4rzhu0';
NAME       VALUE_STRING                   LAST_CAPTURED
---------- ------------------------------ --------------------
:B1        7902                           16-MAR-11

Session 2:
variable b1 number
exec :b1:=7844;
select * from emp where empno = :b1;
SELECT NAME, VALUE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = 'fr63tdr4rzhu0'
/
NAME       VALUE_STRING                   LAST_CAPTURED
---------- ------------------------------ --------------------
:B1        7902                           16-MAR-11

However, after some time.. if i again query this view, it showed me 7844 value for the bind variable.
How much time do I need to wait to get this view refreshed??

0
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
ID: 35153845
Try using OTHER_XML instead as per http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

Other option might be to flush shared pool right before they run the problematic query.
0
 

Author Comment

by:sanpradeep
ID: 35153913
Thanks for the reply... It helped me..
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question