Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 29, 2003

Deleting Historical Data on Oracle Databases - Page 2

By Marin Komadina



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:

Delete Test

Elapsed Time (sec)

Execute Count

Parse Count

Parse Count Hard

Recursive Calls

Enqueue requests

Sorts (memory)

Basic Delete

35,75

156

87

29

2839

33

61

PL/SQL Loop forall

26,94

296

168

36

5008

46

109

PL/SQL Loop
commit 1000

21,25

7219

342

36

18873

302

67

Stored Procedure
commit once

15,52

7105

197

42

25830

54

118

Stored Procedure
commit 1000

12,03

7100

197

38

25700

50

114

Execute Immediate

9,87

298

162

42

5084

50

113

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date