Results 1 to 3 of 3

Thread: Hot indexes

  1. #1
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Unanswered: Hot indexes

    From V$FILESTAT I can see that one tablespace which contains only a few
    indexes is getting many, many physical reads done against it.

    What SQL will provide me visibility into which user(s) & what SQL is generating the activity against these indexes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could join v$sql_plan to v$sqlarea to indentify which sql is using which indexes. And v$sqlarea will also give you the parsing_user_id for the first user which used that sql but it wont give you all users who have accessed the index through that sql I believe. As far as I am aware there is no transparent way of getting which users are accessing which indexes except maybe through heavy handed means such as tracing all sessions.

    As for the indexes if they are not too big stick them in the keep pool or maybe use the compress clause if you can to reduce the physical reads. You might also want to rebuild some of the indexes in another tablespace if they are on one physical disk.

    Alan
    Last edited by AlanP; 07-08-04 at 12:04.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks for the response.
    I was unaware of V$SQL_PLAN until now.
    However the DB in question is V7.3.4.5 where V$SQL_PLAN does not exist. :-(
    It is too bad I can convince Damagement to upgrade the dinosaur.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •