No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48052
Total active users1733
Act. users last 24h10
Act. users last hour0
Registered user hits last week395
Registered user hits last month1204
Go up

Runstats on Few large partitioned tables
Next thread: migrate apex and ords from vm machine to phsyical server
Prev thread: gather system stats

Message Score Author Date
Hi All, recently our apps team complain that th...... Wan Chiun Keat May 10, 2017, 14:40
Hello, I am amazed that it is <i>the apps team<...... Bruno Vroman May 10, 2017, 19:55
Thanks Bruno for your kind reply. Actually Apps t...... Wan Chiun Keat May 11, 2017, 02:08
Hello, the AUTO_SAMPLE_SIZE is not about "<i>ga...... Bruno Vroman May 11, 2017, 10:20
Hi Bruno, Is there any chance that without usin...... Wan Chiun Keat May 11, 2017, 14:56

Follow up by mail Click here


Subject: Runstats on Few large partitioned tables
Author: Wan Chiun Keat, Malaysia
Date: May 10, 2017, 14:40, 159 days ago
Os info: Sun Sparc
Oracle info: 11.2.0.3
Message: Hi All,
recently our apps team complain that the runstats gather statistics getting longer (took about 20 hours - ridiculous right? ) especially large partition tables and found out that the previous dba did gather_schema_stats('schema'); without any others parameter pass in and all take default parameter values. I found out that the 11g version has dbms_stats.gather_schema_stats(USER,options=>'GATHER AUTO',cascade=>TRUE,estimate_percent=>dbms_stats.auto_sample_size); besides the auto_sample_size, this version also provide the INCREMENTAL statistics, question is should combine both to gain maximum performance ? Ultimately is to reduce runstats duration.

Thanks so spending time to look into this.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Runstats on Few large partitioned tables
Author: Bruno Vroman, Belgium
Date: May 10, 2017, 19:55, 159 days ago
Message: Hello,

I am amazed that it is the apps team that complained.
How are they involved with this DBA task?

Is it possible that apps team is launching the "gather_schema_stats" unconditionally?
If this is the case, the best way to reduce runstats duration is to avoid it ;-)

Why don't you use the autotask job that refreshes stats every night (and weekend) ONLY WHEN THEY ARE STALE? Most tables don't need their stats to be refreshed every day...

In some situations it can be that an application (a batch job) has to gather stats (example: every day at 07:00' a table is purged, new data is inserted, and then heavily used by other jobs. In this case the default behaviour is defeated: every night stats would be refreshed "for nothing" and every day "stale" statistics would be used by the jobs -but this is a special case and anyway gathering the stats for this particular table after having loaded the data is enough).

Please provide some more info about what you do if my words don't make much sense.

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Runstats on Few large partitioned tables
Author: Wan Chiun Keat, Malaysia
Date: May 11, 2017, 02:08, 159 days ago
Message: Thanks Bruno for your kind reply.
Actually Apps team just able to access development team but not UAT and Production. Their Database is administered by another vendor. recently they seek our help on this. From what I understand their application is running 24X7, only allow Saturday to perform some maintenance job. Yes is true, not all the tables should be refresh every day, this will really waste of resources. However, there are few very large tables loaded huge data everyday. Luckily these tables has created using partition by day period. Yes archiving is another action need to consider as it will not only save a lot of spaces and it is much easier to maintain.
Are you mean that I should only perform runstats on partition which is actively use rather perform whole table statistics ? what is the different if I use the sampling (auto_sample_size)?

Thanks.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Runstats on Few large partitioned tables
Author: Bruno Vroman, Belgium
Date: May 11, 2017, 10:20, 159 days ago
Message: Hello,

the AUTO_SAMPLE_SIZE is not about "gathering stats or not" but about the size of the sample that has to be used; in other words: if you refresh stats with estimate_percent=>dbms_stats.auto_sample_size for a table for which it is not necessary, the work has to be done anyway, with a small or large sample depending of the data (is it skewed or not...)

What I suggest is to refresh stats only if it is necessary; look at the documentation for "GATHER AUTO".

Search also info about "INCREMENTAL" to refresh statistics only for partitions that have been changed (otherwise once again: even with "auto_sample_size" you will end up with a refresh of stats for all the partitions).

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Runstats on Few large partitioned tables
Author: Wan Chiun Keat, Malaysia
Date: May 11, 2017, 14:56, 158 days ago
Message: Hi Bruno,
Is there any chance that without using INCREMENTAL, it will just perform runstats on those most recent partitions by using dbms_stats auto sample size and and options=> 'AUTO' ? If I read correctly, options=> 'auto' will combine both gather empty and gather stale (> 10% modifications).
As informed by app team tested above command in Development, it improve significantly on the gather statistics timing (only 1hr plus) compare to more than 10 hours conventional runstats without pass in any parameters.
App team also tell me after performed options=> 'auto' and auto_sample_size, the current active partitions being updated latest statistics and old partitions still remain on old statistics. In this case, in that situation we want to advise to use INCREMENTAL ? Thanks.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here