Calculating schema statistics during a huge 10 hour batch job [message #616664] |
Thu, 19 June 2014 06:11 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Hi,
We are on Oracle 11gR2(11.2.0.4) on Linux. The in-built schema stats collection job is configured to run only during night now in production.
We have a huge batch job that moves a very huge amount of data in almost 90% of the tables of the database (only look up tables are not touched). It will take about 10 hours and is scheduled over the weekend. How do I go about ensuring that schema statistics are up to date when this heavy DML occurs constantly during this 10 hour day period? What is the best practice to adopt in such a scenario? What I read in the Oracle documentation is that
" For tables which are being bulk-loaded, the statistics-gathering procedures should be run on those tables immediately following the load process, preferably as part of the same script or job that is running the bulk load"
How to make it part of the batch job? One option is to "divide the batch job in say 5 equal chunks" and after each chunk is completed , run the gather stats. Is that the best way to do this?
I will be thankful for inputs on this.
Thanks,
|
|
|
|
Re: Calculating schema statistics during a huge 10 hour batch job [message #616695 is a reply to message #616673] |
Thu, 19 June 2014 13:27 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Hi Blackswan,
Thanks for your inputs.
About your point: "which metric measures best?"
I meant to ask that, how do we manage statistics in this scenario where heavy DML keep happening during the batch job. And it is an ETL process that depends on the statistics for the query to perform well and the data goes on getting added during the job. The job runs for 10 hours and adds several thousands and (in a tables) even millions of rows in the tables! My issue is , during this job, how do I ensure statistics are reasonably fine?
At the end of batch, yes that is clear. We will surely do it. But during the process how to do?
Thanks,
|
|
|
|
|
|
|
|