Fragmentation in Oracle
What is Fragmentation:-
If a table is only subject to inserts, there will not be any fragmentation. Fragmentation comes with when we update/delete data in table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.
To understand it more clearly, we need to be clear on how oracle manages space for tables.
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
How do you find fragmentation is accrued:-
SQL> select segment_name, bytes/(1024*1024*1024) "Size GB" from dba_segments where segment_name='VIGNESH';
SEGMENT_NAME Size GB
-------------------------------------------------------------------------------------------------------------------------------- ----------
VIGNESH .0703125
SQL> select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from dba_tables WHERE table_name='&TABLE_NAME'; 2 3 4 5
Enter value for table_name: VIGNESH
old 5: from all_tables WHERE table_name='&TABLE_NAME'
new 5: from all_tables WHERE table_name='VIGNESH'
View to find fragmentation:-
In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.
Check actual table size, fragmented size and percentage of fragmentation in a table.
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';
check table size.
SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';
Enter value for table_name: REV
old 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'
new 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'
SUM(BYTES)/1024/1024/1024
-------------------------
.015625
SQL> select count(*)from rev;
COUNT(*)
----------
1048576
Delete some rows in the table.
SQL> delete from rev where id=2;
1048576 rows deleted.
SQL> commit;
Commit complete.
After deletion the size of the table are same.
SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';
Enter value for table_name: REV
old 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'
new 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'
SUM(BYTES)/1024/1024/1024
-------------------------
.015625
Recreate the table in the new tablespace are move the table.
SQL> alter table rev move tablespace users;
Table altered.
After table move the size can be reduced fragmentation will be cleared.
SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME';
Enter value for table_name: REV
old 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='&TABLE_NAME'
new 1: select sum(bytes)/1024/1024/1024 from user_segments where segment_name='REV'
SUM(BYTES)/1024/1024/1024
-------------------------
.000061035
***************************************************************************
No comments:
Post a Comment