delete statement performance impact

Delete Statement Response Time  in Oracle:

Below is a quick observation on my test machine.

———————————–Playing with Delete statement with enabled trigger,Logging/Nologging

Below Analysis Result on below System Configuration:

Two Node RAC 11gR2  created on Virtual Box
CPU: 1(2.00 GHz) each node
RAM: 2gb each node


storage: 100gb
OS: OEL 5.9

———————————–

SQL> create table emp(id int,name char(5));

Table created.
SQL> create table emp_audit(id int,name char(3),date_deleted date,deleted_by varchar2(10));

Table created.

SQL> CREATE OR REPLACE TRIGGER emp_after_delete
  2  AFTER DELETE
  3     ON emp
  4     FOR EACH ROW
  5
  6  DECLARE
  7     v_username varchar2(10);
  8
  9  BEGIN
 10
 11     — Find username of person performing the DELETE on the table
 12     SELECT user INTO v_username
 13     FROM dual;
 14
 15     — Insert record into audit table
 16     INSERT INTO emp_audit
 17     ( id,
 18       name,
 19      date_deleted,
 20       deleted_by)
 21     VALUES
 22     ( :old.id,
 23       :old.name,
 24       sysdate,
 25       v_username );
 26
 27  END;
 28  /

Trigger created.

—Below Error shows that delete command can not be completed until trigger executes successfully.


SQL> delete from emp where id=1;
delete from emp where id=1
            *
ERROR at line 1:
ORA-12899: value too large for column "RISHI"."EMP_AUDIT"."NAME" (actual: 5,
maximum: 3)
ORA-06512: at "RISHI.EMP_AFTER_DELETE", line 11
ORA-04088: error during execution of trigger 'RISHI.EMP_AFTER_DELETE'


SQL> drop trigger emp_after_delete;

Trigger dropped.

SQL> drop table emp_audit;

Table dropped.

–Recreate audit table and after delete trigger again to avoid above error for further delete command performance impact test with and without Trigger:

SQL> create table emp_audit(id int,name char(5),date_deleted date,deleted_by varchar2(10));
Table created.

SQL> SQL>
SQL> set timing on
SQL> delete from emp where id=1;

16777216 rows deleted.

Elapsed: 00:02:40.30——————————delete time without enabled trigger on EMP table
SQL> rollback;

Rollback complete.

Elapsed: 00:04:38.01

—-Now create after delete trigger

SQL> CREATE OR REPLACE TRIGGER emp_after_delete
  2  AFTER DELETE
  3     ON emp
  4     FOR EACH ROW
  5
  6  DECLARE
  7     v_username varchar2(10);
  8
  9  BEGIN
 10
 11     — Find username of person performing the DELETE on the table
 12     SELECT user INTO v_username
 13     FROM dual;
 14
 15     — Insert record into audit table
 16     INSERT INTO emp_audit
 17     ( id,
 18       name,
 19      date_deleted,
 20       deleted_by)
 21     VALUES
 22     ( :old.id,
 23       :old.name,
 24       sysdate,
 25       v_username );
 26
 27  END;
 28  /

Trigger created.

—Now fire delete statement again delete

SQL> delete from emp where id=1;

16777216 rows deleted.

Elapsed: 00:27:52.27——Here is the very high response time with after delete trigger

—Now testing whether nologging can be applied or not on Delete DML statement(As per Oracle "Nologging" is not applicable for DML operations)


SQL> alter table emp nologging;

Table altered.

Elapsed: 00:00:00.82
SQL> delete from emp where id=1;

16777216 rows deleted.

Elapsed: 00:02:40.30———delete time with nologging enabled
SQL> rollback;

Rollback complete.

Elapsed: 00:04:38.01

SQL>alter table emp logging;—–———–enable Logging
SQL> delete from emp where id=1;

16777216 rows deleted.

Elapsed: 00:02:16.10——————–same time taken after and befor logging–no impact
SQL> rollback;

Rollback complete.

Elapsed: 00:04:46.09

—–Show proved that there is no performance benefit on Delete statement(DML) using "Nologging"

 

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply