How can you tell if a SQL statement will see stale optimizer statistics?

Whenever I am sent a SQL statement that’s not performing very well, my first question is always, “are stats fresh?”. If statistics are stale, the optimizer will still use them because it’s the best information it can get (well, in theory, and assuming you’re not using dynamic sampling). If the statistics don’t match the reality, the optimizer may choose a sub-optimal plan.

You can check every table for stale or missing statistics like this:

select table_name,owner 
from  dba_tab_statistics 
where stale_stats = 'YES' or stale_stats is null;

However, wouldn’t it be nice if you could check your ‘problem’ query?

If the query has been executed and it’s still in the cursor cache (and you have the SQL ID), you can do something like this. Note that I don’t claim this is a perfect solution (see the caveat below), but it’s pretty cool IMO:

set pagesize 100
set linesize 150
set trims off
set tab off
set verify off
column table_name format a50
column index_name format a50
column object_type format a40
column owner format a40

accept sql_id prompt 'Enter the SQL ID: ' 
PROMPT ==========
PROMPT Tables
PROMPT ==========
with plan_tables as (
select distinct object_name,object_owner, object_type 
from v$sql_plan 
where object_type like 'TABLE%' 
and   sql_id      = '&sql_id')
select t.object_owner owner,
       t.object_name table_name,
       t.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness   
from   dba_tab_statistics s,
       plan_tables        t
where  s.table_name = t.object_name
and    s.owner      = t.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by t.object_owner, t.object_name;

PROMPT ==========
PROMPT Indexes
PROMPT ==========
with plan_indexes as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'INDEX%'
and   sql_id      = '&sql_id')
select i.object_owner owner,
       i.object_name index_name,
       i.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from   dba_ind_statistics s,
       plan_indexes       i
where  s.index_name = i.object_name
and    s.owner      = i.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by i.object_owner, i.object_name;

This will list the staleness status of tables and indexes accessed by the relevant SQL statement. You can also retrieve plans from AWR and SQL tuning sets if the cursor cache doesn’t contain the statement you want. Alternatively, you can join the plan data with DBA_TAB_MODIFICATIONS, and see exactly how much DML activity has been going on with the tables accessed by the SQL statement.

I have a caveat! Use this information as a guide. A SQL statement in the cursor cache may have been parsed hours or even days ago, so it could have been parsed under completely different circumstances (maybe all stats were fresh or even wholly stale). In addition, if you have gathered statistics recently (with default parameters), the SQL statement in the cursor cache might not have been re-parsed to see the updated statistics. The re-parse does not happen immediately, so the SQL statement could be using a plan based on stale statistics even if everything looks fresh. Eventually, cursor invalidation will initiate a re-parse and the SQL statement will see the new statistics. Be aware of these possibilities, but at least you can tell whether or not a freshly-parsed SQL statement will see up-to-date statistics.