Oracle Performance Analysis 9: Rebuilding indexes

When there is a problem with the index, it can cause serious performance problems, such as index unavailability, index fragmentation, and performance degradation, and we need some means to detect problems with the index and address these issues. This article will tell you how to locate the index problem and provide a solution.

Index not available

There are many reasons why an index is not available, including:
1) The index space is exhausted, causing the Sql*loader update index to fail;
2) The instance failed during the creation of the index;
3) The unique key has a duplicate value;
4) The order of an index differs from the order specified in the sorted indexes clause;
5) Move the table or table partition (ALTER TABLE move and ALTER TABLE move partition);
6) perform on-line redefinition of the table;
7) TRUNCATE TABLE partition (ALTER TABLE TRUNCATE PARTITION);
8) Import the partition;
9) Delete table partition;
10) Split table partition or sub-partition (ALTER TABLE split partition);
11) Maintenance operation of the partitioned index (ALTER INDEX split partition).
In addition to these reasons, you can manually label the index as unavailable, which makes the bulk load faster, and the following is the way to change the state of the index to unavailable:

Alter index IDX_HISTORYALARM_HOUR$01 unusable

If your index is a partitioned index, this operation will cause the indexes of all partitions to be unavailable, or you can specify that the index of a partition is not available:

ALTER index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable

You can view the status of an index by using the following method:

Select Ind. Index_name,ind.status,ind. Partitioned from User_indexes Ind where index_name like '%historyalarm% ' index_ namestatuspartitioned---------------------------------------------------------------------Idx_historyalarm_ Hour$01unusablenoidx_historyalarm$02n/ayes

As you can see, the state of the global index has become unusable, but the state of the local index is identified as N/A, and the following method allows you to view the index status of the local index in each partition:

Index_namepartition_ Namestatus------------------------------------------------------------------------------------------Idx_ historyalarm$02historyalarm20140731unusableidx_historyalarm$02historyalarm20140801usableidx_historyalarm$ 02historyalarm20140802usableidx_historyalarm$02historyalarm20140803usableidx_historyalarm$ 02historyalarm20140804usableidx_historyalarm$02historyalarm20140805usable ...

You can see that the index of the partition HISTORYALARM20140731 is already labeled unusable.
When indexes are marked as unavailable, the optimizer ignores these indexes, and Oracle no longer maintains those indexes when DML changes tables, and if you want the optimizer to use it again, you must first rebuild the (Rebuild) index.

Index fragmentation

Over time, the index can be fragmented due to a large number of deletions. Oracle documentation (performance Tuning Manual for Oracle DataBase 11.2) recommends running the "analyze...validate" statement to identify the indexes that need to be rebuilt. This action puts the statistics for the index into the Index_stats view, which is the key column in the view:
1) Height: The height of the index, starting from 1, 1 represents the index of the root only;
2) block Number (BLOCKS): The number of blocks allocated to the index;
3) Number of leaf rows (lf_rows): Number of leaves (including deleted rows);
4) Number of leaf rows deleted (del_lf_rows): The number of leaf rows entries that have not been cleaned have been deleted;
5) used Space (Used_space): The total space used within the index (including deleted entries);
6) Percent Used (pct_used): Percentage of space used within the index (including deleted entries) ... Calculation formula: (used_space/btree_space) * 100;
7) B-Tree Space (btree_space): The total size of the index (including deleted entries).
Here's an example to learn how to use the view.
First create a test table and create an index on it:

CREATE TABLE test as select RowNum ID, ' test ' text from dual connect by Level <= 100000;create index idx_test on test (ID );

Then execute the index parse statement:

Analyze index idx_test validate structure;

Note that the Index_stats view is empty before executing the parse statement, and now query the view to check the number of leaf rows that were deleted:

Select Lf_rows,lf_blks,del_lf_rows from Index_stats; Lf_rowslf_blksdel_lf_ ROWS--------------------------------------------------------------------------------1000002220

Here we can see that the number of leaf rows deleted is 0, then we delete a large number of rows in the table and run the parse statement again:

Delete test where ID <= 99999;commit;analyze index idx_test validate structure;

Then query the number of leaf rows that were deleted:

Select Lf_rows,lf_blks,del_lf_rows from Index_stats; Lf_rowslf_blksdel_lf_rows-----------------------------------------------------------10000022299999

To enable Oracle to get the correct execution plan, we first collect statistics for tables and indexes:

Begin  Dbms_stats.gather_table_stats (ownname   = user,                                tabname = ' TEST ',                                cascade   = TRUE); end;

Then perform an index range scan of the query:

