Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

According to the documentation, "Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not."

But it seems like AUTO_CASCADE will always gather index statistics, even when it obviously does not need to.

This test case demonstrates the problem:

SQL> --Create table
SQL> create table statistics_test(a number, b number, c number, d number, e number);

Table created.

SQL> --Add 10 million rows (about 320 MB)
SQL> begin
  2     for i in 1 .. 100 loop
  3             insert into statistics_test
  4             select level, level, level, level, level from dual
  5             connect by level <= 100000;
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> --Create large index (AFTER the data load)
SQL> create index statistics_test_index on statistics_test(a,b,c,d,e);

Index created.

SQL> --Statistics are automatically gathered on index creation or rebuild.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:11:43

SQL> --Gather stats, use AUTO_CASCADE.
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

SQL> --FAIL: Time changed - index was re-analyzed even though no data changed.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:12:12

SQL> --Let's assume that LAST_ANALYZED is lying.
SQL> --What I'm really worried about is the extra time spent analyzing indexes.
SQL> --Compare performance of AUTO_CASCADE, TRUE, and FALSE.
SQL> --FAIL: AUTO_CASCADE is as slow as TRUE.  It should be as fast as FALSE.
SQL> set timing on
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.80
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.58
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.27

I do not think it matters, but my system is using the default for ESTIMATE_PERCENT, METHOD_OPT, DEGREE, GRANULARITY, CASCADE, and NO_INVALIDATE. I can reproduce this issue on 10.2.0.4, 11.2.0.1, and 11.2.0.3.

This looks like a bug, but I can't find anything on My Oracle Support. And it's strange that a default setting, with significant performance impact, would be completely broken for so long.

Am I missing something here?

share|improve this question

migrated from stackoverflow.com Feb 3 '12 at 20:21

This question came from our site for professional and enthusiast programmers.

    
I know statistics is usually just a job scheduled by a DBA, but I'm more interested in it as a developer. I deal with a lot of large tables that change frequently, and I need to gather stats as part of an application. –  jonearles Feb 3 '12 at 4:52
    
The first time you run it, this might be reasonable as the AUTO_CASCADE is based on the table stats being stale, not the index stats; so it would interesting to see if it does the same if you gather table stats before creating the index. Based on the subsequent runs it doesn't look like it will make a difference though. –  Alex Poole Feb 3 '12 at 8:27
    
Or I could try it myself, of course... as suspected, it does still gather the index stats if table stats are gathered before the index is created. –  Alex Poole Feb 3 '12 at 8:39

1 Answer 1

Are you sure the index was analyzed - or was it just that DBMS_STATS updated the LAST_ANALYZED timestamp? The best way to find out it to trace the process performing the analysis.

share|improve this answer
1  
Looks like the trace file also confirms that AUTO_CASCADE isn't working. For both CASCADE=>DBMS_STATS.AUTO_CASCADE and CASCADE=>TRUE, there are 3 selects from my table, and delete from ind_stats$ where obj#=:1. For CASCADE=>FALSE, there is only one select from my table, and no delete. (And the 3 select statements all look like statistics recursive SQL, they have the hint dbms_stats.) Everything seems to indicate that DBMS_STATS.AUTO_CASCADE works the same as TRUE. –  jonearles Feb 6 '12 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.