Script for checking the
highest fragmented table.
Set lines 200 pages
200;
SELECT * FROM (SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,NUM_ROWS,AVG_ROW_LEN
ROWLEN,BLOCKS,ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,ROUND(BLOCKS
* (8000 - 23 * INI_TRANS) *(1 - PCT_FREE / 100) / 1000000, 0)
GROSS_MB,ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100)
-(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB" FROM DBA_TABLES
WHERE NUM_ROWS IS NOT NULL AND OWNER LIKE 'FINANCE%' AND PARTITIONED = 'NO' AND
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL) ORDER BY 7 DESC) WHERE ROWNUM <=20;
TABLE_NAME NUM_ROWS ROWLEN
BLOCKS NET_MB GROSS_MB
WASTED_MB
---------------------
---------- ---------- ---------- ---------- ---------- ----------
EMP_RECORDS 68208
2159 341618 147 2453 2305
SALES 1 26
74467 0 535 535
VENDOR_RECORDS 607008 2089
204515 1269 1468 200
We have 3 tables which are fragmented the most.
I am taking EM_RECORDS table for clearing fragmentation. We can do the same for
other tables.
STEP 1) Check the Table size:
SQL> select bytes/1024/1024
from dba_segments where segment_name='EMP_RECORDS';
BYTES/1024/1024
---------------
265
STEP 2) GATHER THE STATS OF TABLE ‘EMP_RECORDS’
SQL> exec dbms_stats.gather_table_stats
(ownname=>'FINANCE',tabname=>'EMP_RECORDS',estimate_percent=>100);
STEP 3) CHECK THE
FRAGMENTATION:
set lines 300 pages 300 ;
col owner form a15
col table_name form a15
select a.owner, a.table_name,
b.bytes/1024/1024 "Size(MB)",
(a.avg_row_len*a.num_rows/1024/1024) "Actual(MB)",
to_char(last_analyzed,'DD-MON-YY')
"LAST ANAL",
trunc((b.bytes/1024/1024) -
(a.avg_row_len*a.num_rows/1024/1024)) "Diff(MB)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/
b.bytes "% Frag"
from dba_tables a,
dba_segments b
where a.table_name =
b.segment_name
and a.owner = b.owner
and a.table_name = '&TABLE_NAME'
order by 6
;
This will prompt table name
type the name of table you want to check for fragmentation.
Enter value for table_name: EMP_RECORDS
old 9: and a.table_name = '&TABLE_NAME'
new 9: and a.table_name = 'EMP_RECORDS'
OWNER TABLE_NAME Size(G) Actual(G) LAST ANAL Diff(G)
% Frag
---------------
--------------- ---------- ---------- --------- ---------- ----------
FINANCE EMP_RECORDS 265 194.0979 29-MAY-18 70 26.7555093
STEP 4) Connect to user ‘FINANCE’ and delete some data from this table:
SQL>conn FINANCE/finance
SQL>
49250 rows deleted.
STEP 5) GATHER THE STATS OF TABLE ‘EMP_RECORDS’
SQL> exec dbms_stats.gather_table_stats
(ownname=>'FINANCE',tabname=>'EMP_RECORDS',estimate_percent=>100);
STEP 6) Now check again tables
fragmentation and the size using steps 3 and step 1 query:
OWNER TABLE_NAME Size(MB) Actual(MB) LAST ANAL Diff(MB)
% Frag
---------------
--------------- ---------- ---------- --------- ---------- ----------
FINANCE EMP_RECORDS 265 75.5155849 29-MAY-18 189 71.5035529
We can see the table still have the same size
even after deleting bulk records. Actual size of the table is only 75 MB but it
occupies the 265 MB of tablespace. But you can see more fragmentation as
compared before deleting records.
We have to clear the fragmentation only than we
can get the desired size of table and for that we have to move the data of the
table to same or any other tablespace. But this makes the index unusable which
are associated with the table.
Let’s check the status of indexes related to
table ‘EMP_RECORDS’
SQL> SELECT INDEX_NAME,
TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';
INDEX_NAME TABLE_OWNER TABLESPACE_NAME STATUS
------------------------- --------------------
--------------- ------------
SYS_IL0000134180C00005$$ FINANCE FINANCEIDX VALID
SYS_IL0000134180C00006$$ FINANCE
FINANCEIDX VALID
MAT_BD FINANCE FINANCE VALID
MAT_RL FINANCE FINANCE VALID
MAT_COUNTRY FINANCE FINANCE VALID
MAT_UNIQ FINANCE FINANCE VALID
PK_REC FINANCE FINANCE VALID
SYS_IL0000134180C00004$$ FINANCE FINANCEIDX VALID
STEP 7) Move the table to same tablespace or
another this will clear the fragmentation and gives you actual size of table.
SQL> alter table FINANCE.EMP_RECORDS move ;
Table altered.
STEP8) Let’s Check the fragmentation again.
set lines 300 pages 300 ;
col owner form a15
col table_name form a15
select a.owner, a.table_name,
b.bytes/1024/1024 "Size(MB)",
(a.avg_row_len*a.num_rows/1024/1024) "Actual(MB)",
to_char(last_analyzed,'DD-MON-YY')
"LAST ANAL",
trunc((b.bytes/1024/1024) -
(a.avg_row_len*a.num_rows/1024/1024)) "Diff(MB)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/
b.bytes "% Frag"
from dba_tables a,
dba_segments b
where a.table_name =
b.segment_name
and a.owner = b.owner
and a.table_name = '&TABLE_NAME'
order by 6;
OWNER
TABLE_NAME Size(MB)
Actual(MB) LAST ANAL Diff(MB) % Frag
--------------- --------------- ----------
---------- --------- ---------- ----------
FINANCE
EMP_RECORDS 104
75.5155849 29-MAY-18 28 27.3888606
Now the table size reduced to 104 MB.
But this makes the indexes unusable. We can check
the same as below:
SQL> SELECT INDEX_NAME,
TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';
INDEX_NAME TABLE_OWNER TABLESPACE_NAME STATUS
------------------------- --------------------
--------------- ------------
SYS_IL0000134180C00005$$ FINANCE FINANCEIDX VALID
SYS_IL0000134180C00006$$ FINANCE
FINANCEIDX VALID
MAT_BD FINANCE FINANCE UNUSABLE
MAT_RL FINANCE FINANCE UNUSABLE
MAT_COUNTRY FINANCE FINANCE UNUSABLE
MAT_UNIQ FINANCE FINANCE UNUSABLE
PK_REC FINANCE FINANCE UNUSABLE
SYS_IL0000134180C00004$$ FINANCE FINANCEIDX VALID
We have to rebuild the indexes to make them
valid again. We have the dynamic script to get the rebuild command.
SQL> select 'alter index
'||owner||'.'||index_name||' rebuild;' from dba_indexes where status in
('UNUSABLE','INVALID') and table_name='EMP_RECORDS' AND OWNER='FINANCE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
----------------------------------------------------------------------------------
alter index FINANCE.MAT_BD rebuild;
alter index FINANCE.MAT_RL rebuild;
alter index FINANCE.MAT_COUNTRY rebuild;
alter index FINANCE.MAT_UNIQ rebuild;
alter index FINANCE.PK_REC rebuild;
Execute the result of above script.
SQL> alter index FINANCE.MAT_BD rebuild;
SQL> alter index FINANCE.MAT_RL rebuild;
SQL> alter index FINANCE.MAT_COUNTRY rebuild;
SQL> alter index FINANCE.MAT_UNIQ rebuild;
SQL> alter index FINANCE.PK_REC rebuild;
Now we can check the status of index as below.
We can see all become valid.
SQL> SELECT INDEX_NAME, TABLE_OWNER,TABLESPACE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='EMP_RECORDS';
INDEX_NAME TABLE_OWNER TABLESPACE_NAME STATUS
------------------------- -------------------- --------------- ------------
SYS_IL0000134180C00005$$ FINANCE FINANCEIDX VALID
SYS_IL0000134180C00006$$ FINANCE FINANCEIDX VALID
MAT_BD FINANCE FINANCE VALID
MAT_RL FINANCE FINANCE VALID
MAT_COUNTRY FINANCE FINANCE VALID
MAT_UNIQ FINANCE FINANCE VALID
PK_REC FINANCE FINANCE VALID
SYS_IL0000134180C00004$$ FINANCE FINANCEIDX VALID
No comments:
Post a Comment