TABLE FRAGMENTATION...
DBA’s faced data fragmentation
issues frequently. Data fragmentation affects the database performance.
In production database, we need to follow any one of the below method to solve
the fragmentation issue.
Methods:
1. Alter table move tablespace ;
--- Also we need to rebuild the indexes associate with tables.
2. Export the table / truncate the table / import the table
3. Create table as select (CTAS)
4. Online Data Redefinition
Table is not accessible for during the reorganization of table using Method 1,
2 & 3.
Using online redefinition, we no
need outage & also user can access the table for DML operation.
About online redefinition
For large, active databases, it is
sometime necessary to redefine large “hot” tables to improve the performance of
queries or data manipulation language (DML) operations performed against these
tables. Additionally business applications may require underlying database
structure to be changed or transformed periodically.
Oracle Online Redefinition mechanism provides a significant increase in
availability compared to traditional methods of redefining tables that require
tables to be taken offline. When a table is redefined online, it is accessible
by all read and write operations during the redefinition process.
Administrators then have control over when to switch from the original to the
newly redefined table.
The switch process is very brief and is independent of the size of the table or
the complexity of the redefinition. The redefinition process effectively
creates a new table and improves its data block layout efficiency.
Last week I have tested the table
fragmentation scenario in my test server.
Operating system: Windows XP
Oracle version: 11.1.0.7
Steps for Online Redefinition of
Tables
There are five basic steps to
redefine a table:
1. Create a new image of the table
with all of the desired attributes.
2. Start the redefinition process.
3. Create any triggers, indexes, grants and constraints on the new image of the
table.
4. Optionally synchronize and validate data in the new image of the table
periodically.
5. Complete the redefinition of the table.
With online table redefinition, you
can
-Modify the physical attributes or
storage parameters of a table.
-Convert a LONG or LONG RAW column to a LOB.
-Add or drop partitioning support.
-Add, drop, or rename columns in a table.
-Reorganize a Single Partition.
-------------------------------------------------------------------------------
Create a user & test the
scenario
SQL> create user NASHIM
identified by NASHIM;
User created.
SQL> grant
sysdba,connect,resource,dba to NASHIM;
Grant succeeded.
--- Created test table
SQL> create table NASHIM.NASHIM_objects
as select * from dba_objects nologging;
Table created.
SQL> select * from dba_tab_privs
where owner='NASHIM';
no rows selected
SQL> grant
select,insert,update,delete on NASHIM.NASHIM_objects to scott,hr;
Grant succeeded.
SQL> select * from dba_tab_privs
where owner='NASHIM';
GRANTEE OWNER TABLE_NAME GRANTOR
PRIVILEGE GRA HIE
---------- ---------- ------------------------- ---------- ----------------------------------------
--- ---
HR NASHIM NASHIM_OBJECTS NASHIM UPDATE NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM UPDATE NO NO
HR NASHIM NASHIM_OBJECTS NASHIM SELECT NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM SELECT NO NO
HR NASHIM NASHIM_OBJECTS NASHIM INSERT NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM INSERT NO NO
HR NASHIM NASHIM_OBJECTS NASHIM DELETE NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM DELETE NO NO
8 rows selected.
SQL> exec
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'NASHIM' , tabname => 'NASHIM_OBJECTS',cascade
=> true, estimate_percent => 100,method_opt=>'for all indexed columns
size 1', granularity => 'ALL', degree => 4);
PL/SQL procedure successfully
completed.
SQL> select
owner,table_name,last_analyzed,num_rows from dba_tables
2 where owner='NASHIM';
OWNER TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ ------------------------------ ---------
----------
NASHIM NASHIM_OBJECTS 12-APR-10 58270
SQL> select count(*) from NASHIM.NASHIM_objects;
COUNT(*)
----------
69262
---Delete the half of the records
from table
SQL> delete from NASHIM.NASHIM_objects
where rownum < 30000;
29999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from NASHIM.NASHIM_objects;
COUNT(*)
----------
29264
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'NASHIM' , tabname
=> 'NASHIM_OBJECTS',cascade => true, estimate_percent =>
100,method_opt=>'for all indexed columns size 1', granularity => 'ALL',
degree => 4);
PL/SQL procedure successfully
completed.
SQL> select count(*) from NASHIM.NASHIM_objects;
COUNT(*)
----------
29264
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- --------------- ---------- ---------- ----------- ---------------
--------------- --------------------
NASHIM NASHIM_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
------ Table actual size is 2.82 MB.
But total size is 8.16 MB. Around 5 MB is fragmented space.
Verifying the eligibility of the
table
Note: online redefinition method to
use the Primary key method or rowid methods
SQL> exec
dbms_redefinition.can_redef_table('NASHIM','NASHIM_OBJECTS');
PL/SQL procedure successfully
completed.
Creating the temporary table
SQL> create table NASHIM.NASHIM_OBJECTS_TEMP
as select * from NASHIM.NASHIM_OBJECTS where 1=2;
Table created.
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- --------------- ---------- ---------- ----------- ---------------
--------------- --------------------
NASHIM NASHIM_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
NASHIM NASHIM_OBJECTS_TE MB Mb MB
MP
Redefining the table
SQL> begin
2 dbms_redefinition.start_redef_table('NASHIM','NASHIM_OBJECTS','NASHIM_OBJECTS_TEMP');
3 end;
4 /
PL/SQL procedure successfully
completed.
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
---- While we start the redefiniton
process,start_redef procedure create two temp tables.
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- -----------
--------------- --------------- --------------------
NASHIM NASHIM_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
NASHIM NASHIM_OBJECTS_TEMP MB Mb MB
NASHIM MLOG$_NASHIM_OBJECTS MB Mb MB
NASHIM RUPD$_NASHIM_OBJECTS MB Mb MB
Checking the records
SQL> select count(*) from NASHIM.NASHIM_OBJECTS_TEMP;
COUNT(*)
----------
29264
SQL> select count(*) from NASHIM.NASHIM_OBJECTS;
COUNT(*)
----------
29264
Copying the dependen objects
Its automatically creating the exist
triggers,grants,indexes & constraints.
SQL> dECLARE
2 NUM_ERRORs pLS_INTEGER;
3 BEGIN
4 dbms_redefinition.copy_table_dependents('NASHIM','NASHIM_OBJECTS','NASHIM_OBJECTS_TEMP',
5 dbms_redefinition.cons_orig_params,TRUE,TRUE,TRUE,TRUE,NUM_ERRORS);
6 end;
7 /
PL/SQL procedure successfully
completed.
---Copy the table privileges
SQL> select * from dba_tab_privs
where owner='NASHIM';
GRANTEE OWNER TABLE_NAME GRANTOR
PRIVILEGE GRA HIE
---------- ---------- ------------------------- ----------
---------------------------------------- --- ---
HR NASHIM NASHIM_OBJECTS NASHIM UPDATE NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM UPDATE NO NO
HR NASHIM NASHIM_OBJECTS NASHIM SELECT NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM SELECT NO NO
HR NASHIM NASHIM_OBJECTS NASHIM INSERT NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM INSERT NO NO
HR NASHIM NASHIM_OBJECTS NASHIM DELETE NO NO
SCOTT NASHIM NASHIM_OBJECTS NASHIM DELETE NO NO
HR NASHIM NASHIM_OBJECTS_TEMP NASHIM UPDATE NO NO
HR NASHIM NASHIM_OBJECTS_TEMP NASHIM SELECT NO NO
HR NASHIM NASHIM_OBJECTS_TEMP NASHIM INSERT NO NO
HR NASHIM NASHIM_OBJECTS_TEMP NASHIM DELETE NO NO
SCOTT NASHIM NASHIM_OBJECTS_TEMP NASHIM UPDATE NO NO
SCOTT NASHIM NASHIM_OBJECTS_TEMP NASHIM SELECT NO NO
SCOTT NASHIM NASHIM_OBJECTS_TEMP NASHIM INSERT NO NO
SCOTT NASHIM NASHIM_OBJECTS_TEMP NASHIM DELETE NO NO
16 rows selected.
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
||'MB' "FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- -----------
--------------- --------------- --------------------
NASHIM NASHIM_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
NASHIM NASHIM_OBJECTS_TEMP MB Mb MB
NASHIM MLOG$_NASHIM_OBJECTS MB Mb MB
NASHIM RUPD$_NASHIM_OBJECTS MB Mb MB
Checking for errors
SQL> select
object_name,base_table_name,DDL_TXT from dba_redefinition_errors;
no rows selected
Synchronizing the interim table
& source tables
SQL> exec
dbms_redefinition.sync_interim_table('NASHIM','NASHIM_OBJECTS','NASHIM_OBJECTS_TEMP');
PL/SQL procedure successfully
completed.
Completing the Redefination process
SQL> exec
dbms_redefinition.finish_redef_table('NASHIM','NASHIM_OBJECTS','NASHIM_OBJECTS_TEMP');
PL/SQL procedure successfully
completed.
Gather the statistics
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'NASHIM' , tabname
=> 'NASHIM_OBJECTS',cascade => true, estimate_percent =>
100,method_opt=>'for all indexed columns size 1', granularity => 'ALL',
degree => 4);
PL/SQL procedure successfully
completed.
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- -----------
--------------- --------------- --------------------
NASHIM NASHIM_OBJECTS_TEMP 1045 29264 101 8.16MB 2.82Mb 5.35MB
NASHIM NASHIM_OBJECTS 448 29264 101 3.5MB 2.82Mb .68MB
SQL> drop table NASHIM.NASHIM_OBJECTS_TEMP;
Table dropped.
SQL> select
owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB'
"TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='NASHIM';
OWNER TABLE_NAME BLOCKS NUM_ROWS
AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- -----------
--------------- --------------- --------------------
NASHIM NASHIM_OBJECTS 448 29264 101 3.5MB 2.82Mb .68MB
