| Table Statistics Keep Getting Stale [message #689725] |
Wed, 03 April 2024 10:33  |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
We had already collect a statistics on this table AQ$_NOTICE_QTABLE_C and lock the statistics on it. Yet after a couple of days when we run the SQL advisor it will tell us that the stats are stale.
Optimizer statistics for table "CCFSRVDBA"."AQ$_NOTICE_QTABLE_C" are stale.
What could be the possible issue on it and how do we fix it. Please help and advise.
This is how we collect the statistics:
exec dbms_stats.gather_table_stats(ownname => 'CCFSRVDBA', tabname => 'AQ$_NOTICE_QTABLE_C', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
This is how we lock the statistics:
exec dbms_stats.unlock_table_stats('CCFSRVDBA', 'AQ$_NOTICE_QTABLE_C');
Thank you,
Warren
|
|
|
|
|
|
|
|
|
|
| Re: Table Statistics Keep Getting Stale [message #689730 is a reply to message #689725] |
Wed, 03 April 2024 11:39  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:We had already collect a statistics on this table AQ$_NOTICE_QTABLE_C and lock the statistics on it. Yet after a couple of days when we run the SQL advisor it will tell us that the stats are stale. I would say that you are seeing the expected behaviour. The stat's ARE stale, because they have not been re-gathered after 10% (the default threshold) of the rows have been modified. Queue tables tend to have quite high activity, so it is hardly surprising that they go stale so quickly.
What are you actually trying to achieve? If you are unlocking and re-gathering, then why lock at all?
[Updated on: Wed, 03 April 2024 11:40] Report message to a moderator |
|
|
|
| Re: Table Statistics Keep Getting Stale [message #689731 is a reply to message #689730] |
Wed, 03 April 2024 12:22 |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
what happens is that for example we run the stats and we locked it. after 2 days the query gets slow and we run the explain plan it shows different plan from the last good plan from when we run the SQL advisor. we run another SQL advisor and it is telling us that the stats are stale. there's recommendation from the SQL advisor to run the stats and i have unlock first because it was lock. i run the stats per recommendations from the SQL advisor and lock the stats in the hope of that it will stay.
after some days have passed the query becomes slow again and when we run the SQL advisor it will tell us that the stats are stale again for the same table.
|
|
|
|
| Re: Table Statistics Keep Getting Stale [message #689732 is a reply to message #689731] |
Wed, 03 April 2024 12:41 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If gathering statistics helps with performance, then why on earth are you locking them? Gather stats every day! Or every ten minutes! Or if you are on engineered systems, set optimizer_real_time_statistics=true and/or enable the high frequency stats job.
There are many reasons for plans changing other than statistics. For example, dynamic sampling. Or the adaptive features. Trust Uncle Oracle: enable everything, unless you can prive it is bad.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
