| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Very bad performances on Oracle
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
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 Fri Feb 12 1999 - 07:49:13 CST
 |
 |