While that's technically TRUE, Oracle also (in the same paper maybe?) says
some databases may better to create your own methodology based on your
workload. They equally support doing either.
I've almost always rolled my own stats routines based on the system in
focus. (For non-critical systems - for systems that 'just run' I just
leave the automated jobs on and let them do the work). But for busy, busy
critical systems, it almost always makes more sense to roll your own .
For instance on a 4-RAC node I rolled out a script that would do the work
and determine the objects with stale stats, then divide those objects by 4
and run the stats job across all 4 nodes and divide the tables up so that
each node did 1/4 of the tables needing new stats. Also get rid of
histograms almost always until you know you need histograms on a specific
table+column.
Chris
On Thu, Aug 9, 2018 at 11:02 AM Q A I S E R <qrasheed@xxxxxxxxx> wrote:
Hi Sanjay,
The preferred method for gathering statistics in Oracle is to use the
supplied automatic statistics gathering job. You could tune the automated
job so it completes succesfully. You could use parallelisim, change stale
percentage, use concurrent stats by setting Gather Optimizer Statistics
Default Options. You can also increase the maintenance window time, so the
job completes.
Following white papaer on "Best Practices for Gathering Optimizer
Statistics" may help. Please see section 'Improving the efficiency of
gathering statistics'
https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
Thank,
--Qaiser
On Wed, Aug 8, 2018 at 4:15 PM, Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx
wrote:
Hi Experts
I had question on Auto Stats job in 12c which is running for 4hr now from
10pm to 2am. I had multiple heavy used environment where lost of objects
shows up for stale stats and the job failed to finish in 4 hr and so
multiple critical table stats were not collected. If I am running the job
as manually then can have more degree and can finish the job in less than 4
hr. So how Oracle allocated Degree when Degree defined for all database
objects is 1 and what can be done to update like preference or so to
complete the auto job in time. Don;t want to add layer by using cron
Rgds
Sanjay
