Logo Background

Oracle SQL Trace File And Explain Plan

  • By on August 20, 2008 | 2 Comments

    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>
    » How To Check Server Open Port
  1. #1 oracle
    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?

    Post ReplyPost Reply
  1. #2 oracle
    November 23, 2010 11:06 pm

    The 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, ”);

    Post ReplyPost Reply
Leave a Comment