Home » RDBMS Server » Performance Tuning » stale (local) index statistics on partitioned table (Oracle 12c)
stale (local) index statistics on partitioned table [message #649778] Wed, 06 April 2016 04:05 Go to next message
quirks
Messages: 66
Registered: October 2014
Member
Hello,
we have recently upgraded to Oracle 12c. Since then we encounter sporadically a strange mystery.

In a partitioned table the (local) index statistics of ALL partitions become stale.

The table is partitioned on a daily bases (interval). In Oracle 11c only the last inserted partition had stale index stats (which is obvious, because changes only occur in this partition). But now every now and then (every 7 to 10 days) Oarcle decides to set them all stale.

I don't know what happens neither why it happens, and I have no clue how to tackle this problem (and it is a problem because the execution plans look awful). I'm open to any suggestions how to figure out why they become stale and even better how to avoid this problem.

Cheers
Quirks
Re: stale (local) index statistics on partitioned table [message #649779 is a reply to message #649778] Wed, 06 April 2016 04:09 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
Are the statistics actually stale, or are they just being marked as stale?

You say the plans are changing, which implies that the statistics are changing. Is that correct? Are they being re-gathered, with incorrect results?
Re: stale (local) index statistics on partitioned table [message #649780 is a reply to message #649779] Wed, 06 April 2016 04:34 Go to previous messageGo to next message
quirks
Messages: 66
Registered: October 2014
Member
Good questions. I'll answer them as good as I can.

1st
I came up with this, because they are marked as stale.

2nd
I suspect, that they are not stale (besides the ones for the last partition, which was automatically created from oracle while inserting new data in a new time slice / partition). Is there a way to figure out if they are truly stale or only marked as stale?

3rd
The plans are changing, so I suspect the cause is the stale status. If I gather the stats again (which takes several hours) the plans become better again.

4th
There is an automatic (daily) gathering process setup by our DBA (I basically know nothing about this one).
Furthermore there is a gathering routine for the new daily partition (see below) which produces correct /valid stats.
Toggle Spoiler
[/align]

[Updated on: Wed, 06 April 2016 04:36]

Report message to a moderator

Re: stale (local) index statistics on partitioned table [message #649788 is a reply to message #649779] Wed, 06 April 2016 04:56 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
Are you clear on what "stale" means? It says that Oracle has detected that (by default - you can change this) 10% of the rows in an object have been changed since the object was last analyzed. You see the figures in dba_tab_modifications. Note that this view is not updated in real time, you need to exec dbms_stats.flush_database_monitoring_info to see up to date figures. So if the stats are marked as stale, there must have been considerable amount of DML against the object.

You say that plans are changing. If the stats have not changed, the plans will not change. Unless you have adaptive features or dynamic sampling enabled. Remember that in your release, the adaptive features may have a huge impact. You could test this with

ALTER SYSTEM SET optimizer_adaptive_features=false;
ALTER SYSTEM SET optimizer_dynamic_sampling=0;

and see if the plans stabilize. I wouldn't do this for anything other than diagnosis.


Re: stale (local) index statistics on partitioned table [message #649797 is a reply to message #649788] Wed, 06 April 2016 07:26 Go to previous messageGo to next message
quirks
Messages: 66
Registered: October 2014
Member
Well, I'm not a DBA. All I know is what I've read / heard. My understanding of how Oracle works is more guessing than knowing. But my guesswork matches very well to the info you just gave.
Toggle Spoiler

"dba_tab_modifications" shows me exact the inserts in the new partition that I would expect.
When I look at "USER_IND_STATISTICS", I find partitions marked as "stale" that are not mentioned in "dba_tab_modifications" (but that probably might be the cause because I don't dare to "exec dbms_stats.flush_database_monitoring_info").

You said John Watson wrote on Wed, 06 April 2016 04:56
If the stats have not changed, the plans will not change.
Does that apply even if STALE_STATS = 'YES'?

I'm not sure if I can / should ALTER SYSTEM. But I obviously can recommend it to our DBA. Our current settings are :
Toggle Spoiler

NAME	VALUE
optimizer_dynamic_sampling	2
optimizer_adaptive_features	TRUE


I've tried to set the options you've recommended as session parameters and indeed the plans became better. While playing around I figured that "optimizer_dynamic_sampling=0;" did the trick. "optimizer_adaptive_features=false;" seems to enable / disable the dynamic sampling as well.

Now I'm a little bit confused. Is it possible that "stale" index statistics do trigger oracle to do dynamic sampling? If so, that leads me back to my first question. Why do they get stale in the first place even if there is no change (in that specific partitions)?
Re: stale (local) index statistics on partitioned table [message #649798 is a reply to message #649797] Wed, 06 April 2016 07:36 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
you need to query different views for partitioned indexes as compared to "regular" indexes

https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin005.htm
Re: stale (local) index statistics on partitioned table [message #649799 is a reply to message #649798] Wed, 06 April 2016 08:12 Go to previous message
quirks
Messages: 66
Registered: October 2014
Member
BlackSwan wrote on Wed, 06 April 2016 07:36
you need to query different views for partitioned indexes as compared to "regular" indexes

For the usability of indices I've to look in the corresponding view. And I can see when it was analyzed.

For the index statistics I only know "USER_IND_STATISTICS". Can you probably give me a hint what to search for in google (I could not find any)?

[Updated on: Wed, 06 April 2016 08:19]

Report message to a moderator

Previous Topic: Performance tuning
Next Topic: How to calculate Index creation time
Goto Forum:
  


Current Time: Wed Jan 17 13:17:35 CST 2018

Total time taken to generate the page: 0.01954 seconds