Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Explain plan for stored procedure

    Could someone please explain to me how to view the query plan for a stored procedure? I have tried "set autotrace on" and then running the proc. I have also tried "alter session set sql_trace=true" and then running tkprof on the .trc file. Neither of these or "explain plan for...." allows me to see the query plan for a stored procedure, only a standalone query. Any help would be appreciated.

    Thanks,

    Eric B.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >then running tkprof on the .trc
    TKPROF TRACEFILE.TRC TRACE.TXT EXPLAIN=USERNAME/PASSWORD
    The EXPLAIN PLAN will be in the *TXT file.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    TKPROF options

    Usage: tkprof tracefile outputfile [explain= ] [table= ]
    [print= ] [insert= ] [sys= ] [sort= ]
    table=schema.tablename Use 'schema.tablename' with 'explain=' option.
    explain=user/password Connect to ORACLE and issue EXPLAIN PLAIN.
    print=integer List only the first 'integer' SQL statements.
    aggregate=yes|no
    insert=filename List SQL statements and data inside INSERT statements.
    sys=no TKPROF does not list SQL statements run as user SYS.
    record=filename Record non-recursive statements found in the trace file.
    sort=option Set of zero or more of the following sort options:
    prscnt number of times parse was called
    prscpu cpu time parsing
    prsela elapsed time parsing
    prsdsk number of disk reads during parse
    prsqry number of buffers for consistent read during parse
    prscu number of buffers for current read during parse
    prsmis number of misses in library cache during parse
    execnt number of execute was called
    execpu cpu time spent executing
    exeela elapsed time executing
    exedsk number of disk reads during execute
    exeqry number of buffers for consistent read during execute
    execu number of buffers for current read during execute
    exerow number of rows processed during execute
    exemis number of library cache misses during execute
    fchcnt number of times fetch was called
    fchcpu cpu time spent fetching
    fchela elapsed time fetching
    fchdsk number of disk reads during fetch
    fchqry number of buffers for consistent read during fetch
    fchcu number of buffers for current read during fetch
    fchrow number of rows fetched
    userid userid of user that parsed the cursor
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •