Check Table Fragmentation Oracle
The table get more update and delete statement means table will get fragmentation. When table getting full table scan in the that time oracle will read the data upto HWM (HIGH WATER MARK LEVEL). HWM is using to find used block and free block in table. When updating the row in block the update not fit into the existing block it go to another block is called row migration. It based on pctfree. Because pctfree it low means the block get row migration. FINDING THE FRAGMENTATION IN TABLE.
What is Oracle Table Fragmentation?If a table is only subject to inserts, there will not be any fragmentation.Fragmentation comes with lot of DML operation in table.The space which gets freed up during non-insert DML operations is notimmediately re-used (or sometimes, may not get reuse ever at all). This leavesbehind holes in table which results in table fragmentation.When rows are not stored contiguously, or if rows are split onto more than oneblock, performance decreases because these rows require additional blockaccesses.Note that table fragmentation is different from file fragmentation.
When a lotof DML operations are applied on a table, the table will become fragmentedbecause DML does not release free space from the table below the HWM.HWM is an indicator of USED BLOCKS in the database. Blocks below the high watermark (used blocks) have at least once contained data. This data might have beendeleted.
Check Table Fragmentation Oracle Software
Steps to Check and Remove Table Fragmentation:-1. Gather table stats:-To check exact difference in table actual size (dbasegments) and stats size(dbatables). The difference between these value will report actual fragmentationto DBA. So, We have to have updated stats on the table stored in dbatables.Check LASTANALYZED value for table in dbatables. If this value is recent youcan skip this step.
Oracle Index Fragmentation Check
Other wise i would suggest to gather table stats to getupdated stats.exec dbmsstats.gathertablestats('&schemaname','&tablename');2. Check Table size:-Now again check table size using and will find reduced size of the table.select tablename,bytes/(1024.1024.1024) from dbatable where tablename='&tablename';3. Check for Fragmentation in table:-Below query will show the total size of table with fragmentation, expectedwithout fragmentation and how much% of size we can reclaim after removingtable fragmentation.