Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use oracle index monitoring". In daily operation, I believe many people have doubts about how to use oracle index monitoring. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use oracle index monitoring". Next, please follow the editor to study!

After using the database for a long time, you will find that there are many indexes, which may seriously affect the dml operation, so you can use index monitoring to find those indexes that are not in use and delete them:

1. Monitoring Index:

Alter index index_name_xxxx MONITORING USAGE

Cancel index monitoring:

Alter index index_name_xxxx NOMONITORING USAGE

You can mass-produce statements for monitoring indexes based on table names:

Select 'alter index' | | index_name | | 'MONITORING USAGE;' from user_indexes where table_name =' table_name_xxxx'

Or directly query to get the index name:

Select index_name from user_indexes

two。 View index monitoring:

SELECT * FROM V$OBJECT_USAGE

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

IDX_STATUS T_APP_USER_N YES NO 10/01/2018 10:24:43

Normally, you can monitor for a week or more, and make your own decisions based on your business or your usual sql:

You can set the index invisible first:

Alter index IDX_STATUS invisible

You can open visible if you need to use it

Alter index IDX_STATUS visible

3. After observing for a period of time, there is no problem to delete. It is recommended that you back up the statements that create the index before deleting:

Drop index IDX_STATUS

4. Index rebuild, specify pctfree:

Alter index IDX_STATUS rebuild online parallel 4 pctfree 20

Alter index IDX_STATUS parallel 1

At this point, the study on "how to use oracle index monitoring" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.