Oracle index usage script sharing, oracle Index

Oracle index usage script sharing, oracle Index

Oracle provides the index monitoring feature to determine whether an index is used. In Oracle 10 Gb, the index is monitored when statistical information is collected. In Oracle 11 GB, this phenomenon no longer exists. However, this method only provides whether the index is used. The frequency of indexing usage cannot be reflected. The following script will get the index usage, which can well measure the index usage and determine whether these indexes can be removed or improved based on the value.

1. Index usage frequency report

-- Running environment SQL> select * from v $ version where rownum <2; BANNER indexing Oracle Database 10g Release 10.2.0.3.0-64bit Production -- Obtain the current Database index usage frequency SQL> @ idx_usage_detail.sqlEnter value for 1: GO_ADMINEnter value for 2: 100 IndexTable name Index type Size MB Index operation Executions quota exceed ------------ ----------- ----------------------- ---------- average bytes NORMAL 3,328.00 range scan 99 sample fast full scan 8 unique scan 3 skip scan 2 **** **************************************** * ************************* ------------- ---------- sum 13,312.00 distinct acc_pos_cash_tbl_arc PK_ACC_POS_CASH_ARCH_TBL NORMAL 2,560.00 range scan 168 UNIQUE SCAN 14 sample fast full scan 12 skip scan 1 ****************************** **************************************** ** ----------- ---------- sum 10,240.00 seconds ACC_HIST_TRANS_DATE_IDX NORMAL 384.00 range scan 917 skip scan 210 sample fast full scan 4 fast full scan 1 PK_ACC_POS_HIST_TBL NORMAL 192.00 unique scan 7 sample fast full scan 3 TRANS_NUM_IDX NORMAL 232.00 RANGE SCAN 41 sample fast full scan 3 fast full scan 1 ***************************** **************************************** * ** ----------- ---------- sum 2,616.00 1, 187ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX FUNCTION-2,622.00 range scan 59 based normal sample fast full scan 4 fast full scan 2 PK_ACC_POS_INT_TBL NORMAL 2,496.00 range scan 65 fast full scan 53 unique scan 14 skip scan 13 sample full scan 1 **************************************** * ***************************** ---------------- sum 20,346.00 211ACC_POS_STOCK_TBL_ARC PK_ACC_POS_STOCK_ARCH_TBL NORMAL 18,977.00 range scan 177 sample fast full scan 10 unique scan 4 skip scan 3 ************************** **************************************** * ***** ----------- ---------- sum 75,908.00 194STK_TBL_ARC PK_STK_ARCH_TBL NORMAL 920.00 range scan 126 unique scan 38 skip scan 17 sample fast full scan 2 ************ **************************************** * ****************** ----------- ---------- sum 3,680.00 183STK_TBL_LOG PK_STK_TBL_LOG NORMAL 480.00 unique scan 56 ********* **************************************** * ******************** ---------------- sum 480.00 56TRADE_BROKER_CHRG_TBL_ARC limit NORMAL 128.00-0 UNI_TDBK_CHRG_ARC NORMAL 104.00 range scan 283 * **************************************** * **************************** ----------- ---------- sum 232.00 283TRADE_BROKER_JOURNAL_TBL_ARC limit NORMAL 168.00- 0 IDX_TDBK_JRNL_ARC_INSTRU_ID NORMAL 144.00 full scan 1 limit NORMAL 144.00 full scan 1 limit NORMAL 144.00 full scan 1 PK_TRADE_BROKER_JOURNAL_ARC NORMAL 200.00-0 **************** **************************************** * *************** ----------- ---------- sum 800.00 3TRADE_CLIENT_CHRG_TBL_ARC limit NORMAL 704.00 range scan 3,537 limit NORMAL 1,539.00 range scan 24 sample fast full scan 2 UNI_TDCL_CHRG_ARC NORMAL 1,216.00 range scan 1,103 fast full scan 3 sample fast full scan 2 *************************** **************************************** * *** ----------- ---------- sum 7,430.00 4, please wait NORMAL 312.00-0 then NORMAL 184.00 full scan 1 IDX_TDCL_DTL_ARC_REF_ID NORMAL 344.00 range scan 4,623 fast full scan 1 full scan 1 IDX_TDCL_DTL_ARC_TRADED_PRICE NORMAL 184.00-0 then NORMAL 432.00-0 then NORMAL 272.00-0 **************************************** * ***************************** ----------- ---------- sum 2,416.00 4, 626TRADE_CLIENT_TBL_ARC IDX_TDCL_ARC_ACC_NUM NORMAL 152.00 range scan 534 limit NORMAL 120.00 range scan 550 fast full scan 1 IDX_TDCL_ARC_INPUT_DATE NORMAL 120.00 range scan 7,231 limit NORMAL 144.00 skip scan 156 range scan 3 full scan 1 IDX_TDCL_ARC_TRADE_DATE NORMAL 120.00 RANGE SCAN 12,778 PK_TRADE_CLIENT_TBL_ARC NORMAL 160.00 range scan 37 UNI_TDCL_ARC_REF_ID NORMAL 112.00 unique scan 157 fast full scan 8 sample fast full scan 1 ****************** **************************************** * ************* ----------- ---------- sum 1,560.00 21,457 -- Author: robinson -- Blog: http://blog.csdn.net/robinson_0612 "Showed only indexes in GO_ADMIN schema whose size> 100 MB in period:" 302.161.2013-07.04.2013

2. Result Analysis and Suggestions

A. The above results list the usage frequency of indexes with the schema of GOEX_ADMIN in the current database and the index size greater than MB.
B. Because the current database is a standard version and does not have the partition table function, we can see many tables ending with the arc, and the index is very large. For example, the index on ACC_POS_STOCK_TBL_ARC reaches 19 GB.
C. In the table ACC_POS_CASH_PL_TBL_ARC, the primary key PK_ACC_POS_CASH_PL_ARCH_TBL has the maximum number of scans in use, and the total number of scans is 112.
D. Check whether the index settings are reasonable for the indexes listed above whose usage is 0.
E. If the index is too large, you should consider whether to use index compression.
F. The final list is the schema name of the report, the filtering condition of the index size, and the date on which the index is collected. Note: The sum of the size of the index column is inaccurate.

3. Obtain the index usage frequency script

-- The author of this script is Damir Vadas. Thanks to Damir Vadas for its contribution. robin @ SZDB :~ /Dba_scripts/custom/SQL> more idx_usage_detail. SQL/* revoke CR/TR #: Purpose: Shows index usage by execution (find problematic indexes) Date: 22.01.2008. author: Damir Vadas, damir.vadas@gmail.com Remarks: run as privileged user Must have AWR run because SQL joins data from there works on 10g> @ index_usage SCHEMA MIN_INDEX_SIZE Changes (DD. MM. YYYY, Name, CR/TR #): 25.11.2010, Damir Vadas added index size as parameter 30.11.2010, damir Vadas fixed bug in query failed */set linesize 140 set pagesize 160 clear breaksclear computes break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MBcompute sum of NR_EXEC on TABLE_NAME SKIP 2 compute sum of MB on TABLE_NAME SKIP 2 set timi OFFset linesize 140 set pagesize 10000 set verify offcol OWNER noprintcol TABLE_NAME for a30 heading 'table name' col INDEX_NAME for a30 heading 'index name' col INDEX_TYPE for a15 heading' index type 'col INDEX_OPERATION for a21 Heading 'index operation' col NR_EXEC for 9G999G990 heading 'executions 'col MB for 999G990D90 Heading 'index | Size MB 'justify right with q as (select s. OWNER A_OWNER, TABLE_NAME A_TABLE_NAME, INDEX_NAME A_INDEX_NAME, INDEX_TYPE A_INDEX_TYPE, SUM (S. bytes)/1048576 A_MB FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S. OWNER = '& 1' and I. OWNER = '& 1' AND INDEX_NAME = SEGMENT_NAME group by s. OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE having sum (S. BYTES)> 1048576 * & 2) SELECT/* + NO_QUERY_TRANSFORMATION (S) */A_OWNER OWNER, A_TABLE_NAME TABLE_NAME, A_INDEX_NAME INDEX_NAME, A_INDEX_TYPE INDEX_TYPE, A_MB MB, DECODE (OPTIONS, null, '-', OPTIONS) INDEX_OPERATION, COUNT (OPERATION) NR_EXEC from q, DBA_HIST_ SQL _PLAN d WHERE D. OBJECT_OWNER (+) = q. a_OWNER and d. OBJECT_NAME (+) = q. a_INDEX_NAME group by A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB, DECODE (OPTIONS, null, '-', OPTIONS) order by A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB DESC, NR_EXEC DESC; PROMPT "Showed only indexes in & 1 schema whose size> & 2 MB in period:" set head off; select to_char (min (BEGIN_INTERVAL_TIME), 'dd. MM. YYYY ') |'-'| to_char (max (END_INTERVAL_TIME), 'dd. MM. YYYY ') from dba_hist_snapshot; SET HEAD ONSET TIMI ON

4. Additional instructions
The script uses two substitution variables: schema and index. By default, DBA_HIST_ SQL _PLAN is not collected for the execution plans of small indexes and SQL statements that only run once or twice. Therefore, the recommended index size input value for script execution is 100. If you need to collect all the historical SQL Execution plans to determine whether the index is used, you need to modify statistics_level to all or the snapshot collection policy. Collection policies have a certain impact on the system and consume a large amount of disk space. Therefore, the Prod environment should be used with caution (UAT and DEV should be fine ).

How to monitor full resolution of Oracle Indexes

By monitoring the use of database indexes, release unused indexes to save the cost of maintaining indexes and optimize performance. 1. in oracle8i, determining which index is used means to run explain PALN for all statements in the SQL area of the language sharing, and then query the OPERATION column in the schedule, in this way, the access to the index identified by the OBJECT_OWNER and OBJECT_NAME columns is identified. The following is a script used to monitor indexes. This script is just a sample and is true under certain conditions: the user who runs this script has the permission to interpret all the SQL statements in v $ sqlarea, except for not being loaded by SYS. Plan_table.remarks can be used to determine errors related to privileged habits. For all the SQL statements in the shared pool, the OPTIMIZER_GOAL parameter is a constant, regardless of v $ sqlarea. optimizer_mode. Statistics are analyzed again between two snapshots. If there are no statements, do not truncate them. All objects are local. All referenced tables or views are owned by users running scripts, or fully qualified names or synonyms are used. Since the last snapshot, no "welcome" statements have been flushed out of the Shared Pool (for example, loading ). Script: Code: [Copy to clipboard] set echo off Rem Drop and recreate PLAN_TABLE for explain plan drop table plan_table; create table PLAN_TABLE (statement_id varchar2 (30), timestamp date, remarks varchar2 (80), operation varchar2 (30), options varchar2 (255), object_node varchar2 (128), object_owner varchar2 (30), object_name varchar2 (30), object_instance numeric, object_type varchar2 (30), optimizer varchar2 (255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2 (255 ), invalid varchar2 (255), invalid varchar2 (255), partition_id numeric, other long, distribution varchar2 (30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2 (4000 ), filter_predicates varchar2 (4000); Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA drop table sqltemp; create table sqltemp (ADDR VARCHAR2 (16), SQL _TEXT VARCHAR2 (2000 ), DISK_READS NUMBER, executions number, parse_cils NUMBER); set ...... remaining full text>

How Does oracle monitor the number of times an index is used? For example, I am very grateful to you for providing methods or SQL statements.

I don't know how many times the index has been used, but you can monitor whether the index is used in one click.

To monitor whether an index is used, you must enable it in advance.
Alter index *** monitoring usage; -- *** indicates your index name.
Then you can monitor this index.
Select * from v $ object_usage can be monitored.
If you use the index after enabling monitoring, this view will have records, otherwise there will be no records