SELECT * from Test where ID > 10;call count CPU Elapsed disk query current rows------          -----------------------------------------------------------------Parse 1 0.00 0.27 0        0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.01 1.53 224 0 1---------------------------------------------- -------------------------Total 3 0.01 1.81-224 0 1Misses in Libr ary Cache during Parse:1optimizer mode:all_rowsparsing user id:5 Rows Row Source operation----------------------   ------------------------------------1 TABLE ACCESS by INDEX ROWID TEST (cr=224 pr=56 pw=0 time=1538212 US) 1 INDEX RANGE SCAN idx_test (cr=223 pr=56 pw=0 time=1527442 US) (Object ID 58594) Elapsed times include waiting on following Events:event waited on                             Times Max.                        Wait Total waited----------------------------------------waited----------------------sql*net message to Client          2 0.00 0.00 Sql*net Message from Client 2 0.79 0.83 db file sequential read 56 0.03 0.41**************************************** ****************************************

The query uses an index range scan, although the entire table has only one piece of data, but because of the index fragmentation in the index, the index scan has allowed 56 blocks of data to be read. Rebuild the index below:

Alter index idx_test rebuild;

Then build and view the index information as described above:

Lf_rowslf_blksdel_lf_rows-----------------------------------110

You can see that index fragmentation disappears.

Precautions:

1) performing an index analysis locks the table until the index analysis is complete before unlocking the lock;
2) In most cases, Oracle will reuse the space of the deleted entries in the index whenever possible.

Why rebuild an index

In fact, rebuilding a (rebuild) index is a good way to recreate an index, but it is better to re-create the index than to delete the original index, because the storage space is assigned to the index when the index is rebuilt, without having to specify the index creation statement.
There are many facts about index rebuilds, but some of them are not accurate, and some of them are listed below:
1) Oracle's B-tree index becomes unbalanced over time
This is incorrect because the height between the root block of the B-tree index and all the leaf blocks is always the same.
2) The deleted space in the index cannot be reused
In fact, Oracle reuses the deleted space.
3) An index that reaches a certain number of layers is inefficient
The number of levels of the index depends on how many entries the index has, and rebuilding does not resolve the issue.
4) Indexes with poor clustering can be repaired by rebuilding
Rebuilding an index does not alter the order of rows or indexes in the table, so the clustering factor (see Using the clustered factor of the index) is completely unaffected by index rebuilds. If you want to improve the clustering factor, you actually need to rebuild the table.
So why rebuild the index exactly?

The index should be rebuilt when the index is not available, but should we rebuild the index to eliminate the index fragmentation?
When most of your queries read a single row through indexed access, rebuilding the index has little impact on performance. For a range query, however, because a large number of index fragments can cause queries to add a large amount of invalid Io, rebuilding the index makes sense, even though Oracle reuses index fragmentation, but rebuilding the index can also make the index more compact, thus increasing the efficiency of the query.

Rebuilding indexes

The above method has been used to rebuild the index:

Alter index idx_test rebuild;

However, when the index is rebuilt, the table is locked and other operations against the table are blocked until the index rebuild is complete. Starting with Oracle 10g, Oracle provides an online method for rebuilding indexes:

Alter index idx_test rebuild online;

Rebuilding an index online no longer causes an index lock.
Oracle also provides some parameters for creating and rebuilding indexes to improve efficiency in the creation and rebuilding of indexes.

Create or rebuild indexes in parallel

In order to create an index, the database needs a full table scan, parallel creation speeds up the creation of the index, and the speed is determined by the degree of parallelism and the number of CPUs:

Create INDEX idx_test on TEST (ID) parallel 4 online;

can also be used to rebuild the index:

Alter INDEX idx_test rebuild parallel 4 online;

It is important to note that this operation will make the index's degree of parallelism (see Using an index) always change to this value, as follows:

Select degree from user_indexes where index_name = ' idx_test ';D egree--------------------4

If you intend to have the database use a parallel mechanism when working with your index, then it is just right, otherwise you will need to disable parallelism after performing parallel creation and rebuilding operations:

Alter index idx_test Noparallel;

If you forget to disable parallelism, you can cause serious performance problems.

Avoid generating redo information when indexing is created or rebuilt

The time to create or rebuild an index is greatly shortened by not writing the created or rebuilt index entries to the redo log:

Create INDEX idx_test on TEST (id) nologging online;

You can also use it when rebuilding an index:

Alter index idx_test rebuild nologging online;

Nologging not only greatly improves performance, but also saves space by not populating multiple redo log files.

Compress index

Using compression in a non-unique index reduces the amount of space a duplicate key occupies:

Compress < numbers, and is less than or equal to the field values included in the index >

An example is as follows:

Create INDEX idx_test on TEST (id) Compress 1 online;

The same can be used to rebuild the index:

ALTER INDEX idx_test rebuild compress 1 online;

Oracle Performance Analysis 9: Rebuilding indexes