Help - Search - Members - Calendar
Full Version: How to find current value of bind variables in oracle 9i Database.
Oracle DBA Forums > Oracle > Oracle Forum
Yash
Hi
I can see many sql queries doing full table scan in the database, as there is a parameter cursor_sharing='SIMILAR' , Queries are
using bind variables :"SYS_B_2" and :"SYS_B_3 hence it is very difficult to analyse a complex queries with 10 to 15 Bind variable.
Let me know if we can trace the current value of bind variable running in the database?
Can we generate the SQL trace for already running sql queries in the database?
dave
do a 10046 trace - you will get the bind vairables in the trace file
Asma
QUOTE (Yash @ Jul 8 2008, 08:51 AM) *
Hi
I can see many sql queries doing full table scan in the database, as there is a parameter cursor_sharing='SIMILAR' , Queries are
using bind variables :"SYS_B_2" and :"SYS_B_3 hence it is very difficult to analyse a complex queries with 10 to 15 Bind variable.
Let me know if we can trace the current value of bind variable running in the database?
Can we generate the SQL trace for already running sql queries in the database?



further to dave's suggestion;you can do so like

- find sid, serial# of the session running the query
select sid,serial# from v$session where username = 'user running the query';

- exec sys.dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true);
- exec sys.dbms_system.set_int_param_in_session(sid,serial#,'max_dump_file_size',900000000000);
- exec sys.dbms_system.set_ev(sid,serial#,10046,12,'');


to switchoff tracing do like

- exec sys.dbms_system.set_ev(sid,serial#,10046,0,'')

hth...
HAL9000
Here are note on how to read the cursor states from a 10046 trace:

http://www.dba-oracle.com/t_10046_trace_fi...rse_execute.htm

http://www.dba-oracle.com/t_10046_loading_...zing_traces.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.