OraFAQ Forum: Performance Tuning » Stats on a partitioned table

Home » RDBMS Server » Performance Tuning » Stats on a partitioned table (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stats on a partitioned table [message #592452]

Thu, 08 August 2013 09:06

johnp12
Messages: 5
Registered: February 2012

Junior Member

Hello,

Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?

I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?

so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?

thanks

John

Report message to a moderator

Re: Stats on a partitioned table [message #592453 is a reply to message #592452]

Thu, 08 August 2013 09:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Stats on a partitioned table [message #592497 is a reply to message #592452]

Thu, 08 August 2013 21:41

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I admit to not knowing the full details. But I am going to do some guessing here. What I do know on this subject I read mostly from manuals.

First there are two kinds of stats ( GLOBAL and LOCAL ).

Quote:

Global stats are stats across an entire table.
Local stats are stats across a single partitions.

For a non-partitioned table there are no local stats (or is it there are no global stats (or does it really matter as there is only one object to create stats across?)) anyway...

Second, for recent versions of Oracle, many GLOBAL stats can be "summed" so to speak by referencing LOCAL stats. So for example when you add a new partition and collect stats on it, Oracle can update these sum-able GLOBAL stats at the same time.

Third, not all GLOBAL stats can be updated from LOCAL stats. Consider for example the meaning of DISTINCT NUMBER OF VALUES. Suppose you have table with two partitions. Let us also say that this table has a COLOR column. You have three rows in each of these two partitions so you have six rows in the table. In the first partition you have the colors RED / BLUE / GREEN. In the second partition you have the colors RED / BLUE / GREEN (yep same colors). Partition 1 says color(num_distinct)=3. Partition2 says color(num_distinct)=3. We know because of the description I presented that the table.color(num_distinct)=3. But if all we had was Partition1.color(num_distinct)=3. and Partition2.color(num_distinct)=3,... then how would you compute table.color(num_distinct) from these two facts alone. There is some formula I am sure but it won't be exact, certainly not as exact as actually distincting across all six rows at one time.

With this in mind, it is in fact conceivable to me that all partitions could list their stats as being current and yet GLOBAL table stats could still list as stale; if Oracle deems enough changes had occurred to the data to make one of the non-sum-able stats likely too far off.

So to answer your question, you collect stats when they no longer provide a good enough representation of your data's actual distributions in order to provide good enough query plans to meet your SLAs. The trick of course is to know when this point has reached or ideally to know just before you reach that point so you take corrective action before plans go south. One solution is of course to let Oracle collect the stats for you so you don't have to try and figure it out.

Kevin

Report message to a moderator

Re: Stats on a partitioned table [message #593909 is a reply to message #592497]

Wed, 21 August 2013 21:47

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I should add also that 11gR2 has a new method of statistics calculation that is a game changer for how we do it and alone seems to me reason enough to migrate to at least this version of the database.

Johnathan Lewis provides this detailed explanation of the new feature (man this guy is just so far ahead of me).

Here too is the showing of how much simpler it is to collect stats well on 11gR2. This also can be found in Johnathan's document.

exec dbms_stats.set_param('APPROXIMATE_NDV','TRUE')

exec dbms_stats.set_param('ESTIMATE_PERCENT', 'DBMS_STATS.AUTO_SAMPLE_SIZE')

exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'LINEITEM', pname => 'INCREMENTAL', pvalue => 'TRUE' )

exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'LINEITEM', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity => 'GLOBAL' )

In the above we set some global statistics collection parameters and then a parameter for a partitioned table. Seems to me we also would be setting additional global parameter to cover granularity and other table preferences like method_opt for specific problem cases. At this point I really begin to believe it is possible to define a statistics collection strategy that will allow Oracle to do it automatically. Boy I see the possibility of more sleep in my life.

Good luck. Kevin

[Updated on: Wed, 21 August 2013 22:40]

Report message to a moderator

Previous Topic:	Efficient Way - Outer join Vs Not Exists
Next Topic:	query tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:15:48 CST 2025