Saving execution plans

[manodba]

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

[Axr2]

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

[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.

[gandolf989]

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.

[Axr2]

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.

[manodba]

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?