Problem Description: How to identify fragmented tables
Symptoms
1. Run the below statement to identified fragmented tables
--Query1
-- TABLE Size with Fragmentation
select table_name,round(blocks*8,2)||'kb' "size fragmented" ,round((num_rows*avg_row_len/1024),2)||'kb' "actual size" from dba_tables where owner='ECS';
and
-- Query2 to order most fragmented tables by size
-- Most Fragmented Tables
select table_name,round(blocks*8,2) - round((num_rows*avg_row_len/1024),2) " Fragmented Data in KB" from dba_tables where owner='ECS' order by 2 desc;
2. Then on the most fragmented tables and indexes identified from the above statement, Run
Alter table xx move tablespace xx;
Alter index ee rebuild tablespace ee;
Then I re run the above statements and I noticed not that much space reclaimed from mostly fragmented tables.
Solution:
1. analyze on all the tables of the schema before running the script to get the correct size.
2. Determine fragmented tables by running:
select table_name,round(blocks*8,2)||'kb' "size fragmented" ,round((num_rows*avg_row_len/1024),2)||'kb' "actual size" from dba_tables where owner='ECS';
3. The move table method is the best way to remove fragmentation as much as possible.
4. Fragmentation cannot be completely removed in case there chained/migrated rows.
Chaining
---------
Occurs when the row is too large to fit into one data block when it is first
inserted. In this case, Oracle stores the data for the row in a chain of data
blocks (one or more) reserved for that segment. Row chaining most often occurs
with large rows, such as rows that contain a column of datatype LONG, LONG RAW,
LOB, etc. Row chaining in these cases is unavoidable.
A row is too large to fit into a single database block. For example, if you use a 4KB block size
for your database,and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store
the row in pieces. Some conditions that will cause row chaining are:
In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved
for that segment.So, instead of just having a forwarding address on one block and the data on another
we have data on two or more blocks.
How you can identify the Chained and Migrated rows ?
=====================================================
You can use the ANALYZE command to list the Chained/Migrated rows in a table, but you should consider the followings:
A-For conventional Non index_organized tables you can create chained-rows table using the following script locally or in another schema:
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date );
Note : If you created the chained-rows table in another schema then in the analyze command prefix the name by the schema name.
sql>analyze table <Non IOT > list chained rows into <chained-rows table name> ;
Reference:
Article-ID: Note 337651.1
Title: How to find Objects Fragmentated below High water mark
