monitoring index

Printable View

07-20-2006, 12:40 PM
gandolf989

monitoring index

I turn on monitoring for a given index and then check to see if monitoring is turned on, and nothing. What did I miss? :confused:

Code:

SQL> ALTER INDEX RATED_EEFI1 MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_I1 MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_I2 MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_I4 MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_I6 MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_I7 MONITORING USAGE; Index altered. Elapsed: 00:00:00.03 SQL> ALTER INDEX RATED_SUBSCR_ID MONITORING USAGE; Index altered. Elapsed: 00:00:00.01 SQL> ALTER INDEX RATED_SVC_ID MONITORING USAGE; Index altered. Elapsed: 00:00:00.03 SQL> select * FROM v$sort_usage; no rows selected Elapsed: 00:00:00.00
07-20-2006, 01:29 PM
dbasan

Quote:

Originally Posted by gandolf989

I turn on monitoring for a given index and then check to see if monitoring is turned on, and nothing. What did I miss? :confused:

SQL> select * FROM v$sort_usage;

no rows selected

Elapsed: 00:00:00.00[/CODE]

Check v$object_usage col monitoring
07-20-2006, 01:43 PM
gandolf989

Quote:

Originally Posted by dbasan

Check v$object_usage col monitoring

Code:

SQL> select * from v$object_usage; no rows selected Elapsed: 00:00:00.00 SQL>

Sorry I used the wrong example. It still doesn't seem to work. Someone suggested that I have to run the command as the schema owner, I ran it as sys.
07-20-2006, 01:57 PM
dbasan

Quote:

Originally Posted by gandolf989

Code:

SQL> select * from v$object_usage; no rows selected Elapsed: 00:00:00.00 SQL>

Index is not being used. V$object_usage col monitoring and used are updated once the index is being used when monitoring is set on index.
07-20-2006, 03:41 PM
gandolf989

Quote:

Originally Posted by dbasan

Index is not being used. V$object_usage col monitoring and used are updated once the index is being used when monitoring is set on index.

Previously I did not have the password to the schema. Now that I have it I was able to log in as that schema and do the same query resulting in:

Code:

SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring 2 FROM v$object_usage; INDEX_NAME MON USE START_MONITORING END_MONITORING ------------------------- --- --- ------------------- ------------------- RATED_EEFI1 YES NO 07/20/2006 12:39:38 RATED_I1 YES NO 07/20/2006 12:39:38 RATED_I2 YES NO 07/20/2006 12:39:38 RATED_I4 YES YES 07/20/2006 12:39:38 RATED_I6 YES NO 07/20/2006 12:39:38 RATED_I7 YES YES 07/20/2006 12:39:39 RATED_SUBSCR_ID YES NO 07/20/2006 12:39:39 RATED_SVC_ID YES NO 07/20/2006 12:39:39 8 rows selected.

It is like one of the user views. :rolleyes: