Re: Oracle Development - Best Practice
- From: Mogens Nørgaard <mln@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 08 Feb 2004 06:01:51 +0100
Yeah, I wouldn't so much worry about before or after triggers - the
difference must be very small. Perhaps it's more important to let the
developers know that triggers in themselves mean overhead, but perhaps a
good or a necessary overhead.
They could even evidence the overhead of the context switch with the
good, old Dave Ensor test: Update 10000 rows. Now add a trigger that
does nothing. Rerun test. Compare times. Repeat test with trigger that
actually does something. Compare times.
It's dangerous to write down Best Practices, because things move or they
become barriers for thinking new thoughts, or because the list becomes
downright random (often depending on whatever the author has experienced
in the six months prior to doing the list).
Might sometimes be better to document Bad or Worst Practices. This list
can be expanded as the project matures, and there's often a better
chance of getting input for such a list from the developers themselves,
which is always better on the motivational side. This is Cary's idea
(twisted a bit). But the more I think about it, the more sense it makes.
How many Best Practices for DBA-related work have you seen? How many are
you using? Exactly.
Imagine being a DBA, and before you start working, the developers come
up with a list of Best Practices you should follow in your work as a
DBA. Yeah, that would work :-).
Mogens
Bobak, Mark wrote:
>Tim,
>As far as his #8, I think he got that from Steve Adams. Steve did some work
>a while ago that determined that, all else being equal, after triggers
>were cheaper than before triggers. Unfortunately, I don't remember the
>details, and the cursory search I just did on IxOra didn't yield any hits.
>
>As I recall, the difference was pretty subtle. People need to keep in mind,
>when Steve says X is cheaper than Y, no doubt he's absolutely right, but
>sometimes, these are small, subtle differences. I'm not sure I'd have
>extrapolate his results into a directive to avoid before triggers at all
>costs.
>
>-Mark
>
>
>-----Original Message-----
>From: Tim Gorman [mailto:tim@xxxxxxxxxxxxx]
>Sent: Sat 2/7/2004 7:16 PM
>To: oracle-l@xxxxxxxxxxxxx
>Cc:
>Subject: Re: Oracle Development - Best Practice
>9) Use DBMS_APPLICATION_INFO procedures to "register" a running program by
>setting the MODULE and ACTION columns in the V$SESSION and V$SQL views.
>Provide a standard set of code fragments to developers to be executed
>immediately after the module entry point and before all module exit points
>(including error handlers).
>
>10) As part of allowing program modules to be cleared for production, review
>TKPROF reports from SQL traces from unit-testing. Have one member of a team
>of 1-5 people who know how to read TKPROF reports approve or disapprove.
>Nothing goes to UAT or production without this approval...
>
>11) Peer code reviews should be performed frequently enough to keep "best
>practices" document up to date. Peer code reviews should not only ensure
>that "best practices" are applied, but also that "not-so-good practices" are
>revised or removed and "better practices" are added...
>
>---
>
>Some notes:
>
> - #1 Applies to every language, including SQL. Just because a language
>supports implicit conversions doesn't mean you should ever use it.
>
> - #2 Doesn't make much sense in PL/SQL, because PL/SQL variables are so
>easy to use as bind variables -- there just doesn't seem to be any other way
>to do things. The danger there is dynamic SQL, where people get carried
>away with building the SQL statement in a string and forget to use bind
>variables...
>
> - #3 What exactly is meant by "whereever required"? Kinda vague.
>Better to say something like "use BULK operations for bulk operations" or
>say nothing at all. People can get into a lot of trouble trying to push a
>square peg into a round hole unnecessarily...
>
> - #4 "Write code that fits into your screen size"? Assuming the number
>of people who develop from a Blackberry are as rare as those who use 35"
>monitors, this can still lead to unreadable and unmaintainable code. I
>suspect that this would fall out after the first code review or so. Just as
>with writing tax policy, strange directives can lead to strange results...
>
> - #5 For one thing, the OTHERS exception must be the last handler
>specified, so it's always "the last resort" anyway. And there's nothing
>wrong with using declared exceptions (i.e. NO_DATA_FOUND, etc) and defining
>your own where pre-defined ones don't exist (i.e. PRAGMA EXCEPTION_INIT),
>instead of building up a big IF SQLCODE ... THEN ... ELSIF ... structure...
>
> - #7 "No code change necessary when schema changes"??? Doubt it!!! How
>about "fewer code changes necessary"...
>
> - #8 That's a new one by me. Can you prove it?
>
>Hope this helps...
>
>-Tim
>
>
>on 2/7/04 4:35 PM, jaysingh1@xxxxxxxxxxxxx at jaysingh1@xxxxxxxxxxxxx wrote:
>
>
>
>>Dear All,
>>
>>We are starting a new oracle development project and my boss wants me to
>>prepare "Oracle Development- Best practice" document/presentation kind of
>>stuff. Basically this is to avoid common mistakes during the development
>>cycle.
>>
>>I have few points..
>>
>>For example,
>>
>>1) While writing pl/sql, use the correct datatype so that implicit conversion
>>will be avoided
>>
>>2) Use bind variable to avoid unnecessary parsing
>>
>>3) Use BULK COLLECT, % ATTRIBUTES wherever required
>>
>>4) MODULARITY
>>Write the code that fit into your screen size.
>>Through successive refinement, you can reduce a complex problem to a
>>set of simple problems that have easy-to-implement solutions.
>>
>>5) EXCEPTION WHEN OTHERS is almost always a BUG unless it is immediately
>>followed by a
>>RAISE.Use WHEN OTHERS exception as the last resort and handle exceptions.
>>
>>For Example,
>>EXCEPTION
>>WHEN OTHERS THEN
>> if (sqlcode=-54) then
>> .... deal with it.
>> else
>> RAISE;
>> end if;
>>
>>
>>6) Tom's Mantra
>>
>>If (possible in SQL)
>>do it;
>>else if(possible in PL/SQL)
>>do it;
>>else if(possible in JAVA)
>>do it;
>>else
>>..
>>..
>>end if;
>>
>>7)% ATTRIBUTES
>>
>>Use %TYPE and %ROWTYPE attributes. No code change is required when schema
>>structure changes.
>>
>>8) BEFORE VS AFTER TRIGGER
>>
>>NEVER USE BEFORE TRIGGER FOR VALIDATIONS. Use BEFORE triggers ONLY to
>>modify :NEW value.
>>
>>AFTER row triggers are slightly more efficient than BEFORE
>>row triggers. With BEFORE row triggers, affected data blocks must
>>be read (logical read, not physical read) once for the trigger and
>>then again for the triggering statement.
>>Alternatively, with AFTER row triggers, the data blocks must be
>>read only once for both the triggering statement and the trigger.
>>
>>These are only few points w.r.t oracle developers. I like to get more info
>>from you.
>>
>>Your help would be really appreciated.
>>
>>Thanks
>>Jay
>>
>>----------------------------------------------------------------
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>----------------------------------------------------------------
>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>put 'unsubscribe' in the subject line.
>>--
>>Archives are at //www.freelists.org/archives/oracle-l/
>>FAQ is at //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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>
>-- Binary/unsupported file stripped by Ecartis --
>-- Type: application/ms-tnef
>-- File: winmail.dat
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
