Search This Blog

Thursday 5 January 2012

Oracle profiling session


SQL> select
  2    u_dump.value   || '/'     ||
  3    db_name.value  || '_ora_' ||
  4    v$process.spid ||
  5    nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  6    || '.trc'  "Trace File"
  7  from
  8               v$parameter u_dump
  9    cross join v$parameter db_name
10    cross join v$process
11          join v$session
12            on v$process.addr = v$session.paddr
13  where
14   u_dump.name   = 'user_dump_dest' and
15   db_name.name  = 'db_name'        and
16   v$session.audsid=sys_context('userenv','sessionid');

Trace File
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/cfdev_ora_9576.trc

SQL> alter session set sql_trace = true;

Session altered.

SQL> -- your SQL or PL/SQL CODE
  2  /

PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>

then analyze C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/cfdev_ora_9576.trc with tkprof
tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\CFDEV\UDUMP/cfdev_ora_9576.trc out.txt sys=no

No comments:

Post a Comment