Oracle SQL Trace File And Explain Plan
Sometimes you do have a SQL running slowly on your database. To have a better explanation of why your SQL running slow, you need to generate an explain plan from a trace file. These steps must be performed by the DBA on the database server.: -
Generate a trace file
SQL> ALTER system SET timed_statistics=TRUE;
SQL> EXEC sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);
Locate the trace file
SQL> SELECT VALUE FROM V$PARAMETER WHERE name = 'user_dump_dest';
Generate the explain plan by running tkprof
UNIX> tkprof <trace file> <output file> explain=<username/password>
PreviousNext» How To Check Server Open Port
2 Comments
Leave a Comment- #2
oracle
November 23, 2010 11:06 pmThe normal set trace is 10046. Use this command ‘exec dbms_system.set_ev(sid, serial#, event, level, name);’ to set trace for 10053 where you can see the optimizer details execution.
Example: exec dbms_system.set_ev(’318′, ’20097′, 10053, 12, ”);
Leave a Comment
Join the over 2,000 people who get free and fresh knowledge as soon as it’s published.
We respect your email privacy.
We promise never to rent, sell or share information we collect because that would be a violation of everything we stand for.
Period.
September 13, 2010 8:35 pm
We faced this error *** DUMP FILE SIZE IS LIMITED TO 10485760 BYTES *** when generate the trace. We tried execute the command ‘alter session set max_dump_file_size=unlimited’ after the command ‘alter session set timed_statistics=true’ but it doesn’t help as well.
When we execute select * from v$parameter where name = ‘max_dump_file_size’, the result is UNLIMITED. Anyone know if we need to change any other parameter in INIT_SID.ora?