Table Statistics Affect Index Only Query?

Question

We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight rows.

The table had no statistics on it, yet the index did. Gathering new statistics on the index did not change the plan, but gathering statistics on the table did. My understanding was that a query that could be satisfied using only the index would not have to access the table, therefore my mental model was that table statistics would not matter in this case, but experience seems to indicate otherwise.

Both the explain plan and auto-trace plan show only index access, yet when the table statistics are not present there is significantly higher cost and cardinality. The autotrace shows higher CPU, DB Time and Consistent Gets. I have not tried to trace it yet, but I can reproduce it by creating/dropping statistics on the table as shown below. Can anyone explain this behavior?

set serveroutput on

DECLARE
 numr NUMBER;
 numb NUMBER;
 avgr NUMBER;
 nrow NUMBER;
 nblk NUMBER;
 numd NUMBER;
 avgl NUMBER;
 avgd NUMBER;
 cfac NUMBER;
 ilvl NUMBER;
 gues NUMBER; 
BEGIN
  --Gather Stats.
  dbms_stats.Gather_table_Stats(USER,'RESULTS');

  --Gather Index Stats.
  dbms_stats.Gather_index_Stats(USER,'I1');

  --Show Index Stats.
  dbms_stats.get_index_stats(USER, 'I1', NULL, NULL, NULL, nrow, nblk
     , numd, avgl, avgd, cfac, ilvl, NULL, gues);
  dbms_output.put_line('Number of rows: ' || TO_CHAR(nrow));
  dbms_output.put_line('Number of blocks: ' || TO_CHAR(nblk));
  dbms_output.put_line('Distinct keys: ' || TO_CHAR(numd));
  dbms_output.put_line('Avg leaf blocks/key: ' || TO_CHAR(avgl));
  dbms_output.put_line('Avg data blocks/key: ' || TO_CHAR(avgd));
  dbms_output.put_line('Clustering factor: ' || TO_CHAR(cfac));
  dbms_output.put_line('Index level: ' || TO_CHAR(ilvl));
  dbms_output.put_line('IOT guess quality: ' || TO_CHAR(gues));

  delete from plan_table;
END;
/

EXPLAIN PLAN FOR SELECT rsample_id FROM results 
   WHERE rsample_id = '0555103360';
SELECT cost, substr(lpad(' ', level-1) || operation || ' (' || options 
   || ')',1,50 ) "Operation", object_name "Object"
FROM plan_table START WITH ID = 0 CONNECT BY PRIOR id=parent_id;



DECLARE
 nrow NUMBER;
 nblk NUMBER;
 numd NUMBER;
 avgl NUMBER;
 avgd NUMBER;
 cfac NUMBER;
 ilvl NUMBER;
 gues NUMBER; 
BEGIN
  --Delete Stats.
  dbms_stats.delete_table_stats(USER,'RESULTS');

  --Gather Index Stats.
  dbms_stats.Gather_index_Stats('LRIFFEL','I1');

  --Show Index Stats.
  dbms_stats.get_index_stats(USER, 'I1', NULL, NULL, NULL, nrow, nblk
     , numd, avgl, avgd, cfac, ilvl, NULL, gues);
  dbms_output.put_line('Number of rows: ' || TO_CHAR(nrow));
  dbms_output.put_line('Number of blocks: ' || TO_CHAR(nblk));
  dbms_output.put_line('Distinct keys: ' || TO_CHAR(numd));
  dbms_output.put_line('Avg leaf blocks/key: ' || TO_CHAR(avgl));
  dbms_output.put_line('Avg data blocks/key: ' || TO_CHAR(avgd));
  dbms_output.put_line('Clustering factor: ' || TO_CHAR(cfac));
  dbms_output.put_line('Index level: ' || TO_CHAR(ilvl));
  dbms_output.put_line('IOT guess quality: ' || TO_CHAR(gues));
  delete from plan_table;
END;
/

EXPLAIN PLAN FOR SELECT rsample_id FROM results 
   WHERE rsample_id = '0555103360';
SELECT cost, substr(lpad(' ', level-1) || operation || ' (' || options 
   || ')',1,50 ) "Operation", object_name "Object"
FROM plan_table START WITH ID = 0 CONNECT BY PRIOR id=parent_id;

This had the following output (modified to fit):

anonymous block completed
Number of rows: 125226611
Number of blocks: 381090
Distinct keys: 5778886
Avg leaf blocks/key: 1
Avg data blocks/key: 3
Clustering factor: 19792294
Index level: 3
IOT guess quality: 

plan FOR succeeded.
COST  Operation             Object                       
----- --------------------- ------
    4 SELECT STATEMENT()   
    4 INDEX (RANGE SCAN)    I1

anonymous block completed
Number of rows: 119034073
Number of blocks: 362402
Distinct keys: 5353024
Avg leaf blocks/key: 1
Avg data blocks/key: 3
Clustering factor: 18852918
Index level: 3
IOT guess quality: 

plan FOR succeeded.
COST  Operation             Object                       
----- --------------------- ------
    9 SELECT STATEMENT()   
    9 INDEX (RANGE SCAN)    I1

After creating this I noticed that the index statistics were different for each run even though nothing should be changing in the table and index statistics are re-gathered on each run. My theory now is that something on the index statistics is retained when gathering table statistics with the cascade option even when index statistics are re-gathered.

Granularity is set to AUTO and Cascade is set to AUTO_CASCADE.

Answer 1

I would guess that CBO somehow computes table stats approximation from index stats or uses some rule of the thumb.

Different stats may be caused by

• DML between executions of dbms_stats.gather_index_stats
• estimate_pct parameter is not set - so estimate is computed from sample, as is described in Oracle's documentation

  estimate_percent Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Hint: Since Oracle 9i we have dbms_xplan package for formatting execution plan, which may be useful in your investigation - you can run query on different "setting" and see actual execution plan used. Check Oracle documentation for dbms_xplan, and some undocumented methods, documented here