How to find table fragmentation?
SQL> select count(*) from big1;
1000000 rows selected.
SQL> delete from big1 where rownum <= 300000;
300000 rows deleted.
SQL> commit;
Commit complete.
SQL> update big1 set object_id = 0 where rownum <=350000;
342226 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.
Table size (with fragmentation)
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb
Actual data in table:
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb
Note = 72952 - 30604 = 42348 Kb is wasted space in table
The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.
We have four options to reorganize fragmented tables:
1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition
Option: 1 "alter table ... move + rebuild indexes"
SQL> alter table BIG1 move;
Table altered.
SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX
SQL> alter index bigidx rebuild;
Index altered.
SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX
SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30727.37kb
Option: 2 “Create table as select”
SQL> create table big2 as select * from big1;
Table created.
SQL> drop table big1 purge;
Table dropped.
SQL> rename big2 to big1;
Table renamed.
SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 68986.97kb
SQL> select status from user_indexes
2 where table_name = 'BIG1';
no rows selected
SQL> --Note we need to create all indexes.
Option: 3 "export / truncate / import"
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb
SQL> select status from user_indexes where table_name = 'BIG1';
STATUS
--------
VALID
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:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1
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 - Produc
tion
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 BIG1 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 big1;
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:\big1.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 - Produc
tion
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 "BIG1" 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"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb
SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 51840kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42542.27kb
SQL> select status from user_indexes where table_name = 'BIG1';
STATUS
--------
VALID
SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-> dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
Option: 4 "dbms_redefinition"
SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);
Table created.
SQL> alter table table1 add constraint pk_no primary key(no);
Table altered.
SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( 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_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> select count(*) from table1;
COUNT(*)
----------
100000
SQL> delete table1 where rownum <= 50000;
50000 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb
SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-> 'TABLE1',-> sys.dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
SQL> --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>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;
Table created.
SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-> 'TABLE1',-> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);
Table altered.
SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb
SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';
STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1
SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';
STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2
SQL> drop table TABLE2 PURGE;
Table dropped.
No comments:
Post a Comment