Reorganization of Tables and Indexes in Oracle Database
Reorganization is very useful and important tasks DBAs perform in order to reduce space used by blocks and it also helps in improving the performance of the Oracle Database.It also helps to reduce the fragmentation.
There are 3 ways to do:
1.Export/Import
2.Alter table Move
3.CTAS method(Create table table_name2 as Select *from table_name1)
I followed Step 2 of the above ways.
Below are the Steps I follow for doing reorganization:
Step 1:Check the last analyzed, tables size before Reorganization
Query 1:Check the last Analyzed and number of rows
SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Query 2:Check the size of the table before Reorganization
SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Step 2: Get the list of all the INDEXes associated with the tables used for Reorganization.
SET LINES 2000
SET LINESIZE 2000
SQL> select OWNER||' '||table_name||' '||INDEX_name from DBA_INDEXES where table_name in ('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL PSATEST_WL
SYSADM PS_TEST_WL PSBTEST_WL
SYSADM PS_TEST_WL PS_TEST_WL
SYSADM PS_TEST_USERINST PSATEST_USERINST
SYSADM PS_TEST_USERINST PSBTEST_USERINST
SYSADM PS_TEST_USERINST PSCTEST_USERINST
SYSADM PS_TEST_USERINST PSDTEST_USERINST
SYSADM PS_TEST_USERINST PSETEST_USERINST
SYSADM PS_TEST_USERINST PS_TEST_USERINST
SYSADM PS_TEST_STEPINST PSBTEST_STEPINST
SYSADM PS_TEST_STEPINST PSCTEST_STEPINST
OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_STEPINST PSDTEST_STEPINST
SYSADM PS_TEST_STEPINST PSETEST_STEPINST
SYSADM PS_TEST_STEPINST PSFTEST_STEPINST
SYSADM PS_TEST_STEPINST PSATEST_STEPINST
SYSADM PS_TEST_STEPINST PS_TEST_STEPINST
SYSADM PSWORKLIST PS_PSWORKLIST
SYSADM PSWORKLIST PS0PSWORKLIST
SYSADM PSWORKLIST PSAPSWORKLIST
SYSADM PSWORKLIST PSBPSWORKLIST
SYSADM PS_TEST_TAUTH_AW PS_TEST_TAUTH_AW
SYSADM PS_TEST_TAUTH_AW SYS_IL0000025105C00012$$
OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_SHEET_AW PSATEST_SHEET_AW
SYSADM PS_TEST_SHEET_AW SYS_IL0000024994C00012$$
SYSADM PS_TEST_SHEET_AW PS_TEST_SHEET_AW
SYSADM PS_TEST_ADV_AW SYS_IL0000024456C00012$$
SYSADM PS_TEST_ADV_AW PS_TEST_ADV_AW
27 rows selected.
Step 3:Move the tables to reduce fragmentation.
SQL> spool move_tables.out
SQL> ALTER TABLE PS_TEST_WL MOVE;
Table altered.
SQL> ALTER TABLE PS_TEST_USERINST MOVE;
Table altered.
SQL> ALTER TABLE PS_TEST_STEPINST MOVE;
Table altered.
SQL> ALTER TABLE PSWORKLIST MOVE;
Table altered.
SQL> ALTER TABLE PS_TEST_TAUTH_AW MOVE;
Table altered.
SQL> ALTER TABLE PS_TEST_SHEET_AW MOVE;
Table altered.
SQL> ALTER TABLE PS_TEST_ADV_AW MOVE;
Table altered.
SQL> spool off
Step 4: Rebuild indexes online
spool rebuild.out
ALTER INDEX PSATEST_WL REBUILD ONLINE;
ALTER INDEX PSBTEST_WL REBUILD ONLINE;
ALTER INDEX PS_TEST_WL REBUILD ONLINE;
ALTER INDEX PSATEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSCTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSDTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSETEST_USERINST REBUILD ONLINE;
ALTER INDEX PS_TEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSCTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSDTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSETEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSFTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSATEST_STEPINST REBUILD ONLINE;
ALTER INDEX PS_TEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSBPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS0PSWORKLIST REBUILD ONLINE;
ALTER INDEX PSAPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS_TEST_TAUTH_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000025105C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PS_TEST_ADV_AW REBUILD ONLINE;
spool off
Step 5:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Step 6:Run the gather stats for the tables.
spool gather_stats_tables_TESTDB.out
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_WL',
method_opt =>'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PSWORKLIST',
method_opt => 'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_USERINST',
method_opt => 'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_STEPINST',
method_opt => 'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_TAUTH_AW',
method_opt => 'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_ADV_AW',
method_opt => 'for all columns size auto');
END;
/
BEGIN
DBMS_STATS.gather_table_stats(
'SYSADM',
'PS_TEST_SHEET_AW',
method_opt => 'for all columns size auto');
END;
/
spool off
Step 7:Verify the statistics ,tables size after reorganization
SQL> SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES
2 WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW 28-NOV-13 NO 1716
SYSADM PS_TEST_SHEET_AW 28-NOV-13 NO 197699
SYSADM PS_TEST_TAUTH_AW 28-NOV-13 NO 128592
SYSADM PSWORKLIST 28-NOV-13 NO 936531
SYSADM PS_TEST_STEPINST 28-NOV-13 NO 292227
SYSADM PS_TEST_USERINST 28-NOV-13 NO 5111123
SYSADM PS_TEST_WL 28-NOV-13 NO 652895
7 rows selected.
SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
2 WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW .25
SYSADM PS_TEST_ADV_AW .125
SYSADM PS_TEST_SHEET_AW 46
SYSADM PS_TEST_SHEET_AW 31
SYSADM PS_TEST_TAUTH_AW 18
SYSADM PS_TEST_TAUTH_AW 4
SYSADM PSWORKLIST 168
SYSADM PS_TEST_STEPINST 18
SYSADM PS_TEST_STEPINST 19
SYSADM PS_TEST_USERINST 365
SYSADM PS_TEST_USERINST 93
OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL 88
SYSADM PS_TEST_WL 64
13 rows selected.
Enjoy DBA tasks and Performance tuning.
No comments:
Post a Comment