Oracle Performance Analysis 9: Re-Indexing

Oracle Performance Analysis 9: Re-Indexing

When an index is faulty, it may cause serious performance problems. index problems include index unavailability and index fragmentation leading to performance degradation. We need some measures to detect the index problems, and solve these problems. This article describes how to locate the index problem and provides a solution.

Index unavailable

The index is unavailable for many reasons, including:
1) The index space is exhausted, causing SQL * Loader to fail to update the index;
2) The instance fails to be created during index creation;
3) unique keys have duplicate values;
4) The order of an index is different from that specified in the sorted indexes clause;
5) move a table or table partition (alter table move and alter table move partition );
6) execute online redefinition on the table;
7) alter table truncate partition );
8) Import partitions;
9) Delete table partitions;
10) alter table split partition );
11) alter index split partition ).
In addition to the preceding reasons, you can manually mark indexes as unavailable to accelerate batch loading. The following describes how to change the index status to unavailable:

alter index IDX_HISTORYALARM_HOUR$01 unusable

If your index is a partition index, this operation will make the indexes of all partitions unavailable. You can also specify that the index of a partition is unavailable:

alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable

You can view the index status 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 global index status has changed to UNUSABLE, but the local index status is identified as N/A. You can view the local index status in each partition using the following method:

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 has been marked as UNUSABLE.
When an index is marked as unavailable, the optimizer ignores these indexes, and Oracle does not maintain these indexes when changing the table in DML. If you want the optimizer to use it again, you must first rebuild the index.

Index fragmentation

Over time, due to a large number of delete operations, indexes may produce fragments. We recommend that you run "analyze..." in the Oracle Performance Tuning Manual for Oracle DataBase 11.2... the validate statement is used to identify the index to be rebuilt. This operation puts the index statistics in the INDEX_STATS view. The following are the key columns in the View:
1) HEIGHT: The index HEIGHT. Starting from 1, 1 indicates that there is only a root index;
2) block count (BLOCKS): number of BLOCKS allocated to the index;
3) Number of leaf rows (LF_ROWS): number of leaf rows (including deleted rows );
4) Number of deleted leaf lines (DEL_LF_ROWS): number of leaf row lines that have not been cleared;
5) used space (USED_SPACE): total space used in the index (including deleted entries );
6) percentage used (PCT_USED): Percentage of space used in 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 ).
The following is an example to learn how to use this view.
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 Analysis Statement:

analyze index idx_test validate structure;

Note that the INDEX_STATS view is empty before the analysis statement is executed. query this view to check the number of lines 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 deleted leaf rows is 0. Next we will delete a large number of rows in the table and run the analysis statement again:

delete test where id <= 99999;commit;analyze index idx_test validate structure;

Then query the number of deleted leaf rows:

select lf_rows,lf_blks,del_lf_rows from index_stats;LF_ROWSLF_BLKSDEL_LF_ROWS-----------------------------------------------------------10000022299999

To make Oracle get the correct execution plan, we first collect the statistics of tables and indexes:

begin  dbms_stats.gather_table_stats(ownname   => user,                                tabname => 'TEST',                                cascade   => TRUE);end;

Then execute an index range scan 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         56        224          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        3      0.01       1.81         56        224          0           1Misses in library 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 the index range scan. Although the entire table only contains one piece of data, the index scan still reads 56 data blocks due to index fragmentation. Rebuild the index as follows:

alter index IDX_TEST rebuild;

Then generate and view the index information according to the above method:

LF_ROWSLF_BLKSDEL_LF_ROWS-----------------------------------110

The index fragmentation disappears.

Note:

1) Executing Index Analysis locks the table until the index analysis is complete;
2) In most cases, Oracle will try its best to reuse the deleted items in the index.

Why re-Indexing

In fact, rebuilding an index is to re-create an index, but it is better than deleting the original index and then re-create an index, because the storage space has been allocated to the index during re-indexing, you do not need to specify the index creation statement.
There are many references for index reconstruction, but some of them are not accurate. The following lists some of them:
1) Oracle B-tree indexes become unbalanced over time
This is incorrect because the height of the root block of the B-tree index is consistent with that of all leaf blocks.
2) The deleted space in the index cannot be reused.
In fact, Oracle will reuse the deleted space.
3) it is inefficient to reach a certain number of layers of indexes.
The number of indexes depends on the number of indexes. Rebuilding cannot solve the problem.
4) An index with a bad clustering factor can be repaired through reconstruction.
Re-indexing does not change the order of data rows or indexes in the Table. Therefore, the clustered factor (see the clustered factor used for indexing) is not affected by index reconstruction. To improve the clustering factor, you actually need to recreate the table.
So why re-indexing?

When the index is unavailable, the index should be re-built. But should we re-build the index to eliminate the index fragmentation?
When most of your queries read a single row through index access, re-indexing has little impact on performance. However, for a range query, because a large number of index fragments will cause a large number of invalid IO queries, re-indexing makes sense, even if Oracle will reuse index fragments, however, re-indexing can also make the index more compact, thus improving the query efficiency.

Re-Indexing

The method for re-indexing has been used above:

alter index IDX_TEST rebuild;

However, during the index reconstruction process, the table will be locked to prevent other operations on the table until the index reconstruction is completed. Starting from Oracle 10 Gb, Oracle provides a method for rebuilding indexes online:

<pre class="sql" name="code">alter index IDX_TEST rebuild online;

Rebuilding indexes online will no longer cause index locking.

To re-create a partition index, you must include the partition information:

alter index IDX_TEST rebuild partition partition_name online;

Oracle also provides some parameters for creating and recreating indexes to improve the efficiency of creating and recreating indexes.

Create or recreate indexes in parallel

To create an index, the database needs to perform a full table scan. parallel creation can speed up index creation. The speed increase is determined by the degree of parallelism and the number of CPUs:

create index IDX_TEST on test(id) parallel 4 online;

It can also be used to re-create an index:

alter index IDX_TEST rebuild parallel 4 online;

Note that this operation will change the degree of parallelism of the index (see using the index) to this value forever, as shown below:

select degree from user_indexes where index_name = 'IDX_TEST';DEGREE--------------------4

If you want to enable the database to use the parallel mechanism when processing your index, this is exactly the case. Otherwise, you need to disable the parallel mechanism after performing the parallel creation and reconstruction operations:

alter index IDX_TEST noparallel;

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

Avoid generating redo information when creating or recreating an index

If you do not write the created or rebuilt index items into the redo log, you can greatly shorten the index creation or reconstruction time:

create index IDX_TEST on test(id) nologging online;

You can also use the following when re-indexing:

alter index IDX_TEST rebuild nologging online;

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

Compress Indexes

Using compression in non-unique indexes can reduce the space occupied by duplicate keys:

Compress <number, less than or equal to the value of the field included in the index>

An example is as follows:

create index IDX_TEST on test(id) compress 1 online;

It can also be used to re-create an index:

alter index IDX_TEST rebuild compress 1 online;