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

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

Hi,

Use Analyze index index1 VALIDATE STRUCTURE

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.

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]

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

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

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

ngwh , yes, sorry about that, I will edit my above mail,

It is


(DEL_LF_ROWS_LEN/LF_ROWS_LEN)

Cheers,

hey! grjohnson

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

junior

Click on the edit/delete link at the bottom of your post.

Cheers,

oh...i see.. i missed those small icons :)

Hi

The script works well in 8i but not 8. Get the following error...

LINE/COL
---------------------------------------------------------------------------------
ERROR
---------------------------------------------------------------------------------------------------------------
28/9
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ;

What corrections do u suggest?

Thanks & Regards