Oracle mechanics

Igor Usoltsev's Oracle blog

Unusable index, hint INDEX and updated RBO in Oracle 11.2

Posted by Игорь Усольцев on June 8, 2011

Oracle shows some interesting things when the hinted query runs against the table with unusable index.

Until the query does not include hints, we can see the expected behavior – optimizer chooses INDEX RANGE SCAN when index status is VALID, and TABLE FULL SCAN when index is UNUSABLE:

10.2.0.4.0#SQL> select count(rowid) from t where owner = 'OUTLN';
 ---------------------------------------------------------------------------
 | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
 |   1 |  SORT AGGREGATE   |       |     1 |    18 |            |          |
 |*  2 |   INDEX RANGE SCAN| T_IDX |    80 |  1440 |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------

10.2.0.4.0#SQL> alter index t_idx unusable;

Index altered.

10.2.0.4.0#SQL> select count(rowid) from t where owner = 'OUTLN';
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |       |       |    12 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    18 |            |          |
 |*  2 |   TABLE ACCESS FULL| T    |    80 |  1440 |    12   (0)| 00:00:01 |
 ---------------------------------------------------------------------------

As expected the 10053 trace records clearly explain the optimizer choise:

BASE STATISTICAL INFORMATION
 ***********************
 ...
 Index Stats::
 Index: T_IDX  Col#: 1
 LVLS: 1  #LB: 4  #DK: 20  LB/K: 1.00  DB/K: 5.00  CLUF: 116.00
 UNUSABLE

i.e. when index is UNUSABLE – INDEX SCAN is unavailable

If the query uses hint INDEX we get error in both Oracle 10.2 and 11.2:

10.2.0.4.0#SQL> select/*+ index(t t_idx) */ count(rowid) from t where owner = 'OUTLN';
select/*+ index(t t_idx) */ count(rowid) from t where owner = 'OUTLN'
*
ERROR at line 1:
ORA-01502: index 'SYSTEM.T_IDX' or partition of such index is in unusable state

despite of parameter skip_unusable_indexes = TRUE (default value for both 10.2 and 11.2). According to Oracle documentation this …

Disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions

Some additional info in 10053 trace:

Index Stats::
Index: T_IDX  Col#: 1
LVLS: 1  #LB: 4  #DK: 20  LB/K: 1.00  DB/K: 5.00  CLUF: 116.00
User hint to use this index

...

Best:: AccessPath: IndexRange  Index: T_IDX

This looks very strange to me: CBO tries hard to use unusable index and considers neither current index status nor other available access pathes!

It happens the same way when the RULE hint is used in query (optimizer_mode=RULE), but only in Oracle 10.2:

10.2.0.4.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';
select/*+ rule*/ count(rowid) from t where owner = 'OUTLN'
*
ERROR at line 1:
ORA-01502: index 'SYSTEM.T_IDX' or partition of such index is in unusable state

The last case is more explainable: RULE mode is not supported for a long time, and may be there are some strict “rules” (to try to use any existing indexes, for example)? and so on…

But when we run the last test (with RULE hint) in Oracle 11.2 – we get no errors:

11.2.0.1.0#SQL> select status from dba_indexes where index_name = 'T_IDX';

STATUS
--------
VALID

11.2.0.1.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T_IDX |
-----------------------------------

11.2.0.1.0#SQL> alter index t_idx unusable;

Index altered.

11.2.0.1.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T    |
-----------------------------------

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

2 - filter("OWNER"='OUTLN')

Note
-----
- rule based optimizer used (consider using cbo)

And even more – 10053 trace (the CBO trace, AFAIK) shows some OUTLINE hint section with RBO_OUTLINE comment (when using optimizer_mode=RULE session parameter only, without quiery hint in query – don’t know why)

  /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
RBO_OUTLINE
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))  or  FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/

And some Query Transformations section: most of operations are bypassed like this one

Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): rule-based mode.

But some operations – not

Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.

It looks like RBO (rule based optimizer mechanism) was updated and became smarter in Oracle 11g R2. It seems that updated RBO can correctly estimate the index status and choose available access path despite of any “rules”, and even can use some types of CBO Query Transformations, isn’t it?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: