A simple index to monitor the use of ORACLE

Jan 6 posted in Oracle (RSS), 110 views.

Description:

Application development may be the establishment of a number of indexes, but these indexes used in the end how kind, is there an index has not been used, which requires us to monitor these indexes in order to determine their usage, and as to whether They can clear the basis is given.

This article describes two ways:

First: Open the monitoring function;

2nd: View the history of implementation of the plans, conduct analysis;

Environment:

SQL> select * from v $ version;

BANNER

-------------------------------------------------- --------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL / SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

Test:

First:

CREATE TABLE tt

AS

SELECT * FROM dba_objects;

DELETE FROM tt

WHERE object_id IS NULL;

ALTER TABLE tt ADD CONSTRAINT pk_tt PRIMARY KEY (object_id);

Note: Use the alter index <INDEX_NAME> nomonitoring usage cancel monitoring.

SELECT object_id, object_name

FROM tt

WHERE object_id = 333;

SELECT INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING

FROM V $ OBJECT_USAGE;

In order to avoid the V $ OBJECT_USAGE only under the current user query to the index to monitor the situation, you can use the following statement to query the database for all monitors the use of an index:

SELECT U. NAME OWNER,

IO.NAME INDEX_NAME,

T. NAME TABLE_NAME,

DECODE (BITAND (I. FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

DECODE (BITAND (OU.FLAGS, 1), 0, 'NO', 'YES') USED,

OU.START_MONITORING START_MONITORING,

OU.END_MONITORING END_MONITORING

FROM SYS.USER $ U,

SYS.OBJ $ IO,

SYS.OBJ $ T,

SYS.IND $ I,

SYS.OBJECT_USAGE OU

WHERE I. OBJ # = OU.OBJ #

AND IO.OBJ # = OU.OBJ #

AND T. OBJ # = I. BO #

AND U. USER # = IO.OWNER #

Second:

Library cache data on the use of a

SELECT OBJECT_NAME

FROM V $ SQL_PLAN A, V $ SQLAREA B

WHERE A. SQL_ID = B. SQL_ID

AND A. OBJECT_TYPE = 'INDEX'

AND OBJECT_OWNER IN ( 'XXXX', 'XXXX');

2 the use of STATSPACK

SELECT A. OBJECT_OWNER, A. OBJECT_NAME

FROM STATS $ SQL_PLAN A, STATS $ SQL_PLAN_USAGE B

WHERE A. PLAN_HASH_VALUE = B. PLAN_HASH_VALUE

AND A. OBJECT_TYPE = 'INDEX';

Note: The need to open Statspack snapshot feature, or finding out data;

3 If it is more than 10g version of the data, you can use AWR

SELECT 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 B. OBJECT_OWNER IN ( 'XXX', 'XXXX');

Note: You can adopt the following statement to query the operation of AWR

SELECT *

FROM DBA_HIST_WR_CONTROL;

Reference:

http://www.hellodba.com/Doc/monitor_index.htm

http://www.oracle.com/technology/global/cn/pub/articles/10gdba/week6_10gdba.html

Pending further study of content:

http://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm

Added:

With regard to the index to monitor or track, this is just to stay in a simple theory of phase, and in-depth in practice there are many elements to understand.