Re: Compare sql and dynamic sql

A copy of this was sent to "Dave Waterworth" <pscdaw_at_ihug.com.au> (if that email address didn't require changing) On Sat, 2 Oct 1999 00:03:52 +1000, you wrote:

>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?
>

No, PLSQL always runs in CHOOSE mode (it will pick RBO or CBO independent of the session settings) -- it ignores the session level setting for the optimizer. So, if any stats were in place when you ran the plsql -- it used the CHOOSE mode and used chose CBO.

PLSQL runs in CHOOSE mode because of shared sql. If every session could have its own query plans for the queries inside of compiled procedures, the sharing going on in the shared pool would be much smaller.

In PLSQL, you can add /*+ RULE */ to queries that you want to run in RULE mode. It will obey that setting.

>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?
>

It could be because someone once upon a time analyzed the tables. You run your queries in sessions with the optimizer goal set to rule. They get one plan. You run them in plsql and they get another (suboptimal) plan because the stats are way out of wack. You update the stats and plsql starts running better.

the bottom line:

If you have at least one table that is analyzed -- make sure to keep the stats current and analyze *lots* of your tables, not just 1. You should either have:

• no table analyzed
• all tables (currently) analyzed

anything in between (or badly out of date) is a potential problem spot.

>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
>

-- 
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