-
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.
Jeff Hunter
-
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,
OCP 8i, 9i DBA
Brisbane Australia
-
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,
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|