Home » RDBMS Server » Performance Tuning » Table Statistics Keep Getting Stale (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Table Statistics Keep Getting Stale [message #689725] Wed, 03 April 2024 10:33 Go to next message
wtolentino
Messages: 404
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 #689726 is a reply to message #689725] Wed, 03 April 2024 10:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Warren,

One or more of us is confused.  You say,


"This is how we lock the statistics:
exec dbms_stats.unlock_table_stats('CCFSRVDBA', 'AQ$_NOTICE_QTABLE_C');"

So, you are using unlock to lock?
Re: Table Statistics Keep Getting Stale [message #689728 is a reply to message #689726] Wed, 03 April 2024 11:23 Go to previous messageGo to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
oh sorry it's

exec dbms_stats.lock_table_stats('CCFSRVDBA', 'AQ$_NOTICE_QTABLE_C');

Re: Table Statistics Keep Getting Stale [message #689729 is a reply to message #689728] Wed, 03 April 2024 11:37 Go to previous messageGo to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
The reason why I have the unlock is because when everytime the SQL advisor tells that the statistics are stale I have to unlock the stats and rerun the stats and lock the stats again.
Re: Table Statistics Keep Getting Stale [message #689730 is a reply to message #689725] Wed, 03 April 2024 11:39 Go to previous messageGo to next message
John Watson
Messages: 8935
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 Go to previous messageGo to next message
wtolentino
Messages: 404
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 Go to previous message
John Watson
Messages: 8935
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.
Previous Topic: query output speed very slow
Next Topic: Performances and tuning
Goto Forum:
  


Current Time: Mon May 27 14:20:21 CDT 2024