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: Compare sql and dynamic sql

Re: Compare sql and dynamic sql

From: Dave Waterworth <pscdaw_at_ihug.com.au>
Date: Sat, 2 Oct 1999 00:03:52 +1000
Message-ID: <7t2f5b$ku6$1@toto.tig.com.au>


Hi

I created a standard query which I tuned for rule based optimisation (no stats on any table etc.), which executed very quickly. I then defined it as a PL/SQL cursor in a package and wrote a basic PL/SQL loop to fetch each row. The performance was terrible! I then recreated the query as a view, and modified the PL/SQL cursor to SELECT * FROM view and re-ran the procedure. It performance was much quicker than defining the very same SQL statement as the PL/SQL cursor. I then retuned the query for CBO and executed as a PL/SQL cursor. Performance was fine.

Why would that be? Do the same CBO/RBO rule apply to SQL executed by PL/SQL? It appeared to me that PL/SQL cursor always run under CBO (in using 7.3.4) regardless of presence of stats/hints etc?

If this happens when I execute standard SQL from PL/SQL then maybe the same occur when executing dynamic SQL, hence the appearance that it is slower?

Dave Waterworth

Power Systems Consultants NZ Ltd

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:fprzNyGqNXF7OA3ZAGPNvqkeNeAY_at_4ax.com...
> A copy of this was sent to Jay <mighty_dragon_at_hotmail.com>
> (if that email address didn't require changing)
> On Thu, 30 Sep 1999 10:20:53 -0500, you wrote:
>
> >For a large number of sql statements w/c contain a pattern, for example:
> >
> > insert into foo_1 values (1, one);
> > insert into foo_2 values (2, two);
> > insert into foo_3 values (3, three);
> > ...
> > insert into foo_n values (n, n);
> >
> >Someone had claimed that the above execution is faster than any means
> >like using stored procedures with pl/sql OR dynamic sql OR native
> >dynamic sql.
> >
>
> all SQL in Oracle is dynamic sql -- even static sql. There is sql that is
known
> when you compile a program -- it is 'static' sql. We can make it easier
to
> write programs if we know the SQL at compile time. It runs no faster nor
slower
> then 'dynamic' sql. Dynamic sql is just sql we don't know at compile
time. We
> don't store compiled SQL plans (well, in 8i, release 8.1 there are query
> outlines....).
>
> Dynamic sql is as fast as Static sql since they are the *same*.
>
> >Can anyone explain any reason why using stored procedures would be
> >slower.
> >
>
> Nope -- ask them to give an example.
>
> >Is there any overhead using dynamic sql over straight sql???
> >
> >In any case, can anyone refer me to a document that will explain in some
> >details how dynamic sql execute internally.
> >
>
> same as static. the client ships the sql to the srver, the server parses
the
> query. client might add some bind variables and then ask the server to
execute
> the compiled query.
>
> >
> >
> >Thanks in advance for any help.
>
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Fri Oct 01 1999 - 09:03:52 CDT

Original text of this message

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