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 ?
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?