Home > Oracle, Performance > APPEND hint and Table space management

APPEND hint and Table space management


This case is written for an application team  to show – how to club DELETE and table rebuild while using INSERT /*+ APPEND */ for daily loading.  The original problem was, tablespace was growing fast everyday even though application was DELETing old records.

As we know, when ever we use APPEND in INSERT or APPEND in sqlldr, the records will be placed above the HWM (High Water Mark) after formatting the new blocks, leaving the free unused blocks below HWM as shown in the below test case.

SQL> create table test as select * from dba_objects;
Table created.

A new table TEST created using the data from DBA_OBJECTS.

SQL> select segment_name,extents,bytes/1024 size_KB 
from dba_segments where owner='THOMAS' and segment_name='TEST';

SEGMENT_NA   EXTENTS    SIZE_KB
----------   ---------- ----------
TEST         20          2080

There are 20 extents and the size of the table is 2080 KB.  And I am going to DELETE and INSERT the records back.

SQL> delete from test;
17389 rows deleted.

SQL> commit ;
Commit complete.

SQL> select segment_name,extents,bytes/1024 size_KB 
from dba_segments where owner='THOMAS' and segment_name='TEST';
SEGMENT_NA   EXTENTS      SIZE_KB
----------   ----------   ----------
TEST         20           2080

SQL> insert /*+ APPEND */ into test select * from dba_objects;
17389 rows created.

SQL> commit ;
Commit complete.

SQL> select segment_name,extents,bytes/1024 size_KB 
from dba_segments where owner='THOMAS' and segment_name='TEST';
SEGMENT_NA   EXTENTS     SIZE_KB
----------   ----------  ----------
TEST          38         3952

So, a DELETE 100% data and insert same amount of data caused the table grow another 18 extents or 1872 KB. The reason is, records get INSERTed above the HWM water mark – means new blocks added to the table to INSERT the new records.

I repeated the same process to see the table get growing.

SQL> delete from test;
17389 rows deleted.

SQL> commit ;
Commit complete.

SQL> insert /*+ APPEND */ into test select * from dba_objects;
17389 rows created.

SQL> commit ;
Commit complete.

SQL> select segment_name,extents,bytes/1024 size_KB 
from dba_segments where owner='THOMAS' and segment_name='TEST';
SEGMENT_NA  EXTENTS      SIZE_KB
----------  ----------  ----------
TEST        56           5824

Finally, I have DELETEd all records from the table, and with zero rows, table remained with the same size.

SQL> delete from test;
17389 rows deleted.

SQL> commit ;
Commit complete.

SQL> select count(*) from test;
COUNT(*)
----------
 0

SQL> select segment_name,extents,bytes/1024 size_KB 
from dba_segments where owner='THOMAS' and segment_name='TEST';
SEGMENT_NA   EXTENTS      SIZE_KB
----------   ----------   ----------
TEST         56           5824

That shows clearly, a DELETE will never release the space from the table or it will never re-use for INSERT /*+ APPEND */ or sqlldr with APPEND clause.  It is not just space usage, the SELECT or any table access will take more time as it need to scan the blocks below the HWM.

SQL> select count(*) from test;
 COUNT(*)
 ----------
 0
Statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 710 consistent gets
 0 physical reads
 0 redo size
 521 bytes sent via SQL*Net to client
 523 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL> truncate table test;
Table truncated.

SQL> select count(*) from test;
COUNT(*)
 ----------
 0

 Statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 3 consistent gets
 0 physical reads
 0 redo size
 521 bytes sent via SQL*Net to client
 523 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

As you see above it took 710 consistent reads to report zero rows while just 3 consistent reads after a TRUNCATE.

The question is, in the case how can we release unused space from a table after removing the records.  TRUNCATE may not be possible if we are DELETing only part of the table.  So, I did use the PARTITION way.  The application requirement was to DELETE records below a specific DATE.  So, I have created the table using a RANGE PARTITION and INSERTed sample records using /*+ APPEND */ hint.

