Re: Trace SQL based on PID

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 13 Jan 2006 06:03:40 -0800
Message-ID: <1137161020.287227.212030@g14g2000cwa.googlegroups.com>

Tom wrote:
> 9.2.0.4
> Linux EL3
>
> I have some long running sql (i presume) and one of the cpu's is nearly
> 100% so knowing the pid of the process that is eating this CPU is it
> possible to trace the sql that is being run?
>
> thanks

The OS PID is no good, but you can used it to help find the Oracle session you are interested in. To start/stop tracing for a session login as a dba user, and get the sid and serial# for the session you want to trace and run:-

begin

sys.dbms_system.set_bool_param_in_session(sid => 123,							    serial#
=> 40044,						                    parnam => 'timed_statistics',

bval => true);

sys.dbms_system.set_int_param_in_session(sid => 123,							 serial# =>
40044,					                                 parnam =>
'max_dump_file_size',
				 intval => 2147483647);

• first param sid, second serial# sys.dbms_system.set_ev(123, 40044, 10046, 12, ''); end; /

To stop tracing use:-

begin
sys.dbms_system.set_ev(123, 40044, 10046, 0, ''); end;
/ Received on Fri Jan 13 2006 - 08:03:40 CST