Saving execution plans
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Saving execution plans

  1. #1
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29

    Saving execution plans

    Hi,

    Is there any tool/procedure that would capture and save SQL execution plans? I want to do this in our batch processing database where same set of jobs run every day. It would be very helpful to diagnose any long running jobs for missing indexes or altered execution plans due to parameter changes etc. I tried to see if there is any in OEM but I couldn't find any.

    Thanks
    Manohar

  2. #2
    Join Date
    Oct 2002
    Posts
    807

    Re: Saving execution plans

    Originally posted by manodba
    Is there any tool/procedure that would capture and save SQL execution plans?
    Plans are stored in the plan_table. As long as it's not defined as a temporary table, it is there for posterity until someone deletes it.

    As for looking at plans, you can use 1) sqlplus and format it yourself, 2) dbms_xplan 3) myriad GUI tools

  3. #3
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    True, but I want some automated tool that collects that information for me. I would run it on a "stable database" for 1 whole batch cycle and save it elsewhere as some baseline.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Originally posted by manodba
    True, but I want some automated tool that collects that information for me. I would run it on a "stable database" for 1 whole batch cycle and save it elsewhere as some baseline.
    Have you looked at the dba tools, like db artisan? Oracle Enterprise Manager has some interesting tools as well, like DB Expert which goes away in 10g.
    this space intentionally left blank

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by manodba
    True, but I want some automated tool that collects that information for me. I would run it on a "stable database" for 1 whole batch cycle and save it elsewhere as some baseline.
    Aaah, now the real question. To do this - you probably need to backup v$sql_plan. Then compare new plans with old ones based on hash value and address.

  6. #6
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    Unfortunately, the database in question for me now is in 8i it has got no V$SQL_PLAN
    Can someone explain in detail abt SQL_ADDRESS and HASH_VALUE? will HASH_VALUE be different if database is bounced?

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