Sunday, 28 April 2013

ONLINE TABLE REDEFINATION



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

No comments:

Post a Comment