loss of performance when using anonymous pl/sql blocks?!

[Markus Breuer]

Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:

DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;

Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?

thx, markus

[Jim Kennedy]

Re: loss of performance when using anonymous pl/sql blocks?!


"Markus Breuer" <markus.breuer@ gmx.dewrote in message
news:3F8D36EF.4 090309@gmx.de.. .

Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:
>
DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;
>
Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?
>
thx, markus
>
>

The fastest way would be a straight insert with a host variable, keep the
cursor around, rebind and reexecute. If you were inserting a lot of rows at
a time then use the array interface to blast them in 100 or more rows at a
time.
Jim

[Ryan Gaffuri]

Re: loss of performance when using anonymous pl/sql blocks?!

Markus Breuer <markus.breuer@ gmx.dewrote in message news:<3F8D36EF. 4090309@gmx.de> ...

Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:
>
DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;
>
Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?
>
thx, markus

why dont you run it and find out? Oracle will still cache it. This
shouldnt have any effect on performance.