jump to navigation

An Index or Disaster, You Choose (It’s The End Of The World As We Know It) October 30, 2015

Posted by Richard Foote in Oracle Indexes, skip_unusable_indexes.
trackback

This come up recently with a customer and I saw a related question on OTN that made me realize I haven’t previously discussed the SKIP_UNUSABLE_INDEXES parameter.

Let me start by making a very simple point. All it potentially takes to make your entire database effectively inoperable or indeed to maybe bring down a RAC instance is for one teeny weeny index to become unusable. That’s it.

If the unusable/missing index is used often enough on a table that is big enough, the resultant explosion of load on the database generate by 10,000s of (say) massive Full Table Scans can be catastrophic on all users of the database (or indeed on all users of the disk subsystem or database servers as in the case of my customer).

Sometimes the lesser evil would be to simply ensure the statement(s) that were reliant on the now unusable index are prevented from executing and causing general havoc, so that all other database activities can continue unabated.

Hence why we have the SKIP_UNUSABLE_INDEXES parameter. When set to TRUE (the default), the CBO simply ignores the fact there’s an index in an unusable state it would have used and chooses the next best available plan (which might be plan horrendous, such as a FTS on a massive table).

If however SKIP_UNUSABLE_INDEXES is set to FALSE, then an error is generated if an index the CBO would have used is in an unusable state. Yes, such statements don’t now run causing possible frustration for some end users, but at least the “damage” is minimized to just those users, with the rest of the database community blissfully unaware and non-impacted by the unusable index.

To illustrate, a simple little example. Let’s just create a table and populate it with 1M rows. Note that the CODE column has 100,000 distinct values, so 10 rows returned for each value.

SQL> create table ziggy (id number, code number, name varchar2(42), description varchar(42));

Table created.

SQL> insert into ziggy select rownum, mod(rownum,100000), 'ZIGGY STARDUST', 'AND THE SPIDERS FROM MARS' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

Let’s create an index on this column as this would be a really cheap way to find a particular CODE of interest:

SQL> create index ziggy_code_i on ziggy(code);

Index created.

So a query that just selects a CODE of interest is going to be nice and efficient:

SQL> select * from ziggy where code=42;

10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3294205578

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   510 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZIGGY        |    10 |   510 |    13   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ZIGGY_CODE_I |    10 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
14  consistent gets
0  physical reads
0  redo size
1325  bytes sent via SQL*Net to client
551  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
10  rows processed

The index is used as expected and at just 14 consistent gets, it burns little CPU and performs no physical IOs.

If however the index were to become unusable …

SQL> alter index ziggy_code_i unusable;

Index altered.

And we now re-run the query:

SQL> select * from ziggy where code=42;

10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2421001569

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    10 |   510 |  2122   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ZIGGY |    10 |   510 |  2122   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("CODE"=42)
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
7765  consistent gets
7760  physical reads
0  redo size
965  bytes sent via SQL*Net to client
551  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
10  rows processed

We can see the query is now using the next best execution plan involving a FTS on our “large” table, which is performing many more consistent gets and physical IOs. If this query (or others also dependent on the now unusable index) were executed frequently enough and if the table was big enough, the resultant increase in both CPU and IO could be crippling to the entire database environment(s).

Rather than continually running this now extremely costly query, perhaps the better alternative is to prevent this query from running at all.

By changing the SKIP_UNUSABLE_INDEXES parameter to FALSE:

SQL> alter system set skip_unusable_indexes=false;

System altered.

If we try now to execute this query:

SQL> select * from ziggy where code=42;
select * from ziggy where code=42
*
ERROR at line 1:
ORA-01502: index 'BOWIE.ZIGGY_CODE_I' or partition of such index is in unusable
state

We get our beloved (in this scenario) ORA-01502 error. We now prevent 10,000s of expensive FTS and the general database remains unaffected.

So you have the choice in what to do here. In the event of an unusable index, is there a sufficiently “good enough” alternate option that makes executing statements impacted by the unusable index viable or is the resultant impact on the database just too much and preventing such statements from executing at all the  better option.

It’s potentially a quick fix for a dire database emergency.

Comments»

1. Dom Brooks - October 30, 2015

One interesting topic of discussion is that skip_unusable_indexes is ignored if there is a hint for the unusable index (don’t just think manual hinting but consider sql profiles and sql plan baselines as well).
https://orastory.wordpress.com/2014/03/13/an-example-of-where-the-optimizer-should-ignore-a-hint/

Richard Foote - November 2, 2015

Hi Dom

Yes, but I think there’s a reasonable argument for this behavior. If you have an explicit hint that say you shall use this index, and there is such an index but it’s currently unusable, then for the “protection” of the database, it kinda makes sense that the SQL fails. Some for a profile/baseline, this is the “approved” behavior you want “hard-coded”, the use of this specific index and if the index is currently unusable, then fail rather than potentially disrupt the whole database.

If you don’t want this behavior and you want the SQL to run regardless of the index/partition being unusable, then the get out of jail card is to make the index invisible.

I fully understand though why some might regard this as odd behavior.

2. Log Buffer #447: A Carnival of the Vanities for DBAs | InsideMySQL - November 3, 2015

[…] Index or Disaster, You Choose (It’s The End Of The World As We Know […]


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: