Oracle10DBMS_MONITOR tracks and analyzes other sessionSQL statements

Oracle10DBMS_MONITOR tracks and analyzes other sessionSQL statements

Oracle 10 DBMS_MONITOR tracks and analyzes other session SQL statements

Oracle 10 DBMS_MONITOR tracks and analyzes other session SQL statements

1. session information

View the current session information statement

Select sid, serial #, username, machine
From v $ session
Where type! = 'Background'
And username = 'sys ';

-- Session 1 ----

Session_id = 146
Serial # = 18

-- Session 2 --

Session_id = 140
Serial # = 31

2. Execution Process

1) -- session 1 ----
-- Start trace --
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => 140, serial_num => 31, waits => TRUE, binds => TRUE );
#############################
2) -- session 2 --
Execute SQL statements
###########################

3) -- session 1 ----

Select SQL _trace, SQL _trace_waits, SQL _trace_binds
From v $ session
Where sid = 140;

-- End trace --
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id = & gt; 140, serial_num = & gt; 31 );

4) generate a trail File
Automatically generate session 2 SPID tracking file oradb1_ora_3732.trc after tracking ends

3. view results

1) View session SID, SERIAL #, process PID, and SPID

SQL> select s. sid, s. serial #, p. pid, p. spid
2 from v $ session s, v $ process p
3 where s. paddr = p. addr
4 and s. username = 'sys ';

Sid serial # PID SPID
------------------------------------------
140 31 16 3732
146 18 17 4072

2) generate session 2 analysis result File

Tkprof C: \ oracle \ product \ 10.2.0 \ admin \ oradb1 \ udump \ oradbw.ora_3732.trc 3732.txt

Recommended reading:

[Oracle] dbms_metadata.get_ddl usage Summary

Use the DBMS_HPROF package to collect PL/SQL Performance Information

[Oracle] statistics and dbms_stats packages

,