OraFAQ Forum: Performance Tuning » Table Statistics Keep Getting Stale

Home » RDBMS Server » Performance Tuning » Table Statistics Keep Getting Stale (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

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

Table Statistics Keep Getting Stale [message #689725]

Wed, 03 April 2024 10:33

wtolentino
Messages: 422
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

Report message to a moderator

Re: Table Statistics Keep Getting Stale [message #689726 is a reply to message #689725]

Wed, 03 April 2024 10:57

Barbara Boehmer
Messages: 9106
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?

Report message to a moderator

Re: Table Statistics Keep Getting Stale [message #689728 is a reply to message #689726]

Wed, 03 April 2024 11:23

wtolentino
Messages: 422
Registered: March 2005

Senior Member

oh sorry it's

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

Report message to a moderator

Re: Table Statistics Keep Getting Stale [message #689729 is a reply to message #689728]

Wed, 03 April 2024 11:37

wtolentino
Messages: 422
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.

Report message to a moderator

Re: Table Statistics Keep Getting Stale [message #689730 is a reply to message #689725]

Wed, 03 April 2024 11:39

John Watson
Messages: 8981
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: 422
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.

Report message to a moderator

Re: Table Statistics Keep Getting Stale [message #689732 is a reply to message #689731]

Wed, 03 April 2024 12:41

John Watson
Messages: 8981
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.

Report message to a moderator

Previous Topic:	query output speed very slow
Next Topic:	Parallel Degree Limit correct num to use on Parallel Hints

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 13 14:59:51 CDT 2025