Hello Chris,
Interesting way how you distribute the workload on 4 nodes dynamically (i
guess) after 1/4 objects as per stale statistics objects , can i request you to
please
Share the way you how it's done.
Thanks & Regards,
orion
--
Thursday, 09 August 2018, 09:50p.m. +05:30 from Chris Taylor
christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>:
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<mailto: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<mailto: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
