With the advent of bulk bind and bulk collection facilities in PL/SQL, you can get very close to the "correct" SQL mechanisms...its just that not many people tend to do it, and you end up with a gazillion 'one-row-at-a-time' applications out there.
Cheers Connor --- Cary Millsap <[EMAIL PROTECTED]> wrote: > 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). ===== 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).
