|
Monitoring an object usage
Introduction
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 column
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
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 oracle user.
SQL> CONNECT
oracle/learning
Check
monitored objects
Check to see if there are any index objects
to be monitored.
SQL> SELECT * FROM
v$object_usage
/
Notice that there are no objects
to be monitor.
Create an
index
Let's create a unique index on the EMP
table.
SQL> CREATE
UNIQUE INDEX uk_emp
ON emp(ename)
TABLESPACE oracle_data
/
Monitor an
index table
Now, let's start monitoring the index usage
of the newly created index table.
SQL> ALTER INDEX
uk_emp
MONITORING USAGE
/
Monitor an index usage
First, let's
see if the index table was used.
SQL> SELECT * FROM
v$object_usage
/
Notice that the USED column
indicates the NO value. It means that the
index has not been used.
Use index
Query the statement so that the query has to
use the index table.
SQL> SELECT *
FROM emp
WHERE ename = 'KING'
/
Check index
usage
Now, once again let's check to see if the
index table was used.
SQL> SELECT * FROM
v$object_usage
/
Notice that the USED column is
set to YES this time. It means that the
index has been used.
Stop
monitoring an index usage
Let's now stop
monitoring the index usage of the unique
index.
SQL> ALTER INDEX
uk_emp NOMONITORING USAGE
/
Let's now see if the index table was stopped
being monitored.
SQL> 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.
Now, drop the index table.
SQL> DROP
INDEX uk_emp
/
Questions:
Q: How do you
monitor a usage of an index table?
Q: How do you
start monitoring a usage of an index table?
Q: How do you
stop monitoring a usage of an index table?
Q: Describe
the V$OBJECT_USAGE view.
Q: What do the
following columns indicate in the
V$OBJECT_USAGE view?
USED
column
MONITORING column
END_MONITORING column
Q: What do the
following SQL statements do?
SQL> ALTER
INDEX uk_emp
MONITORING USAGE
/
SQL> SELECT * FROM v$object_usage
/
SQL> ALTER
INDEX uk_emp NOMONITORING USAGE
/ |
