HOW TO CHECK INDEX FRAGMENTATION.
When a database is frequently updated via UPDATE, or DELETE statements we can expect it to become fragmented over the time.
If database indexes are fragmented, the SQL query optimizer may chose a non-optimal execution plan when using an index to resolve a query.
This will affect the overall query performance and you may notice a query behaving slower than normal.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
-- select only those indexes with an estimated space saving percent greater than 25% VAR savings_percent NUMBER; EXEC :savings_percent := 25; -- select only indexes with current size (as per cbo stats) greater then 1MB VAR minimum_size_mb NUMBER; EXEC :minimum_size_mb := 1; SET SERVEROUT ON ECHO OFF FEED OFF VER OFF TAB OFF LINES 300; COL report_date NEW_V report_date; SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') report_date FROM DUAL; SPO /tmp/indexes_2b_shrunk_&&report_date..txt; DECLARE l_used_bytes NUMBER; l_alloc_bytes NUMBER; l_percent NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('PDB: '||SYS_CONTEXT('USERENV', 'CON_NAME')); DBMS_OUTPUT.PUT_LINE('---'); DBMS_OUTPUT.PUT_LINE( RPAD('INDEX_NAME', 35)||' '|| RPAD('TABLE_NAME', 35)||' '|| LPAD('SAVING %', 10)||' '|| LPAD('CURRENT SIZE', 20)||' '|| LPAD('ESTIMATED SIZE', 20)); DBMS_OUTPUT.PUT_LINE( RPAD('-', 35, '-')||' '|| LPAD('-', 10, '-')||' '|| LPAD('-', 20, '-')||' '|| LPAD('-', 20, '-')); FOR i IN (SELECT x.owner,x.table_name, x.index_name, SUM(s.leaf_blocks) * TO_NUMBER(p.value) index_size, REPLACE(DBMS_METADATA.GET_DDL('INDEX',x.index_name,x.owner),CHR(10),CHR(32)) ddl FROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter p WHERE u.oracle_maintained = 'N' AND x.owner = u.username AND x.tablespace_name NOT IN ('SYSTEM','SYSAUX') AND x.index_type LIKE '%NORMAL%' AND x.table_type = 'TABLE' AND x.status = 'VALID' AND x.temporary = 'N' AND x.dropped = 'NO' AND x.visibility = 'VISIBLE' AND x.segment_created = 'YES' AND x.orphaned_entries = 'NO' AND p.name = 'db_block_size' AND s.owner = x.owner AND s.index_name = x.index_name GROUP BY x.owner, x.table_name,x.index_name, p.value HAVING SUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20) ORDER BY index_size DESC) LOOP DBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes); IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THEN l_percent := 100 * (i.index_size - l_alloc_bytes) / i.index_size; DBMS_OUTPUT.PUT_LINE( RPAD(i.owner||'.'||i.index_name, 35)||' '|| RPAD(i.table_name, 35)||' '|| LPAD(TO_CHAR(ROUND(l_percent, 1), '990.0')||' % ', 10)||' '|| LPAD(TO_CHAR(ROUND(i.index_size / POWER(2,20), 1), '999,999,990.0')||' MB', 20)||' '|| LPAD(TO_CHAR(ROUND(l_alloc_bytes / POWER(2,20), 1), '999,999,990.0')||' MB', 20)); END IF; END LOOP; END; / |
OUTPUT :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> @index_rebuilt PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. REPORT_DATE ------------------- 2018-11-27T14:48:32 PDB: ORCL1 INDEX_NAME TABLE_NAME SAVING % CURRENT SIZE ESTIMATED SIZE ----------------------------------- ---------------------------------- ------------ ------------- -------------------- SCOTT.IPC_SALE_PART_N3_M IPC_SALE 31.1 % 5,482.9 MB 3,776.0 MB SCOTT.KTEXP_EMAIL_N1 KTEXP_EMAIL 46.6 % 4,914.6 MB 2,624.0 MB SCOTT.KTEXP_KIGHT_ISSUE_MSG_HEADR KTEXP_KIGHT_ISSUE_MSG_HEADER 51.9 % 4,660.6 MB 2,240.0 MB SCOTT.IPC_SALE_P_M IPC_SALE 46.1 % 4,633.1 MB 2,496.0 MB SCOTT.SALE_PROFILE_IDX_M IPC_SALE 41.8 % 4,182.3 MB 2,432.0 MB SCOTT_PRV.IPC_SALE_P_M IPC_SALE 45.2 % 3,385.6 MB 1,856.0 MB SCOTT.PK_KTEXP_EMAIL_EMAIL_ID KTEXP_EMAIL 47.6 % 3,298.9 MB 1,728.0 MB SCOTT.KTEXP_ISSUE_MESSAGE_PK KTEXP_ISSUE_MESSAGE 40.3 % 1,594.7 MB 952.0 MB SCOTT.KTEXP_ISSUE_MESSAGE_IDX_PROF KTEXP_ISSUE_MESSAGE 25.1 % 1,271.7 MB 952.0 MB SCOTT_PRV.KTEXP_ISSUE_MESSAGE_PK KTEXP_ISSUE_MESSAGE 41.8 % 961.4 MB 560.0 MB SCOTT.KTEXP_NET_DEVICES_P KTEXP_NET_DEVICES 97.7 % 705.4 MB 16.0 MB SCOTT.KTEXP_NET_DEVICES_IDX2 KTEXP_NET_DEVICES 97.8 % 667.6 MB 15.0 MB SCOTT.KTEXP_KIGHT_SALE_VALUES_PK KTEXP_KIGHT_ISSUE_VALUES 51.4 % 592.6 MB 288.0 MB SCOTT.SYS_C0034152 KTEXP_SALE_RISK 41.9 % 372.1 MB 216.0 MB SCOTT.KTEXP_KIGHT_ISSUE_MSG_IDX KTEXP_KIGHT_ISSUE_MSG 53.6 % 362.2 MB 168.0 MB SCOTT.KTEXP_KIGHT_ISSUE_MSG_PK KTEXP_KIGHT_ISSUE_MSG 54.2 % 349.3 MB 160.0 MB SCOTT.CSRT_UPD_PROF_P TKT_UPD_PROPS 33.6 % 337.4 MB 224.0 MB SCOTT.DMS_LIMBO_PTYPES_P DMS_LIMBO_PTYPES 32.3 % 318.9 MB 216.0 MB SCOTT.PK_KTEXP_ITEM_DYN_PROMO_SHAR KTEXP_ITEM_DYNAMIC_PROMO_SHARE 41.9 % 316.9 MB 184.0 MB SCOTT.LIMBO_PROPS_PK DMS_LIMBO_PROPS 31.2 % 314.0 MB 216.0 MB SCOTT.KTEXP_KIGHT_ISSUE_MSG_IDX_D KTEXP_KIGHT_ISSUE_MSG 48.4 % 309.8 MB 160.0 MB SCOTT.KTEXP_KIGHT_ISSUE_MSG_IDX_P KTEXP_KIGHT_ISSUE_MSG 41.6 % 274.2 MB 160.0 MB |
Note: Please test scripts in Non Prod before trying in Production.
(8 votes, average: 4.00 out of 5)
Loading...
