-----check INDEX SIZE more than 1 gega bytes

SELECT OWNER AS OWNER,

SEGMENT_NAME AS SEGMENT_NAME,

SEGMENT_TYPE,

SUM(BYTES) / 1024 / 1024 AS "SEGMENT_SIZE(M)"

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = '&TBS'

AND BYTES > 1073741824

GROUP BY OWNER, SEGMENT_NAME,SEGMENT_TYPE

ORDER BY 3;

----analyze the INDEXES in tablespace which the space used rate are too high

SELECT 'ANALYZE INDEX ' || SEGMENT_NAME || ' VALIDATE STRUCTURE;'

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = '&TBS'

AND BYTES > 1073741824

AND SEGMENT_TYPE = 'INDEX'

ORDER BY SEGMENT_NAME;

----check the empty blocks in INDEXES

SELECT NAME, BLOCKS, LF_BLKS, BR_BLKS, BLOCKS - (LF_BLKS + BR_BLKS) EMPTY

FROM INDEX_STATS

WHERE NAME IN (SELECT SEGMENT_NAME

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = '&TBS'

AND BYTES > 1073741824

AND SEGMENT_TYPE = 'INDEX'

ORDER BY SEGMENT_NAME);

----check delete rows in INDEXES

SELECT NAME,

LF_ROWS,

T.LF_BLKS,

T.DEL_LF_ROWS,

(T.DEL_LF_ROWS / T.LF_ROWS) * 100 RATIO

FROM INDEX_STATS T

WHERE T.NAME = '&IND_NAME';

----check INDEXES degree and usage rate

SELECT HEIGHT, NAME, BTREE_SPACE, USED_SPACE, PCT_USED FROM INDEX_STATS;

if found the degree of indexes are bigger than 4, We can rebuild those indexes

----gather table

SELECT 'exec dbms_stats.gather_table_stats(''' || OWNER || ''',''' ||

TABLE_NAME || ''',' || 'DEGREE=>8,CASCADE => TRUE);'

FROM DBA_INDEXES

WHERE TABLESPACE_NAME = '&TBS';

----ensure indexes used size

SELECT OWNER AS OWNER,

SEGMENT_NAME AS SEGMENT_NAME,

SUM(BYTES) / 1024 / 1024 AS "SEGMENT_SIZE(M)"

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'MD0002ASTCOM_SNAP_INDEX'

GROUP BY OWNER, SEGMENT_NAME

ORDER BY 3;

----connect the schema where indexes is

alter session set current_schema=MD0002ASTCOM;

----rebuild the indexes which size are bigger than 2G

select 'alter index '||owner||'.'||index_name|| ' rebuild online' from dba_indexes where tablespace_name='MD0002ASTCOM_SNAP_INDEX';

Rebuild Indexes

相關文章