Ooops
Cefers.br, January 26, 2003 - 6:13 pm UTC
Tom, I think I need your help here.
I just didn't understand the meaning of IN_PROCEDURE / IN_TRIGGER at the end of "select from DUAL" statement.
I search the Oracle OnLine help, but didn't find anything.
Can you explain?
Thanks.
January 26, 2003 - 6:17 pm UTC
it is just a table alias, you know, like:
select * from emp E, dept D where e.deptno = d.deptno;
E and D are table aliases.
I used IN_PROCEDURE and IN_TRIGGER so that when I showed you the tkprof, it would be clear as to which one was executed where. That is all - nothing "magical", just a documentation trick if you will.
Ohh, I knew it!
Cefers.br, January 26, 2003 - 9:04 pm UTC
Hi Tom.
Now I got it. It is a tkproof trick... A nice one!
When I first looked at IN_PROCEDURE/IN_TRIGGER, they seemed like table aliases to me. But I got a litle confused once there was only one table in the statement (I usualy use table aliases when there's 2 or more tables on the query).
I didn't see the "tricky side" of the whole thing...
Thank you for your answer and I'm sorry about my stupid question (I feel like a dumb now, you know)! :)
Learn something new every day!
Chuck Jolley, January 27, 2003 - 10:51 am UTC
It would never have occured to me that trigger code was not cached.
Is a procedure called from a trigger cached?
My database has a LOT of trigger activity.
Should I move the code into procedures in a package and just call the code in the triggers?
Thanks!
January 27, 2003 - 11:34 am UTC
trigger code is cached.
the sql statements inside maybe reparsed.
Here is what you should do:
o turn on sql_trace in your application
o exercise it (run it for a while)
o run tkprof against the trace file
Now, if you see lots of PARSES and EXECUTES of SQL in this trace file that are in triggers, like this:
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 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 18 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 18 0 6
6 parses, 6 executes -- that would be a candidate for moving the logic into a procedure so you get to 1 parse, 6 executes.
If you see lots of 1 parse, N executes -- they are fine, leave them be. It is only if you see excessive parsing of a statement that you need to worry about it.
Package per Module/ Package per Table?
Tony, January 28, 2003 - 3:06 am UTC
There is an argument among the developers in my company.
1. Some guys argue that we need to have one package per module that consists of many tables.
2. Someother guys argue that we need to have one package per table to put all insert, update , delete and select on that table in single package.
In the first approach, we will have 4 packages if there are 4 modules in an application.
In the second approach, we will have 100 packages if there are 100 tables in the application.
Which approach is correct?
Can you share your expertise please?
January 28, 2003 - 7:21 am UTC
how about option #3
3) like any other 3gl, you would put into a package that which logically belongs together. You would use common sense, good modular coding practices in order to develop readable, maintainable code.
Hows that? that is what I do -- you use common sense, traditional coding practices, modular design and what goes into a package falls out *naturally*.
Trigger Code is precompiled
Tony, January 28, 2003 - 3:11 am UTC
Tom, Reparsing should not happen as the trigger body is precompiled. Am I correct?.
Is there any concurrency issue if triggers are extensively used in multiuser environment?
January 28, 2003 - 7:24 am UTC
Hmm, compilation happens when we create the trigger.
Parsing -- when we run. The sql isn't parsed and executed when we compile, only when we run.
I'm sort of surprised at the comment:
"Reparsing should not happen as the trigger body is precompiled. Am I
correct?"
since my example (which I thought was as straightforward as can be???) shows conclusively the opposite is not true????
Anyway, the only concurrency issues with triggers are those you code yourself. That is, there is no more chance of concurrency issues with trigger then there is with anyother construct.
My Ignorance
Tony, January 28, 2003 - 7:46 am UTC
Tom, I know there is a difference between compilation and parsing, but I couln't make out what exactly the difference is. Could you please?. [ Sorry if the question is so basic].
January 28, 2003 - 8:07 am UTC
compile compiles plsql code into something that can be executed. compiling involves "parsing" but "parsing of the plsql" -- not a conventional sql parse.
in the context of the database, when we talk of parsing, we are generally refering to the step you take before actually executing a statement -- parsing of the sql statement.
"the sql statements inside maybe reparsed."
Martin, January 28, 2003 - 11:11 am UTC
Tom,
Isn't it just better to always call a (packaged) procedure and never put SQL-statements directly in the trigger-body?
Otherwise, How will I know (beforehand)if a SQL-statement in the triggerbody will be reparsed?
January 28, 2003 - 2:52 pm UTC
you know this: it will be reparsed if called more then once across calls.
Consider:
ops$tkyte@ORA920> create table t1 ( x int );
Table created.
ops$tkyte@ORA920> create table t2 ( x int );
Table created.
ops$tkyte@ORA920> create or replace procedure p
2 as
3 begin
4 for x in ( select * from dual p) loop null; end loop;
5 end;
6 /
Procedure created.
ops$tkyte@ORA920> create trigger t1_trigger before insert on t1 for each row
2 begin
3 for x in ( select * from dual t1 ) loop null; end loop;
4 p;
5 end;
6 /
Trigger created.
ops$tkyte@ORA920> create trigger t2_trigger before insert on t2 for each row
2 begin
3 for x in ( select * from dual t2 ) loop null; end loop;
4 p;
5 end;
6 /
Trigger created.
ops$tkyte@ORA920> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA920> begin
2 for i in 1 .. 4
3 loop
4 insert into t1 values ( i );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA920> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA920> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA920> insert into t2 values ( 1 );
1 row created.
Now, when we tkprof that we'll be interested in seeing the
o query in P
o query in T1
o query in T2
and how many times each was parsed/executed:
select * from dual t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 12 0 4
select * from dual p
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 24 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.00 0.00 0 24 0 8
select * from dual t2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.00 0.00 0 12 0 4
So, the query in T1 was parsed ONCE and executed 4 times. Four inserts <b>in a single call - the cursor was cached</b>.
The query in T2 however -- across calls -- was <b>parsed 4 times, once per call</b>.
The query in P -- regardless of how it was invoked, via t1 or t2 -- was parsed once per session.
"the sql statements inside maybe reparsed."
Martin, January 28, 2003 - 11:12 am UTC
Tom,
Isn't it just better to always call a (packaged) procedure and never put SQL-statements directly in the trigger-body?
Otherwise, How will I know (beforehand)if a SQL-statement in the triggerbody will be reparsed?
parsing of pl/sql
Ashiq Shamsudeen, January 28, 2003 - 12:55 pm UTC
Hi Tom,
I know the parsing in context of sql statements ,Can u explain what parsing is all about in context of pl/sql
January 28, 2003 - 3:05 pm UTC
in order to compile code -- someone has to parse it, turn it into a structure that can be interpreted. hence we parse it.
confused ? conventional sql v/s pl/sql
Ian, February 13, 2003 - 10:36 pm UTC
(Sorry for submitting review of this question in another question)
When I am creating a trigger , is the compiled code not stored in the database ?
Then why would it only parse pl/sql and not the conventional sql.
So that when trigger is actually fired at time of execution it gets parsed
statement and executes it.
Also then why is parse not happening in procedure body ? Is the conventional SQL
in procedure body parsed and stored.
I guess if you could clarify this in a little detail , this will be totally
clear as this applies to all sql code we write.
February 14, 2003 - 7:35 am UTC
Just 1 last thing
Ian, February 14, 2003 - 3:43 pm UTC
Things are very clear now except 1 last thing which will summarise the whole thing :-
You began with
"SQL inside procs is cached for your session while for triggers it is not"
Q. Is this by design in Oracle ? (I guess yes) Any reason for doing it so ?
As you put it in another reply when trigger is fired across calls it gets reparsed while in procs it is once per session.
So then it would make sense to have the same parsing/caching logic for triggers .
Hence the question why is the behaviour different.
Thank you very much for all the help
Ian
February 14, 2003 - 8:47 pm UTC
q) obviously yes, it does it that way.
not sure why...
well, triggers have an entirely different history then stored procedures so the reasoning is probably legacy in nature. Prior to 7.3, they were not stored compiled - they were just stored as "text". I have a feeling that deep down they are still very much like an anonymous plsql block as they used to be -- just stored compiled. Hence, just like an anonymous block executed two times:
begin
for i in 1 .. 5 loop
for x in ( select * from emp ) loop null; end loop; <<<== sql is parsed once
end loop;
end;
/
begin
for i in 1 .. 5 loop
for x in ( select * from emp ) loop null; end loop; <<<== sql is parsed once
end loop;
end;
/
the sql is parsed ONCE per block execution -- that is the way anonymous blocks are done - so I'm guessing it is some of that legacy.
This Tuning issue always challenges whatever you learnt!!!
Anand H S, February 23, 2003 - 6:09 am UTC
Hi Tom,
Great Explanation Given for the parseing/caching of triggers when compared to procedures. Infact I happened to use a lot of triggers in my applications and I was once asked this question By an interviewer as to why i used trigger and not procedure for the operations that I had just described! And I was stumped that time!!!!
And I must say that I have come to like your style of handling all the queries!!
Great!!!
Referencing OLD and NEW values
Martin, February 27, 2003 - 8:30 am UTC
Hi Tom,
just a quick question about the use of stored procs etc. within triggers. When you write a trigger which references :OLD and :NEW attributes, what do you recommend about utilising stored procs in that situation? Keep them in the trigger as they were originally, or put them as parameters etc in the stored proc?
Thanks
February 27, 2003 - 7:02 pm UTC
depends on what I'm doing. Might pass them as parameters, might set them directly. Do whatever feels "natural", just consider them variables you didn't have to declare.
Confusion
Riaz Shahid, March 22, 2003 - 6:49 am UTC
Can't we pin the trigger through dbms_pool so that its execution doesn't need parse calls when called again???
Riaz
March 22, 2003 - 9:44 am UTC
Pinning causes code to be "locked down" in the shared pool.
Pinning will never reduce PARSE counts.
Pinning could reduce HARD PARSES since the statement should always be found in the shared pool
Hence, no, pinning will not affect this behaviour. In any case, it is not the TRIGGER that is getting parsed -- it is the SQL within the trigger. Pinning that SQL will do nothing either.
Pinning is something we do not have a need to ever do, not since version 8.0 when they introduced the LARGE POOL.
Robert, March 22, 2003 - 3:43 pm UTC
need more explanation please!
Chandru, March 23, 2003 - 1:28 pm UTC
"Pinning is something we do not have a need to ever do, not since version 8.0 when they introduced the LARGE POOL."
Tom, could you please explain a little more why? I was under the impression that pinning most frequently used packages/procedures at the startup would help. Thanks.
March 23, 2003 - 5:15 pm UTC
help with what? what problem were you trying to solve?
Helena Marková, March 24, 2003 - 5:12 am UTC
excellent thread! a few doubts
A reader, July 09, 2003 - 3:51 pm UTC
Could you kindly explain the followin quote? Does this
imply that using anonymous blocks creates more parses than
it should? What are the two for loops depicting?
Thank you so much!!
"I have a feeling that deep down
they are still very much like an anonymous plsql block as they used to be --
just stored compiled. Hence, just like an anonymous block executed two times
begin
for i in 1 .. 5 loop
for x in ( select * from emp ) loop null; end loop; <<<== sql is parsed
once
end loop;
end;
/
begin
for i in 1 .. 5 loop
for x in ( select * from emp ) loop null; end loop; <<<== sql is parsed
once
end loop;
end;
/
"
July 09, 2003 - 7:32 pm UTC
the two for loops are just depicting "some work performed"
Everytime you execute an anonymous block containing SQL, the SQL contained therein is parsed once if executed (hard or soft).
If you put it into a named plsql block (procedure/function/package), the SQL contain therein is parsed once PER SESSION if executed.
Consider:
ops$tkyte@ORA920> create or replace procedure p
2 as
3 l_cnt number;
4 begin
5 select count(*) into l_cnt from dual;
6 select count(*) into l_cnt from dual;
7 select count(*) into l_cnt from dual;
8 select count(*) into l_cnt from dual;
9 select count(*) into l_cnt from dual;
10 select count(*) into l_cnt from dual;
11 select count(*) into l_cnt from dual;
12 select count(*) into l_cnt from dual;
13 select count(*) into l_cnt from dual;
14 select count(*) into l_cnt from dual;
15 end;
16 /
Procedure created.
<b>procedure with 10 queries</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> column value new_val V
ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'parse count (total)'
5 /
NAME VALUE
------------------------------ ----------
parse count (total) 356
ops$tkyte@ORA920> declare
2 l_cnt number;
3 begin
4 select count(*) into l_cnt from dual;
5 select count(*) into l_cnt from dual;
6 select count(*) into l_cnt from dual;
7 select count(*) into l_cnt from dual;
8 select count(*) into l_cnt from dual;
9 select count(*) into l_cnt from dual;
10 select count(*) into l_cnt from dual;
11 select count(*) into l_cnt from dual;
12 select count(*) into l_cnt from dual;
13 select count(*) into l_cnt from dual;
14 end;
15 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select a.name, b.value, b.value-&V DIFF
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'parse count (total)'
5 /
old 1: select a.name, b.value, b.value-&V DIFF
new 1: select a.name, b.value, b.value- 356 DIFF
NAME VALUE DIFF
------------------------------ ---------- ----------
parse count (total) 371 15
<b>run an anonymous block with 10 queries and we did 15 parses -- now there was a parse for the block of code, a parse each for the queries to print out this info and a parse or two by sqlplus to call dbms_output.get_lines and such -- 10 of these parsed were for the queries themselves.</b>
ops$tkyte@ORA920> declare
2 l_cnt number;
3 begin
4 select count(*) into l_cnt from dual;
5 select count(*) into l_cnt from dual;
6 select count(*) into l_cnt from dual;
7 select count(*) into l_cnt from dual;
8 select count(*) into l_cnt from dual;
9 select count(*) into l_cnt from dual;
10 select count(*) into l_cnt from dual;
11 select count(*) into l_cnt from dual;
12 select count(*) into l_cnt from dual;
13 select count(*) into l_cnt from dual;
14 end;
15 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select a.name, b.value, b.value-&V DIFF
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'parse count (total)'
5 /
old 1: select a.name, b.value, b.value-&V DIFF
new 1: select a.name, b.value, b.value- 371 DIFF
NAME VALUE DIFF
------------------------------ ---------- ----------
parse count (total) 386 15
<b>run the same block, same activity -- all 10 queries were parsed (soft parsed but parsed)</b>
ops$tkyte@ORA920> exec p
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select a.name, b.value, b.value-&V DIFF
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'parse count (total)'
5 /
old 1: select a.name, b.value, b.value-&V DIFF
new 1: select a.name, b.value, b.value- 386 DIFF
NAME VALUE DIFF
------------------------------ ---------- ----------
parse count (total) 401 15
<b>initially looks no different however...</b>
ops$tkyte@ORA920> exec p
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select a.name, b.value, b.value-&V DIFF
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'parse count (total)'
5 /
old 1: select a.name, b.value, b.value-&V DIFF
new 1: select a.name, b.value, b.value- 401 DIFF
NAME VALUE DIFF
------------------------------ ---------- ----------
parse count (total) 406 5
<b>10 less parses! plsql nicely cached the cursors for us, now we just have the queries, the block that executed the procedure, dbms_output/dbms_application_info calls. In your real program -- you would have ZERO parses (the 5 is only because we are using sqlplus, a very very generic tool)</b>
excellent - thank you!
A reader, July 10, 2003 - 12:35 pm UTC
could you kindly alo explain "In your real program -- you would have ZERO parses (the 5 is only because we are using sqlplus, a very very generic tool)"
Thank you!
July 10, 2003 - 2:09 pm UTC
in your real program, you would parse "begin p; end;" ONCE in your program and you would execute it over and over.
The second execute would therefore involve ZERO parses. No parse of the anonymous block, no parse of the SQL contained therein. Just an EXECUTE.
I dont understand the question
A reader, July 10, 2003 - 1:10 pm UTC
A procedure and a trigger are two separate entities.
I guess (from the answers by Tom), we are aksing if
the code inside the trigger should be implemented
directly or should we call a plsql procedure to do
that.
July 10, 2003 - 3:05 pm UTC
I would implement the code as a packaged procedure and have the trigger be a one liner that simply calls the packaged procedure.
How true!
A reader, July 10, 2003 - 4:21 pm UTC
"I don't take the answer and try to
make it fit the question."
This is something I seem to see a lot with a single function: DECODE. I've been in quite a few shops as a consultant, and there seems to be an uncanny tendancy to look at a question and ask 'How can I make DECODE do this?' I've seen some incredibly convoluted, albeit creative, uses of DECODE that seem to indicate that it's the one function that most would-be 'developers' understand completely.
I'm curious if you've seen the same thing, and if so, why do you think that is?
July 10, 2003 - 5:26 pm UTC
yes, I've seen some gnarly decodes -- CASE makes them more readable.
I don't really have any "why I think this is so" answers for that....
I like to introduce decode (and now case) lots myself... as a tuning device (to remove unions most often)...
Excellent Tom!
Sami, July 11, 2003 - 6:57 am UTC
Is a good idea to move it to a procedure?
A reader, March 17, 2004 - 4:24 pm UTC
Hi Tom, please
Only to be sure, do you think is a good idea to move this code to a trigger, if it is executed frequently (some hundreds of time each day)
I think it can be more simple.
CREATE OR REPLACE TRIGGER daz.tgr_utl_tablas_me
BEFORE
INSERT
ON daz.utl_tablas_me
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.TBL_EMPRESAGESTION IS NULL THEN
:NEW.TBL_EMPRESAGESTION := DB_UTL_ME;
END IF;
END;
/
Thanks Tom
March 17, 2004 - 4:27 pm UTC
what is "db_utl_me"?
and what would you move into a procedure?
and you could use "when" in the declare to have this trigger only run "when" that column is null
Thanks Tom
A reader, March 17, 2004 - 4:38 pm UTC
A request..
Matt, March 17, 2004 - 6:12 pm UTC
"I like to introduce decode (and now case) lots myself... as a tuning device (to
remove unions most often)... "
Can you please provide a brief example of the above?
THanks,
March 17, 2004 - 6:43 pm UTC
A question:
Matt, March 30, 2004 - 5:51 pm UTC
From the links I see two possible approaches to re-writing queries that use a union.
case 1: Unioning the same table (when there are two column values in a record that must be treated as a 'attribute' value)
The following:
select pk, col1 from tab1
union all
select pk, col2 from tab1;
can be re-written as:
select case when rn=1 then col1
when rn=2 then col2
end unioned_col
from tab1
,( select 1 rn from dual
union all
select 2 from dual );
case 2: Unioning multiple different tables
select pk, col1 from tab1
union all
select pk, col2 from tab2
can be re-written as:
select pk
,case when rn=1 then a.col1
when rn=2 then b.col2
end unioned_col
from tab1 a
,tab2 b
,( select 1 rn from dual
union all
select 2 from dual )
where a.pk = b.pk;
Do you have any idea when either if these approaches become more favourable than the alternative query with the union all (or even just union to remove duplicates)?
Thanks.
March 30, 2004 - 6:39 pm UTC
case 2 is not a "good idea" in general. it doesn't work in general.
Almost anytime I can remove a UNION/ALL of the same table with itself -- I feel better as I'm doing less work overall.
Is this still the case w/10g?
Kari Jacobs, November 30, 2004 - 6:27 pm UTC
Tom,
You stated 'I would implement the code as a packaged procedure and have the trigger be a one
liner that simply calls the packaged procedure.'
Is this still the case in 10g? Or has trigger processing improved?
November 30, 2004 - 8:45 pm UTC
the problem is the trigger isn't really a "named unit", it is a strange thing.
triggers should be tiny.. (i'd like to have as few as humanly possible, zero is best)
same non-cached sql over calls in 10g.
How About for Last Modified Timestamp
Marshall B Thompson, December 01, 2004 - 5:02 pm UTC
Suppose we want a fairy typical last modified timestamp and last modified by column on our table. Using a trigger to set the timestamp on insert/update will assure it always gets set. My application will do inserts/updates with packaged procedures, so I could also set it there. Which would be best performance-wise? Or, does it really matter?
December 01, 2004 - 7:57 pm UTC
if you are doing packaged apis (good), do it there -- as long as that will be the method used to always do the modifications. the trigger would be useful if the modifications can happen directly.
the triggers will be slower -- but you have to ask "are they faster than fast enough"
Cached or not?
Tracy, December 07, 2004 - 7:33 am UTC
From your original answer...
and we said...
1) in a PACKAGED procedure. The SQL in packages/procedures will be cached for
your session, reducing the number of parses whereas it is not in a trigger.
And then in a later followup...
Followup:
trigger code is cached.
Please can you clarify - I understood that SQL in a package is cached for the session, but SQL in a trigger is not. What (if anything) in trigger code is cached then?
December 07, 2004 - 10:30 am UTC
trigger code is cached in the shared pool.
the sql is cached in the shared pool
the point I was making is that the sql in your plsql packaged procedure will be parsed approximately ONCE per session whereas the sql in your trigger will be parsed approximately ONCE PER EXECUTION.
therefore, put the sql into a packaged procedure to gain the extra advantage of parse once, execute many.
Triggers, packages, and parsing
Tim Hall, July 17, 2005 - 4:04 am UTC
This thread provides a superb explanation of several important "best practices" every developer should know. The excellent examples illustrate how simple it can be for a developer to prove these concepts for himself/herself.
July 17, 2005 - 9:23 am UTC
written like one of those script kiddies ;)
trigger vs procedure
Menon, September 27, 2005 - 11:52 am UTC
Hi Tom
Interestingly, if you do the insert in a loop, the trigger and procedure show the same parse counts
---
scott@MYORA92> drop table t;
Table dropped.
scott@MYORA92> create table t( x number, y varchar2(20) );
Table created.
scott@MYORA92>
scott@MYORA92> create or replace trigger t_trig
2 before insert or delete or update of x, y
3 on t
4 for each row
5 declare
6 l_count number;
7 begin
8 select /* in trigger */ count(*) into l_count from dual;
9 p;
10 end;
11 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> create or replace procedure p
2 is
3 l_count number;
4 begin
5 select /* in procedure */ count(*) into l_count from dual;
6 end p;
7 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> @trace
scott@MYORA92> alter session set timed_statistics=true;
Session altered.
scott@MYORA92> alter session set max_dump_file_size=unlimited;
Session altered.
scott@MYORA92> alter session set events '10046 trace name context forever, level 12';
Session altered.
scott@MYORA92>
scott@MYORA92> begin
2 for i in 1..10000
3 loop
4 insert into t (x, y ) values( i, i||'*');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
scott@MYORA92> spool off
-----
tkprof's relevant portions:
SELECT /* in procedure */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.37 0.35 0 0 0 0
Fetch 10000 0.19 0.14 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.56 0.50 0 30000 0 10000
SELECT /* in procedure */ count(*)
from
dual
and for procedure
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.37 0.35 0 0 0 0
Fetch 10000 0.19 0.14 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.56 0.50 0 30000 0 10000
September 27, 2005 - 1:35 pm UTC
the trigger sql is cached for the duration of the top level statement that caused the trigger to fire, in your case, that would be the plsql block - it is "sql" as far as Oracle is concerned, it is the "statement", it is the atomic statement to be performed.
cut and paste error in prev post
Menon, September 27, 2005 - 12:32 pm UTC
the last tkprof output should be for trigger case
SELECT /* in trigger */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.36 0.34 0 0 0 0
Fetch 10000 0.11 0.16 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.47 0.51 0 30000 0 10000
Thanx for the clarification!
Menon, September 27, 2005 - 3:05 pm UTC
further clarification
Menon, March 15, 2006 - 8:50 pm UTC
"the trigger sql is cached for the duration of the top level statement that
caused the trigger to fire, in your case, that would be the plsql block - it is
"sql" as far as Oracle is concerned, it is the "statement", it is the atomic
statement to be performed"
I thought I had understood this the first time, but apparently not:) I tried an experiment where I called an outer procedure that does the insert in a loop (this would be the most common scenario, typically.)
Following is the schema:
--scott@MYORA92>
scott@MYORA92> drop table t1;
Table dropped.
scott@MYORA92> create table t1( x number, y varchar2(20) );
Table created.
scott@MYORA92>
scott@MYORA92> create or replace package pkg
2 is
3 procedure p;
4 end pkg;
5 /
Package created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace package body pkg
2 is
3 procedure p
4 is
5 l_count number;
6 begin
7 select /* in procedure */ count(*) into l_count from dual;
8 end p;
9 end pkg;
10 /
Package body created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace trigger t1_trig
2 before insert or delete or update of x, y
3 on t1
4 for each row
5 begin
6 pkg.p;
7 end;
8 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> create or replace procedure p_t_outer
2 is
3 begin
4 for i in 1..10000
5 loop
6 insert into t (x, y ) values( i, i||'*');
7 end loop;
8 end p_t_outer;
9 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace procedure p_t1_outer
2 is
3 begin
4 for i in 1..10000
5 loop
6 insert into t1 (x, y ) values( i, i||'*');
7 end loop;
8 end p_t1_outer;
9 /
Procedure created.
scott@MYORA92> show errors;
No errors.
---
Following is my run
---
@trace
exec p_t_outer
exec p_t1_outer
---
And my trace file shows:
---
SELECT /* in trigger */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.36 0.31 0 0 0 0
Fetch 10000 0.13 0.13 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.49 0.44 0 30000 0 10000
SELECT /* in procedure */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.27 0.32 0 0 0 0
Fetch 10000 0.14 0.14 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.41 0.47 0 30000 0 10000
---
So no difference. Could you please elaborate why there is no difference in the above scenario?
March 16, 2006 - 7:55 am UTC
I don't see any selects in a trigger here.
But - I do only see two calls - p_t_outer p_t1_outer - each is "a call", I would expect the same parse counts from either.
re-read the original example above, it does multiple calls - each call runs a procedure with some SQL (that is cached for the SESSION) and each call runs the trigger with some SQL (that sql is cached for the duration of the STATEMENT/CALL).
sorry forgot to paste the schema completely
Menon, March 15, 2006 - 8:52 pm UTC
The complete schema is as follows:
scott@MYORA92> drop table t;
Table dropped.
scott@MYORA92> create table t( x number, y varchar2(20) );
Table created.
scott@MYORA92> create or replace trigger t_trig
2 before insert or delete or update of x, y
3 on t
4 for each row
5 declare
6 l_count number;
7 begin
8 select /* in trigger */ count(*) into l_count from dual;
9 end;
10 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> drop table t1;
Table dropped.
scott@MYORA92> create table t1( x number, y varchar2(20) );
Table created.
scott@MYORA92>
scott@MYORA92> create or replace package pkg
2 is
3 procedure p;
4 end pkg;
5 /
Package created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace package body pkg
2 is
3 procedure p
4 is
5 l_count number;
6 begin
7 select /* in procedure */ count(*) into l_count from dual;
8 end p;
9 end pkg;
10 /
Package body created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace trigger t1_trig
2 before insert or delete or update of x, y
3 on t1
4 for each row
5 begin
6 pkg.p;
7 end;
8 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> create or replace procedure p_t_outer
2 is
3 begin
4 for i in 1..10000
5 loop
6 insert into t (x, y ) values( i, i||'*');
7 end loop;
8 end p_t_outer;
9 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace procedure p_t1_outer
2 is
3 begin
4 for i in 1..10000
5 loop
6 insert into t1 (x, y ) values( i, i||'*');
7 end loop;
8 end p_t1_outer;
9 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> spool off
OK
Kumar, March 16, 2006 - 9:17 am UTC
Hi Tom,
I have some questions.
1)How to serialize the execution of procedure so that
other users cannot execute it while one user is running it??
2)If I call a procedure 10 times,will it be loaded 10 times
into the library cache??
March 16, 2006 - 2:31 pm UTC
1) read about dbms_lock - you can use it to serialize things. search on this site for examples
2) that would defeat the purpose of a cache! No, it would be there just once.
Thanx Tom - still there is some confusion
Menon, March 16, 2006 - 1:20 pm UTC
"I don't see any selects in a trigger here.
But - I do only see two calls - p_t_outer p_t1_outer - each is "a call", I would
expect the same parse counts from either."
Sorry for that I did not cut and paste the whole thing in the first review.
"
re-read the original example above, it does multiple calls - each call runs a
procedure with some SQL (that is cached for the SESSION) and each call runs the
trigger with some SQL (that sql is cached for the duration of the
STATEMENT/CALL).
"
I re ran my tests.
Here is the schema.
Table t -> has a trigger with the select within the trigger
Table t1 -> has a trigger that invokes a procedure with the same select (different comment)
procedure p_trig_sel_in_trigger -> invokes an insert into table t
procedure p_trig_sel_in_procedure -> invokes an insert into table t1
The schema
------------------
scott@MYORA92> drop table t;
Table dropped.
scott@MYORA92> create table t( x number, y varchar2(20) );
Table created.
scott@MYORA92> create or replace trigger t_trig
2 before insert or delete or update of x, y
3 on t
4 for each row
5 declare
6 l_count number;
7 begin
8 select /* in trigger */ count(*) into l_count from dual;
9 end;
10 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> drop table t1;
Table dropped.
scott@MYORA92> create table t1( x number, y varchar2(20) );
Table created.
scott@MYORA92>
scott@MYORA92> create or replace package pkg
2 is
3 procedure p;
4 end pkg;
5 /
Package created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace package body pkg
2 is
3 procedure p
4 is
5 l_count number;
6 begin
7 select /* in procedure */ count(*) into l_count from dual;
8 end p;
9 end pkg;
10 /
Package body created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace trigger t1_trig
2 before insert or delete or update of x, y
3 on t1
4 for each row
5 begin
6 pkg.p;
7 end;
8 /
Trigger created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92>
scott@MYORA92> create or replace procedure p_trig_sel_in_trigger ( p_num number )
2 is
3 begin
4 insert into t (x, y ) values( p_num , p_num||'*');
5 end p_trig_sel_in_trigger;
6 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> create or replace procedure p_trig_sel_in_procedure ( p_num number )
2 is
3 begin
4 insert into t1 (x, y ) values( p_num, p_num||'*');
5 end p_trig_sel_in_procedure;
6 /
Procedure created.
scott@MYORA92> show errors;
No errors.
scott@MYORA92> spool off
------------------
Now I tan a test where I invoke the two procedures twice in separate blocks (separate "calls").
---
-- execute outer procedure that inserts into t
@trace
begin
p_trig_sel_in_trigger(1);
end;
/
begin
p_trig_sel_in_trigger(2);
end;
/
begin
p_trig_sel_in_procedure(1);
end;
/
begin
p_trig_sel_in_procedure(2);
end;
/
---
the trace showed same parse counts for both cases
----
-SELECT /* in procedure */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 6 0 2
SELECT /* in trigger */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 0 6 0 2
-----
Only if I invoke the insert directly in the anonymous block does the parse count show any difference. I did that as follows:
------ execute outer procedure that inserts into t
@trace
insert into t (x, y ) values( 1 , 1||'*');
insert into t (x, y ) values( 1 , 1||'*');
insert into t (x, y ) values( 1 , 1||'*');
insert into t1 (x, y ) values( 1, 1||'*');
insert into t1 (x, y ) values( 1, 1||'*');
insert into t1 (x, y ) values( 1, 1||'*');
---
The trace shows
SELECT /* in procedure */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 9 0 3
versus
SELECT /* in trigger */ count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.02 0.02 0 0 0 0
Execute 3 0.01 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.03 0.02 0 9 0 3
------
So looks like if the statement is within a procedure that invokes the insert, there is no extra cost in parsing regardless of if the trigger in the insert statement uses a procedure or directly codes the SQL within the trigger code.Does this contradict your statements getting cached for session versus per call? What did I miss?
Thanx!
March 16, 2006 - 2:51 pm UTC
the insert in the plsql routine never "closed". The statement (the insert) is still open.
Did not get that...
Menon, March 16, 2006 - 3:10 pm UTC
Sorry Tom!
Did not quite get what you mean by "the insert in the plsql routine never "closed". The statement (the insert) is still open."
March 16, 2006 - 3:12 pm UTC
plsql cached the insert statement.
hmmm
Menon, March 16, 2006 - 3:20 pm UTC
"plsql cached the insert statement."
Well, if you are talking of PL/SQL caching the static statements, then should not that have reduced the number of soft parses?
Or are you talking about session cursor cache (I think not.)
Thanx!
March 16, 2006 - 3:31 pm UTC
plsql doesn't close cursors unless and until it needs to. Look at the number of parses on the INSERT.
well,
Menon, March 16, 2006 - 3:41 pm UTC
Here is the first insert into t (code in trigger)
INSERT into t (x, y )
values
( :b1 , :b1||'*')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 2 0.06 0.06 0 1 3 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.09 0.08 0 1 3 2
Here is the second insert into t1 (code in procedure)
INSERT into t1 (x, y )
values
( :b1, :b1||'*')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.02 0.02 0 1 2 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 1 2 2
Both have the same parse counts...
March 17, 2006 - 3:51 pm UTC
exactly - 1 parse.
when the insert is parsed over and over (as applications executing sql are typically doing unfortunately) it all changes.
Trigger with SELECT event
Suvendu, March 20, 2006 - 7:15 am UTC
Hi Tom,
Here is the question regarding trigger similar to SELECT event and the scenario is as follows:
An application named P1 was refering to table named A and now same table A is currently being refering by another applicationn named P2. Now, all the data will refer by application P1 is available in table B. Is there any way to redirect all queries from apolication P1 to point to table B at runtime (dynamically).
Where, there is no way to touch/modify the application code.
Please, correct me if Im wrong.
Thanks a lot for your kind help.
Regards,
Suvendu
March 20, 2006 - 7:22 am UTC
This would be in the category of "hugely bad idea" - not without the application code anyway.
You MIGHT be able to use a synonym, but it depends on the schema used when you use application P1.
for you see, the database doesn't really see "applications", the database sees "users". If you use the same userid for P1 and a different one for P2, a private synonym might be able to do it - but this kind of surgery on an application without the source code is "not smart"
Well,
Menon, March 20, 2006 - 12:05 pm UTC
"exactly - 1 parse.
when the insert is parsed over and over (as applications executing sql are
typically doing unfortunately) it all changes.
"
1 parse in BOTH cases for two executions - whether you store trigger code in procedure or not. The original followup question of mine was an attempt to verify and further understand the improvement one can get by storing trigger code in procedure (instead of in trigger body.)
March 22, 2006 - 1:01 pm UTC
you are missing the point.
remove the procedure which is caching the parsed insert. MAKE THE INSERT PARSE OVER AND OVER.
Not true anymore on 10.2.0.3
Jocelyn Simard, September 07, 2007 - 5:25 am UTC
I ran you test case of the first followup on a 10.2.0.3 database and I get same number of parse for the trigger and the procedure.
That's good news! They've improved that since the original post. Just wanted others to know.
Regards,
ERRATUM
Jocelyn Simard, September 07, 2007 - 5:54 am UTC
Please ignore my last post. I misread my tkprof output... need another coffee ;)
Packeged
karthick, September 13, 2007 - 8:53 am UTC
You said...
1) in a PACKAGED procedure. The SQL in packages/procedures will be cached for your
session, reducing the number of parses whereas it is not in a trigger.
I am asking...
In all your example in this thread where you used the procedure "P" is not a procedure inside a package, correct??
Then how its catched for a session.
Am i missing some thing??
September 15, 2007 - 7:29 pm UTC
I said
the sql in packages/procedures
meaning the sql in packages and/or procedures (and well, functions too)
Always use packaged procedures for real code, it promotes modular coding and encapsulation.
I use standalone procedures to demonstrate simple concepts only.
Use Triggers or Modifying Code
David Piazza, June 06, 2008 - 5:54 pm UTC
I have looked through this topic to determine if it is better to update a column through a trigger or in an application(JAVA). I see that the trigger can cause more parses, and be slower, but is slower fast enough?
Developers want to compare two date fields in a table and if the interval is within a certain range, update another column in the same table. They want to do this on every insert. There are thousands of tables and they want to put a trigger on each table. Each table has thousands of rows a day inserted into it. I think it would be better to add a few lines of JAVA in the application to do the compare, but they don't want to modify the code. I'm gathering information to show them it would be better to change the code. What is your opinion?
June 09, 2008 - 11:35 am UTC
... Developers want to compare two date fields in a table and if the interval is
within a certain range, update another column in the same table. ...
Umm, no they do not. This is what is called "derived data", we derive this data, we do not compute it and store it.
Tell them to look up "view".
No trigger
No column
Just a view that derives the necessary data
and if they say "we want to index it", say "ok, we'll index it" - that is all - you don't want or need to compute/store/maintain this value at insert/update time.
Same parses on 11gR1
Dhruva, June 20, 2012 - 4:42 am UTC
Just to make the community aware, the number of parses for a SQL are same in 11gR1, be it triggers or in procedures.
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 557 0.01 0.01 0 0 0 0
Fetch 557 0.00 0.00 0 0 0 557
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1115 0.01 0.01 0 0 0 557
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 557 0.00 0.01 0 0 0 0
Fetch 557 0.01 0.00 0 0 0 557
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1115 0.01 0.01 0 0 0 557
June 21, 2012 - 7:27 am UTC
Yes, it's been that way since at least 10g.
but still, put it in packages. Modularize the code.
trigger parsing
A reader, June 21, 2012 - 7:37 am UTC
Please confirm my understanding from the entire post
Boris, April 19, 2013 - 9:31 pm UTC
This is what I understood after reading this entire post:-
1. In Anonymous block, the SQL is parsed ONCE per block execution in any oracle version (initially hard parse and then soft parse)
2. In stored procedure, the SQL is parsed once per session in any oracle version (initially hard parse and then soft parse)
3. Triggers
3.1) Prior Oracle 7.3 - triggers were not stored compiled. They were just text. They will get compiled for each call (hard parse always). SQL in a trigger is not cached.
3.2) Before 10g, SQL in a trigger is cached for the duration of the statement/call (hard parse for each call)
3. From Oracle 10g - SQL inside the trigger is cached for the entire SESSION (same as stored procedures). (Initially hard parse; and then soft parse)
Could you please advise on my understanding?
Thanks in advance and thanks for doing such a wonderful service to the oracle community.
Regards,
Boris
April 22, 2013 - 8:07 pm UTC
1) the sql contained in an anonymous block will have to be parsed to be executed whenever the anonymous block is parsed, yes.
2) assuming we do not exceed the plsql cursor cache (set by the session_cached_cursors parameter)
3) correct, assuming we do not exceed the plsql cursor cache again..
Boris, April 23, 2013 - 2:00 am UTC
Thank you so much for clarifying doubts. You are simply the best.
Still higher number parsing in trigger than stored procedures in 10g
Sasa Petkovic, April 23, 2013 - 2:13 pm UTC
Hi,
Seems that number of parsing in trigger is still 6 versus in procedure is 1 (for the same test provided) for 10g (EE 10.2.0.5). While in 11g it works same..one parse in trigger and one parse in stored procedures.
Regards,
April 23, 2013 - 3:03 pm UTC
doh, you are correct, it was 11g that made triggers cache the sql - sorry about that.
all releases - plsql units cache
11g and above - triggers cache
Excess redo generation
Shankar Sen, July 11, 2013 - 12:54 pm UTC
As you pointed out in your book expert one on one (starting page no 216) triggers on tables cause more redo generation for insert, update or delete statement. Is it true for version 10g?