Striving for Optimal Performance

Few weeks ago the Chinese Edition of my book, Troubleshooting Oracle Performance, was published by China-Pub! Honestly, this is something I did not expect when publishing the book.

Even though I still have to touch one of them myself, let me spend few words about it…

I still remember when I saw for the first time part of my book translated in Chinese. I was looking at the news provided to me by Google Alerts when, among the other links, there was a link related to “Troubleshooting Oracle Performance” (this is one of the terms I monitor through Google Alerts) pointing to a Chinese page. I opened the referenced page and, it goes without saying, I was not able to read it. I just recognized the cover of my book and its name. So, I put the first part of the text in yet another Google application to let translate it to English. I immediately understood what it was all about. It was part of chapter 1 of my book. Amazing!

Since the book is now available, it means that the translators managed to finish their work. I know for sure that it was a huge undertaking and, therefore, I would like to thank David Feng, Jame Tong, Yiwen Hu and Yi Zhu for doing it. I know that they spent a considerable amount of their valuable time to translate the text. Thanks to them, more people will be able to learn something about Oracle Database, how to use it efficiently and how to approach performance problems. And that is something that makes me proud.

In case you are interested in it, click here.

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of statistics is performed only when this clause is specified. As of Oracle Database 10g, whenever statistics are not locked, their gathering is done by default, which means the COMPUTE STATISTICS clause is deprecated and available for backward compatibility only.

Unfortunately, CREATE INDEX does not gather global statistics. As a result, whenever you are creating partitioned indexes, the global statistics might be inaccurate. Let me show you an example:

• Create partitioned table, insert data (notice that the number of distinct values is equal to the number of rows) and create a local index

SQL> CREATE TABLE t (n1 number, n2 number)
  2  PARTITION BY RANGE (n1) (
  3    PARTITION p1 VALUES LESS THAN (11),
  4    PARTITION p2 VALUES LESS THAN (21)
  5  );

Table created.

SQL> INSERT INTO t
  2  SELECT rownum, rownum
  3  FROM dual
  4  CONNECT BY level <= 20;

20 rows created.

SQL> CREATE INDEX i ON t (n2) LOCAL;

Index created.

• The CREATE INDEX statement gathered the statistics for the index; let’s check them…

SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               NO                      10
P1             NO                      10
P2             NO                      10

As you can see 1) the number of distinct keys at the global level is wrong; it should be 20! 2) the GLOBAL_STATS column at the index level is set to NO. As a result, when you create a partitioned index, you should manually gather the global index statistics straight after. In other words, you should do the following:

• Manually gather global level index statistics

SQL> execute dbms_stats.gather_index_stats(ownname=>user, indname=>'i', granularity=>'global')

PL/SQL procedure successfully completed.

• Check whether the index statistics are accurate

SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               YES                     20
P1             NO                      10
P2             NO                      10

There are situations, however, where it is not necessary to manually gather the global index statistics. For example, when the index is prefixed. But, as a general rule, I would not rely on the automatically gathered statistics for partitioned indexes.