Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    4

    Lightbulb Unanswered: Index get_hit_ratio is quiet low

    Hi

    I queried V$LIBRARYCACHE and found get_hit_ratio of namespace index is .035.
    I guess indexes are reason of my db performance degradation.

    I have already reindex all important transactional tables but get_hit_ratio of index remain around same ration.

    I would like to know
    whether simply reindexing is enough or thr is another better way to reindex?
    How do i get know which index is being corrupted?

    thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a couple of different ways to check the validity of indexes:

    SELECT a.name, a.obj#, a.dataobj#, b.dataobj#
    FROM obj$ a, ind$ b
    WHERE a.obj# = b.obj#
    AND a.dataobj# != b.dataobj# ;


    ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE;

    -- This will cross check the table with all of its indexes. This can take
    -- a while and will block DML while it runs. It will report an ORA-1499
    -- error if there is a mismatch between the table and one of its indexes.
    -- This will not detect IOT data loss. An alternative but less rigorous check
    -- is to use statements of the form:

    -- OR

    SET TRANSACTION READ ONLY;

    -- Find the total number of entries in the table

    SELECT /*+ FULL(tablename) */ count(*) FROM tablename;

    -- Find the total number of entries in the index

    SELECT /*+ INDEX_FFS(tabname indexname) */ count(*) FROM tablename;

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >get_hit_ratio of namespace index is .035.
    So why is this number meaningful?
    I suggest that you enable SQL_TRACE for those queries which run "slow"
    and then concentrate on making the SQL perform better.
    You are wasting your time because you have no facts which actually identify
    where the bottleneck exists.
    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
  •