Topics: Monitoring an object
usage
|
More Resources by
Google: |
|
|
|
|
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
|