Index Usage

Oracle provides index monitoring to see if an index is used or not, since you can set on if use is made of index or if not, by the time you want, turn this sometimes is difficult, especially in critical environments or large database and that may cause locks in the system (when you set). An alternative is to look for in memory, plans are in memory if they being used or are relatively recent. We have the v$SQL_PLAN, look for the name the index of interest. of course, this query does not guarantee that is not in use if the query returns no rows, but time could be a solution.

Example

 

SELECT sql_id FROM v$sql_plan WHERE object_name =’TAB1_PK’;

 

We can see also the executions per statement.

 

SELECT sql_id, executions FROM v$sql WHERE sql_id IN
(SELECT sql_id FROM v$sql_plan WHERE object_name =’TAB1_PK’);

 

HTH – Antonio NAVARRO

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