Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Too many db calls: Follow up question

RE: RE: Too many db calls: Follow up question

From: Jonas Rosenthal <oracleminded_at_yahoo.com>
Date: Mon, 18 Nov 2002 14:34:22 -0800
Message-ID: <F001.0050606B.20021118143422@fatcity.com> 





Hi folks,



I have one follow-up question on this.



Suppose you are doing commit work with pl/sql with large amounds of data and
you are limited in rollback size.  The goal being that you wish to fetch
Large amounts of data and use to commit data inserts to an unrelated table.
Of course, you don't want to reparse and increase the db calls.



Fetch data from table a.


Commit fetched data to table b.



Note - Assume nobody is on the system you aren't changing any data from
table a.



My question is which of the following scenarious is the preferred/correct?



    
  
  1.   Open and close the cursor for a data range and commit the data.  Re-open
for the next data range and repeat.  This goes agains implied excess call
principals.  However, my understanding from support many moons ago and and
personal experience is that the cursor is invalidated on the commit causing
the old ora-1555 snapshot too old.  Support indicated the best way to handle
this was opening and closing the data set fetches in ranges, as above, with
data change commits between each data set to avoid the ora-1555.






2)On the other hand, if you keep the cursor open and commit all data on
completion, that's a lot of rollback you may need.



Any thoughts would be appreciated.




Thanks,




Jonas Rosenthal


Oxford Health Plans



-----Original Message-----


Millsap


Sent: Monday, November 18, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L




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






-----Original Message-----


dgoulet_at_vicr.com


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" <cary.millsap_at_hotsos.com>
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






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







> 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: sqlgreg_at_pacbell.net
>
> 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
>
> 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: ListGuru_at_fatcity.com (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: sqlgreg_at_pacbell.net

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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com

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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com

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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com

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: ListGuru_at_fatcity.com (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: Jonas Rosenthal
  INET: oracleminded_at_yahoo.com

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: ListGuru_at_fatcity.com (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).


Received on Mon Nov 18 2002 - 16:34:22 CST












Original text of this message













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