Massive Deletes Vs Truncating Partitions

This is not a revolutionary post, everything I discuss here has been discussed numerous times. Massive deletes are nothing new in a DBA’s life. They were there, they are there and they will continue to be there. From time to time DBA’s fall prey to the bad application design. My last post was about heavy redo log switches and this post is a continuation.

The application was performing massive DELETE operations resulting in excessive redo being generated. This in turn was responsible for too many redo log switches and severely affecting database performance. It was possible that the same DELETE statement on a non-partitioned table could be translated into a “ALTER TABLE … TRUNCATE PARTITION” statement by partitioning the same table.

In this post, I will demonstrate how one can achieve extreme performance by employing right techniques for the right job.

For this demo, I will create a test table “T” (without any indexes) with a single column. Populate the table with 2 Million records and then DELETE 1 Million records. Before and after the delete we will make a note of the redo generated.

Here we go:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id number) tablespace test_ts;

Table created.

SQL>
SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Before we delete, let’s make a note of redo generated by our session:

SQL> column name format a20
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                30.93

SQL>

Now perform the massive DELETE operation:

SQL>
SQL> set timing on
SQL> delete from t where id <= 1000000;

1000000 rows deleted.

Elapsed: 00:02:12.87
SQL> set timing off
SQL>

Make a note of redo generated by the DELETE statement:

SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                257.41

SQL>

So, the DELETE statement generated 226.48 MB (257.41 – 30.93) of redo and it took over 2 minutes to clock time.

Now, let us improvise the purge strategy but partitioning the table. I range partition the table into six partitions as shown:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number)  tablespace test_ts
  2  partition by range (id) (
  3  partition p1 values less than (500000),
  4  partition p2 values less than (1000000),
  5  partition p3 values less than (1500000),
  6  partition p4 values less than (2000000),
  7  partition p5 values less than (2500000),
  8  partition p6 values less than (MAXVALUE)
  9  );

Table created.

SQL>

Now run the same INSERT statement against the partitioned table:

SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Make a note of redo before and after executing the purge command:

SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.53

SQL>
SQL> set timing on
SQL> alter table t truncate partition p1;

Table truncated.

Elapsed: 00:00:00.62
SQL> alter table t truncate partition p2;

Table truncated.

Elapsed: 00:00:00.29
SQL> set timing off
SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.64

SQL>

Purging of two partitions consumed less than a second and generated only 0.11 MB of redo.

In some situations it may be possible that the redo logs were sized inadequately but in this case it was the application’s bad design responsible for massive redo log generation.

What happens when you have additional columns and indexes associated with the table? You could take that as a further exercise.

Redo Log Switches at their Peak

One of our customers was having extreme performance issues with their Oracle database. During my investigations I found they had 215 redo log switches per hour. This means a 3.65 log switches per minute or a redo log switch every 16.7 seconds.

Although this was not the only reason of performance issues but this was certainly one of the causes. The redo log files were sized pretty small and the poor database was spending most of its time switch the redo log files.

Here’s a screenshot for you guys:

Below is the query I use to find redo log switches. This query lists redo log switches per hour for the last seven days.

column day format a10
column Switches_per_day format 9999
column 00 format 999
column 01 format 999
column 02 format 999
column 03 format 999
column 04 format 999
column 05 format 999
column 06 format 999
column 07 format 999
column 08 format 999
column 09 format 999
column 10 format 999
column 11 format 999
column 12 format 999
column 13 format 999
column 14 format 999
column 15 format 999
column 16 format 999
column 17 format 999
column 18 format 999
column 19 format 999
column 20 format 999
column 21 format 999
column 22 format 999
column 23 format 999

select to_char(first_time,'DD-MON') day,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",
count(to_char(first_time,'MM-DD')) Switches_per_day
from v$log_history
where trunc(first_time) between trunc(sysdate) - 6 and trunc(sysdate)
group by to_char(first_time,'DD-MON') 
order by to_char(first_time,'DD-MON') ;

Updated: The redo log files were sized at 25 MB. To relieve the database from this pain, I increased the size of the redo log files to 1 GB each.

Updated-2: Here is the post related to this.