Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

10gR2 - Gathering Stats While Under Load

TonyGMay 14 2010 — edited May 15 2010
Situation:
I have a long running process that inserts/updates a large amount of data to a small subset of tables. Unfortunately, due to the volume of work, the stats on these tables become stale while the job is running. In turn, performance degrades considerably and the job bogs down even more.

Action Being Considered:
While monitoring the process, I'm thinking of running a job that gathers stats only on stale objects in the schema as they become stale This means stats would be gathered on the objects while records are being inserted/updated.

By focusing only on the objects whose stats are stale using dbms_stats, the process is much faster than running it against all objects in the schema.

Question:
Has anyone had susccess with running stats on an object while it was being inserted into? If so, what kind of issues (if any) did you run into?
I am thinking of trying this with the hope that I can keep the stats 'fresh' while the long job is running. I know that sql gets invalidated while running stats on an object but I'm not sure how 'big a deal' that is. Since testing and monitoring this important process takes up some manpower resoruces and time, I'd like some other opinions before I march off in that direction.

Thanks in advance.

Tony G.
This post has been answered by 591186 on May 14 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2010
Added on May 14 2010
7 comments
2,245 views