Oracle : TKPROF simple steps

1. Set the following parameters
TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE = unlimited (also see metalink article 108723.1)
USER_DUMP_DEST = /oracle/admin/ora9i/udump


2. Enable SQL TRACE for a session
alter session set SQL_TRACE true;


3. Run the query


4.obtain the number included in the trace filename
Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1)

or

Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select userenv('sid') from dual)


5.Run TKPROF at the command line to put the TRACE file into readable format
tkprof ora_19554.trc rich2.prf explain=system/manager sort=FCHCPU,EXECPU,PRSCPU print=5


Related Posts:
- Avoiding unnecessary function calls to optimize SQL statements
- Alter System Set Events to Generate Trace Files on Ora Error
- Query Optimization Tips for Oracle
- How to Get Execution Plan and Statistics of Query
- Append String to CLOB in Optimized way
- Why prefer COALESCE over NVL
- DBMS_PROFILER: Overview and How to Install
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Oracle: Analyze Table or Index
- plsql_warnings: Ensure more quality and performance in plsql code using Oracle Compiler
- PLSQL Tuning: Bind Variables and execute immediate
- PLSQL Tuning: Bulk Collect with Dynamic SQL

7 comments:

  1. this is bang on target. thanks for sharing it in this simple n short way :)

    ReplyDelete
  2. when i doing 5th point it through err. can u tell me how i will overcome this problrm

    ReplyDelete
  3. Hi Nimish Garg

    Thanks for your post.It will be more helpful if You provide screenshots with Navigation..

    ReplyDelete
  4. Hi Nimish, Wonderful post. Thanks.
    @Gopal Das
    ]$tkprof /prod_ora_.trc rich2.prf explain=system/manager sort=FCHCPU,EXECPU,PRSCPU print=5
    The outfile rich2.prf will be generated at home location.

    ReplyDelete