Friday, November 25, 2011

Parallel Delete on a nonpartitioned Table in Oracle 10g

Recently a developer asked my help to improve the performance of delete statements. The
development team wanted to delete 12 million rows in one table and 4 million rows in the
second table. He said that his 2 delete statements ran for 6 hrs in a test environment and he
did not have 6 hrs maintenance window in production environment.
The original delete statement is:
DELETE FROM T1 XA
WHERE EXISTS
      (SELECT 1
         FROM T2 TMP
        WHERE TMP.ID = XA.ID);
He verified that the delete statement would run in a maintenance window, and the DB will not be accessed by
end users.
Immediately I thought of using parallel DML operation because the table T1 has 180 Million rows, out  of
which we wanted to delete 12 Million rows.
The oracle document says for parallel delete/update operation, the table has to be partitioned. I am having a
small doubt about the statement.  However, there are few restrictions for parallel DML operations.
Oracle doc says:
Rules for UPDATE, MERGE, and DELETE
UPDATE, MERGE, and DELETE operations are parallelized by partition or subpartition. Update, merge, and
delete parallelism are not possible within a partition, nor on a nonpartitioned table. See "Limitation on the
Degree of Parallelism" for a possible restriction.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm
I tried the below script and the parallel delete operation completed in 26 minutes.
SET ECHO ON
SET PAGESIZE 60
SET LINESIZE 132
SPOOL pll_del.log
SELECT to_char(SYSDATE,'DD-MON-YYYY HH24:mi:ss') FROM DUAL;
alter session set db_file_multiblock_read_count=128;
alter table T1 parallel 8;
---- force parallel DML operation
alter session force parallel dml ;
DELETE /*+ PARALLEL(XA,8) */
      FROM T1 XA
WHERE EXISTS
      (SELECT 1
         FROM T2 TMP
        WHERE TMP.ID = XA.ID);
COMMIT;
alter table T1 noparallel ;
SELECT to_char(SYSDATE,'DD-MON-YYYY HH24:mi:ss') FROM DUAL;
SPOOL OFF;
Important points are:
1.      Changed db_file_multiblock_read_count to 128 so that Oracle would read maximum of
1MB in a single IO.
2.    Set parallel degree 8 on the table.
3.    Forced parallel DML at the session level.
4.    Ensured that delete statement has parallel hint.  

You can monitor the progress of deletion by querying V$transaction view or
v$pq_tqstat, v$pq_sysstat and v$pq_slave.
Having tested this statement in a test environment, the developer would ask for less
maintenance window, when he run the delete statement.
Hope this helps....
Tamilselvan G

4 comments:

  1. Baccarat 101 | Casino
    The game 바카라사이트 is 1xbet the most played variant of bet in the world. 샌즈카지노 The game is the easiest to learn, the easiest to learn and the best way to win is to play in

    ReplyDelete