Shulou(Shulou.com)06/01 Report--

Oracle Index Monitoring-unused indexes

Oracle indexes are used to optimize DML processing speed, but indexes take up table space, sometimes taking up more table space than indexed tables. Therefore, it is important for indexes to monitor unused indexes.

Turn on an index monitor:

Alter index owner.index_name monitoring usage

Turn off an index monitoring:

Alter index owner.index_name nomonitoring usage

The usage information for index monitoring is included in the v$object_usage view. The following ddl statement for v$object_usage in oracle:

Create or replace view sys.v$object_usage

(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)

As

Select io.name, t.name

Decode (bitand (i.flags, 65536), 0, 'NO',' YES')

Decode (bitand (ou.flags, 1), 0, 'NO',' YES')

Ou.start_monitoring

Ou.end_monitoring

From sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

Where io.owner# = userenv ('SCHEMAID')-current user's index

And i.obj# = ou.obj#

And io.obj# = ou.obj#

And t.obj# = i.bo#

This attempt can only check the indexes created by users who are currently logged into the database.

So we can follow suit and write our own sql sentences. But you must have the corresponding authority to go, the specific authority to add their own line.

As long as it is the query permission (sys.obj$, sys.obj$, sys.ind$, sys.object_usage) for these tables.

Sql omitted.

Alter index INDEX_USER monitoring usage

Select table_name,index_name,used from v$object_usage

Alter index pk_emp nomonitoring usage

Select * from v$object_usage

It is only valid under the current user, and you can only see the monitoring index under the current user.

SQL > COL INDEX_NAME FOR A20

SQL > COL TABLE_NAME FOR A10

SQL > COL MONITORING FOR A10

SQL > COL USED FOR A10

SQL > COL START_MONITORING FOR A20

SQL > COL END_MONITORING FOR A20

SQL > SELECT * FROM V$OBJECT_USAGE

Select z.name | |'. | | io.name, t.name

Decode (bitand (i.flags, 65536), 0, 'NO',' YES')

Decode (bitand (ou.flags, 1), 0, 'NO',' YES')

Ou.start_monitoring

Ou.end_monitoring

From sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,sys.user$ z

Where i.obj# = ou.obj#

And io.obj# = ou.obj# and io.owner#=z.user#

And t.obj# = i.bo#

Order By 4 Desc,2 Desc

CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE

(

OWNER

INDEX_NAME

TABLE_NAME

MONITORING

USED

START_MONITORING

END_MONITORING

)

AS

Select u.name, io.name, t.name

Decode (bitand (i.flags, 65536), 0, 'NO',' YES')

Decode (bitand (ou.flags, 1), 0, 'NO',' YES')

Ou.start_monitoring

Ou.end_monitoring

From sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u

Where i.obj# = ou.obj#

And io.obj# = ou.obj#

And t.obj# = i.bo#

And io.owner# = u.user#

COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 'Record of all index usage'

GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"

CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE

V$OBJECT_USAGE

You can use this view to monitor index usage. The view displaysstatistics about index usage gathered from the database. Allindexes that have been used at least once can be monitored anddisplayed in this view.

Note:

1. $object_usage only includes the usage records of the index owned by the current user, that is, the creator of the index or the owner of the index (owner). After this user logs in, you can see the use of the index owned by this user in the entire database. If the index is used, the field USED of the view V$OBJECT_USAGE is marked YES. Otherwise, it's NO. Of course, first of all, these indexes are enabled for usage monitoring. SQL > alter index index_test_pk monitoring usage

2. If you need to find out the usage records of all the indexes owned by users, use the following sql, which is from the DBA diary:

SQL > select u.name owner, io.name index_name, t.nametable_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#

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.