SQL*PLUS >

conn hr/hr

DROP TABLE tt purge ;
CREATE TABLE tt nologging as select * from all_users ;

DROP TABLE t purge ;
CREATE TABLE t (ldate date, luser varchar2(32)) nologging ; 

CREATE OR REPLACE TRIGGER trg_tt_statement_level
	AFTER INSERT ON tt
BEGIN
	  IF INSERTING THEN
		insert into t  values(to_date('01.01.1900', 'dd.mm.yyyy'), 'TONGUC');		!! attention pure SQL !!
	  END IF;
END trg_tt_statement_level;
/

conn hr/hr

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = Trigger_Trace_1 ;

--  start tracing from this session
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

BEGIN
   insert into tt values ('TO', 1000, sysdate);
   insert into tt values ('NG', 2000, sysdate-1);
   insert into tt values ('UC', 3000, sysdate+1);									!! attention trigger will fire 3 times !!
END;
/

--  stop tracing
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

conn hr/hr

DROP TABLE tt purge ;
CREATE TABLE tt nologging as select * from all_users ;

DROP TABLE t purge ;
CREATE TABLE t (ldate date, luser varchar2(32)) nologging ; 

CREATE OR REPLACE PROCEDURE prc_t_insert AS
BEGIN
	insert into t  values(to_date('01.01.1900', 'dd.mm.yyyy'), 'TONGUC');
END prc_t_insert;
/

CREATE OR REPLACE TRIGGER trg_tt_statement_level
	AFTER INSERT ON tt
BEGIN
	  IF INSERTING THEN
		prc_t_insert ;																!! attention this time calling a procedure!!
	  END IF;
END trg_tt_statement_level;
/

conn hr/hr

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = Trigger_Trace_2 ;

--  start tracing from this session
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

BEGIN
   insert into tt values ('TO', 1000, sysdate);
   insert into tt values ('NG', 2000, sysdate-1);
   insert into tt values ('UC', 3000, sysdate+1);								!! attention again trigger will fire 3 times !!
END;
/

--  stop tracing
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

CMD >

tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_5656_trigger_trace_1.trc D:\temp\xe_ora_5656_trigger_trace_1.txt explain=hr/hr waits=yes sort=prscpu

tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2748_trigger_trace_2.trc D:\temp\xe_ora_2748_trigger_trace_2.txt explain=hr/hr waits=yes sort=prscpu

NOTEPAD >

D:\temp\xe_ora_5656_trigger_trace_1.txt >
..
INSERT INTO T 
VALUES
(TO_DATE('01.01.1900', 'dd.mm.yyyy'), 'TONGUC')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.00          0          0          0           0		!! attention parse count = exec count !!
Execute      3      0.00       0.00          0          3         25           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.00          0          3         25           3
..

D:\temp\xe_ora_2748_trigger_trace_2.txt >
..
INSERT INTO T 
VALUES
(TO_DATE('01.01.1900', 'dd.mm.yyyy'), 'TONGUC')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.17          0          0          0           0		!! attention parse count = 1 !!
Execute      3      0.00       0.00          0          1         21           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.17          0          1         21           3
..