RE: Memory strangeness on Win2k
You need to increase value for SGA_MAX_SIZE parameter. Ashish OCP DBA -Original Message- Baumgartel Sent: Friday, April 04, 2003 5:04 PM To: Multiple recipients of list ORACLE-L So I have DB (9.2.0.1) running on Win2K, with db_cache_size of 32M. Windows Task Manager shows 600+ MB of free physical memory. ALTER SYSTEM SET DB_CACHE_SIZE=50248000 SCOPE=BOTH fails with ORA-00384: Insufficient memory to grow cache. I'm going to try ...SCOPE=SPFILE, then bouncing the instance, but any other ideas would be appreciated. I really prefer running Oracle on Unix. TIA, Paul Baumgartel __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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.net -- Author: Ashish 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).
Memory strangeness on Win2k
So I have DB (9.2.0.1) running on Win2K, with db_cache_size of 32M. Windows Task Manager shows 600+ MB of free physical memory. ALTER SYSTEM SET DB_CACHE_SIZE=50248000 SCOPE=BOTH fails with ORA-00384: Insufficient memory to grow cache. I'm going to try ...SCOPE=SPFILE, then bouncing the instance, but any other ideas would be appreciated. I really prefer running Oracle on Unix. TIA, Paul Baumgartel __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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).
Re: ROWNUM strangeness?--SOLVED
> As for sorting, rownum is assigned before any sorting. However, if the data > is read via an index, the rownum appears to be assigned after the sort. Dan, I wouldn't say that. What *appears* is the sort, which doesn't take place since data is fetched in sorted order. Rownum is assigned in the order data is fetched, as you say elsewhere. I am pretty sure it's clear in your mind, but it may sound confusing to others. When Naveen said that the rows is ALWAYS assigned before sorting, it's true, even in that case since there is no sort (in the 'shuffling of things in memory' acceptance of the word). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
It is not the predicate that causes the behavior, but the distinct. I'm taking an educated guess here, so please correct me if I am wrong. As the row is read, the predicate is applied. If the row matches the predicate, a rownum is assigned and it is saved as part of the result set. Once the rownum criteria is met, all subsequent rows are discarded as not meeting the criteria or oracle stops processing. I'm not sure which, but a few tests could confirm which is true. Regardless, the result is the same. After all matching rows have been placed in the result set, the DISTINCT operation sorts the data and discards any duplicates. As such, no new rows are read to match the 'rownum' criteria. SQL> select deptno, job 2 from emp 3 where rownum < 5; DEPTNO JOB -- - 20 CLERK 30 SALESMAN 30 SALESMAN 20 MANAGER SQL> edit Wrote file afiedt.buf 1 select distinct deptno, job 2 from emp 3* where rownum < 5 SQL> / DEPTNO JOB -- - 20 CLERK 20 MANAGER 30 SALESMAN -Original Message- Sent: Thursday, August 29, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Paul, It's because of how ROWNUM is interpreted. The first query only returned the first 50 rows it found and you got 24 that passed the rest of your criteria. The second query returned 1000 rows of which only 336 passed the rest of your criteria. Makes perfect sense to me. This is why ROWNUM can cause confusing results. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
Yes sir. Paul Baumgartel wrote: >I believe you have ROWNUM confused with ROWID. > >--- ltiu <[EMAIL PROTECTED]> wrote: > > >>Holes in your extents/segments. Normal. You get this after repeated >>updates/deletes/inserts. >> >>ltiu >> >>Paul Baumgartel wrote: >> >> >> >>>I've been given a query to investigate. The performance varies >>>extremely depending on whether the entire result set is fetched or a >>>ROWNUM < n clause is attached; that makes sense to me. What doesn't >>>make sense is the following: if we add WHERE ROWNUM < 50, the query >>>returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns >>> >>> >>336 >> >> >>>rows! >>> >>>I don't know the size of the entire result set (I'm running a count >>>right now), but if the result set consists of at least 336 rows, >>>shouldn't WHERE ROWNUM < 50 return 49 rows? >>> >>>TIA! >>> >>> >>> >>>= >>>Paul Baumgartel, Adept Computer Associates, Inc. >>>[EMAIL PROTECTED] >>> >>> >>> >>> >>> >>>__ >>>Do You Yahoo!? >>>Yahoo! Finance - Get real-time stock quotes >>>http://finance.yahoo.com >>> >>> >>> >>> >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>-- >>Author: ltiu >> INET: [EMAIL PROTECTED] >> >>Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >>San Diego, California-- Public Internet access / Mailing >>Lists >> >>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). >> >> > > >__ >Do You Yahoo!? >Yahoo! Finance - Get real-time stock quotes >http://finance.yahoo.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
I agree. I got confused and my second email to disregard my erroneous first emai on this matterl was apparently shot down by the mail filters so it never got to you guys in time. Sorry for the confision. ltiu Fink, Dan wrote: >Actually, ROWNUM has nothing to do with >extents/segments/inserts/updates/deletes. It is all about access paths. The >solution posted makes sense as ROWNUM can be affected by many subsequent >operations. > >-Original Message- >Sent: Thursday, August 29, 2002 11:04 AM >To: Multiple recipients of list ORACLE-L > > >Holes in your extents/segments. Normal. You get this after repeated >updates/deletes/inserts. > >ltiu > >Paul Baumgartel wrote: > > > >>I've been given a query to investigate. The performance varies >>extremely depending on whether the entire result set is fetched or a >>ROWNUM < n clause is attached; that makes sense to me. What doesn't >>make sense is the following: if we add WHERE ROWNUM < 50, the query >>returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 >>rows! >> >>I don't know the size of the entire result set (I'm running a count >>right now), but if the result set consists of at least 336 rows, >>shouldn't WHERE ROWNUM < 50 return 49 rows? >> >>TIA! >> >> >> >>= >>Paul Baumgartel, Adept Computer Associates, Inc. >>[EMAIL PROTECTED] >> >> >> >> >> >>__ >>Do You Yahoo!? >>Yahoo! Finance - Get real-time stock quotes >>http://finance.yahoo.com >> >> >> >> > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?--SOLVED
To be exact... Rownum is sequentially assigned for each row that meets the predicate. If the row does not match the predicate condition, it is discarded and not assigned a rownum. Below is an example using our friendly EMP table. As for sorting, rownum is assigned before any sorting. However, if the data is read via an index, the rownum appears to be assigned after the sort. SQL> l 1 select rownum, empno, ename, deptno 2* from emp SQL> / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7369 SMITH 20 2 7499 ALLEN 30 3 7521 WARD 30 4 7566 JONES 20 5 7654 MARTIN 30 6 7698 BLAKE 30 7 7782 CLARK 10 8 7788 SCOTT 20 9 7839 KING 10 10 7844 TURNER 30 11 7876 ADAMS 20 12 7900 JAMES 30 13 7902 FORD 20 14 7934 MILLER 10 15 null1 16 9998 null2 16 rows selected. SQL> 2 2* from emp SQL> i 3 where rownum < 5 4 SQL> l 1 select rownum, empno, ename, deptno 2 from emp 3* where rownum < 5 SQL> / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7369 SMITH 20 2 7499 ALLEN 30 3 7521 WARD 30 4 7566 JONES 20 1 select rownum, empno, ename, deptno 2 from emp 3 where rownum < 5 4* and deptno = 30 SQL> / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7499 ALLEN 30 2 7521 WARD 30 3 7654 MARTIN 30 4 7698 BLAKE 30 1 select rownum, empno, ename, deptno 2 from emp 3 where rownum < 5 4and deptno = 30 5* order by ename SQL> / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7499 ALLEN 30 4 7698 BLAKE 30 3 7654 MARTIN 30 2 7521 WARD 30 -Original Message- Sent: Thursday, August 29, 2002 11:44 AM To: Multiple recipients of list ORACLE-L >As we all know, ROWNUM is *usually* assigned when rows are >read into the cache, before sorting; Always -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
Actually, ROWNUM has nothing to do with extents/segments/inserts/updates/deletes. It is all about access paths. The solution posted makes sense as ROWNUM can be affected by many subsequent operations. -Original Message- Sent: Thursday, August 29, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: >I've been given a query to investigate. The performance varies >extremely depending on whether the entire result set is fetched or a >ROWNUM < n clause is attached; that makes sense to me. What doesn't >make sense is the following: if we add WHERE ROWNUM < 50, the query >returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 >rows! > >I don't know the size of the entire result set (I'm running a count >right now), but if the result set consists of at least 336 rows, >shouldn't WHERE ROWNUM < 50 return 49 rows? > >TIA! > > > >= >Paul Baumgartel, Adept Computer Associates, Inc. >[EMAIL PROTECTED] > > > > > >__ >Do You Yahoo!? >Yahoo! Finance - Get real-time stock quotes >http://finance.yahoo.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
I believe you have ROWNUM confused with ROWID. --- ltiu <[EMAIL PROTECTED]> wrote: > Holes in your extents/segments. Normal. You get this after repeated > updates/deletes/inserts. > > ltiu > > Paul Baumgartel wrote: > > >I've been given a query to investigate. The performance varies > >extremely depending on whether the entire result set is fetched or a > >ROWNUM < n clause is attached; that makes sense to me. What doesn't > >make sense is the following: if we add WHERE ROWNUM < 50, the query > >returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns > 336 > >rows! > > > >I don't know the size of the entire result set (I'm running a count > >right now), but if the result set consists of at least 336 rows, > >shouldn't WHERE ROWNUM < 50 return 49 rows? > > > >TIA! > > > > > > > >= > >Paul Baumgartel, Adept Computer Associates, Inc. > >[EMAIL PROTECTED] > > > > > > > > > > > >__ > >Do You Yahoo!? > >Yahoo! Finance - Get real-time stock quotes > >http://finance.yahoo.com > > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ltiu > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
Paul, It's because of how ROWNUM is interpreted. The first query only returned the first 50 rows it found and you got 24 that passed the rest of your criteria. The second query returned 1000 rows of which only 336 passed the rest of your criteria. Makes perfect sense to me. This is why ROWNUM can cause confusing results. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
Title: RE: ROWNUM strangeness? show the full sql please, perhaps its how you are grouping your conditions Paul Baumgartel wrote: >I've been given a query to investigate. The performance varies >extremely depending on whether the entire result set is fetched or a >ROWNUM < n clause is attached; that makes sense to me. What doesn't >make sense is the following: if we add WHERE ROWNUM < 50, the query >returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 >rows! > >I don't know the size of the entire result set (I'm running a count >right now), but if the result set consists of at least 336 rows, >shouldn't WHERE ROWNUM < 50 return 49 rows? > >TIA! > > > >= >Paul Baumgartel, Adept Computer Associates, Inc. >[EMAIL PROTECTED] > > > > > >__ >Do You Yahoo!? >Yahoo! Finance - Get real-time stock quotes >http://finance.yahoo.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
Paul Baumgartel wrote: > > I've been given a query to investigate. The performance varies > extremely depending on whether the entire result set is fetched or a > ROWNUM < n clause is attached; that makes sense to me. What doesn't > make sense is the following: if we add WHERE ROWNUM < 50, the query > returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 > rows! > > I don't know the size of the entire result set (I'm running a count > right now), but if the result set consists of at least 336 rows, > shouldn't WHERE ROWNUM < 50 return 49 rows? > > TIA! > > = > Paul Baumgartel, Adept Computer Associates, Inc. > [EMAIL PROTECTED] > Wow! a nice case ! Could you please post the query ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?--SOLVED
>As we all know, ROWNUM is *usually* assigned when rows are >read into the cache, before sorting; Always -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
Actually. Scratch my previous email on this matter. We have seen this problem before and I couldn't figure out how to fix it. ltiu Paul Baumgartel wrote: >I've been given a query to investigate. The performance varies >extremely depending on whether the entire result set is fetched or a >ROWNUM < n clause is attached; that makes sense to me. What doesn't >make sense is the following: if we add WHERE ROWNUM < 50, the query >returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 >rows! > >I don't know the size of the entire result set (I'm running a count >right now), but if the result set consists of at least 336 rows, >shouldn't WHERE ROWNUM < 50 return 49 rows? > >TIA! > > > >= >Paul Baumgartel, Adept Computer Associates, Inc. >[EMAIL PROTECTED] > > > > > >__ >Do You Yahoo!? >Yahoo! Finance - Get real-time stock quotes >http://finance.yahoo.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
Hi Paul, How's going? Maybe there are other criteria's that limits the result set to only return 24 rows? Richard Ji -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: ROWNUM strangeness?
Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: >I've been given a query to investigate. The performance varies >extremely depending on whether the entire result set is fetched or a >ROWNUM < n clause is attached; that makes sense to me. What doesn't >make sense is the following: if we add WHERE ROWNUM < 50, the query >returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 >rows! > >I don't know the size of the entire result set (I'm running a count >right now), but if the result set consists of at least 336 rows, >shouldn't WHERE ROWNUM < 50 return 49 rows? > >TIA! > > > >= >Paul Baumgartel, Adept Computer Associates, Inc. >[EMAIL PROTECTED] > > > > > >__ >Do You Yahoo!? >Yahoo! Finance - Get real-time stock quotes >http://finance.yahoo.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ROWNUM strangeness?--SOLVED
In my haste, I hadn't noticed that, although the query did not contain an ORDER BY, it did contain SELECT DISTINCT, which causes an implicit ORDER BY. As we all know, ROWNUM is usually assigned when rows are read into the cache, before sorting; thus the order of ROWNUMs in the output is not sequential, and ROWNUM 999 could be the 336th row in sorted order. __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: ROWNUM strangeness?
Post your query -Original Message- Sent: Thursday, August 29, 2002 9:23 PM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ROWNUM strangeness?
I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: sys.dbms_system.ksdwrt Strangeness
There is a level parameter in ksdind I believe, so just before you call ksdwrt, call ksdind(0). the lvl controls the indentation (sp?). Anjo. "Post, Ethan" wrote: > Calls to sys.dbms_system.ksdwrt to add entries to the alert log mysteriously > insert a space infront of the string and sometimes doesn't > > SERVERERROR[955] SESSION 17,355 USER SYSTEM,oracle PROGRAM sqlplus@foo (TNS > V1-V3),foo > SERVERERROR[955] SESSION 13,5385 USER SYSTEM,oracle PROGRAM sqlplus@foo (TNS > V1-V3),foo > SERVERERROR[942] SESSION 13,7282 USER SYSADM,jdoe PROGRAM ,JOHN\DOE > > Anyone know why the last line has a space inserted into it? I have tried > duplicating it but have had no luck. > > Ethan Post > perotdba (AIM), epost1 (Yahoo) > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
sys.dbms_system.ksdwrt Strangeness
Calls to sys.dbms_system.ksdwrt to add entries to the alert log mysteriously insert a space infront of the string and sometimes doesn't SERVERERROR[955] SESSION 17,355 USER SYSTEM,oracle PROGRAM sqlplus@foo (TNS V1-V3),foo SERVERERROR[955] SESSION 13,5385 USER SYSTEM,oracle PROGRAM sqlplus@foo (TNS V1-V3),foo SERVERERROR[942] SESSION 13,7282 USER SYSADM,jdoe PROGRAM ,JOHN\DOE Anyone know why the last line has a space inserted into it? I have tried duplicating it but have had no luck. Ethan Post perotdba (AIM), epost1 (Yahoo) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Strangeness
Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From: Robertson Lee - lerobe > > Sent: Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From: Lawlor Michael - mlawlo > > Sent: 05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Friday, March 01, 2002 12:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Yesterday it was executing the same code but without the > > > UPDATE statement. I'd agree that the performance would be > > > affected by having to do the UPDATEs but from the figures you > > > observed it was executing the UPDATE statement over 1000 > > > times per second. It is performing a commit every 10K records > > > although I don't know how long this is taking, but from what > > > I can see in Toad the rate of increase in the number of times > > > the UPDATE is executing indicates that the COMMIT is probably > > > not the problem. > > > > > > -Original Message- > > > From: Robertson Lee - lerobe > > > Sent: Friday, March 01, 2002 11:57 AM > > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Erm...couldn't see the wood for the trees time. Why are > you > > > doing single updates per record. Were you doing this in > > > batches yesterday ? > > > > > > If this is the case and unless I am mistaken, then a > severe > > > degradation in performance is the sort of thing I would > expect > > > > > > > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: 01 March 2002 10:00 > > > To: Khiroya Asit - akhiro >
Re: Strangeness
If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible that a minor bug in the Pro*C could mean that each update was updating every single row in the table on every update (don't laugh, I've seen it before), whereas the PL/SQL, being easier to read and write, is coded correctly. Is it possible that the Pro*C uses an 'in-house library' for its updates that generates code to update every column in the table ? Whereas the PL/SQL is hand-coded to update only the changed columns Is the code doing single row commits inside a loop ? I wouldn't expect this to make a factor of 100 difference (correct my arithmetic if it's wrong), but PL/SQL cheats on commits in loops, and the saving can be significant. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 11:21 |Oracle 8.0.5.0.0 |Tru64 4.0f | |We have a process running here and without going into the detail of it we |have a Pro C program that is taking ages to run updates and selects (2 |hours to do 1 records). The program was changed to PL/SQL and we |suddenly were seeing 5 million records processed in 1 hour. | |Is PL/SQL that much faster than Pro C. Can somone more in the know give me |some hints ?? | |TIA | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). <>
Re: Re[2]: Strangeness
Jared, yes it reminds me of burning peat. But since I am not the one drinking it, I don't care what it tastes like. Me, I'm debating the merits of a Brooklyn Pennant '55 Pale ale or a nice large glass of Glenmorangie.. hm maybe tonight is a Macallan's night? Been that sort of week. Rachel --- [EMAIL PROTECTED] wrote: > > Hmmph. Topics like this on a Friday make me want to dig deeper > into > > my toolbox (the malted compartment of course). > > I'm having similar feelings. Time to break out the Lagavulin > tonight. > > And Rachel, yes, I know it reminds you of burning peat. > > Maybe I *like* burning peat. :) > > Re the rewrite of the OOP App I mentioned:A developer that was > intimately > familiar with the inner workings of the app ( he inherited it ) and > myself > offered > to do the rewrite in 2 months. They spent $1M+ on the app, and > didn't > appreciate > a couple of Oracle hacks telling them it could be rewritten for $25k, > and > several > orders of magnitude faster. > > Jared > > > > > > > Robert Eskridge <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 03/08/02 02:43 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:Re[2]: Strangeness > > > Jared, > > Yeah, they don't want to hear about PL/SQL because then they can't > scale it up on the middle tier where they can have dozens of machines > with the same poorly written app simultaneously pounding the database > thousands of times more intensely than the task requires > > Hmmph. Topics like this on a Friday make me want to dig deeper into > my toolbox (the malted compartment of course). > > -rje > > J> Lee, > > J> I've had similar experiences. > > J> The problem is not PRO*C, but how the program is designed. > > J> Is it by any chance written in C++? I once had the 'privilege' of > J> administering an the databases for an application written in C++. > J> The software featured and award winning design, literaly. The OOP > J> design was honored in some OOP magazine. > > J> When you consider though that this wonderful OOP design treated > every > J> piece of data from the database as atomic, and retrieved them that > way, > J> you can begin to see the problem. > > J> The average SQL*Net packet size was 200 bytes, sub optimal to say > the > J> least. This is because the app preferred to retrieve it's own > information > > J> from the database and do the joins in the software. > > J> In a couple of hours this app could process all of 10k > transactions, > and > J> generate several million TCP/IP packets in the process. > > J> I suggested they move the app to the database server: this > resulted in > J> a 40% decrease in runtime. > > J> We offered to rewrite the whole thing in PL/SQL, but that was a > J> politically incorrect suggestion. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robert Eskridge > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ:
Recall: Strangeness
Robertson Lee - lerobe would like to recall the message, "Strangeness". The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). <>
RE: RE: Strangeness with PL/SQL and ProC
Thanks to all for the replys on this one. It looks like we are going to get the chaps to seriously think about using PL/SQL for the future. Regards Lee -Original Message- Sent: 08 March 2002 16:38 To: Multiple recipients of list ORACLE-L Lee, Over the years I've developed a very strong appreciation for TCP/IP as a fast, error free communication protocol. At the same time I've also developer a VERY strong appreciation for how slow SQL*Net is. Anything you can do to minimize that part of an application helps 10 fold. BTW, getting onto the object bandwagon a bit. I've developed, over the last three years, a strong appreciation for doing a lot of database access stuff in PL/SQL and hopefully JAVA in the future. Having a package with a defined interface to the external programs modularizes things so nicely it's breathtaking. I've now a wonderful relation ship with duhvelopers as we do that interface definition. They go off and develop their front ends as they want knowing what the interface at the database looks like. I can then go off and create a very nice, efficient, and normalized (with referential integrity) database and code the package body as needed, including modifications and bug fixes without causing them a pile of grief. In the end we get the job done faster, neater, and with less hassle. Guess I'm going to have to start calling them developers pretty soon. :-) Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 3:23 PM Thanks for that Dick, the communication issue was one we had considered, you are confirming this from what you say below. Thanks again for the response. Lee -Original Message- Sent: 08 March 2002 13:53 To: Robertson Lee - lerobe; Multiple recipients of list ORACLE-L Lee, Stop a minute and take a look at what your doing. I assume that when the process was pure PRO*C there must have been a pile of communication between the database and the program. This communication, even if done by IPC takes time. Now when you re-code it in PL/SQL there is no reason for process to database communication, hence it takes less time. Allow me to provide an illustration: We had a OCI program that would load tester data from NT shares every morning into our Unix based database. Normally this process took around 8 to 10 hours to run. When MicroSoft OS/2 died several years ago yours truly ported the program from OS/2 to NT in the process re-coding it in PRO*C. Now since paramaterization of SQL was not the original authors forte, the program started running a little faster, but still 6 to 8 hours. Now comes Y2K, a new server & database evrsion & some normalization takes place resulting in a new database design. When I reviewed the program I note that there were a number of back and forth communication requirements that were in the original. It took something like 5 round trips to the database for queries to decide if we were going to insert a new record or update an existing one. Well, I took all of that code out of PRO*C, re-coded it as a PL/SQL package and today that same program runs in a little over 1 hour with one round trip from the client to the database. Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 2:03 AM > Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: Re[2]: Strangeness
Jared, yes it reminds me of burning peat. But since I am not the one drinking it, I don't care what it tastes like. Me, I'm debating the merits of a Brooklyn Pennant '55 Pale ale or a nice large glass of Glenmorangie.. hm maybe tonight is a Macallan's night? Been that sort of week. Rachel --- [EMAIL PROTECTED] wrote: > > Hmmph. Topics like this on a Friday make me want to dig deeper > into > > my toolbox (the malted compartment of course). > > I'm having similar feelings. Time to break out the Lagavulin > tonight. > > And Rachel, yes, I know it reminds you of burning peat. > > Maybe I *like* burning peat. :) > > Re the rewrite of the OOP App I mentioned:A developer that was > intimately > familiar with the inner workings of the app ( he inherited it ) and > myself > offered > to do the rewrite in 2 months. They spent $1M+ on the app, and > didn't > appreciate > a couple of Oracle hacks telling them it could be rewritten for $25k, > and > several > orders of magnitude faster. > > Jared > > > > > > > Robert Eskridge <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 03/08/02 02:43 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:Re[2]: Strangeness > > > Jared, > > Yeah, they don't want to hear about PL/SQL because then they can't > scale it up on the middle tier where they can have dozens of machines > with the same poorly written app simultaneously pounding the database > thousands of times more intensely than the task requires > > Hmmph. Topics like this on a Friday make me want to dig deeper into > my toolbox (the malted compartment of course). > > -rje > > J> Lee, > > J> I've had similar experiences. > > J> The problem is not PRO*C, but how the program is designed. > > J> Is it by any chance written in C++? I once had the 'privilege' of > J> administering an the databases for an application written in C++. > J> The software featured and award winning design, literaly. The OOP > J> design was honored in some OOP magazine. > > J> When you consider though that this wonderful OOP design treated > every > J> piece of data from the database as atomic, and retrieved them that > way, > J> you can begin to see the problem. > > J> The average SQL*Net packet size was 200 bytes, sub optimal to say > the > J> least. This is because the app preferred to retrieve it's own > information > > J> from the database and do the joins in the software. > > J> In a couple of hours this app could process all of 10k > transactions, > and > J> generate several million TCP/IP packets in the process. > > J> I suggested they move the app to the database server: this > resulted in > J> a 40% decrease in runtime. > > J> We offered to rewrite the whole thing in PL/SQL, but that was a > J> politically incorrect suggestion. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robert Eskridge > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ:
Re: Re[2]: Strangeness
> Hmmph. Topics like this on a Friday make me want to dig deeper into
> my toolbox (the malted compartment of course).
I'm having similar feelings. Time to break out the Lagavulin tonight.
And Rachel, yes, I know it reminds you of burning peat.
Maybe I *like* burning peat. :)
Re the rewrite of the OOP App I mentioned:A developer that was
intimately
familiar with the inner workings of the app ( he inherited it ) and myself
offered
to do the rewrite in 2 months. They spent $1M+ on the app, and didn't
appreciate
a couple of Oracle hacks telling them it could be rewritten for $25k, and
several
orders of magnitude faster.
Jared
Robert Eskridge <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
03/08/02 02:43 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re[2]: Strangeness
Jared,
Yeah, they don't want to hear about PL/SQL because then they can't
scale it up on the middle tier where they can have dozens of machines
with the same poorly written app simultaneously pounding the database
thousands of times more intensely than the task requires
Hmmph. Topics like this on a Friday make me want to dig deeper into
my toolbox (the malted compartment of course).
-rje
J> Lee,
J> I've had similar experiences.
J> The problem is not PRO*C, but how the program is designed.
J> Is it by any chance written in C++? I once had the 'privilege' of
J> administering an the databases for an application written in C++.
J> The software featured and award winning design, literaly. The OOP
J> design was honored in some OOP magazine.
J> When you consider though that this wonderful OOP design treated every
J> piece of data from the database as atomic, and retrieved them that way,
J> you can begin to see the problem.
J> The average SQL*Net packet size was 200 bytes, sub optimal to say the
J> least. This is because the app preferred to retrieve it's own
information
J> from the database and do the joins in the software.
J> In a couple of hours this app could process all of 10k transactions,
and
J> generate several million TCP/IP packets in the process.
J> I suggested they move the app to the database server: this resulted in
J> a 40% decrease in runtime.
J> We offered to rewrite the whole thing in PL/SQL, but that was a
J> politically incorrect suggestion.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Eskridge
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
Re[2]: Strangeness
Jared, Yeah, they don't want to hear about PL/SQL because then they can't scale it up on the middle tier where they can have dozens of machines with the same poorly written app simultaneously pounding the database thousands of times more intensely than the task requires Hmmph. Topics like this on a Friday make me want to dig deeper into my toolbox (the malted compartment of course). -rje J> Lee, J> I've had similar experiences. J> The problem is not PRO*C, but how the program is designed. J> Is it by any chance written in C++? I once had the 'privilege' of J> administering an the databases for an application written in C++. J> The software featured and award winning design, literaly. The OOP J> design was honored in some OOP magazine. J> When you consider though that this wonderful OOP design treated every J> piece of data from the database as atomic, and retrieved them that way, J> you can begin to see the problem. J> The average SQL*Net packet size was 200 bytes, sub optimal to say the J> least. This is because the app preferred to retrieve it's own information J> from the database and do the joins in the software. J> In a couple of hours this app could process all of 10k transactions, and J> generate several million TCP/IP packets in the process. J> I suggested they move the app to the database server: this resulted in J> a 40% decrease in runtime. J> We offered to rewrite the whole thing in PL/SQL, but that was a J> politically incorrect suggestion. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Strangeness
Lee,
I've had similar experiences.
The problem is not PRO*C, but how the program is designed.
Is it by any chance written in C++? I once had the 'privilege' of
administering
an the databases for an application written in C++. The software featured
and award winning design, literaly. The OOP design was honored in some
OOP magazine.
When you consider though that this wonderful OOP design treated every
piece of data from the database as atomic, and retrieved them that way,
you can begin to see the problem.
The average SQL*Net packet size was 200 bytes, sub optimal to say the
least. This is because the app preferred to retrieve it's own information
from the database and do the joins in the software.
In a couple of hours this app could process all of 10k transactions, and
generate several million TCP/IP packets in the process.
I suggested they move the app to the database server: this resulted in
a 40% decrease in runtime.
We offered to rewrite the whole thing in PL/SQL, but that was a
politically
incorrect suggestion.
Jared
Robertson Lee - lerobe <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
03/08/02 01:28 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Strangeness
Oracle 8.0.5.0.0
Tru64 4.0f
We have a process running here and without going into the detail of it we
have a Pro C program that is taking ages to run updates and selects (2
hours to do 1 records). The program was changed to PL/SQL and we
suddenly were seeing 5 million records processed in 1 hour.
Is PL/SQL that much faster than Pro C. Can somone more in the know give
me
some hints ??
TIA
Lee
> -Original Message-
> From: Lawlor Michael - mlawlo
> Sent: 05 March 2002 17:17
> To:Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya
Asit - akhiro
> Cc:Richardson Phil - pricha; Mathew Varghese - vmathe;
Peters Roy -
> ropete
> Subject: RE: Roy's extract job
>
>
> Dunno, was hoping it might spark an idea.
>
> From the dark recesses of my mind, do I recall that if you have
TWO_TASK,
> you always connect through the listener, even from the same box. Is it
> possible that it could have a bottleneck of some sort? Maybe that's
> impossible - I don't know
>
> Mick
>
> > -Original Message-
> > From:Robertson Lee - lerobe
> > Sent:Tuesday, March 05, 2002 5:17 PM
> > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya
> > Asit - akhiro
> > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > Peters Roy - ropete
> > Subject: RE: Roy's extract job
> >
> > erm how ??
> >
> > -Original Message-
> > From:Lawlor Michael - mlawlo
> > Sent:05 March 2002 17:11
> > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya
> > Asit - akhiro
> > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > Peters Roy - ropete
> > Subject: RE: Roy's extract job
> >
> >
> > Something to do with the Oracle listener?
> >
> > M
> >
> > > -Original Message-
> > > From: Fremaux Ian - ifrema
> > > Sent: Tuesday, March 05, 2002 5:04 PM
> > > To:Robertson Lee - lerobe; Khiroya Asit - akhiro
> > > Cc:Richardson Phil - pricha; Mathew Varghese -
vmathe;
> > > Peters Roy - ropete; Lawlor Michael - mlawlo
> > > Subject: RE: Roy's extract job
> > >
> > > Chaps,
> > >
> > > We have some information that may be of interest. We had this
> > > problem (discussed below) last week where a Pro-C program was
> > > running very slowly but when Lee monitored the database
> > > response time (SELECTs & UPDATEs) it was performing very
> > > quickly. We converted the code to PL-SQL and ran it yesterday
> > > and it flew along and processed 5 million records in 1 hour.
> > > This morning we tried the Pro-C version of the program again
> > > and after two hours it had processed less than 10K records.
> > > So we executed the PL-SQL again this afternoon and it has
> > > almost completed the 5 million rows in about 2 hours again.
> > >
> > > Any thoughts on the implications of this?
> > >
> > > Regards, Ian.
> > >
>> > -Original Message-
>> > From: Fremaux Ian - ifrema
>
Re:RE: Strangeness with PL/SQL and ProC
Lee, Over the years I've developed a very strong appreciation for TCP/IP as a fast, error free communication protocol. At the same time I've also developer a VERY strong appreciation for how slow SQL*Net is. Anything you can do to minimize that part of an application helps 10 fold. BTW, getting onto the object bandwagon a bit. I've developed, over the last three years, a strong appreciation for doing a lot of database access stuff in PL/SQL and hopefully JAVA in the future. Having a package with a defined interface to the external programs modularizes things so nicely it's breathtaking. I've now a wonderful relation ship with duhvelopers as we do that interface definition. They go off and develop their front ends as they want knowing what the interface at the database looks like. I can then go off and create a very nice, efficient, and normalized (with referential integrity) database and code the package body as needed, including modifications and bug fixes without causing them a pile of grief. In the end we get the job done faster, neater, and with less hassle. Guess I'm going to have to start calling them developers pretty soon. :-) Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 3:23 PM Thanks for that Dick, the communication issue was one we had considered, you are confirming this from what you say below. Thanks again for the response. Lee -Original Message- Sent: 08 March 2002 13:53 To: Robertson Lee - lerobe; Multiple recipients of list ORACLE-L Lee, Stop a minute and take a look at what your doing. I assume that when the process was pure PRO*C there must have been a pile of communication between the database and the program. This communication, even if done by IPC takes time. Now when you re-code it in PL/SQL there is no reason for process to database communication, hence it takes less time. Allow me to provide an illustration: We had a OCI program that would load tester data from NT shares every morning into our Unix based database. Normally this process took around 8 to 10 hours to run. When MicroSoft OS/2 died several years ago yours truly ported the program from OS/2 to NT in the process re-coding it in PRO*C. Now since paramaterization of SQL was not the original authors forte, the program started running a little faster, but still 6 to 8 hours. Now comes Y2K, a new server & database evrsion & some normalization takes place resulting in a new database design. When I reviewed the program I note that there were a number of back and forth communication requirements that were in the original. It took something like 5 round trips to the database for queries to decide if we were going to insert a new record or update an existing one. Well, I took all of that code out of PRO*C, re-coded it as a PL/SQL package and today that same program runs in a little over 1 hour with one round trip from the client to the database. Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 2:03 AM > Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 FAX: (858) 538-5051 San Diego, California-- Public In
RE: Strangeness
sql_trace and tkprof should tell you where the time was spent and what the code is doing! Waleed -Original Message- Sent: Friday, March 08, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From: Robertson Lee - lerobe > > Sent: Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From: Lawlor Michael - mlawlo > > Sent: 05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Friday, March 01, 2002 12:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Yesterday it was executing the same code but without the > > > UPDATE statement. I'd agree that the performance would be > > > affected by having to do the UPDATEs but from the figures you > > > observed it was executing the UPDATE statement over 1000 > > > times per second. It is performing a commit every 10K records > > > although I don't know how long this is taking, but from what > > > I can see in Toad the rate of increase in the number of times > > > the UPDATE is executing indicates that the COMMIT is probably > > > not the problem. > > > > > > -Original Message- > > > From: Robertson Lee - lerobe > > > Sent: Friday, March 01, 2002 11:57 AM > > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Erm...couldn't see the wood for the trees time. Why are > you > > > doing single updates per record. Were you doing this in > > > batches yesterday ? > > > > > > If this is the case and unless I am mistaken, then a > severe > > > degradation in performance is the sort of thing I would > expect > > > > > > > > > > > > -Orig
RE: Strangeness
Thanks for the input Jonathan, I will pass the relevant parts on to the development team responsible. Regards Lee -Original Message- Sent: 08 March 2002 11:33 To: Multiple recipients of list ORACLE-L If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible that a minor bug in the Pro*C could mean that each update was updating every single row in the table on every update (don't laugh, I've seen it before), whereas the PL/SQL, being easier to read and write, is coded correctly. Is it possible that the Pro*C uses an 'in-house library' for its updates that generates code to update every column in the table ? Whereas the PL/SQL is hand-coded to update only the changed columns Is the code doing single row commits inside a loop ? I wouldn't expect this to make a factor of 100 difference (correct my arithmetic if it's wrong), but PL/SQL cheats on commits in loops, and the saving can be significant. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 11:21 |Oracle 8.0.5.0.0 |Tru64 4.0f | |We have a process running here and without going into the detail of it we |have a Pro C program that is taking ages to run updates and selects (2 |hours to do 1 records). The program was changed to PL/SQL and we |suddenly were seeing 5 million records processed in 1 hour. | |Is PL/SQL that much faster than Pro C. Can somone more in the know give me |some hints ?? | |TIA | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Strangeness
If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible that a minor bug in the Pro*C could mean that each update was updating every single row in the table on every update (don't laugh, I've seen it before), whereas the PL/SQL, being easier to read and write, is coded correctly. Is it possible that the Pro*C uses an 'in-house library' for its updates that generates code to update every column in the table ? Whereas the PL/SQL is hand-coded to update only the changed columns Is the code doing single row commits inside a loop ? I wouldn't expect this to make a factor of 100 difference (correct my arithmetic if it's wrong), but PL/SQL cheats on commits in loops, and the saving can be significant. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 11:21 |Oracle 8.0.5.0.0 |Tru64 4.0f | |We have a process running here and without going into the detail of it we |have a Pro C program that is taking ages to run updates and selects (2 |hours to do 1 records). The program was changed to PL/SQL and we |suddenly were seeing 5 million records processed in 1 hour. | |Is PL/SQL that much faster than Pro C. Can somone more in the know give me |some hints ?? | |TIA | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Recall: Strangeness
Robertson Lee - lerobe would like to recall the message, "Strangeness". The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Strangeness with PL/SQL and ProC
> Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Recall: Strangeness
Robertson Lee - lerobe would like to recall the message, "Strangeness". The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Strangeness
Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From: Robertson Lee - lerobe > > Sent: Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From: Lawlor Michael - mlawlo > > Sent: 05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Friday, March 01, 2002 12:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Yesterday it was executing the same code but without the > > > UPDATE statement. I'd agree that the performance would be > > > affected by having to do the UPDATEs but from the figures you > > > observed it was executing the UPDATE statement over 1000 > > > times per second. It is performing a commit every 10K records > > > although I don't know how long this is taking, but from what > > > I can see in Toad the rate of increase in the number of times > > > the UPDATE is executing indicates that the COMMIT is probably > > > not the problem. > > > > > > -Original Message- > > > From: Robertson Lee - lerobe > > > Sent: Friday, March 01, 2002 11:57 AM > > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Erm...couldn't see the wood for the trees time. Why are > you > > > doing single updates per record. Were you doing this in > > > batches yesterday ? > > > > > > If this is the case and unless I am mistaken, then a > severe > > > degradation in performance is the sort of thing I would > expect > > > > > > > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: 01 March 2002 10:00 > > > To: Khiroya Asit - akhiro >
