How Oracle carries out index monitoring analysis and optimization

in the production environment, we will find:

① Index Table Space I/O is very high
② "DB file sequential read" Wait event is also higher
This indicates that the whole database system, index read and write operation is more, has become the main bottleneck of the system

The general reasons are as follows:
① large numbers of SQL are indexed
②DML operation causes an explosion in index maintenance effort
③ frequent DML causes many index fragments, increasing I/O overhead
④ indexing strategy error, walk index as full table sweep

If, a table field 30, but the index has 50!?
As a DBA, you may not understand the business logic, do not dare to delete, do not know what to delete, what to do?

SuggestionsIntegratedThe following two strategies are used:
⑴ According to the principle to Judge

There must be many composite indexes in this situation! Based on the two principles of prefix and optional of compound index
Analyze the distribution of the specific fields of the 50 indexes, make a merger, and integrate the judgment

⑵ Leveraging Oracle index monitoring features

Before the typical business cycle begins, execute:
[Email protected]> ALTER INDEX < index name > monitoring usage;

After the typical business cycle ends, execute:
[Email protected]> ALTER INDEX < index name > nomonitoring usage;

Then, query:
[Email protected]> select * from V$object_usage;

That way, you know that in this typical cycle, the index is actually useful for wood.
This is the case, but, the friendship hints two, the above conclusion is not necessarily correct, because:
①10G will cause the index to be monitored when collecting statistics, which is not generated by SQL statements, but not in 11g.
The ② foreign key index will not be monitored because of the DML operation of the primary table, and should not be deleted because the index is useless.

However, here, after all, there will be a problem, for a complex system, the number of indexes may be large
So, how do weSelect suspects, reduce the scope of surveillance?
Two methods are described below:
① using the library cache data

In the library cache, the query plan (not all of which is limited by the size of the library cache) is stored in the system's midstream cursor.

With view V$sql_plan, we can query this data. With this data, we can exclude those indexes that appear in the query plan:

[Plain]View Plaincopyprint?

1. Select A.object_owner, A.object_name
2. From V$sql_plan A, V$sqlarea b
3. where a.sql_id = b.sql_id and a.object_type= ' INDEX ' and
4. B.last_load_time > <START_AUDIT_DATE>

② using AWR data

After 10g, we can use AWR to analyze which indexes are used in the

[Plain]View Plaincopyprint?

1. Select B.object_owner, B.object_name
2. From Dba_hist_snapshot A, Dba_hist_sql_plan B, Dba_hist_sqlstat c
3. where a.snap_id = c.snap_id and
4. B.SQL_ID=C.SQL_ID and
5. B.object_type = ' INDEX ' and
6. A.startup_time > <START_AUDIT_DATE>

Using the above method, filter out the most definitely used index, then comprehensive application, select suspicious index to monitor, find and delete useless index, lose weight for database

Frequent DML operations on indexed fields, resulting in significant fragmentation of indexes, greatly affecting the efficiency of indexing and increasing index I/O
So how to do index fragmentation analysis and collation?

Execute the following statement to monitor the fragmentation of the index:

Analyze index < indexed name > validate structure online;

Select Name, (Del_lf_rows_len/lf_rows_len) *100 from Index_stats;

Where, index fragmentation rate (%) = (Del_lf_rows_len/lf_rows_len) *100
If the index fragmentation rate exceeds20%, Oracle considers index fragmentation to be very serious

It is recommended that the DBA write a script that detects all index fragmentation rates, runs periodically, and maintains monitoring of the index fragmentation rate
Because, index fragmentation analysis and collation is one of the tasks of DBA routine maintenance

Oracle's processing of index fragmentation consists of two strategies:
① Rebuilding an index

Alter INDEX < index name > rebuild;

② Compression Index

Alter INDEX < index name > coalesce;

However, Rocky recommends that you take regular index reconstruction strategies, such as online rebuilds of the published index every weekend or night.

This article transferred from: http://blog.csdn.net/dba_waterbin/article/details/8805010

How Oracle carries out index monitoring analysis and optimization