-
Index usage
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 Index Monitoring 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
-
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
-
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
like this?
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
-
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
You don't need to join to the dba_indexes view,
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|