Feeds:
Posts
Comments

Posts Tagged ‘table fragmentation’

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

Small example to find the 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.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              72952          30604.2

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) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              38224           30727.37

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) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              85536            68986.97

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…..

Export terminated successfully without warnings.

SQL> truncate table big1;

Table truncated.

imp scott/tiger@Orcl file=c:big1.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on….

Import terminated successfully without warnings.

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”….Which we will disscus afterwords….

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »