There is a script on Metalink which can help you automate gathering statistics for an E-Business Suite environment. As you are probably aware in an EBS environment you have to use FND_STATS or some predefined concurrent programs such as "Gather Schema Statistics".
Subject: coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes
Doc ID: Note:156968.1
This note contains a script which verifies statistics and generates a script and report which lists all the tables that need to be analyzed. Beware that coe_stats.sql automatically executes the dynamic script coe_fix_stats.sql script at the end of its execution. So make sure you comment out the line "START coe_fix_stats.sql" if you don't want this to happen.
The report generated also contains some information about the table. If its partitioned, number of rows (at the time it was last analyzed), sample percent, etc.. As well as "Days since last analyze". Immediately I noticed that the time since the tables were last analyzed was over a week. Our main Gather Schema Statistics job is scheduled to run every weekend.
What does this have to do with terminations? In a previous post I mentioned that our environment crashed as a result of bug 5907779. This happened as our weekly concurrent request to gather statistics was running. After the environment was bounced this job restarted but it was now during business hours. Statistics should always be gathered at periods of low activity because it causes performance issues. So I canceled the request via OAM, which changed the status to Phase: Completed, Status: Terminated.
The side affect to terminating a job, which had slipped my mind* is that it will not be rescheduled. I'm not sure why that happens, it doesn't make much sense to me but it appears to be by design. The solution is to reschedule the job.
* Apparently 8hrs of sleep in the past 72hrs can have that affect. Partly due to a last night out while on vacation, traveling, falling ill and being paged at 3am.
In summary, the goal of this post was to point out a useful metalink note and refresh your memory to reschedule canceled requests if you need them to run again.
No comments:
Post a Comment