Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Deadly sins againts database performance/scalability

Re: Deadly sins againts database performance/scalability

From: Joel Garry <joel-garry_at_home.com>
Date: 26 Nov 2003 16:44:17 -0800
Message-ID: <91884734.0311261644.440e0a3e@posting.google.com>


Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0311261044.3b66c9cc_at_posting.google.com>...
> pagesflames_at_usa.net (Dusan Bolek) wrote in message news:<1e8276d6.0311250803.3cbc43be_at_posting.google.com>...
> > Hello,
> >
> > I'm just creating some document how developers should/must not behave
> > when dealing with Oracle database. I written down some basic rules and
> > came with idea what's the most stupid thing that you can do to your
> > database.
> > I have two on mind, first one is not using BIND variables and the
> > second one is using COMMIT in a loop statement. However, there will be
> > more of these.
> > So I have question to you here. What's the most stupid, but in real
> > world foundable, mistakes in development applications using Oracle
> > datatabase?
>
> Dusan, I would suggest you change your wording about issuing commit
> within a loop since in many cases the commits need to be performed
> within a loop. I think it is the frequency of commits rather than the
> logical coding structure that is the issue. In my opinion how
> frequently a commit should be issued is dependent on how likely
> another session will need update access to the rows being processed.
> In a transaction driven system the inventory rows may well require
> commits be issued on a per-row or very few row basis. While another
> program in the application, which is the only source of update
> activity for a table that has low select activity, would best be
> served by one or very few commits.

The problem here is beyond semantics. You are likely breaking up logical transactions into several physical transactions, which means you must go beyond the inherent Oracle mechanisms to maintain atomicity. Some people would be horrified. As someone who deals with your example every day, I say you are exactly right, and wonder why "transaction levels" have never been implemented into the mechanisms (the stuff [4GL/OCI generator] I work with actually had that documented as a future enhancement, but the future never seems to have arrived; there is a language construct for controlling how often a set of rows will commit, so it is easy to fix per loop/per site without major code hassles).

With what we have in Oracle now, there is a legitimate trade-off between your example and "correct" transactional programming. What Dusan needs is a rule that codifies that. There have certainly been some amazingly bad examples of loopy commit.

>
> Be careful in the process of trying to get something into the hands of
> developers that you do not over simplify the problem and end up making
> a statement that you later consider "dumb". Writing for developers is
> not an easy task.

You got that right! Beyond that, it is difficult to make such a list without sounding like, shall we say, authors who have been pummelled on this group for making lists.

Dusan, I encourage you to do this, and volunteer to edit for good English if you email me (s/home.com/cox.net). Apologies in advance if I'm a bit slow reading email.

jg

--
@home.com is bogus.
"The cursor..For loop approach for the update was calculated to take
53.7 years
to complete!"  - Ask Tom posting.
Received on Wed Nov 26 2003 - 18:44:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US