Identifying Unused Objects in Database

Sometimes in the database, there exist objects that have never been used. As you can guess they are junk objects and are waisting necessary space in the database. So we should identify them and remove from the DB. Oracle offers us some useful techniques to do this.

First of all, you should turn on monitoring process on that objects that are the most candidates of junk.

For example:

Assume that index sal_indx is considered to be the object, which has never been used. For to identify it, let’s turn on monitoring on that index.

–In your schema

alter index sal_indx monitoring usage;

–In other schema

alter index schemaName.sal_indx monitoring usage;

To check if it is monitored, query the following view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | NO   | 02/13/2011 21:48:25

Note: the result shows that this index has not been used since we tuned on monitoring, but it still doesn’t mean that this object is junk. It depends on what is the maximum period of time in which this object should be used. For example, if you know that this object should be used minimum once in a day, let it to be monitored till tomorrow, then query again v$object_usage view and you will see if this object has been used since yesterday or not and identify its necessity.

For example, someone run the following query:

select *
from salary
where id=1;

Assume that index sal_indx is created on id column of salary table.

–Check again the v$object_usage view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | YES  | 02/13/2011 21:48:25

From here you can identify that this index is necessary and should not be removed from database.

Turning Off Monitoring

–In your schema

alter index sal_indx nomonitoring usage;

–In other schema

alter index schemaName.sal_indx nomonitoring usage;

–Check if the monitoring is disabled

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | NO  | YES  | 02/13/2011 21:48:25
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: