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; /
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; /
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.
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
Every DBA has to test all existing methods and find one that best suite.
Some last recommendations to speed a delete operation:
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."