Already a member?
Sign in
Welcome! This is a website that everyone can build together. It's easy!
Location: Administration
Discussion: monitoring index usage
Keyword tags:
Database Administration DBA
Watch
 mcgg |
monitoring index usage
Jun 24 2008, 3:41 AM EDT Hi all, can you tell me something about this? I'd like to know what happens when you execute: alter index i1 monitoring/nomonitoring usage; I mean, is there some effect on the queries and cursors that are in the SGA? Do they have to be reparsed again, as it happens when you rebuild an index? Are they flushed from SGA? And, is there any other thing to take into account? thank you very much 1 out of 1 found this valuable. Do you? |
|
ashminder ashminder |
RE: monitoring index usage
Jun 25 2008, 6:37 AM EDT HI. Using that alter index doesnt cause problems with the SGA or reparsing. All it does is put a flag into the USED column of v$object_usage. Generally the performance hit of using montoring is so so so small you wont notice. As an alternative you could use this script to check index usage: col c1 heading 'Object|Name' format a30 col c2 heading 'Operation' format a30 col c3 heading 'Option' format a15 col c4 heading 'Index|Usage|Count' format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; 5 out of 5 found this valuable. Do you? |
|
mcgg |
RE: monitoring index usage
Jun 25 2008, 7:09 AM EDT the script you provided seems really interesting but I think it is for 10g or 11g and we are using 9i thank you anyway ashminder 2 out of 2 found this valuable. Do you? |
