Does anyone have script to determine the index usage for a given
database ?
Thanks
Printable View
Does anyone have script to determine the index usage for a given
database ?
Thanks
version?
9i - put index monitoring on
Yes, Index monioring is a good option in 9i .
Alter IndexMonitoring usage.
You can then check V$object_usage to see the usefulness of the index
nomonitoring would turn of monitoring .
Hope this helps
hi hun...just a related one...
Do I need to analyze indexes often in order to improve performance?
or if I analyzed tables to make the optimizer perform well should
I included indexes too?
Thanks
easier to do it in the table statement as far as I am concerned with dbms_stats use cascade => true
thanks hun:D
I do not want to use index monitoring - but i am interested in knowing
the allocation of bytes for every index what's allocated space
what's being used in terms of space. Do you have any scripts for that
Thank you very much
like this?Quote:
Originally posted by prekida
I do not want to use index monitoring - but i am interested in knowing
the allocation of bytes for every index what's allocated space
what's being used in terms of space. Do you have any scripts for that
Thank you very much
Code:SELECT owner, segment_name, SUM( bytes ) bytes
FROM dba_segments
WHERE segment_type = 'INDEX'
GROUP BY owner, segment_name
/
You are talking about space usage ! Perhaps you can try something like this .
select a.index_name
, a.tablespace_name
, round(sum (b.bytes) / 1024 / 1024 , 8 ) MB
from dba_indexes a
, dba_extents b
where a.index_name = b.segment_name
and a.tablespace_name = b.tablespace_name
Group by a.tablespace_name , a.index_name
order by MB desc , a.index_name asc
Suggest you to have a good luck , I just keyed this in as I saw this .... Could have overlooked something
You don't need to join to the dba_indexes view,Quote:
Originally posted by Dosth
You are talking about space usage ! Perhaps you can try something like this .
select a.index_name
, a.tablespace_name
, round(sum (b.bytes) / 1024 / 1024 , 8 ) MB
from dba_indexes a
, dba_extents b
where a.index_name = b.segment_name
and a.tablespace_name = b.tablespace_name
Group by a.tablespace_name , a.index_name
order by MB desc , a.index_name asc
Suggest you to have a good luck , I just keyed this in as I saw this .... Could have overlooked something
but if you do you should join by the right columns.
Code:SELECT a.index_name, a.tablespace_name,
ROUND( SUM( b.bytes ) / 1024 / 1024, 8 )
FROM dba_indexes a, dba_extents b
WHERE a.index_name = b.segment_name AND
a.owner = b.owner
GROUP BY a.tablespace_name,
a.index_name
ORDER BY mb DESC,
a.index_name ASC;