[Oracle] ANALYZE vs. DBMS_STATS or how-to play tricks on the optimizer

[stefan_koehler]

Introduction

A few days ago i received an e-mail from one of my clients. Basically it was about a particular SQL, that suddenly run slow for a few hours and then fast for the rest of the day. The SQL itself was executed by a customer application (Z) and joined a customer (Z) table with various SAP standard tables. The database version was 11.2.0.2. Nothing spectacular so far.

The analysis

I checked the execution plan for that particular SQL and found 2 plans for it. The join, join order and predicates were always the same, but there was an access difference on an index of the customer table. There was an "Index Full Scan" and on the other hand there was an "Index Fast Full Scan".

Two features came to my mind regarding the description of the problem:

• Adaptive cursor sharing
• Cardinality Feedback

Both features are usually disabled in a SAP environment by setting the hidden parameters "_OPTIMIZER_ADAPTIVE_CURSOR_SHARING" and "_OPTIMIZER_USE_FEEDBACK" to FALSE regarding sapnote #1431798. So i have checked the settings (and the note section for execution plans) and both features were disabled as recommended.

Ok next try ... Let's compare the statistics for the involved database objects (tables and indexes). I found out that the statistics were gathered twice a day for the customer table and its indexes, but the index statistics for the leaf blocks differed tremendous by each collection.

I remembered a statement from book "Cost-Based Oracle Fundamentals" by Jonathan Lewis (i strongly recommend this to any DBA or performance analyst) which basically says the following:

• The ANALYZE command counts the leaf blocks, that are currently within the index structure
• The DBMS_STATS package counts the leaf blocks, that have currently data in them

So i asked my client if he deleted a huge amount of data from the customer table and if he collects statistics with ANALYZE and DBMS_STATS. He confirmed, that a delete job was executed a few days ago, but he was not sure about the statistic collection. After some researches there was a script which exactly run the ANALYZE command on different customer tables. It looked like a download from the internet.

The use of ANALYZE to gather statistics is a deprecated feature, so we disabled it and use DBMS_STATS for all segments from now.

Test case

This test case should demonstrate the described behavior for gathering statistics with ANALYZE and DBMS_STATS. This test was run on an Oracle 11.2.0.3 database.

SQL> create table ZTEST as select rownum id, object_name from dba_objects;

SQL> create index ZTESTI on ZTEST(id);

SQL> exec dbms_stats.gather_table_stats('SYS','ZTEST');

SQL> select OWNER, INDEX_NAME, LEAF_BLOCKS, LAST_ANALYZED from DBA_INDEXES

           where OWNER = 'SYS' and INDEX_NAME = 'ZTESTI';

OWNER                          INDEX_NAME              LEAF_BLOCKS  LAST_ANALYZED

------------------------------ ------------------------------ ---------------------- -------------------

SYS                                 ZTESTI                           165                        31.07.2012 11:11:16

SQL> analyze index ZTESTI compute statistics;

SQL> select OWNER, INDEX_NAME, LEAF_BLOCKS, LAST_ANALYZED from DBA_INDEXES

           where OWNER = 'SYS' and INDEX_NAME = 'ZTESTI';

OWNER                          INDEX_NAME              LEAF_BLOCKS  LAST_ANALYZED

------------------------------ ------------------------------ ---------------------- -------------------

SYS                                 ZTESTI                           165                        31.07.2012 11:13:06

SQL> delete from ZTEST where id > 2000;

SQL> commit;

SQL> exec dbms_stats.gather_table_stats('SYS','ZTEST');

SQL> select OWNER, INDEX_NAME, LEAF_BLOCKS, LAST_ANALYZED from DBA_INDEXES

           where OWNER = 'SYS' and INDEX_NAME = 'ZTESTI';

OWNER                          INDEX_NAME              LEAF_BLOCKS  LAST_ANALYZED

------------------------------ ------------------------------ ---------------------- -------------------

SYS                                 ZTESTI                            5                            31.07.2012 11:15:48

SQL> analyze index ZTESTI compute statistics;

SQL> select OWNER, INDEX_NAME, LEAF_BLOCKS, LAST_ANALYZED from DBA_INDEXES

           where OWNER = 'SYS' and INDEX_NAME = 'ZTESTI';

OWNER                          INDEX_NAME              LEAF_BLOCKS  LAST_ANALYZED

------------------------------ ------------------------------ ---------------------- -------------------

SYS                                 ZTESTI                           165                        31.07.2012 11:16:41

As you can see there is a difference how the leaf blocks are counted between ANALYZE and DBMS_STATS. This can lead to different execution plans, if both are used at the same time.

Summary

The use of deprecated features can have a huge impact on the runtime behavior. Database internals change between the currently supported and the old methods. Be aware of that and do not simply copy and paste recommendations from the internet. There is still a need for the ANALYZE command today, but not for gathering statistics anymore.

If you have any further questions - please feel free to ask or in case of performance issues get in contact directly.

References

• Cost-Based Oracle Fundamentals by Jonathan Lewis
• Oracle Documentation of ANALYZE
• Oracle Documentation of DBMS_STATS
• Sapnote #1431798