It should be boring to trace session with the session_id and serial#, as we will have to query the v$session firstly using other information we knew, such as username, osuser, program, machine, and sometimes, we will query v$sqltext for db objects the session acessed, or query v$process with the session’s corresponding process’s pid. This staff are so cumbersome. And, it should be an good idea to record down the sql trace history to further reference.
Life goes easy with SQLGotcha, an open source project located in http://sourceforge.net/projects/sqlgotcha.
All the cumbersome work have been done by SQLGotcha.
Examples
For example, if you want to do a standard trace for users that login with the username APPUSER, you start the trace as follows:
1: exec sql_gotcha_pack.start_trace_for_session ('USERNAME','APPUSER');
If you want to wait 60 seconds for this session to come up, do:
exec sql_gotcha_pack.start_trace_for_session ('USERNAME','APPUSER', 'STANDARD', 0, NULL, 60);
On Real Application Clusters you’ll find that there is another difficulty in searching sessions. If users connect via a load balanced connection, the session you look for can be on any of the RAC instances. When searching for a session, SQLGotcha can look on other instances than the one you’re connected to with, but it can only mention that a session is elsewhere:
exec sql_gotcha_pack.start_trace_for_session ('USERNAME','APPUSER', 'STANDARD', 0, 'OTHER_INST', 60);
Suppose you’ve run Unix’ top and found out that process 13245 is taking 30% CPU. You want to see every detail, so you choose event 10046 tracing, level 8. You can do this as follows:
exec sql_gotcha_pack.start_trace_for_session ('PID','13245', 10046, 8);
Next you’re visited by an application administrator who asks you to find out why a certain part of the application is slow. All he knows is that the package app_pack is involved. You trace this as folows:
exec sql_gotcha_pack.start_trace_for_session ('OBJECT','APP_PACK', 10046, 8, NULL, 600);
Trace user HSIMPSON who connects from the machine evergreen. max_dump_file_size=2000
exec sql_gotcha_pack.search_and_trace_session('USERNAME=HSIMPSON, MACHINE=evergreen', 10046, 12, 2000);
exec sql_gotcha_pack.end_trace_session('USERNAME=HSIMPSON, MACHINE=evergreen');
Trace user TOAD, osuser hsimpson, who logged on with TOAD.exe.
exec sql_gotcha_pack.search_and_trace_session('USERNAME=TOAD, OSUSER=hsimpson, PROGRAM=TOAD.exe', 10046, 12);
exec sql_gotcha_pack.end_trace_session('USERNAME=TOAD, OSUSER=hsimpson, PROGRAM=TOAD.exe');
Wait 60 seconds for user GBURNS to log on.
exec sql_gotcha_pack.search_and_trace_session('USERNAME=GBURNS', 0, NULL, NULL, 60);
exec sql_gotcha_pack.end_trace_session('USERNAME=GBURNS');
And, the table sqlgotcha_trace_log can be used to query the history of sql traces.
sqlgotcha@ORCL> select sid,serial#,search_type,search_for,trace_type,trace_filename from sqlgotcha_trace_log;
SID SERIAL# SEARCH_TY SEARCH_FOR TRACE_TYPE TRACE_FILENAME
---------- ---------- --------- ------------------------------ ---------- --------------------
124 2731 PROGRAM SQLPLUS STANDARD orcl_ora_7414.trc
125 2861 PROGRAM SQLPLUS STANDARD orcl_ora_7428.trc
105 2725 USERNAME PLFLOW STANDARD orcl_ora_2764.trc
124 14128 USERNAME PLFLOW STANDARD orcl_ora_3509.trc
