Index performance Issue - When an Index Should be Rebuilt?

Index performance Issue - When an Index Should be Rebuilt?

When an Index Should be Rebuilt?

Applies to Oracle Server - Enterprise Edition - Version: 8.1.7.3 to 11.2.0.3 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

-- Drilling:

1- Find indexes having height(blevel+1) > 4
i.e. Indexes having BLEVEL > 3
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3

2- Analyze indexes to find ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20 by "analyzing the index with validate structure option" and then:
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

*** Please note that the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.

-- Solution:

See this example:
SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index.

-- Drilling
-- Find hight of the Index

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME         HEIGHT     LF_ROWS     LF_BLKS     DEL_LF_ROWS
------------     ----------    ----------         -----------         -------------
TEST_INDX     8     938752         29575         73342

You can see height of the index is 8 and also high number of DEL_LF_ROWS.
SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';

COUNT(*)
----------
39700
Elapsed: 00:00:27.25

-- Solution:
Now you rebuild the indexes

SQL> alter index TEST_INDX rebuild;
Index altered.

-- Re-drill again
SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;

NAME         HEIGHT     LF_ROWS     LF_BLKS     DEL_LF_ROWS
---------------    ------------    --------------     ----------         ----------
TEST_INDX     4     865410         15434         0

This clearly indicates the rebuilt helped query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0.

Most important thing, when Index is unusable state, always rebuild the index. When table row movement is on, then usually index will be unusable state. So rebuild the index.


Conditions for Rebuilds :
 
Large free space (generally 50%+), which indexes rarely reach, and Large selectivity, which most index accesses never reach, and
Response times are adversely affected, which rarely are.
Note requirement of some free space anyways to avoid insert and subsequent free space issues


Benefit of rebuild based on various dependencies which include:
 
- Size of index
- Clustering Factor
- Caching characteristics
- Frequency of index accesses
- Selectivity (cardinality) of index accesses
- Range of selectivity (random or specific range)
- Efficiency of dependent SQL
- Fragmentation characteristics (does it effect portion of index frequently used)
- I/O characteristics of index (serve contention or I/O bottlenecks)
- The list goes on and on ....


Myths:

• The vast majority of indexes do not require rebuilding
• Oracle B-tree indexes can become "unbalanced" and need to be rebuilt is a myth
• Deleted space in an index is "deadwood" and over time requires the index to be rebuilt is a myth
• If an index reaches "x" number of levels, it becomes inefficient and requires the index to be rebuilt is a myth
• If an index has a poor clustering factor, the index needs to be rebuilt is a myth
• To improve performance, indexes need to be regularly rebuilt is a myth

12c Enhanced Online Index DDL Operations 

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX UNUSABLE ONLINE
So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

SQL> create table radiohead (id number, code number, name varchar2(30));

Table created.

SQL> insert into radiohead select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_code_i on radiohead(code);

Index created.

If we now insert a new row in one session but not commit:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

And then attempt any of the following DDL commands in another session:

SQL> drop index radiohead_code_i;

drop index radiohead_code_i

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> alter index radiohead_code_i invisible;

alter index radiohead_code_i invisible

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> alter index radiohead_code_i unusable;

alter index radiohead_code_i unusable

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

They all get the well-known “ORA-00054: resource busy” error.
If on the other hand, one of these DDL statements is already running in a session:

SQL> alter index radiohead_code_i unusable;


All DML statements in other sessions will hang until the DDL completes:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

Once the index is finally made unusable:

SQL> alter index radiohead_code_i unusable;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                     STATUS

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

RADIOHEAD_CODE_I               UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

no rows selected

 

We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.


So these commands prior to the Oracle 12c Database previously had locking related issues.
If we now perform the same setup in 12c and again have an outstanding transaction in a session:

SQL> drop index radiohead_code_i online;

The Drop Index command doesn’t now get the Ora-00054: resource busy, but rather hangs until all prior transactions complete.

However, while the Drop Index command hangs, it doesn’t in turn lock out transactions within other sessions. In another session:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

1 row created.

And in yet other session:

SQL> delete radiohead where id = 42;

1 row deleted.

SQL> commit;

Commit complete.

These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.

SQL> drop index radiohead_code_i online;

Index dropped.

Another more subtle difference in behaviour with 12c. If there’s an existing transaction when you decide to make an index unusable:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

SQL> alter index radiohead_code_i unusable online;

As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:

SQL> commit;

Commit complete.

SQL> alter index radiohead_code_i unusable online;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                STATUS

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

RADIOHEAD_CODE_I          UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

SEGMENT_NAME         BLOCKS    EXTENTS

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

RADIOHEAD_CODE_I       2176         32

We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.