DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: explain plans

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    explain plans

    Hi,
    I am currently supporting an Oracle 10g database that has many stored procedures. Each stored procedure consists of many complex quierries, approx 2000 lines of code. I would like to get an explain plan for each querry in the stored procedure without actually running the stored procedure.
    I tried with the various "set autotrace" parameters but did not get any output. How can I achieve this? Please help. Thanks.

  2. #2
    Join Date
    Jun 2006
    Location
    Wales, UK
    Posts
    62

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Hi. Thanks for the info. I am very familiar with "set autotrace". This is great when you are testing sql statements. What about if you are testing a stored procedure with many sql(s)? When I execute the stored procedure ie. exec (xxxxx.xxx), how do I get an explain plan from executing the stored procedure? Thanks

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, I would say NO WAY. You can enable sql_trace, EXECUTE the procedure and find all the statements (even with their execution plans ) into the trace file.
    Later you can use tkprof to format that trace file and even insert the statements in a database table. You can then take these statements and optimize them one by one, although you should keep in mind that executing the statement stand alone causes the statement to be executed in a bit different context that in the stored procedure case but still...
    The best way is to drop that task to the developers. Since there are 2000+ statements in one procedure, I guess they hava what to think about ;-)

    Cheers
    Boris

Posting Permissions

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


Click Here to Expand Forum to Full Width