Skip to Main Content
  • Questions
  • dba_tab_statistics remains stale after rollback

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hendrik.

Asked: July 07, 2025 - 1:05 pm UTC

Last updated: July 09, 2025 - 2:57 pm UTC

Version: 19

Viewed 100+ times

You Asked

Hi Tom.

When running a DML, the dba_tab_statistics, stale_stats column will be set to "YES" for the related table also when it was before "NO".
It will remain to yes, also when the entire transaction was rolled back.
So it looks like the update in dba_tab_statistics set stale_stats = "YES" is running in an autonomous transaction and will not be rolled back with the main transaction.

Does it makes sense ?

and Chris said...

The stale_stats column is based on the changes recorded in the *_tab_modifications views. It's this that isn't being reversed when you rollback. The stale_stats column will be set when the changes recorded here exceed the stale threshold for the table (10% by default).

For example:

create table t ( c1 ) as 
  select level from dual 
  connect by level <= 100;

Table T created.

select stale_stats 
from   user_tab_statistics
where  table_name = 'T';

STALE_S
-------
NO

-- one row added; not enough to cross stale threshold
insert into t values ( -1 );

1 row inserted.

select inserts 
from   dba_tab_modifications 
where  table_name = 'T';

INSERTS
----------
        1

select stale_stats 
from   user_tab_statistics
where  table_name = 'T';

STALE_S
-------
NO

-- add ten rows; stale threshold now passed
insert into t
  select -level from dual 
  connect by level <= 10;

10 rows inserted.

select inserts 
from   dba_tab_modifications 
where  table_name = 'T';

INSERTS
----------
       11

select stale_stats 
from   user_tab_statistics
where  table_name = 'T';

STALE_S
-------
YES

rollback;

Rollback complete.

-- changes are still recorded
select inserts 
from   dba_tab_modifications 
where  table_name = 'T';

INSERTS
----------
       11

select stale_stats 
from   user_tab_statistics
where  table_name = 'T';

STALE_S
-------
YES


The question here is: Is this really a problem?

In most applications, the chances of a single transaction changing more rows than the stale percentage for a table and being rolled back are tiny. (ignoring tables with a handful of rows, so one row is greater than 10% of the total).

If the changes in a transaction cross the stale threshold for a table, there are good odds that the table was close to stale before the transaction began. So even if you roll the changes back, the table is likely a good candidate for stats gathering.

Have you found a situation where this is causing you an issue?

Rating

  (1 rating)

Comments

dba_tab_statistics stale_stats

Hendrik, July 09, 2025 - 2:02 pm UTC

Thanks Chris for your response.

not a problem at all.
we had some discussion around how often gather stale stats should be executed and in this context. the item came up.
Attached my use case.
Kind Regards
Hendrik

DROP TABLE gaga;

CREATE TABLE gaga
(
    id         NUMBER GENERATED ALWAYS AS IDENTITY,
    myddate    DATE
);

BEGIN
    FOR i IN 1 .. 1000
    LOOP
        INSERT INTO gaga (myddate)
             VALUES (SYSDATE - i);
    END LOOP;
END;

COMMIT;
EXEC dbms_stats.gather_schema_stats (ownname => 'TESTTAB');

SELECT owner, table_name, stale_stats
  FROM dba_tab_statistics
 WHERE owner = 'TESTTAB' AND table_name = 'GAGA';

DELETE FROM gaga;

ROLLBACK;

SELECT owner, table_name, stale_stats
  FROM dba_tab_statistics
 WHERE owner = 'TESTTAB' AND table_name = 'GAGA';

Chris Saxon
July 09, 2025 - 2:57 pm UTC

Thanks for confirming; if this does turn out to cause you real problems let us know.