DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Index usage

  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Index usage

    Does anyone have script to determine the index usage for a given
    database ?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    version?

    9i - put index monitoring on

  3. #3
    Join Date
    Apr 2001
    Location
    United Kingdom
    Posts
    31
    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

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    easier to do it in the table statement as far as I am concerned with dbms_stats use cascade => true

  6. #6
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    thanks hun

  7. #7
    Join Date
    Dec 2004
    Posts
    2
    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

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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
    /

  9. #9
    Join Date
    Apr 2001
    Location
    United Kingdom
    Posts
    31
    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

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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
  •  


Click Here to Expand Forum to Full Width