Oracle 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.
- 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
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:
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:_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.
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.
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 <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;
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 validSQL> alter index <INDEX_NAME> 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
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'
/
Option: 2 Export and Import the table(exp/imp) SQL> select table_name, round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BENZ';
TABLE_NAME size
------------------------------ ------------------------------------------
BENZ 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables 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 - Production
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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" from user_tables where table_name = 'BENZ';
TABLE_NAME size
------------------------------ ------------------------------------------
BENZ 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables 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" from user_tables where table_name = 'BENZ';
TABLE_NAME size
------------------------------ ------------------------------------------
BENZ 51840kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables 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 <table_name> 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 <table_name> shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.
2. Directly reset the HWM:
SQL> alter table <table_name> 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" from user_tables 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.
--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
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" from user_tables where table_name = 'NIJAM';
TABLE_NAME size
------------------------------ ------------------------------------------
NIJAM 1376kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'NIJAM';
TABLE_NAME size
------------------------------ ------------------------------------------
NIJAM 841.4kb
SQL> select status,constraint_name from user_constraints where table_name = 'NIJAM';
STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1
SQL> select status ,trigger_name from user_triggers where table_name = 'NIJAM';
STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_BENZ
SQL> drop table BENZ PURGE;
Table dropped.
Comments
Post a Comment