10gR2 - Gathering Stats While Under Load
TonyGMay 14 2010 — edited May 15 2010Situation:
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.