Auto Stats Job

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

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
RgdsSanjay

[sachin pawar]

hi Sanjay,

Can you show more details of what you are seeing.
When running the job manually how much degree are you seeing?
Have you configured the resource manager?



Rgds,
Sachin Pawar
https://twitter.com/sach_pwr

Post by "Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)
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

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Sachin
Degree setting on all schema objects is 1 and when I ran manually with 12 Degree, I was able to complete the stats in 3hr 10 min with 3-4 test done to double check . While running the Auto stats it timed out on daily basis and while checking the details of the stats, I found 90% of time was spent on only one objects and so allocated 3.5 hr out of 4 hr going there on daily basis.
I had resource manager and most priority to SYS jobs. Also this is not scenario in one environment, I found now the same issue in multiple 12.1.0.2 environment where almost 2-3 days the auto job timing out
Sanjay
On Thursday, August 9, 2018, 10:11:17 AM EDT, sachin pawar <***@gmail.com> wrote:

hi Sanjay,
Can you show more details of what you are seeing.  When running the job manually how much degree are you seeing?Have you configured the resource manager? 


Rgds,Sachin Pawarhttps://twitter.com/sach_pwr


On Wed, Aug 8, 2018 at 5:17 PM Sanjay Mishra <dmarc-***@freelists.org> 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
RgdsSanjay

[Q A I S E R]

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

Post by "Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)
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

[Chris Taylor]

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

Post by Q A I S E R
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

Post by "Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)
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

[Orion ten]

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 ***@gmail.com<mailto:***@gmail.com>:

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 <***@gmail.com<mailto:***@gmail.com>> 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-***@freelists.org<mailto:dmarc-***@freelists.org>> 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

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Can you please share some details about your process/scripts. I can also try to use the setup in some of the heavily used non-prod Multitenant architecture with multiple pdbs in it
ThanksSanjay
On Thursday, August 9, 2018, 12:22:22 PM EDT, Chris Taylor <***@gmail.com> wrote:

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 <***@gmail.com> 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-***@freelists.org> 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
RgdsSanjay

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Qaiser

I like some good tips in the document. Do we have the same for 12c too with some change as otherwise most still work for 12c. Will go more to see if I can get some optimization in the environment
Sanjay
On Thursday, August 9, 2018, 12:02:56 PM EDT, Q A I S E R <***@gmail.com> 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-***@freelists.org> 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
RgdsSanjay

[Stefan Knecht]

dbms_stats already has built-in what you need:

https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68674

Just use set_table_prefs to set the desired degree for the largest tables.

Stefan

Post by "Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)
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

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Stefan
I will be working some of these for the table reporting high time with auto stats and will see how it can help. Will update the results
Sanjay
On Thursday, August 9, 2018, 12:18:47 PM EDT, Stefan Knecht <***@gmail.com> wrote:

dbms_stats already has built-in what you need:
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68674

Just use set_table_prefs to set the desired degree for the largest tables.
Stefan

On Thu, Aug 9, 2018 at 4:15 AM, Sanjay Mishra <dmarc-***@freelists.org> 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
RgdsSanjay