Skip to Main Content
  • Questions
  • Table Statistics - Can massive updates on table leave stats stale for subsequent Selects ?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Achal.

Asked: August 31, 2017 - 2:39 pm UTC

Last updated: September 01, 2017 - 1:24 am UTC

Version: 11 gr2

Viewed 1000+ times

You Asked

[Scenario]

A table, in an OLTP environment, has ~40 columns and ~1,00,000 rows. One of the column stores timestamp values, like last maintenance date time for entity represented by that row. This column is not used in access or filter predicates .A reporting process updates value of this column for ~20,000 rows. Other than this, no updates, insert or deletes on the table.

We believe that at end of the reporting process stats for this table will be marked as stale. This is the expected behavior as statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table.

[Question]

1. Can these stale statics lead oracle to choose a sub-optimal plan for subsequent Selects on the table? We issue selects on same table as a part of same reporting process. If yes, what would be best option to prevent it.
2. Maybe, we can do something so that Updates on this particular column don't contribute to making stats stale for the table?

and Chris said...

You're right, updating > stale_percent rows in a table will mark the stats as stale:

create table t as 
  select rownum c1, date'2000-01-01' c2 from dual
  connect by level <= 1000;
  
exec dbms_stats.gather_table_stats(user, 't');

select coalesce(stale_stats, 'NO') from user_tab_statistics
where  table_name = 'T';

COALESCE(STALE_STATS,'NO')  
NO                          

update t 
set    c2 = sysdate
where  rownum <= 200;
commit;

exec dbms_stats.flush_database_monitoring_info; 

select coalesce(stale_stats, 'NO') from user_tab_statistics
where  table_name = 'T';

COALESCE(STALE_STATS,'NO')  
YES  


1. No. If you only change this column and it's not in your where clauses it doesn't matter this is out of date. All your other stats are still "good". So the optimizer should still pick the "right" plan.

Though this table will be picked up by the regular stats gathering job. Which leads to some redundant work.

2. If the stats being stale concerns you, you could increase the stale_percent for the table:

exec dbms_stats.gather_table_stats(user, 't');

select coalesce(stale_stats, 'NO') from user_tab_statistics
where  table_name = 'T';

COALESCE(STALE_STATS,'NO')  
NO 

exec dbms_stats.set_table_prefs(user, 't', 'STALE_PERCENT', 50);

update t 
set    c2 = sysdate
where  rownum <= 200;
commit;

exec dbms_stats.flush_database_monitoring_info; 

select coalesce(stale_stats, 'NO') from user_tab_statistics
where  table_name = 'T';

COALESCE(STALE_STATS,'NO')  
NO                         


Eventually they will become stale though.

Alternatively you could look at selectively locking stats on the table.

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

More to Explore

Performance

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