Home » RDBMS Server » Server Administration » index frag report
| index frag report [message #56373] |
Tue, 25 March 2003 12:06 |
Bob
Messages: 49
Registered: March 1999
|
Member |
|
|
I am trying to come up with a complete index fragmentation report on my database. I have two scripts, one calculates the average row length for the index, the other calculates the height, blevel
and fragmentation using that average row. I want to dynamically get a report of all indexes with blevel>4,height>3 and badness (delete) >15%.
Does anyone have a script off hand to create an overall index fragmentation report? Or a script that basically performs a join of the two following scripts? thanks..
col name newline
col height newline
col lf_blk_rows newline
col del_lf_rows newline
col branch_util newline
col leaf_util newline
col can_reduce_level newline
col ibadness newline
col space_util newline
col percent newline
col col1 newline
ACCEPT own PROMPT 'Enter value for owner > '
ACCEPT ind PROMPT 'Enter value for INDEX name > '
ACCEPT avgrow PROMPT 'Enter value for average row > '
validate index &own..&ind;
rem height > 3 consider rebuilding
rem ibadness > 15-20% lots of deletes consider rebuilding
rem blevel > 4 consider rebuilding
spool indstat_temp.sql
select 'define db_block_size = ' || value
from v$parameter
where name = 'db_block_size';
select 'define num_rows = '|| num_rows
from dba_tables
where table_name = (Select table_name from dba_indexes
where index_name = UPPER('&ind')
and owner = UPPER('&own') );
select 'define leaf_blocks = ' || leaf_blocks col1
,'define init_trans = '|| ini_trans col1
,'define blevel = '|| blevel col1
,'define pctfree = '|| pct_free col1
from dba_indexes
where index_name = UPPER('&ind')
and owner = UPPER('&own') ;
spool off
@indstat_temp
select 'index name : '||name
,'height : '||height
,'blevel : ' || &blevel col1
,'space util : '||to_char (
( &num_rows*&avgrow ) * 100 /
(&leaf_blocks *
(
&db_block_size
- (113 + 2*&init_trans)
- (&db_block_size - (113 + 2*&init_trans))*&pctfree/100
)
)
,'99.9')||'%' space_util
,'branch util : '||to_char(
(br_rows_len*100)/(br_blk_len*br_blks)
,'99.9')||'%' branch_util
,'leaf util : '||to_char(
(lf_rows_len - del_lf_rows_len)*100 / (lf_blk_len*lf_blks)
,'99.9')||'%' leaf_util
,'can_reduce_level : '|| DECODE(SIGN(CEIL(
LOG(br_blk_len/(br_rows_len/br_rows)
, lf_blk_len/((lf_rows_len - del_lf_rows_len)
/(lf_rows - del_lf_rows)))) + 1 - height)
, -1, 'YES','NO') can_reduce_level
,'leaf rows deleted : '||to_char(del_lf_rows,'999,999,990') del_lf_rows
,'leaf rows in use : '||to_char(lf_rows-del_lf_rows,'999,999,990') lf_blk_rows
,'index badness : '||to_char((del_lf_rows*100)/(lf_rows)
,'99.9')||'%' ibadness
,'leaf to branches : '||to_char(
(lf_blk_len*100)/(lf_blk_len+br_blk_len),'99.9')||'%' percent
from index_stats
/
second script:
ACCEPT own PROMPT 'Enter value for owner (RETURN for ALL) > '
ACCEPT tab PROMPT 'Enter value for table name (RETURN for ALL) > '
DECLARE
colsize number DEFAULT 0;
avgrow number DEFAULT 0;
ind_avgrow number DEFAULT 0;
l_sql varchar2(2000);
UB1 CONSTANT number := 1; /* Get from v$type_size for your platform */
UB4 CONSTANT number := 4; /* Get from v$type_size for your platform */
SB2 CONSTANT number := 2; /* Get from v$type_size for your platform */
CURSOR get_tabs IS SELECT owner,table_name,avg_row_len FROM all_tables
WHERE table_name like UPPER('%&tab%')
AND OWNER NOT IN ('SYS','SYSTEM')
AND OWNER LIKE UPPER('%&own%')
ORDER BY table_name;
CURSOR get_ind (p_tab_name all_indexes.table_name%TYPE
,p_owner all_indexes.owner%TYPE) IS
SELECT *
FROM all_indexes
WHERE table_name = p_tab_name
AND owner = p_owner;
CURSOR get_ind_cols (p_index all_ind_columns.index_name%TYPE
,p_tab_name all_ind_columns.table_name%TYPE
,p_owner all_ind_columns.table_owner%TYPE) IS
SELECT ic.* , tc.avg_col_len
FROM all_ind_columns ic, all_tab_columns tc
WHERE ic.table_owner = tc.owner
AND ic.table_name = tc.table_name
AND ic.column_name = tc.column_name
AND ic.table_name = p_tab_name
AND ic.table_owner = p_owner
AND ic.index_name = p_index;
CURSOR get_cols (p_tab_name all_tables.table_name%TYPE
,p_owner all_tab_columns.owner%TYPE) IS
SELECT *
FROM all_tab_columns
WHERE table_name = p_tab_name
AND owner = p_owner;
BEGIN
FOR tab_rec IN get_tabs LOOP
-- Do the INDEXES First
FOR ind_rec IN get_ind(tab_rec.table_name,tab_rec.owner) LOOP
ind_avgrow := 0;
FOR get_ind_col IN get_ind_cols (ind_rec.index_name
,tab_rec.table_name,tab_rec.owner) LOOP
IF get_ind_col.avg_col_len IS NULL THEN
-- Try and calculate the average row length using VSIZE
l_sql := 'select round(avg(nvl(vsize(' || get_ind_col.column_name ||
'),0)))' || ' from ' ||
get_ind_col.index_owner||'.'||get_ind_col.table_name
|| ' where rownum < 1000000';
EXECUTE IMMEDIATE l_sql INTO colsize ;
ELSE
colsize := get_ind_col.avg_col_len;
END IF;
ind_avgrow := ind_avgrow + colsize;
END LOOP;
dbms_output.put_line ('Index '||ind_rec.index_name
||' Avg Row Size = '||CEIL(to_char(ind_avgrow)) );
END LOOP;
avgrow := UB1*3; /* row header */
IF tab_rec.avg_row_len IS NULL THEN
-- Try and calculate the average row length using VSIZE
FOR get_col_rec IN get_cols (tab_rec.table_name,tab_rec.owner) LOOP
l_sql := 'select round(avg(nvl(vsize(' || get_col_rec.column_name ||
'),0)))' || ' from ' ||
get_col_rec.owner||'.'||get_col_rec.table_name;
EXECUTE IMMEDIATE l_sql INTO colsize;
--Uncomment this to display the column vsize
--dbms_output.put_line (' Column '||get_col_rec.column_name
-- ||' Col Size = '||to_char(colsize));
avgrow := avgrow + colsize + SB2;
END LOOP;
ELSE
avgrow := tab_rec.avg_row_len;
END IF;
dbms_output.put_line ('Table '||tab_rec.table_name
||' Avg Row Size = '||CEIL(to_char(avgrow)));
END LOOP;
END;
/
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 06:44:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
