Table stats are generated by the default automatic process that is created by Oracle when the database is installed i.e. dbms_stats.gather_database_stats. (I believe)
This process uses the view dba_table_modifications to establish which tables have changed by more than 10% (not configurable) since the last stats were gathered. For the vast majority of tables this works very well but for certain tables it is not adequate. I have carried out repeatable experiments that show (I believe) that certain queries perform extremely well (2 sec response time) on those partitions that have stats that are very recent and perform extremely badly against partitions that have out of date stats (2 minute response times) even though data in the partition has changed by less than 10%.
Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3058895998401
No comments:
Post a Comment