|
"The significant
problems we face cannot be solved at the same level of thinking
we were at when we created them." - Albert Einstein
(1879-1955) |
Read
first then play the video:
DBA018(VIDEO)-Monitoring
an object usage
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 iself user.
SQL> CONNECT iself/schooling
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 iself_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
/
|
"I have often
regretted my speech, never my silence." - Xenocrates
(396-314 B.C.) |
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
/
|