DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: how come there's nothing in INDEX_STATS despite ANALYZE INDEX <name> ?

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    Hi,

    Juz to seek some expertise here...how come when i perform a ANALYZE INDEX compute STATISTIC...then i chk the INDEX_STATS there's no nothing inside...does this mean
    the index is ok? or am i missing somethg?

    junior

  2. #2
    Join Date
    Jul 2001
    Posts
    5
    Hi,

    Use Analyze index index1 VALIDATE STRUCTURE


  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    COMPUTE STATISTICS doesn't populate index_stats. As pvl suggested, you must use VALIDATE STRUCTURE.

    See http://otn.oracle.com/docs/products/...8a.htm#2058795 for details.
    Jeff Hunter

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    INDEX_STATS table will need to be viewed after each VALIDATE STRUCTURE command. Cause it is overwriten by the next command. I wrote the script below to check for wasted space on the indexes in my system, and report the indexes which are > 20%, use it if you like. It's a bit untidy, but I didn't spent a lot of time on it, it also has minor dbms debugging still in it.

    PROCEDURE validate_indexes

    -- Author: Greg Johnson
    -- Date: 29/07/2001
    -- Purpose: Determine is Indexes have high wasted space
    -- and, advise if rebuild may be required.

    IS

    CURSOR c_validate_index IS
    SELECT INDEX_NAME FROM USER_INDEXES
    WHERE INDEX_NAME NOT LIKE ('%_LOAD')
    AND INDEX_TYPE = 'NORMAL';

    vSQL VARCHAR2(100);
    v_wasted NUMBER(3);
    v_del_lf_rows NUMBER(8);
    v_index VARCHAR2(30);

    BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    FOR r_validate_index IN c_validate_index LOOP

    vSQL := 'ANALYZE INDEX ' || r_validate_index.index_name ||' VALIDATE STRUCTURE';

    EXECUTE IMMEDIATE VSQL;

    vSQL := '';

    SELECT NAME
    INTO v_index
    FROM INDEX_STATS;

    -- Oracle recommends that is PCT WASTED > 20%, consider index rebuild

    SELECT DEL_LF_ROWS
    INTO v_del_lf_rows
    FROM INDEX_STATS;

    DBMS_OUTPUT.PUT_LINE('Analyzed '||v_index || ' '||v_del_lf_rows);

    IF v_del_lf_rows > 0 THEN

    SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
    INTO v_wasted
    FROM INDEX_STATS;

    IF v_wasted > 20 THEN
    DBMS_OUTPUT.PUT_LINE(r_validate_index.index_name ||' has '|| v_wasted ||'% wasted SPACE, consider rebuilding');
    END IF;

    END IF;

    v_wasted := 0;

    END LOOP;

    END validate_indexes;


    Cheers,

    [Edited by grjohnson on 08-15-2001 at 11:21 PM]

  5. #5
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    oh? great! thanks for the valuable feedback...BTW can i
    confirm that to qualify an index for rebuild...the percentage of must be
    (DEL_LF_ROW_LEN/LF_ROW_LEN)*100 > 20%

    OR

    (DEL_LF_ROWS/LF_ROWS_LEN) * 100 as suggested by grjohnson???

    not that i am doubting his suggestions but I would just wish
    to clarify to get the fact right.

    junior

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    This >20% is only a recommendation by Oracle, it obviously depends on your own individual system purpose, another Oracle recommendation is if the indexes blevel >= 4.

    Cheers

  7. #7
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    grjohnson,

    no that's not what i mean...the parameter that u used for measurement is it

    (DEL_LF_ROWS/LF_ROWS_LEN)

    OR

    (DEL_LF_ROWS_LEN/LF_ROWS_LEN)

    ..if u study it closely..these are diff parameters.


    junior

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    ngwh , yes, sorry about that, I will edit my above mail,

    It is


    (DEL_LF_ROWS_LEN/LF_ROWS_LEN)

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hey! grjohnson

    how did undo your previous posting on the index_Stats parameter measurement?
    ...can we undo wat we posted??

    junior

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Click on the edit/delete link at the bottom of your post.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

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