Deleting Historical Data on Oracle Databases - Page 2

August 29, 2003

Code Sample for Testing

Test Case 1 - Basic Delete

delete from artist_test 
    where SECOND_COLL='JAVA CLASS';

Test Case 2 - Delete with PLSQL and forall loop, committing only once

DECLARE 
TYPE artist_value IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER; 
del_value artist_value; 
rowkey PLS_INTEGER; 
BEGIN 
SELECT rowid BULK COLLECT INTO del_value FROM artist_test WHERE SECOND_COLL='JAVA CLASS'; 
IF del_value IS NOT NULL THEN 
FORALL rowkey IN del_value.FIRST..del_value.LAST 
DELETE artist_test WHERE rowid = del_value(rowkey); 
commit;
END IF; 
END;
/

Test Case 3 - Delete using anonymous PL/SQL block, looping through the cursor, and deleting one tow at a time. Loop has counter, programmed to trigger commit after each 1000 records.

SQL> declare
        rec_read       number(6)    := 0;
        rec_rest       number(6)    := 0; 
        vc_var_out   varchar2(41) := 'Delete Archive Data';
        cursor rec_sql is select SECOND_COLL,rowid from artist_test 
			where SECOND_COLL='JAVA CLASS' order by SECOND_COLL,rowid;
begin
       for rec_sql_run in rec_sql loop
            rec_read := rec_read + 1 ;
            rec_rest := rec_rest + 1 ;
            delete from artist_test A where A.rowid = rec_sql_run.rowid ;
            if rec_rest > 1000 THEN
                 COMMIT;
                 rec_rest := 1;
                 dbms_output.put_line('COMMIT AT..'|| rec_read);
             END IF;
        end loop;
end;
/  

Test Case 4
a.) Deleting using PL/SQL stored procedure, commit each 1000 records. ( exec delete_table(1000))
b.) Deleting using PL/SQL stored procedure, commit only once ( exec delete_table(100000))

CREATE OR REPLACE PROCEDURE delete_table (commit_size in number default 1000) is
  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  cur_stmt    VARCHAR2(2000);
  del_rowid   ROWID;
BEGIN
  cur_stmt := 'SELECT rowid FROM artist_test where SECOND_COLL=''JAVA CLASS''';
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,cur_stmt,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete from artist_test where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      exec_del := DBMS_SQL.EXECUTE(del_id);
      if mod(dbms_sql.last_row_count,commit_size) = 0 
      then
     COMMIT;
      end if;
   ELSE 
       exit;
   END IF;
 END LOOP;
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);
END;
/

Test Case 5 - Delete using standard anonymous PL/SQL block, using execute immediate function and committing only once.

SQL > declare
    proc_rows number := 0;
  begin
    execute immediate 'begin 
                        delete from artist_test where SECOND_COLL=''JAVA CLASS''; 
                        :x :=sql%rowcount;
                        commit;
                        end;' 
    using OUT proc_rows;
    dbms_output.put_line('Records processed........'|| proc_rows);
end;
/

Final Recommendations

The result from tracing files, the v$roger session performance view and from the timing information obtained using DBMS_UTILITY.GET_TIME procedure was collected and sorted in the following table:

Elapsed Time - real time needed to finish test

Execute Count - number of execution recorded for the session

Parse Count - total number of parsing calls for the session (soft and hard)

Parse Count Hard - number of hard parsing calls for the session

Recursive Calls - number of recursive calls for the session

Enqueue Requests - number of locking requests for he session

Sorts (memory) - number of sorts in memory for the session

The best result was using EXECUTE_IMMEDIATE calls inside an anonymous PL/SQL block, during test case 5. This is not surprising, since Oracle suggests this method for mass data deletion. The EXECUTE IMMEDIATE call came first time in version Oracle8i. It is supposed to be a replacement for DBMS_SQL package. EXECUTE_IMMEDIATE calls parses and immediately executes a dynamic SQL statement or a PL/SQL block, which is preferred and faster than other coding tests.

I did not look in the tests for other potential problems, rising CPU usage and disk I/O utilization due to mass delete action. The assumption was that we were going to delete from the big table via a regular batch job, on system offloaded from regular users. In our test case, ARTIST_TEST table was not partitioned and distributed
over different disk devices, which is an additional point to consider for performance
improvements.

Every DBA has to test all existing methods and find one that best suite.

Some last recommendations to speed a delete operation:

• do not delete all the data in one shot ; break data into chunks and commit in between
• delete indexes before and recreate after
• for tables with many deletes, try to avoid bitmap indexes which cause locking problems and performance degradation 
• use parallel DML when possible
• check for index on the FK columns, which have to be locked when a row is deleted from the parent (each row deleted in the Master is causing a full table scan in one or both of the cascaded tables.)
• deleting using ROWID (fetching the entire ROWID result set into a cursor)

Conclusion

Oracle 9i has several new and improved features aimed at making the availability, manageability and serviceability of the database easier. Beside Oracle 9i's strong partition orientation, at least 1/5 of the running Oracle databases have no implemented partitions.

Deleting historical data at the end of month becomes a question of existence:

"To delete old data and continue working or close the business due to bad performance and high costs."

» See All Articles by Columnist Marin Komadina