| Sql Trace in VB [message #65357] |
Tue, 17 August 2004 12:43  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
Hi all, I am trying to tune some sql's. But the problem is they are run from VB application. Can anybody pls tell me how to turn on tracing from Vb application or any other work around. Pls explain in detail as i have no idea abt VB application
Thanx in advance
Sam.
|
|
|
|
| Re: Sql Trace in VB [message #65358 is a reply to message #65357] |
Tue, 17 August 2004 13:15  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
If your app has a persistent connection to the database, then you can easliy start the trace on the identified session using something like TOAD or OEM.
To do it manually, find the session you want to trace, and then issue the pl/sql to start it:
-- Get SID, Serial#
SELECT sid, serial#, username, status, osuser, machine, terminal, module
FROM v$session
WHERE serial# != 1
ORDER BY 4;
-- run this from your session (with the values from above)to trace another session
begin
sys.dbms_system.set_sql_trace_in_session(my_sid, my_serial#, TRUE );
end;
/
Alternately, create a logon trigger but I'd advise getting the trigger to read from a table to decide whether to trace or not and who to trace.
-- or login trigger...
create or replace trigger login_trigger
after logon on schema
begin
execute immediate 'ALTER SESSION SET TIMED_STATISTICS=TRUE';
execute immediate 'ALTER SESSION SET SQL_TRACE=TRUE;
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
