Table level fragmentation

The rows are split into more than one block is called fragmentation. it decrease the block accesses and it slow the database performance. fragmentation are two type one is table fragmentation and second is index fragmentation.

TABLE FRAGMENTATION:

Each table have the blocks and it stored in the data files. The data will store in the block level. When the data split into more than one blocks is called table fragmentation. 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.

ROW CHAINING:

When inserting the row, in that time the block does not have any space means oracle allocate the next block for that inserting its called the row chaining.

ROW MIGRATION:

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:

Create one table and add 2 column in that table.
Insert max 1000000 records in that table and commit it.
Now do some delete operation in that table and analyze the table using estimate or compute statistics or dbms_space.space_usage package.
Now check the info in dba_tables using query

(SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;)

Its show the num_rows and chain_cnt, if u get any values in chain_cnt means fragmentation is accured.
Using dbms_space.space_usage

Steps to Check and Remove Table Fragmentation:-

1. Gather table stats:

To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

2. Check Table size:

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

3. Check for Fragmentation in table:

SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;

(or)

set pages 50000 lines 32767

select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’

/Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. DEFRAGMENTATIONS (or) remove fragemenation

1. move table to another tablespace

2. export and import the table(exp/imp)

3. shrink command (fron oracle 10g)

(shrink command is only applicable for tables which are tablespace with auto segment space management)

4. CTAS method

5.online redefinition

option: 1 move table to another tablespace

alter table

enable row movement;

alter table

move tablespace ;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

REBUILD ALL INDEXES:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

Here, value in status field may be valid or unusable.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

——– ——————————

UNUSABLE INDEX_NAME

Here UNUSABLE status going to valid

SQL> alter index rebuild online;

Index altered.

Here, value in status field must be valid.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

——– ——————————

VALID INDEX_NAME

Gather table stats:

SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);

PL/SQL procedure successfully completed.

Check Table size:

table size will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

Check for Fragmentation in table:

set pages 50000 lines 32767

Option: 2Export and Import the table(exp/imp)

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\benz.dmp tables=benz

Export: Release 10.1.0.5.0 – Production on Sat Jul 28 16:30:44 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …

. . exporting table BENZ 468904 rows exported

Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:12 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> truncate table benz;

Table truncated.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\benz.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:54 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SCOTT

. . importing table “BENZ” 468904 rows imported

Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:32:21 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BENZ’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42542.27kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’BENZ’,-

> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 3 Shrink command (fron Oracle 10g):-

Shrink command:

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

step to shrink

SQL> alter table

enable row movement;

Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

Part 1: Rearrange (All DML’s can happen during this time)

SQL> alter table

shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table

shrink space;

Table altered.

2. Directly reset the HWM:

SQL> alter table

shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Advantages are:

1. Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.

Option: 4 CTAS method

Examples:-

SQL> create table benz as select * from nijam;

Table created.

SQL> drop table nijam purge;

Table dropped.

SQL> rename benz to nijam;

Table renamed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 68986.97kb

SQL> select status from user_indexes

2 where table_name = ‘NIJAM’;

no rows selected

Note:- we need to create all indexes.

Option:5 online redefinition examples

SQL> create table NIJAM (

2 no number,

3 name varchar2(20) default ‘NONE’,

4 ddate date default SYSDATE);

Table created.

SQL> alter table nijam add constraint pk_no primary key(no);

Table altered.

SQL> begin

2 for x in 1..100000 loop

3 insert into nijam ( no , name, ddate)

4 values ( x , default, default);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_nijam

2 after insert on nijam

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> select count(*) from nijam;

COUNT(*)

———-

100000

SQL> delete nijam where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 822.69kb

Minimum Privs required “DBA” role or “SELECT” on dbms_redefinition pkg

First check table is condidate for redefinition.

SQL> exec sys.dbms_redefinition.can_redef_table(‘SCOTT’,-

> ‘NIJAM’,-

> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

After verifying that the table can be redefined online, you manually crea

te an empty interim table (in the same schema as the table to be redefined)

SQL> create table BENZ as select * from nijam WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

This procedure keeps the interim table synchronized with the original tab

SQL> exec sys.dbms_redefinition.sync_interim_table (‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> –Create PRIMARY KEY on interim table(BENZ)

SQL> alter table BENZ

2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_benz

2 after insert on benz

3 begin

4 null;

5 end;

6 /

Trigger created.

Disable foreign key on original table if exists before finish this proces

SQL> exec sys.dbms_redefinition.finish_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 841.4kb

SQL> select status,constraint_name

2 from user_constraints

3 where table_name = ‘NIJAM’;

STATUS CONSTRAINT_NAME

——– ——————————

ENABLED PK_NO1

SQL> select status ,trigger_name

2 from user_triggers

3 where table_name = ‘NIJAM’;

STATUS TRIGGER_NAME

——– ——————————

ENABLED TRI_BENZ

SQL> drop table BENZ PURGE;

Table dropped.

Design a site like this with WordPress.com

Get started

postgres

Category Archives: Table level fragmentation

Fragmentation Defragementation In Table Level