| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL text within TRIGGERs
You will also find that triggers share similar behavior to random sql =
statements in that they have to be loaded every time their used. If =
your not short on SHARED_POOL space that's not really a problem, but if =
you are it could become one. Packages on the other hand load and remain =
resident (save for variables) until they age out. Packages can also be =
pinned, triggers cannot.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: J.Velikanovs_at_alise.lv [mailto:J.Velikanovs_at_alise.lv]
Sent: Tuesday, June 22, 2004 6:04 AM
To: oracle-l_at_freelists.org
Subject: RE: PL/SQL text within TRIGGERs
Good discussion, indeed!
Thank you Justin for pointing me ;)
Summary:
>> Triggers have an entirely different history then stored procedures so =
the=20
>> reasoning is probably legacy in nature. Prior to 7.3, they were not=20
stored=20
>> compiled - they were just stored as "text". I have a feeling that =
deep=20
down=20
>> they are still very much like an anonymous plsql block as they used =
to=20
be --=20
>> just stored compiled.
Any SQL within TRIGGER will be parsed (probably soft parsed) for each =
SQL=20
call. If we write SQL within procedure and call this procedure from the=20
trigger SQL can be cashed and parsing step will be skipped.
Thank you again,
It is right that I looking for,
Jurijs
9268222
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
http://otn.oracle.com/ocm/jvelikanovs.html
"Justin Cave" <justin_at_askddbc.com>
Sent by: oracle-l-bounce_at_freelists.org
22.06.2004 12:47
Please respond to oracle-l
=20
To: <oracle-l_at_freelists.org>
cc:=20
Subject: RE: PL/SQL text within TRIGGERs
This is generally still a good recommendation. Tom Kyte has a =
discussion=20
of
this here:
http://asktom.oracle.com/pls/ask/f?p=3D4950:8:::::F4950_P8_DISPLAYID:7507=
70687
5149
The Cliff Notes version is that trigger code has to be (soft) parsed on=20
each
execution, so you'll get more scalability embedding the code in stored
procedures.=20
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of J.Velikanovs_at_alise.lv
Sent: Tuesday, June 22, 2004 3:46 AM
To: oracle-l_at_freelists.org
Subject: PL/SQL text within TRIGGERs
Hi gurus!
At the beginning of my career ~5-6 years ago some one, told me that =
write
PL/SQL text within TRIGGER body is not good idea, because trigger text
compiling by Oracle at each trigger call. And it is better idea, to call
from trigger just procedure there we implement all the code. At the =
moment=20
I
don't remember who told me about this issue, I even don't know was it at
Oracle course or at other event time.
Is this recommendation is myth or it still true for Oracle environment. Can anybody comment on it?
Thank you in advance,
Jurijs
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 22 2004 - 07:17:13 CDT
 |
 |