Skip to Main Content
  • Questions

  • When does STALE_STAS changes from NO to YES

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, donki.

Asked: February 07, 2018 - 9:40 pm UTC

Last updated: December 12, 2018 - 3:40 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi,
I'm trying to understand the trigger that changes the column value STALE_STATS in DBA_TAB_STATISTICS from NO to YES.
I have duplicated a table, gathered stats, deleted 35% of the records, and the stats aren't STALE='YES'.
I'd appreciate your insight on that one;

--CHECKING COUNT OF ORIGINAL TABLE
SELECT COUNT(1) FROM ALL_OBJECTS --1051227

--CREATING A DUP
CREATE TABLE TMP.TMP_ALL_OBJECTS AS SELECT * FROM all_objects

--CHECKING THE DUP IS IDENTICAL
SELECT COUNT(1) FROM TMP.TMP_ALL_OBJECTS --1051227

--CHECKING FOR STALE_STATS IN NEW TABLE
SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS' --NULL

--GATHERING STATS
BEGIN
 dbms_stats.gather_table_stats('TMP','TMP_ALL_OBJECTS',cascade=>TRUE);
END;

--CHECKING FOR STALE_STATS AFTER STATS GATHERING
SELECT STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS' --NO

--DELETEING 35% OF RECORDS
DELETE FROM CIT_INFRA_REPOS.TMP_ALL_OBJECTS WHERE OWNER='FIXED_INCOME';
COMMIT;

SELECT COUNT(1) FROM CIT_INFRA_REPOS.TMP_ALL_OBJECTS --651677

--checking the threshold
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'TMP', 'TMP_ALL_OBJECTS') STALE_PERCENT FROM DUAL; --10

--CHECKING STALE_STATS 
SELECT STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS' --NO


Thanks in advance

and Connor said...

You were close :-)

We don't *immediately* up date the staleness, because if we did, we would have to do it every time someone ran a DML statement. So we track it in memory, and flush it to the dictionary from time to time.

Or....you can flush it manually

SQL> SELECT COUNT(1) FROM ALL_OBJECTS;

  COUNT(1)
----------
     73853

1 row selected.

SQL> CREATE TABLE TMP_ALL_OBJECTS AS SELECT * FROM all_objects;

Table created.

SQL> SELECT COUNT(1) FROM TMP_ALL_OBJECTS;

  COUNT(1)
----------
     73853

1 row selected.

SQL> SELECT STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS';

STA
---
NO

1 row selected.

SQL> exec dbms_stats.gather_table_stats('','TMP_ALL_OBJECTS',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS';

STA
---
NO

1 row selected.

SQL> DELETE FROM TMP_ALL_OBJECTS WHERE rownum <= 30000;

30000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(1) FROM TMP_ALL_OBJECTS;

  COUNT(1)
----------
     43853

1 row selected.

SQL> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', user, 'TMP_ALL_OBJECTS') STALE_PERCENT FROM DUAL;

STALE_PERCENT
----------------------------------------------------------------------------------------------------------------------------------
10

1 row selected.

SQL> SELECT STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS';

STA
---
NO

1 row selected.

<b>SQL> exec dbms_stats.flush_database_monitoring_info;</b>

PL/SQL procedure successfully completed.

SQL> SELECT STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME = 'TMP_ALL_OBJECTS';

STA
---
YES

1 row selected.

SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great answer

Yaniv, February 08, 2018 - 3:04 am UTC

Thanks.
But what is the definition of
"So we track it in memory, and flush it to the dictionary from time to time. "

- Is "time to time" is a configurable attribute ?
- Can we know what is the time period ?


Connor McDonald
February 09, 2018 - 2:17 am UTC

From memory, I think its 15mins

Additional point

A reader, February 09, 2018 - 7:46 am UTC

Hi,

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68568

Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.

This means you can just use GATHER STALE/AUTO, without manually flushing first.

regards,
Connor McDonald
February 09, 2018 - 1:27 pm UTC

Thanks for that - I was unaware of that, but of course it makes perfect sense.

Question

Shira, December 06, 2018 - 8:25 am UTC

Do the optimizer lay on statistics staleness and how?

Connor McDonald
December 12, 2018 - 3:40 am UTC

lay ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.