How to trace SQL sessions to identify Oracle Database bottlenecks DBMS_MONITOR

How to trace SQL sessions to identify Oracle Database bottlenecks

   Refer  Blog Theme - Details (oracle.com)

 To start the session trace, you first need to know the unique identifiers of the session: the SID and the SERIAL#. Use the following query

select sid, serial#
from v$session
where username = 'SH';

Using the SID and SERIAL# returned, turn on tracing for the session by executing the following SQL statement as the SYS user:

begin
  dbms_monitor.session_trace_enable (
    session_id => <SID>, 
    serial_num => <serial#>, 
    waits      => true, 
    binds      => true
    plan_stat  => 'all_executions');
end;

/

After tracing is enabled, the trace file— a text file—is generated on the database server at the location specified by the DIAGNOSTIC_DEST database initialization parameter. For RAC Server there can be multiple location.

Now execute the activities need to be Traced...

Then close / disable the Tracing thru dbms_monitor.session_trace_disable.

Run the Tkprof upon the raw trace file xe_ora_<spid>.trc that is generated.

>Tkprof xe_ora_4736.trc xe_ora_4736.out sys=no waits=yes aggregate=no width=180.

Code listing 3: tkprof output, part 1
... output truncated ...
SELECT     sum(s.amount_sold) AS dollars
  FROM     sales s
  ,        times t
  WHERE    s.time_id = t.time_id
  AND      t.calendar_month_desc = '1998-05'
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        2     0.00       0.01         0          3          0           1
————— ———————— ———————— —————————— ————————— —————————— —————————— ———————————
total        4     0.00       0.01         0          3          0           1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
—————————— —————————— —————————— ———————————————————————————————————————————
         1          1          1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=61 us)
         1          1          1  MAT_VIEW REWRITE ACCESS FULL CAL_MONTH_
                                  SALES_MV (cr=3 pr=0 pw=0 time=52 us cost=3 
                                  size=15 card=1)
... output truncated …
..