Plsql_ Performance Optimization Series 14_oracle Index anaylsis indexes analysis

2014-10-04 Baoxinjian

I. Summary

1. Index Quality

Index quality has a direct impact on the overall performance of the database.

Good, high-quality indexes increase the number of database performance levels, while inefficient redundant indexes make database performance slow, even with high-end hardware configurations.

Therefore, the index at the beginning of the design needs to undergo repeated testing and consideration.

For databases that have been placed in a production environment, we can also get the quality of the indexes by querying the relevant data dictionaries, and this analysis will guide how to improve the performance of the indexes.

2. Basic Guidelines for index creation

The creation of indexes should follow the principle of fine and few

Collect a variety of different combinations of all queries on the table, find columns with the best dispersion (or primary key columns, etc.) to create a single index

A composite index is created for columns that are frequently read and lack a relatively ideal discrete value

For composite indexes, consider the following factors to make a reasonable index column order, with the following priority levels being high to low as the leading column of the index, the second column, and so on

• The frequency at which the column is used
• Whether the column often uses "=" as a common query condition
• The degree of dispersion on a column
• The order in which the combined columns are often sorted
• Which columns are added as attachment columns

Ii. Case-Index and index quality on the table

1. Querying information about indexed columns on a single table

Sql>@/Home/Oracle/Sql/Idx_info.sqlenter Value forOwner:shenter Value forTable_name:salesTable                     IndexCl_nam cl_pos STATUS Idx_typ dscd------------------------- ------------------------- -------------------- ------ -------- --------------- ----SALES Sales_channel_bix channel_id1N/A BITMAPASCSales_cust_bix cust_id1N/A BITMAPASCSales_prod_bix prod_id1N/A BITMAPASCSales_promo_bix promo_id1N/A BITMAPASCSales_time_bix time_id1N/A BITMAPASC5Rows selected.

(1). From the above query results, the current table TRADE_CLIENT_TBL contains 4 indexes, it should be said that the table index has a certain redundancy.
(2). In most cases, 6-7 indexes on a single table are ideal. Too many indexes result in too much resource overhead, as well as reduced DML performance.

2. Get the Index quality information report on the specified schema or table

Sql>@/Home/Oracle/Sql/Idx_quality.sqlenter Value forInput_owner:shenter Value forInput_tbname:salesTable      Table                             IndexData blks Leaf blks clustIndexTableRows BlocksIndexSize MB perKeyPerKeyFactor Quality------------------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ -------------SALES918,843       1769Sales_prod_bix0         -         1        1,074 5-ExcellentSales_cust_bix0         5         1        *,808 5-Excellent Sales_time_bix0         1         1        1,460 5-ExcellentSales_channel_bix0         at         One            the 5-ExcellentSales_promo_bix0         -         7            Wu 5-Excellent5Rows selected.

(1). The index quality of the output from the above single table shows that there are 4 indexes at the poor level, which means that these indexes have a large clustering factor, almost close to the rows on the table.
(2). The quality of these indexes should also be combined with the frequency of use of the index to consider the need for the index to exist
(3). For clustering factors, you can only improve by re-organizing the data on the table and adjusting the order of the corresponding indexed columns

Iii. case-frequency of use of the index report

Oracle provides index monitoring features to determine if an index is being used. In Oracle 10g, the collection of statistics will cause the index to be monitored and the phenomenon no longer exists in Oracle 11g.

However, this approach only provides that the index is used. The frequency at which the index was used was not reflected.

The following script will get the index usage, a good measure of the usage of the index, and based on this value to determine whether the current indexes can be removed or improved. \

Reference to the Great God of novices

1. Determine if the index is being used

Sql>@/Home/Oracle/Sql/Idx_usage_detail.sql SH1                                                                                    IndexTableNameIndexNameIndexType Size MBIndexoperation Executions------------------------------ ------------------------------ --------------- ----------- --------------------- -- --------COSTS Costs_prod_bix BITMAP1.75        -                       0Costs_time_bix BITMAP1.75        -                       0****************************** ****************************** *************** -----------                       ----------sum                                                                                  3.50                                0SALES Sales_channel_bix BITMAP1.75        -                       0Sales_cust_bix BITMAP5.69  SingleVALUE2FAST FullSCAN1Sales_prod_bix BITMAP1.75  SingleVALUE3FAST FullSCAN1Sales_promo_bix BITMAP1.75  FullSCAN1Sales_time_bix BITMAP1.94        -                       0****************************** ****************************** *************** -----------                       ----------sum                                                                                 20.31                                89Rows selected.

(1). The above results list the usage frequency of an index with a schema of SH in the current database and an index size greater than 1MB.

(2). Because the current database is standard, there is no partition table function, so you can see a lot of arc-ending tables, and the index is large, such as the index on Acc_pos_stock_tbl_arc up to 19G.

(3). Table Sales Primary key Sales_prod_bix the largest range scan, the total is used 3 times.

(4). For those indexes listed above with a number of 0 used, consider whether the index is set up reasonably.

(5). Indexes that are too large should consider the ability to use index compression.

(6). The last listed is the schema name of the report and the filter criteria for the index size, and the date the index was collected. Note that the size of the index column sum sums some inaccuracies.

2. Summary

This uses 2 substitution variables, one schema and one index size.

By default, historical execution plans for smaller indexes and SQL statements that run only one to two times are not collected to Dba_hist_sql_plan.

Therefore, the recommended value for index size input when executing the script is 100.

If you need to collect all the historical SQL execution plans to determine if the index is being used, you need to modify the statistics_level to all or modify the snapshot collection strategy.

The collection strategy has a certain impact on system performance and consumes a lot of disk space, so the PROD environment should be used with caution (UAT and Dev).

Script download (organized by novices, borrowed)

1. Idx_info.sql Http://files.cnblogs.com/eastsea/idx_info.zip

2. Idx_quality.sql Http://files.cnblogs.com/eastsea/idx_quality.zip

3. Idx_usage_detail.sql Http://files.cnblogs.com/eastsea/idx_usage_detail.zip

Reference: The novices great god http://blog.csdn.net/leshami/article/details/23687137

Plsql_ Performance Optimization Series 14_oracle Index anaylsis indexes analysis