Cary,
Not that I'm advocating improper SQL (like 'select ...
from dual') but I thought pl/sql held its cursors open
anyway (by default).
Cheers
Connor
--- Cary Millsap <[EMAIL PROTECTED]> wrote: >
Dick,
>
> I think you've misunderstood me. I'm not advocating
> the case for doing
> joins in the client or anything like that. I'm
> saying only that PL/SQL
> makes it too easy to write code that is extremely
> db-call-inefficient.
> Here's an excerpt from a Hotsos-internal document
> written by Jeff Holt
> that is relevant to the issue...
>
> * * *
>
> Here are some working examples of improper and
> proper use of cursors in
> PL/SQL:
>
> IMPROPER: This code uses an implicit cursor to get
> dummy into x. Each
> time this block is executed it opens a cursor,
> parses 'select dummy from
> dual' into the cursor, it executes the cursor,
> fetches one row into x,
> and then closes the cursor. If this code were
> executed frequently enough
> by at least 2 or 3 concurrent sessions, then you'd
> see library cache
> latch contention. set serveroutput on declare
> x varchar2(1);
> begin
> select dummy into x from dual;
> dbms_output.put_line('dummy is ' || x);
> end;
> /
>
> IMPROPER: This code is does exactly the same thing
> as the above example
> except that it uses explicit cursors. The problem is
> that repeated calls
> to this block still require a parse. The irony is
> that this is the
> preferred method described in many application
> developer books including
> Oracle's.
> set serveroutput on declare
> x varchar2(1);
> cursor getd is select dummy from dual;
> begin
> open getd;
> fetch getd into x;
> close getd;
> dbms_output.put_line('dummy is ' || x);
> end;
> /
>
> PROPER: Here's the ONLY way to do a good job. It's
> fully documented in
> the file ?/rdbms/admin/dbmssql.sql. You'll also note
> that there's no
> call to dbms_sql.close_cursor. All well written
> applications won't close
> their cursors until they exit.
> create or replace package session_cursors is
> type sesscur_type is table of binary_integer index
> by binary_integer;
> sesscur sesscur_type;
> getd binary_integer := 0;
> getd_open boolean := false;
> getd_text varchar2(22) := 'select dummy from
> dual';
> end session_cursors;
> /
> show errors
> set serveroutput on
> declare
> x varchar2(1);
> r number;
> icid binary_integer := session_cursors.getd;
> cid binary_integer;
> begin
> if session_cursors.getd_open then
> cid := session_cursors.sesscur(icid);
> else
> cid := dbms_sql.open_cursor;
> session_cursors.sesscur(icid) := cid;
> dbms_sql.parse(cid, session_cursors.getd_text,
> dbms_sql.native);
> session_cursors.getd_open := true;
> end if;
> /* if you had bind variables then you would bind
> them before
> the execute */
> r := dbms_sql.execute(cid);
> dbms_sql.define_column(cid, 1, x, 1);
> r := dbms_sql.fetch_rows(cid);
> dbms_sql.column_value(cid, 1, x);
> dbms_output.put_line('dummy is ' || x);
> end;
> /
>
> If you execute each of these in SQL*Plus you'll see
> one parse/execute of
> 'select dummy from dual' for the first two examples
> but you'll see only
> one parse of 'select dummy from dual' for the last
> example.
>
> * * *
>
> This is what I meant in my original note.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12 Dallas
> - Jonathan Lewis' Optimising Oracle, Nov 19-21
> Dallas
>
>
> -----Original Message-----
> [EMAIL PROTECTED]
> Sent: Monday, November 18, 2002 5:33 AM
> To: Multiple recipients of list ORACLE-L
>
> Cary,
>
> This is one topic I'll disagree with you.
> Assume an application
> that uses
> the database, but is on a machine outside the db
> server. Having a
> number of
> calls that return one or two rows will have a
> negative network impact
> that is
> the results of SQL*Net and it's inefficiencies. It
> is better in this
> case to
> encapsulate all of the database interaction into a
> package where bind
> variables
> will be used to return the desired results. Using
> DBMS_SQL is a really
> BAD
> thing to do for stuff like that. OH, I really think
> that using DBMS_SQL
> is a
> whole lot easier, for some things that is, than
> PRO*C's prepare,
> declare, open,
> fetch, and close especially if you have to use that
> unwieldy SQLDA.
> Lastly, I
> am not a proponent of having the application merge
> result sets. Most
> times the
> merged results are smaller in size than the sum of
> the source giving
> your
> network one heck of a headache.
>
> BTW: I don't evaluate applications by their
> BCHR, but by their
> response
> time. Hit the return key, if I get an answer back
> in 10 seconds from
> the
> original and 5 seconds from the revised, something
> was done right.
>
> Dick Goulet
>
> ____________________Reply
> Separator____________________
> Author: "Cary Millsap" <[EMAIL PROTECTED]>
> Date: 11/16/2002 1:49 AM
>
> Greg,
>
> That's one case. PL/SQL is a really poor language in
> which to write an
> application. The language tricks you into believing
> that writing a
> scalable application can be accomplished in just a
> few lines of 4GL
> code, but it's really not true. To write scalable
> PL/SQL, you need to
> use DBMS_SQL. The resulting code is even more
> cumbersome than the same
> function written in Pro*C.
>
> Any language can be abused, though. We see a lot of
> Java, Visual Basic,
> and Powerbuilder applications that do stuff like...
>
> 1. Parse inside loops, using literals instead of
> bind variables.
> 2. Parse *twice* for each execute by doing
> describe+parse+execute.
> 3. Manipulate one row at a time instead of using
> array processing
> capabilities on fetches or inserts (this one,
> ironically, raises a
> system's BCHR while it kills response time).
> 4. Join result sets in the application instead of in
> the database.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12 Dallas
> - Jonathan Lewis' Optimising Oracle, Nov 19-21
> Dallas
>
>
> -----Original Message-----
> Sent: Saturday, November 16, 2002 2:38 AM
> To: Multiple recipients of list ORACLE-L
>
> Cary,
>
> Thank you.
>
> Could you elaborate on the issue of excessive
> database calls, which show
> up
> as excessive network traffic?
>
> I can picture a PL/SQL loop, which executes an SQL
> statement over and
> over
> again. This would produce many database calls, and
> it might be possible
> to
> remove the loop altogether, replacing it with a
> single SQL statement.
> This
> would reduce the database calls.
>
> Is this the "classic" type of situation that
> produces too many db calls?
> Or
> are there other situations I'm missing that are more
> likely to be the
> source
> of this problem?
>
> Thanks again.
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Friday, November 15, 2002 4:13 PM
>
>
> > Greg,
> >
> > I believe that the cultural root cause of the
> excessive LIO problem is
> > the conception that physical I/O is what makes
> databases slow. Disk
> I/O
> > certainly *can* make a system slow, but in about
> 598 of 600 cases
> we've
> > seen in the past three years, it hasn't. ["Why you
> should focus on
> LIOs
> > instead of PIOs" at www.hotsos.com/catalog]
> >
> > The fixation on PIO of course focuses people's
> attention on the
> database
> > buffer cache hit ratio (BCHR) metric for
> evaluating efficiency. The
> > problem is that the BCHR is a metric of INSTANCE
> efficiency, not SQL
> > efficiency. However, many people mistakenly apply
> it as a metric of
> SQL
> > efficiency anyway.
> >
> > Of course, if one's radar equates SQL efficiency
> with the BCHR's
> > proximity to 100%, then a lot of really bad SQL is
> going to show up on
> > your radar wrongly identified as really good SQL.
> ["Why a 99% buffer
> > cache hit ratio is not okay" at
> www.hotsos.com/catalog]
> >
> > One "classic" result is that people go on search
> and destroy missions
> > for all full-table scans. They end up producing
> more execution plans
> > that look like this than they should have:
> >
> > NESTED LOOPS
> > TABLE ACCESS BY INDEX ROWID
> > INDEX RANGE SCAN
> > TABLE ACCESS BY INDEX ROWID
> > INDEX RANGE SCAN
> >
> > This kind of plan produces great hit ratios
> because it tends to
> revisit
> > the same small set of blocks over and over again.
> This kind of plan is
> > of course appropriate in many cases. But sometimes
> it is actually less
> > work in the database to use full-table scans.
> ["When to use an index"
> at
> > www.hotsos.com/catalog.]
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic, Dec 9-11 Honolulu
> > - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12 Dallas
> > - Jonathan Lewis' Optimising Oracle, Nov 19-21
> Dallas
> >
> >
> > -----Original Message-----
> > Sent: Friday, November 15, 2002 4:39 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > A while back someone mentioned that the two main
> causes of slow SQL
> are
> > excesive LIO's and excesscive database calls,
> which show up as
> excessive
> > CPU
> > use and excessive network traffic, respectively.
> >
> > Regarding the database calls, is there a "classic"
> reason for this
> > problem?
> >
> > My best guess is it's caused by an SQL statement
> in a PL/SQL loop,
> which
> > could be rewritten as a single SQL statement. But
> is this the single,
> > commonly seen cause for this problem, or are there
> other common ways
> > this
> > inefficiency is introduced?
> >
> > Thanks in advance for help in understanding this.
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Greg Moore
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > San Diego, California -- Mailing list and
> web hosting services
> >
>
---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Cary Millsap
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > San Diego, California -- Mailing list and
> web hosting services
> >
>
---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Greg Moore
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will
sit in a boat and drink beer all day"
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
