Tracing with dbms_monitor

I know that dbms_monitor is available since 10g but most of the time we forgot about it. or we are using the older method.

but dbms_monitor is really easy to compared to the old method of tracing.

i’ts sunday today and i’m working (just setting in the office waiting for an issue 😦 )

i’ll just try to trace my session using dbms_monitor.

+------------------------------------------------------------------------+
| Report   : Active User Sessions (All)                                  |
| Instance :  XXXXDIT3                                                    |
+------------------------------------------------------------------------+

Instance       SID Serial ID Status    Oracle User        O/S User           O/S PID  Terminal   Machine                        Session Program
---------- ------- --------- --------- ------------------ ------------------ -------- ---------- ------------------------------ ----------------------------------------
 XXXXDIT3      1251     13207 ACTIVE    GGS_ADMIN          xxxx504            23658    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           sqlplus.exe

SQL> 

i’ll be tracing the above session.


SQL> exec dbms_monitor.session_trace_enable(session_id=>1251,serial_num=>13207,binds=>true,waits=>true);

PL/SQL procedure successfully completed.

-- on that session i just executed select name from v$database;

SQL> exec dbms_monitor.session_trace_disable(session_id=>1251,serial_num=>13207);

PL/SQL procedure successfully completed.

SQL> 

from your trace directory check the latest trace file.. and execute tkprof


$ tkprof XXXXDIT3_ora_23658.trc XXXXDIT3_ora_23658.tkprofs

TKPROF: Release 11.2.0.4.0 - Development on Sat Apr 16 21:23:28 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


$

now its time to view the readable format.


$ cat  XXXXDIT3_ora_23658.tkprofs

TKPROF: Release 11.2.0.4.0 - Development on Sat Apr 16 21:23:28 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: XXXXDIT3_ora_23658.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: grwydz59pu6mc Plan Hash: 3684871272

select text
from
 view$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=17 us cost=1 size=15 card=1)

********************************************************************************

SQL ID: 0ata065ztg2u0 Plan Hash: 735420252

select name
from
 v$database


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 84
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=1520 us cost=0 size=1900 card=100)
         1          1          1   FIXED TABLE FULL X$KCCDI (cr=0 pr=0 pw=0 time=685 us cost=0 size=19 card=1)
         1          1          1   BUFFER SORT (cr=0 pr=0 pw=0 time=837 us cost=0 size=0 card=100)
         1          1          1    FIXED TABLE FULL X$KCCDI2 (cr=0 pr=0 pw=0 time=823 us cost=0 size=0 card=100)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  control file sequential read                    7        0.00          0.00
  SQL*Net message from client                     1        0.23          0.23



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  control file sequential read                    7        0.00          0.00
  SQL*Net message from client                     1        0.23          0.23


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1

    1  user  SQL statements in session.
    1  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: XXXXDIT3_ora_23658.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      60  lines in trace file.
       0  elapsed seconds in trace file.


$

Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s