Hands-On 18 (Monitoring an object usage)

As a DBA, you are also responsible for monitoring newly created indexes. Your organization wants you to monitor the index column so that if the indexed column was not used then you can drop it, since so many indexing in the database affects the database performance. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a new index table

Monitoring an index usage

Using the V$OBJECT_USAGE view

Checking the USED colum

Checking the MONITORING column

Checking the END_MONITORING column

Creating a unique index column

Starting monitoring an index usage

Using a monitored index column

Stopping monitoring an index usage

Dropping an index table

Commands:

CREATE UNIQUE INDEX ON TABLESPACE

ALTER INDEX MONITORING USAGE

ALTER INDEX NOMONITORING USAGE

DROP INDEX

-- Hands-On 18 (Monitoring an object usage)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a30
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

col used format a4
col index_name format a15
col table_name format a15
col monitoring format a10

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to monitor
-- the index usage of the newly created index table.

-- Let's connect to SQLPlus as the iself user.

pause


CONNECT iself/schooling

pause

CLEAR SCR
-- Check to see if there are any index objects to be monitored.

pause


SELECT * FROM v$object_usage
/

-- Notice that there are no objects to be monitor.

pause

CLEAR SCR
-- Let's create a unique index on the EMP table.

pause


CREATE UNIQUE INDEX uk_emp 
ON emp(ename) 
TABLESPACE iself_data
/

pause

CLEAR SCR
-- Now, let's start monitoring the index usage of the 
-- newly created index table.

pause


ALTER INDEX uk_emp 
MONITORING USAGE
/

pause

CLEAR SCR
-- First, let's see if the index table was used.

pause


SELECT * FROM v$object_usage
/

-- Notice that the USED column indicates the NO value.
-- It means that the index has not been used.

pause

CLEAR SCR
-- Query the statement so that the query has to
-- use the index table.

pause


SELECT *
FROM emp
WHERE ename = 'KING'
/

pause


CLEAR SCR
-- Now, once again let's check to see if the index table was used.

pause


SELECT * FROM v$object_usage
/

-- Notice that the USED column is set to YES this time.
-- It means that the index has been used.

pause

CLEAR SCR
-- Let's now stop monitoring the index usage of the 
-- unique index.

pause


ALTER INDEX uk_emp NOMONITORING USAGE
/

pause

CLEAR SCR
-- Let's now see if the index table was stopped being monitored.

pause


SELECT * FROM v$object_usage
/
-- Notice that the MONITORING column is now set to NO.

-- The END_MONITORING column is not null. It indicates 
-- the stop date of the index monitoring.

pause

CLEAR SCR
-- Now, drop the index table.

pause


DROP INDEX uk_emp
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause