RE: Oracle slower ?
Hi again Your example is a good one of what I meant by treating OMWB as a test bed. The code is merely there to emulate the sqlserver/sybase trancount variable, this is almost certainly not needed in your procedure and you could *probably* replace the entire statement with IF ( TEST_4.TranFlag = 0) THEN commit; END IF; Niall Litchfield Oracle DBA Audit Commission 0117 975 7805 -Original Message- Sent: 25 February 2003 17:30 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Both Oracle (817) and Sybase running on the same test machine (Sun Solaris 6) but in different mount point. Both not at optimum datafiles and I/O configuration. If I run every SQL in the procedure manually from SQLplus it run at minimum cost (explain plan), indexes used. There are loops in the procedure. I guess it takes more time because more logic (if then else) inside the loops. For example, the following code generated by OMWB is inside the loops. Any other idea ? IF ( TEST_4.TranFlag = 0) THEN BEGIN /* Emulating @@TRANCOUNT functionality in Oracle model */ IF omwb_emulation.globalPkg.trancount = 1 THEN COMMIT WORK; END IF; IF omwb_emulation.globalPkg.trancount 0 THEN omwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount-1; END IF; -Original Message- Sent: Tuesday, February 25, 2003 11:20 AM To: Multiple recipients of list ORACLE-L We are converting from SQL Server and Sybase to Oracle. To convert functions and stored procedures we have been using the Oracle Migration Workbench (OMWB) which produced WEAK REF CURSOR and TRANCOUNT global variable. I looked at (trace and explain plan) statements in the procedures and functions, it seems everything are OK. Init.ora parameters are big enough, statspack generated good values. The same function and procedure took about 1 minute in Sybase but take 9 minutes in Oracle. The difference is the procedures or functions after migration calling OMWB package (globalpkg). Any idea or tuning tips after migrating to Oracle ? Is Oracle slower than Sybase and SQL Server ? Tia. Rivaldi I have had this kind of problem (only 8mn against 80mn) some months ago ... not exactly the same physical schema in Oracle as in Sybase :-). You cannot say that Oracle is inherently slower or faster than Sybase or SQL Server. It depends on a lot of things, the underlaying hardware, your application of course, the use of some 'native tricks' (and from experience IOTs are not exactly equivalent to clustered indexes). The problem is that automated tools may be good for a first pass, but you really need to dive in the code and tweak it manually if you want good results. A RDBMS is not the 'neutral' component you may think. FYI we managed to bring Oracle performance in line (very slightly better, in fact) with Sybase performance in the case mentioned above. But it hasn't been easy. HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) 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
RE: Oracle slower ?
Hi No Oracle isn't slower than alternative RDBMS, at least if it is 9 times slower then it is either misconfigured, or the code is bad, or both. I'd strongly suspect the code to start with. If you treat the output of OMWB as a first cut, or functional prototype then you have a reasonable approach to the migration. If you can post the code for the slow proc I'm sure folk will be very quick to pick up why it isn't performing. Niall Litchfield Oracle DBA Audit Commission 0117 975 7805 -Original Message- Sent: 25 February 2003 15:09 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] We are converting from SQL Server and Sybase to Oracle. To convert functions and stored procedures we have been using the Oracle Migration Workbench (OMWB) which produced WEAK REF CURSOR and TRANCOUNT global variable. I looked at (trace and explain plan) statements in the procedures and functions, it seems everything are OK. Init.ora parameters are big enough, statspack generated good values. The same function and procedure took about 1 minute in Sybase but take 9 minutes in Oracle. The difference is the procedures or functions after migration calling OMWB package (globalpkg). Any idea or tuning tips after migrating to Oracle ? Is Oracle slower than Sybase and SQL Server ? Tia. Rivaldi This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) 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). ** This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or professional privilege. Any dissemination, distribution, copyright or use of this communication without prior permission of the sender is strictly prohibited. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: Oracle slower ?
We are converting from SQL Server and Sybase to Oracle. To convert functions and stored procedures we have been using the Oracle Migration Workbench (OMWB) which produced WEAK REF CURSOR and TRANCOUNT global variable. I looked at (trace and explain plan) statements in the procedures and functions, it seems everything are OK. Init.ora parameters are big enough, statspack generated good values. The same function and procedure took about 1 minute in Sybase but take 9 minutes in Oracle. The difference is the procedures or functions after migration calling OMWB package (globalpkg). Any idea or tuning tips after migrating to Oracle ? Is Oracle slower than Sybase and SQL Server ? Tia. Rivaldi I have had this kind of problem (only 8mn against 80mn) some months ago ... not exactly the same physical schema in Oracle as in Sybase :-). You cannot say that Oracle is inherently slower or faster than Sybase or SQL Server. It depends on a lot of things, the underlaying hardware, your application of course, the use of some 'native tricks' (and from experience IOTs are not exactly equivalent to clustered indexes). The problem is that automated tools may be good for a first pass, but you really need to dive in the code and tweak it manually if you want good results. A RDBMS is not the 'neutral' component you may think. FYI we managed to bring Oracle performance in line (very slightly better, in fact) with Sybase performance in the case mentioned above. But it hasn't been easy. HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Oracle slower ?
Oracle isn't slower, you need to analyze the SQL and re-tune it. Indexes, indexes, indices. -Original Message- From: Bahar, Rivaldi (BBASSI-CHQ) [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Subject: Oracle slower ? We are converting from SQL Server and Sybase to Oracle. To convert functions and stored procedures we have been using the Oracle Migration Workbench (OMWB) which produced WEAK REF CURSOR and TRANCOUNT global variable. I looked at (trace and explain plan) statements in the procedures and functions, it seems everything are OK. Init.ora parameters are big enough, statspack generated good values. The same function and procedure took about 1 minute in Sybase but take 9 minutes in Oracle. The difference is the procedures or functions after migration calling OMWB package (globalpkg). Any idea or tuning tips after migrating to Oracle ? Is Oracle slower than Sybase and SQL Server ? Tia. Rivaldi ** ** This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ** ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) 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: Gogala, Mladen 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: Oracle slower ?
Both Oracle (817) and Sybase running on the same test machine (Sun Solaris 6) but in different mount point. Both not at optimum datafiles and I/O configuration. If I run every SQL in the procedure manually from SQLplus it run at minimum cost (explain plan), indexes used. There are loops in the procedure. I guess it takes more time because more logic (if then else) inside the loops. For example, the following code generated by OMWB is inside the loops. Any other idea ? IF ( TEST_4.TranFlag = 0) THEN BEGIN /* Emulating TRANCOUNT functionality in Oracle model */ IF omwb_emulation.globalPkg.trancount = 1 THEN COMMIT WORK; END IF; IF omwb_emulation.globalPkg.trancount 0 THEN omwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount-1; END IF; -Original Message- Sent: Tuesday, February 25, 2003 11:20 AM To: Multiple recipients of list ORACLE-L We are converting from SQL Server and Sybase to Oracle. To convert functions and stored procedures we have been using the Oracle Migration Workbench (OMWB) which produced WEAK REF CURSOR and TRANCOUNT global variable. I looked at (trace and explain plan) statements in the procedures and functions, it seems everything are OK. Init.ora parameters are big enough, statspack generated good values. The same function and procedure took about 1 minute in Sybase but take 9 minutes in Oracle. The difference is the procedures or functions after migration calling OMWB package (globalpkg). Any idea or tuning tips after migrating to Oracle ? Is Oracle slower than Sybase and SQL Server ? Tia. Rivaldi I have had this kind of problem (only 8mn against 80mn) some months ago ... not exactly the same physical schema in Oracle as in Sybase :-). You cannot say that Oracle is inherently slower or faster than Sybase or SQL Server. It depends on a lot of things, the underlaying hardware, your application of course, the use of some 'native tricks' (and from experience IOTs are not exactly equivalent to clustered indexes). The problem is that automated tools may be good for a first pass, but you really need to dive in the code and tweak it manually if you want good results. A RDBMS is not the 'neutral' component you may think. FYI we managed to bring Oracle performance in line (very slightly better, in fact) with Sybase performance in the case mentioned above. But it hasn't been easy. HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) 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).
