Home » RDBMS Server » Performance Tuning » Sql Trace in VB
Sql Trace in VB [message #65357] Tue, 17 August 2004 12:43 Go to next message
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 Go to previous message
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;
/
Previous Topic: Tunning the performance
Next Topic: parallel query performance
Goto Forum:
  


Current Time: Fri Mar 29 09:23:56 CDT 2024