Monday, February 22, 2010

Understanding SKIP_UNUSABLE_INDEXES Initialization Parameter

Recently there was question on Technet discussion forum where the OP complained that the Optimizer is ignoring Index for a particular query:

Need to understand why optimizer unable to use index in case of "OR" whenn we set one partition index to unusable, the same query with between uses index.

He created a partitioned table with a local index and one on the partitioned index was set to UNUSABLE state. Later, when a query is executed with Optimizer refuses to use the index.

Let’s create the table and the index:

SQL>  CREATE TABLE t (
  2     id NUMBER NOT NULL,
  3     d DATE NOT NULL,
  4     n NUMBER NOT NULL,
  5     pad VARCHAR2(4000) NOT NULL
  6   )
  7   PARTITION BY RANGE (d) (
  8     PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9     PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10     PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11     PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12     PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13     PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14     PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15     PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16     PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17     PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18     PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19     PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20   );

Table created.

SQL>
SQL>  INSERT INTO t
  2   SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3   FROM dual
  4   CONNECT BY level <= 100000;

100000 rows created.

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

Index created.

SQL>
SQL>  execute dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>

Now mark the last index partition as UNUSABLE.

SQL> ALTER INDEX i MODIFY PARTITION t_dec_2009 UNUSABLE;

Index altered.
Now execute the query in question:

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
              "D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>
SQL>

From the execution plan, it’s clear that the Optimizer is performing a FULL TABLE SCAN on the partitions in question (t_jan_2009 & t_feb_2009).

By default starting from Oracle 10g, SKIP_UNUSABLE_INDEXES initialization parameter is set to TRUE. This means, ignore index any index or index partition with UNUSABLE state. Turning this to FALSE enables index usage when possible. Index usage was ignored by the above query as one of the index partitions is in UNUSABLE state.

Let’s disable (FALSE) this parameter and run the same query:

SQL>
SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 3795917108

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |     8 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE          |      |     1 |     8 |            |          |       |       |
|   2 |   CONCATENATION          |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|   5 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter(LNNVL("D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
              LNNVL("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss')))

SQL>
SQL> set autotrace off
SQL>

There you go; Optimizer is now picking the index. As we have disabled SKIP_UNUSABLE_INDEXES parameter at the session level, all the index partitions except the one in the UNUSABLE state will be used by the optimizer when appropriate.

When we try to access the last partition we will receive an error as shown below as the index is in UNUSABLE state.


SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );
SELECT count(d)
*
ERROR at line 1:
ORA-01502: index 'TEST.I' or partition of such index is in unusable state

SQL>

Enabling SKIP_UNUSABLE_INDEXES will cause the query to complete successfully instead of failing.

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D">=TO_DATE(' 2009-12-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-12-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>

If you look at the 10053 trace when SKIP_UNUSABLE_INDEXES is set to TRUE, you will notice that the optimizer tends to ignore index access path and simply reports that the index is in UNUSABLE state. Below is an excerpt of 10053 trace.


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T  (Using composite stats)
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 100000  #Blks:  2928  AvgRowLen:  116.00
  PARTITIONS::
  PRUNED: 2
  ANALYZED: 2  UNANALYZED: 0
    #Rows: 100000  #Blks:  488  AvgRowLen:  116.00
Index Stats::
  Index: I  Col#: 2
    USING COMPOSITE STATS
    LVLS: 1  #LB: 270  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 1696.00
    UNUSABLE
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): D(DATE)
    AvgLen: 8.00 NDV: 100000 Nulls: 0 Density: 1.0000e-005 Min: 2454833 Max: 2455198
  Table:  T  Alias: T     
    Card: Original: 100000  Rounded: 27  Computed: 26.84  Non Adjusted: 26.84
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  135.09  Resp: 135.09  Degree: 0
      Cost_io: 134.00  Cost_cpu: 8050409
      Resp_io: 134.00  Resp_cpu: 8050409
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 135.09  Degree: 1  Resp: 135.09  Card: 26.84  Bytes: 0

7 comments:

Rafi said...

Hi Asif,
If we set SKIP_UNUSABLE_INDEXES=FALSE will it create any overhead?

Will it work for all kinds of tables?
I mean partitioned or non partitioned table.

Thanks,
Rafi.

Asif Momen said...

Rafi,

There is no overhead when you set it to TRUE/FALSE.

Yes, it works for both partitioned and non-partitioned tables.

Rafi said...

Thanks for clarifying Asif.Just one more question.So its good to set SKIP_UNUSABLE_INDEXES=FALSE ?


Best regards,
Rafi.

Asif Momen said...

Rafi,

You have to understand the pros & cons of setting SKIP_UNUSABLE_INDEXES to TRUE/FALSE.

When it is set to "TRUE":
1) Your end-users will NOT receive errors if underlying indexes are in UNUSABLE state. They continue to work.
2) Execution plan of the queries will change (as index will not be used) and might lead to degraded performance.

When it is set to "FALSE":
1) Your end-users will receive errors if underlying indexes are in UNUSABLE state. They CANNOT continue to work.

Setting this parameter to TRUE/FALSE depends on the nature of the database.

Rafi said...

Thanks very much Asif.I got it.



Best regards,
Rafi

mohamed said...

Setting index in an usable state should be for a short period like during insert of a huge volume of data.

But keep in mind that even in this case you can not set indexes that are used to enforce uniqueness in an usable state. When you insert into such kind of indexes you will be faced to the
ORA-01502: index 'I' or partition of such index is in unusable state
even if you set your session to skip unusable indexes

Regards

Mohamed Houri

Asif Momen said...

@mohamed

Thanks for the valuable information.

This post mainly addresses the impact of this parameter on SELECT statements.