Home » RDBMS Server » Performance Tuning » Calculating schema statistics during a huge 10 hour batch job (Oracle 11gR2(11.2.0.4) on Linux)
Calculating schema statistics during a huge 10 hour batch job [message #616664] Thu, 19 June 2014 06:11 Go to next message
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 #616673 is a reply to message #616664] Thu, 19 June 2014 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is that the best way to do this?
which metric measures best?

>How to make it part of the batch job?
add new line at the end of the batch job to start the collection of statistics.
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 Go to previous messageGo to next message
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,

Re: Calculating schema statistics during a huge 10 hour batch job [message #616696 is a reply to message #616695] Thu, 19 June 2014 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> 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.
OK, but OLTP has good statistics which remain valid during whole 10 hours and are NOT impacted by new rows being added to DW database.
Re: Calculating schema statistics during a huge 10 hour batch job [message #616697 is a reply to message #616696] Thu, 19 June 2014 13:49 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks again Blackswan.

My issue is that: It is not that OLTP has good stats. This job runs on OLTP! We are acually migrating a OLTP database from one application version to another by this complex ETL kind of job. And for this we also need the stats to keep current during the execution of the job. How to achive it?

Sorry if I did not describe properly.

[Updated on: Thu, 19 June 2014 13:49]

Report message to a moderator

Re: Calculating schema statistics during a huge 10 hour batch job [message #616698 is a reply to message #616697] Thu, 19 June 2014 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is like you are driving a race car & want to know how to change a tire without making a pit stop.
How to achieve it?

Not every need can be realized.
Re: Calculating schema statistics during a huge 10 hour batch job [message #616728 is a reply to message #616698] Fri, 20 June 2014 01:03 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Since when is this a problem?

/forum/fa/11976/0/
Re: Calculating schema statistics during a huge 10 hour batch job [message #616729 is a reply to message #616728] Fri, 20 June 2014 01:13 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Previous Topic: DBA_HIST_SEG_STAT view
Next Topic: Materialized View- Delay refresh
Goto Forum:
  


Current Time: Thu Mar 28 10:00:51 CDT 2024