In-depth understanding of Oracle indexes (8): How to perform Index Monitoring Analysis and Optimization

InProduction environment, we will find:

① The index tablespace I/O is very high
② The "DB file sequential read" Wait event is also relatively high
This shows that the entire database system and indexes have many read/write operations and have become the main bottleneck of the system.

The general cause is as follows:
① Indexes are used for a large number of SQL statements.
② DML operations led to a surge in indexing maintenance workload
③ Frequent DML results in many index fragments and increases I/O overhead
④ Incorrect indexing policies and full-Table Indexing

If there are 30 fields in a table, but 50 are indexes !?
As a DBA, you may not know the business logic, do not dare to delete, or do not know what to delete, what should you do?

SuggestionsComprehensiveUse the following two policies:
(1) judge based on principle

There must be many composite indexes in this situation! Based on the prefix and optionality of composite indexes
Analyze the distribution of the specific fields of the 50 indexes, and make the judgment of merging and integration by yourself.

(2) using Oracle index monitoring

Before a typical business cycle begins, execute:
Ora @ node1> alter index <index Name> monitoring usage;

After the end of a typical business cycle, execute:
Ora @ node1> alter index <index Name> nomonitoring usage;

Then, query:
Ora @ node1> select * from V $ object_usage;

In this way, you will know that this index is useful in this typical cycle.
Even if this is the case, but the friendship prompts two times, the above conclusion is not necessarily correct, because:
① 10 Gb index will be monitored when collecting statistics, which is not generated by SQL statements, but this will not happen at 11 GB.
② The foreign key index will not be monitored due to the DML operation on the master table. Do not delete the index because it is useless.

However, here, after all, there will be a problem. For a complex system, the number of indexes may be large.
So what should we do?What about selecting suspect objects and reducing monitoring scope?
The following two methods are described:
① Use library cache data

 
In the library cache, the query plan for the midstream objects in the system is stored (not all, limited by the library cache size)

Through view v $ SQL _plan, We can query the data. Using this data, we can exclude the indexes that appear in the query plan:

select a.object_owner, a.object_name  from v$sql_plan a, v$sqlarea b where a.sql_id = b.sql_id and a.object_type='INDEX' and        b.last_load_time > <START_AUDIT_DATE>

② Use AWR data

After 10 Gb, we can use AWR to analyze which indexes are used

select b.object_owner, b.object_name  from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c where a.snap_id = c.snap_id and       b.sql_id=c.sql_id and        b.object_type = 'INDEX' and        a.startup_time > <START_AUDIT_DATE>

The above method filters out most of the indexes that must be used, then comprehensively applies them, selects suspicious indexes for monitoring, finds and deletes useless indexes, and loses weight for the database.

Frequent DML operations on index fields will cause a large number of fragmentation on the index, greatly affecting the efficiency of index usage and increasing index I/O.
So how can we analyze and organize index fragmentation?

Execute the following statement to monitor the index fragmentation:

Analyze index <index Name> validate structure online;

Select name, (del_lf_rows_len/lf_rows_len) * 100 from index_stats;

The index fragmentation Rate (%) = (del_lf_rows_len/lf_rows_len) * 100
If the index fragmentation rate exceeds20%Oracle considers the index fragmentation to be very serious.

It is recommended that DBA write a script to check all index fragmentation rates, run regularly, and maintain monitoring of index fragmentation rates
Because performing index fragmentation analysis and sorting is one of the daily maintenance tasks of DBA.

There are two policies for Oracle to process index fragmentation:
① Re-Indexing

Alter index <index Name> rebuild;

② Compression index

Alter index <index Name> coalesce;

However, rocky suggests that you adopt a regular index reconstruction strategy. For example, you can rebuild indexes that are flushed online every weekend or every day or night.