Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    46

    Unanswered: Index gethitratio

    Dear all,

    My index gethitration is zero(0) for a long time now.

    I have rebuilt with the indexes with the statement below but it is still 0. Is anything to account for this?

    ================
    SELECT 'ALTER INDEX ' || OWNER || ' .' || INDEX_NAME || ' REBUILD ; '
    FROM DBA_INDEXES
    WHERE OWNER ='USER_NAME'
    ================
    I thought after rebuilding at least the gethitratio value should increase from 0.

    any help is welcome.

    Much thanks in advance.

    "knowledge is a baobab tree, it cannot be embraced by one man"

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL & results that GETHITRATIO is zero
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A hit ratio of 0 indicates that the index is not being used and rebuilding it will not cause it to be used.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    could you post:

    -- the query (where you expect the index to be used)
    -- the execution plan of the query (and please use [CODE] tags !):
    -- the cardinality of the involved tables
    -- the block size of your db (in sqlplus: "show parameter block_size")
    -- the output of the sqlplus statement "show parameter multi_block_read_count"
    -- the exact parameters you used in the "dbms_stats.gather_XXX_statistics()" procedure call (you did analyze the tables and indexes, didn't you?)
    -- And, by far most important: the Oracle version (this should be first in EVERY request but it's especially essential for analyzing optimizer behavior).

    Otherwise this ends up - best case - in educated guessing where someone here might stumble over the solution, but a structured approach looks more promising to me ...
    Last edited by magicwand; 10-12-12 at 21:59.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

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