The SQL you code in packages and procedures will be cached for your entire session, thus reducing the number of parse calls. The SQL inside a trigger is cached only for the duration of the triggering statement, causing additional soft parses whenever the trigger is executed. The bottom line here is to put the logic into packaged PL/SQL procedures and call them from the trigger.
opstar@OPSTAR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
opstar@OPSTAR> create table t (x number);
Table created.
opstar@OPSTAR> create or replace procedure p
2 as
3 l_count number := 0;
4 begin
5
6 select count(*)
7 into l_count
8 from dual in_procedure;
9
10 end p;
11 /
Procedure created.
opstar@OPSTAR> create or replace trigger t_trig
2 before insert on t
3 for each row
4 declare
5 l_count number := 0;
6 begin
7 select count(*)
8 into l_count
9 from dual in_trigger;
10
11 p; -- Calling procedure inside Trigger;
12 end;
13 /
Trigger created.
opstar@OPSTAR> alter session set timed_statistics=true;
Session altered.
opstar@OPSTAR> alter session set events '10046 trace name context forever, level 12';
Session altered.
opstar@OPSTAR> insert into t values (1);
1 row created.
opstar@OPSTAR> insert into t values (1);
1 row created.
opstar@OPSTAR> insert into t values (1);
1 row created.
opstar@OPSTAR> insert into t values (1);
1 row created.
opstar@OPSTAR> insert into t values (1);
1 row created.
opstar@OPSTAR> insert into t
2 select rownum
3 from all_users;
25 rows created.
Now, when I review the resulting TKPROF report, I observe the following
insert into t
values
(1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.04 3.57 2 8 35 5
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.04 3.57 2 8 35 5
SELECT COUNT(*)
FROM
DUAL IN_TRIGGER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 30 0.00 0.06 0 0 0 0
Fetch 30 0.00 0.00 0 0 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66 0.00 0.06 0 0 0 30
SELECT COUNT(*)
FROM
DUAL IN_PROCEDURE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 30 0.00 0.14 0 0 0 0
Fetch 30 0.00 0.00 0 0 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 61 0.00 0.14 0 0 0 30
Now the parse count for Trigger is 6, which is 5 for single row insert and 1 for single multi row insert. The SQL statement tagged IN_PROCEDURE, however, did not exhibit that behavior. It was parsed once for my session, and PL/SQL will keep it cached as long as possible for me, reusing that cached cursor over and over. Since excessive parsing (even soft parsing) not only consumes resources (such as CPU time) but also requires latches into the shared pool, it will lead to performance and scalability issues over time.
But this behavior is changed in Oracle 11g.
scott@ORCL> select * from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
After repeating the same scenario, when I review the resulting TKPROF report, I observe the following.
insert into t
values
(1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.01 0.02 2 8 35 5
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.01 0.02 2 8 35 5
SELECT COUNT(*)
FROM
DUAL IN_TRIGGER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 42 0.00 0.00 0 0 0 0
Fetch 42 0.01 0.00 0 0 0 42
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 85 0.01 0.00 0 0 0 42
SELECT COUNT(*)
FROM
DUAL IN_PROCEDURE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 42 0.00 0.00 0 0 0 0
Fetch 42 0.00 0.00 0 0 0 42
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 85 0.00 0.00 0 0 0 42
Now the parse count for Trigger is reduced to 1 instead of 6.
No comments:
Post a Comment