SQL> create table test (OBJECT_NAME varchar(50),OBJECT_ID number,OBJECT_TYPE VARCHAR2(20),CREATED date)
 2 partition by range (created)
 3 (partition test10 VALUES LESS THAN (to_date('2011-09-01','yyyy-mm-dd')),
 4 partition test9 VALUES LESS THAN (to_date('2011-10-01','yyyy-mm-dd')),
 5 partition test8 VALUES LESS THAN (to_date('2011-11-01','yyyy-mm-dd')),
 6 partition test7 VALUES LESS THAN (to_date('2011-12-01','yyyy-mm-dd')),
 7 partition test6 VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd')),
 8 partition test5 VALUES LESS THAN (to_date('2012-02-01','yyyy-mm-dd')),
 9 partition test4 VALUES LESS THAN (to_date('2012-03-01','yyyy-mm-dd')),
 10 partition test3 VALUES LESS THAN (to_date('2012-04-01','yyyy-mm-dd')),
 11 partition test2 VALUES LESS THAN (to_date('2012-05-01','yyyy-mm-dd')),
 12 partition test1 VALUES LESS THAN (to_date('2012-06-01','yyyy-mm-dd')),
 13 partition test11 VALUES LESS THAN (MAXVALUE)) ; 
Table created. 

SQL> insert /*+ APPEND */ into test select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED 
from dba_objects; 
17400 rows created. 

SQL> commit ;
Commit complete.

SQL> insert /*+ APPEND */ into test select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED from dba_objects;
17400 rows created.

Now I have 11 partitions and each partitions contains a date range.

SQL> select PARTITION_NAME,extents,bytes/1024 
from dba_segments where owner='THOMAS' and segment_name='TEST';
PARTITION_NAME  EXTENTS     BYTES/1024
--------------- ----------  ----------
TEST1            1          104
TEST11           1          104
TEST2            1          104
TEST3            1          104
TEST4            3          312
TEST5            1          104
TEST6            1          104
TEST7            1          104
TEST8            1          104
TEST9            1          104
TEST10           23         2392
11 rows selected.

Each partition is a separate physical entity and act like an individual table for storage.  You can create indexes local to each partition as,

SQL> create index i_object_id on test(object_id) local;
Index created.

Life is much easy now – to DELETE old records below a specific date range – just drop a specific partition.

 
SQL> select sum(bytes)/1024 from dba_segments 
where owner='THOMAS' and segment_name='TEST';
SUM(BYTES)/1024
---------------
 3640

SQL> alter table test drop partition test10;
Table altered.

SQL> alter table test drop partition test9;
Table altered.

SQL> alter table test drop partition test8;
Table altered.

SQL> alter table test drop partition test7;
Table altered.

SQL> alter table test drop partition test6;
Table altered.

SQL> select PARTITION_NAME,extents,bytes/1024 
from dba_segments where owner='THOMAS' and segment_name='TEST';
PARTITION_NAME    EXTENTS     BYTES/1024
---------------   ----------  ----------
TEST1               1          104
TEST11              1          104
TEST2               1          104
TEST3               1          104
TEST4               3          312
TEST5               1          104
6 rows selected.

SQL> select sum(bytes)/1024 from dba_segments 
where owner='THOMAS' and segment_name='TEST';
SUM(BYTES)/1024
---------------
 832

Now the size of the table reduced to 832KB from 3640 – you have removed the records in an easy way as well as reclaimed the space.

There are number of advantages by using the PARTITION tables,

  • No undo or redo as DROP PARTITION is in the physical storage level
  • It is much faster as it is not by records by record
  • Space released back to tablespace
  • Table will contain only less storage of new partitions
  • Less CPU used as the number of blocks are less and less CBC (Cache Buffer Chain) latches used
  • Faster SELECTs as less consistent gets

Note: This is specifically written for an application group, not a new concept.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: