Re: Very bad performances on Oracle

As I'm sure you're aware, there are times when record by record processing is appropriate (not to mention that SQL Server might perform better with a set based update as well).

Asside from the redo log sizing, is there anything else that could explain the diference in performance? I have an interest in this, as I'm experiencing slower than expected update performance in Oracle.

Thanks,

Jim

Thomas Kyte wrote in message <36c53073.6268924_at_192.86.155.100>...
>A copy of this was sent to "Florian RIVAL" <florian.rival_at_caramail.fr>
>(if that email address didn't require changing)
>On Fri, 12 Feb 1999 13:03:15 +0100, you wrote:
>
>>
>>I have two scripts : one for SQLServer and one other for Oracle. Althought
>>these two scripts do the same job (upgrade of a database), the
performances
>>are very different : less than 1h30 on SQLServer, more than 9h00 on
Oracle.
>>For the most part of this upgrade, I used cursor in order to select datas
>>from a table and to update this table.
>>
>>For example :
>>
>>MyTable
>>{
>> Name varchar2 (30), /* Primary key */
>> PostCode Number (6)
>>}
>>
>>The aim is to convert the PostCode in characters.
>>
>>alter table MyTable add PostCodeChar varchar2 (6) null
>>/
>>
>>DECLARE
>>
>> Key varchar2 (30);
>> Buffer integer;
>>
>> CURSOR Selection IS
>> SELECT Name, PostCode
>> FROM MyTable
>> where PostCode is not null;
>>BEGIN
>>
>> OPEN Selection;
>>
>> FETCH Selection INTO Key, Buffer;
>>
>> WHILE Selection%FOUND
>> LOOP
>> update MyTable set PostCodeVar = TO_CHAR ( Buffer ), PostCode =
null
>> where Name = Key;
>>
>> FETCH Selection INTO Key, Buffer;
>> END LOOP;
>>
>> CLOSE Selection;
>>
>> commit;
>>
>>END;
>>/
>>
>>For a table of 450 000 lines, this operation takes 2h00 on Oracle and 10
>>minutes on SQLServer.
>>
>
>why not just run:
>
>SQL> update mytable set postcodevar = to_char(postcode), postcode = null
> where postcode is not null;
>
>a single sqlstatement to do something will always be faster then cursor'ing
>through the data to do it procedurally.
>
>>I would like to know why Oracle is very slow.
>>
>
>have you correctly sized your online redo logs for large transactions?
look in
>your alert file for messages to the effect "checkpoint not complete, cannot
>allocate new log". If you see them, add more log -- you are whipping
through
>the log faster then the checkppoints can complete.
>
>>If someone have got any idea to optimize this script, it will be welcome.
>>
>>Thanks.
>>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.
Received on Sun Feb 14 1999 - 07:17:53 CST