Tuesday, April 13, 2010

Online redefintion in oracle - 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 raja identified by raja;

User created.

SQL> grant sysdba,connect,resource,dba to raja;

Grant succeeded.

--- Created test table

SQL> create table raja.raja_objects as select * from dba_objects nologging;

Table created.

SQL> select * from dba_tab_privs where owner='RAJA';

no rows selected

SQL> grant select,insert,update,delete on raja.raja_objects to scott,hr;

Grant succeeded.

SQL> select * from dba_tab_privs where owner='RAJA';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ------------------------- ---------- ---------------------------------------- --- ---
HR RAJA RAJA_OBJECTS RAJA UPDATE NO NO
SCOTT RAJA RAJA_OBJECTS RAJA UPDATE NO NO
HR RAJA RAJA_OBJECTS RAJA SELECT NO NO
SCOTT RAJA RAJA_OBJECTS RAJA SELECT NO NO
HR RAJA RAJA_OBJECTS RAJA INSERT NO NO
SCOTT RAJA RAJA_OBJECTS RAJA INSERT NO NO
HR RAJA RAJA_OBJECTS RAJA DELETE NO NO
SCOTT RAJA RAJA_OBJECTS RAJA DELETE NO NO

8 rows selected.


SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'RAJA' , tabname => 'RAJA_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='RAJA';

OWNER TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ ------------------------------ --------- ----------
RAJA RAJA_OBJECTS 12-APR-10 69262


SQL> select count(*) from raja.raja_objects;

COUNT(*)
----------
69262

---Delete the half of the records from table

SQL> delete from raja.raja_objects where rownum < 30000; 29999 rows deleted. SQL> commit;

Commit complete.

SQL> select count(*) from raja.raja_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 => 'RAJA' , tabname => 'RAJA_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 raja.raja_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='RAJA';

OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- --------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_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('RAJA','RAJA_OBJECTS');

PL/SQL procedure successfully completed.

Creating the temporary table

SQL> create table RAJA.RAJA_OBJECTS_TEMP as select * from RAJA.RAJA_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='RAJA';

OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- --------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
RAJA RAJA_OBJECTS_TE MB Mb MB
MP

Redefining the table

SQL> begin
2 dbms_redefinition.start_redef_table('RAJA','RAJA_OBJECTS','RAJA_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='RAJA';

---- 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
---------- ------------------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
RAJA RAJA_OBJECTS_TEMP MB Mb MB
RAJA MLOG$_RAJA_OBJECTS MB Mb MB
RAJA RUPD$_RAJA_OBJECTS MB Mb MB


Checking the records


SQL> select count(*) from RAJA.RAJA_OBJECTS_TEMP;

COUNT(*)
----------
29264

SQL> select count(*) from RAJA.RAJA_OBJECTS;

COUNT(*)
----------
29264


Copying the dependent 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('RAJA','RAJA_OBJECTS','RAJA_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='RAJA';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ------------------------- ---------- ---------------------------------------- --- ---
HR RAJA RAJA_OBJECTS RAJA UPDATE NO NO
SCOTT RAJA RAJA_OBJECTS RAJA UPDATE NO NO
HR RAJA RAJA_OBJECTS RAJA SELECT NO NO
SCOTT RAJA RAJA_OBJECTS RAJA SELECT NO NO
HR RAJA RAJA_OBJECTS RAJA INSERT NO NO
SCOTT RAJA RAJA_OBJECTS RAJA INSERT NO NO
HR RAJA RAJA_OBJECTS RAJA DELETE NO NO
SCOTT RAJA RAJA_OBJECTS RAJA DELETE NO NO
HR RAJA RAJA_OBJECTS_TEMP RAJA UPDATE NO NO
HR RAJA RAJA_OBJECTS_TEMP RAJA SELECT NO NO
HR RAJA RAJA_OBJECTS_TEMP RAJA INSERT NO NO
HR RAJA RAJA_OBJECTS_TEMP RAJA DELETE NO NO
SCOTT RAJA RAJA_OBJECTS_TEMP RAJA UPDATE NO NO
SCOTT RAJA RAJA_OBJECTS_TEMP RAJA SELECT NO NO
SCOTT RAJA RAJA_OBJECTS_TEMP RAJA INSERT NO NO
SCOTT RAJA RAJA_OBJECTS_TEMP RAJA 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='RAJA';

OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_OBJECTS 1045 29264 101 8.16MB 2.82Mb 5.35MB
RAJA RAJA_OBJECTS_TEMP MB Mb MB
RAJA MLOG$_RAJA_OBJECTS MB Mb MB
RAJA RUPD$_RAJA_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('RAJA','RAJA_OBJECTS','RAJA_OBJECTS_TEMP');

PL/SQL procedure successfully completed.

Completing the Redefination process


SQL> exec dbms_redefinition.finish_redef_table('RAJA','RAJA_OBJECTS','RAJA_OBJECTS_TEMP');

PL/SQL procedure successfully completed.


Gather the statistics

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'RAJA' , tabname => 'RAJA_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='RAJA';

OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_OBJECTS_TEMP 1045 29264 101 8.16MB 2.82Mb 5.35MB
RAJA RAJA_OBJECTS 448 29264 101 3.5MB 2.82Mb .68MB



SQL> drop table raja.RAJA_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='RAJA';

OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE
---------- ------------------------- ---------- ---------- ----------- --------------- --------------- --------------------
RAJA RAJA_OBJECTS 448 29264 101 3.5MB 2.82Mb .68MB

I Hope this article helped to you. Suggestions are welcome.

Reference: Sam R.Alapati - Expert oracle database 11g administration

Regards

Rajabaskar Thangaraj
WWW.dbarajabaskar.blogspot.com

2 comments:

  1. I read your post and it is very useful. I used your query and in my case actual size is greater than total size and thus fragmentation space is showing -ve values. What does this imply and does it have an effect on performance?

    ReplyDelete
  2. Very good post. two remarks:
    1. the table you want to be redifined must have a primary key.
    SQL> create table hr.test2 as select * from dba_tables;

    Table created.
    SQL> exec dbms_redefinition.can_redef_table('HR','test2');
    BEGIN dbms_redefinition.can_redef_table('HR','test2'); END;

    *
    ERROR at line 1:
    ORA-12089: cannot online redefine table "HR"."TEST2" with no primary key
    ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
    ORA-06512: at "SYS.DBMS_REDEFINITION", line 1783
    ORA-06512: at line 1
    2. select * from v$option where parameter = 'Advanced replication';-- must be true (EE)
    for exec dbms_redefinition.start_redef_table('hr','TEST','TEST_temp');
    ERROR at line 1:
    ORA-00439: feature not enabled: Advanced replication

    ReplyDelete