Re: Please help with performance problem

Keith Boulton wrote:
>
> On Thu, 20 Nov 1997 20:42:22 -0200, Gena <gshepin_at_webspan.net> wrote:
>
> snip
> >
> >To insert 2500 records on a 4 field table (which has only about 2000
> >records) takes 45 seconds to a minute.
> >To update or delete the same # of records (the delete criteria is on an
> >indexed field) takes 30-40 seconds.
> snip
> Strange problem: each subsequent execution of the sqlplus command file
> containing the insert statements takes thirty seconds longer i.e.
> first run takes 30 seconds, second run takes 60 seconds, third run
> takes 90 seconds. I stopped when it was taking five minutes. I commit
> between runs. This is not affected by exiting and re-entering sqlplus,
> nor does it seem to be affected by db_block_buffers. The time resets
> to 30 seconds after the database is shutdown and restarted. Does
> anyone have an explanation of this?
> snip

Each statement in SQL*Plus must be parsed from scratch, since all values are
literals (or literal substitutions). Each SQL statement is added to the SQL
cache, which exacts a noticeable performance hit and, periodically, requires
a cache re-organisation.

Using a re-executable statement with substitutable variables (as in a parameterised Pro* or PL/SQL procedure) requires only a single statement entry
in the cache.

HTH.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"