Oracle9i monitoring index

xiaoxiao2021-03-06 113

Introduction

DBA and developers like indexes. They can accelerate query search, especially in a data warehouse environment, because the database receives many AD-HOC requests. To avoid full table search, we generally build an index in each column that may be searched. However, the index will occupy a lot of tablespaces; in many cases, the index consumes more storage space than the indexed table. When inserting and deleting rows, the index will also introduce additional overhead. Before Oracle9i, you know if an index is used is difficult, so many databases have many indexes. The purpose of this article is to introduce you to the new features in Oracle9i to distinguish unused indexes.

Identify unused indexes

Oracle9i provides a new technique to monitor indexs to identify whether the index is used. To start monitoring an index, use this command:

Alter Index Index_name Monitoring Usage;

To stop monitoring an index, type:

Alter Index Index_name Nomonitoring Usage;

In the V $ Object_USAGE view contains the usage information with index monitoring.

Create or Replace View Sys.v $ Object_USAGE

INDEX_NAME,

Table_name,

Monitoring,

Used,

START_MONITORING,

END_MONITORING

)

Selectuary, 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 $ o, sys.obj $ t, sys.ind $ i, sys.object_usage.com

Where io.owner # = useerenv ("schemaid")

And i.obj # = ou.obj #

Anduary.obj # = ou.obj #

And T.Obj # = I.bo #

Comment on Table Sys.v $ Object_usage IS "Record of Index Usage"

Grant SELECT ON SYS.V $ Object_usage to "public"

This view shows the index usage statistics collected by the database. The following is a description of the column in this view:

Index_name: Sys.obj $ .NAME Index Name Table_Name: Sys.obj $ OBJ $ Name Monitoring: YES (index is being monitored), no (index is not monitored) Used: yes (index has been used) Over), NO (index is not used) start_monitoring: Start monitoring time END_MONITORING: Time to end monitoring

All indexes used at least once can be monitored and displayed in this view. However, a user can only receive index in its own mode. Oracle does not provide a view to receive indexes in all modes. To receive indexing of all modes, log in with SYS users and run the following scripts (Note: This is not a script provided by Oracle.v $ all_Object_usage is a custom view. It contains more than one column, the owner of the index )

$ Cat All_Object_usage.sql

Create or Replace View Sys.v $ all_Object_usage

(

Owner,

INDEX_NAME,

Table_name, Monitoring,

Used,

START_MONITORING,

END_MONITORING

)

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 #

Anduary.obj # = ou.obj #

And T.Obj # = I.bo #

Ando.owner # = u.user #

Comment on Table Sys.v $ all_Object_usage IS

"Record of All Index Usage - Developed by Daniel Li"

Grant SELECT ON SYS.V $ all_Object_usage to "public"

Create Public Synonym V $ all_Object_usage

For sys.v $ all_Object_usage

Every time you use Monitoring Usage, the view will reset for special indexes. All previous usage information will be cleared and reset, and the next new startup time is recorded. Every time you perform nomonitoring usage, there will be no further monitoring; the end time during monitoring will be recorded. If you delete an index that is being monitored, the relevant information of this index will be removed from the V $ Object_USAGE and V $ ALL_Object_USAGE view.

Identify all unused indexes in the database

This script will start monitoring all indexes:

######################################################################################################################################################################################################################################################################################################## ###################

## START_INDEX_MONITORING.SH ##

#! / bin / ksh

# input parameter: 1: Password

# 2: SID

IF (($ # <1))))

THEN

Echo "please enter" system "user password as the first parameter!"

EXIT 0

IF (($ # <2)))

THEN

Echo "please enter instance name as the second parameter!"

EXIT 0

SQLPlus -s

Set heading off

SET feed OFF

Set PageSize 200

Set linesize 100

SPOOL START_INDEX_MONITORING.SQL

Select "Alter Index" | | || index_name || "Monitoring Usage;"

From dba_indexes

WHERE OWNER NOT IN ("Sys", "System", "Outln", "Aurora / $ JIS / $ UTILITY / $"); spool off

exit

SQLPlus -s

@. / start_index_monitoring.sql

exit

This script will stop monitoring all of the index:

## stop_index_monitoring.sh ##

#! / bin / ksh

# input parameter: 1: Password

# 2: SID

IF (($ # <1))))

THEN

Echo "please enter" system "user password as the first parameter!"

EXIT 0

IF (($ # <2)))

THEN

Echo "please enter instance name as the second parameter!"

EXIT 0

SQLPlus -s

Set heading off

SET feed OFF

Set PageSize 200

Set linesize 100

Spool stop_index_monitoring.sql

SELECT "alter index" | | || index_name || "nomonitoring usage;"

From dba_indexes

WHERE OWNER NOT IN ("Sys", "System", "Outln", "Aurora / $ JIS / $ UTILITY / $");

Spool off

exit

SQLPlus -s

@. / stop_index_monitoring.sql

exit

This script will generate a report for all unused indexs:

## odentify_unused_index.sh ##

#! / bin / ksh

# input parameter: 1: Password

# 2: SID

IF (($ # <1))))

THEN

Echo "please enter" system "user password as the first parameter!"

EXIT 0

IF (($ # <2)))

THEN

Echo "please enter instance name as the second parameter!"

EXIT 0

SQLPlus -s

SET feed OFF

Set PageSize 200

Set linesize 100

TTITE CENTER "Unused Indexes Report" SKIP 2

Spool unused_index.rpt

SELECT OWNER, INDEX_NAME, TABLE_NAME, Used

From v / $ all_Object_usage

Where buy = "no";

Spool off

exit

Here is an example of an unused index report:

Unused Indexes Report

Owner index_name table_name us

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

Hr dept_id_pk Departments NO

HR dept_location_ix departments no

HR EMP_DEPARTMENT_IX EMPLOYEES NO

HR EMP_EMAIL_UK EMPLOYEES NO

HR EMP_EMP_ID_PK EMPLOYEES NO

HR EMP_JOB_IX EMPLOYEES NO

HR EMP_MANAGER_IX EMPLOYEES NO

HR EMP_NAME_IX EMPLOYEES NO

HR jhist_department_ix job_history no

HR jhist_employee_ix job_history no

HR jhist_emp_id_st_date_pk job_history no

HR jhist_job_ix job_history no

HR JOB_ID_PK JOBS NO

HR LOC_CITY_IX LOCATIONS NO

HR LOC_COUNTRY_IX LOCATIONS NO

HR LOC_ID_PK LOCATIONS NO

HR LOC_STATE_PROVINCE_IX LOCATIONS NO

HR REG_ID_PK Regions NO

OE inventory_pk inventories no

OE INV_PRODUCT_IX Inventories No

OE INV_WAREHOUSE_IX Inventories No

OE ITEM_Order_ix ORDER_ITEMS NO

OE ITEM_PRODUCT_IX ORDER_ITEMS NOOE ORDER_ITEMS_PK ORDER_ITEMS NO

OE ORDER_ITEMS_UK Order_Items NO

OE Order_pk Orders No

in conclusion

Oracle9i provides a new method for the use of monitoring indexes and helps us identify unused indexes. This ability to find and delete unused index is not only helpful to the performance of insertion and deletion operations, but also saves storage space. The performance of performance will not be seen when using index monitoring.

转载请注明原文地址:https://www.9cbs.com/read-126908.html

9cbs

New Post(0)