iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 19

"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:

   DBA-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
/