Re: Oracle much slower than MS SQL Server on NT?!

The most probably you have a problem with "overlapping" checkpoints in Oracle when making a bulk data load. The point is that when a log file is filled the "log switch" occurs which triggers a checkpoint (writing all the "dirty" buffers to disk). In principle this doesn't slow down the database performance since checkpoints are astynchronous in Oracle. If the checkpoint doesn't finish before the next log switch occur the 2 checkpoints "overlap". When performing a log switch Oracle must ensure that the checkpoint initiated with the previous filling out the log file which is about to be refilled is finished (otherwise it cannot guarantee the full data recovery after failure). If you have overlapping checkpoints Oracle7 cannot distinguish (Oracle8 can) the checkpoint initiated with "needed" log file from the checkpoints initiated by other log files. As a result Oracle waits for the last checkpoint to be completed!
To avoid such situation increase the size of log files (by default they are very small) and also increase the parameter LOG_BUFFER (it's also very small by default). I think after that you'll have Oracle perfoming your data load much faster.
And the last. You will not see any significant preformance benefits of Oracle when trying one-user tests. Oracle performs much better than others in multi-user configurations especially when several users update the same tables concurrently.

L. Tseng wrote:

> I recently did a testing to see the performance difference
> between Oracle 7.3 and MS SQL 6.5.
>
> The test simply does 5000 and 10000 rows insert to a table, to my
> surprise, Oracle is 2-3 times slower and gets even slower when
> inserting
> more rows. This seems to contradict to Oracle's claim as the fastest
> database engine.
>
> The environment for testing is on the same NT 4.0 box. Two server were
>
> configured to use about same amount of memory. The results are:
>
> insert
> 5000 recs Oracle: 2 min. 20 sec.
> MS SQL: 58 sec.
>
> 10000 recs Oracle: 4 min. 55 sec.
> MS SQL: 1 min. 42 sec.
>
> I was wondering if anyone experience the same thing or
> anyone know some tips to speed up the Oracle.
>
> Thanks for your help in advance.
>
> Leslie
>
> I here include the table structure below for reference.
>
> CREATE TABLE Employee
> (
> EmployeeID int NOT NULL ,
> FirstName VARCHAR2 (50) NULL ,
> LastName VARCHAR2 (50) NULL ,
> MiddleInitial char (2) NULL ,
> DefaultLoginName VARCHAR2 (30) NULL ,
> DefaultDomainName VARCHAR2 (50) NULL ,
> EmailAddress VARCHAR2 (75) NULL ,
> SetupDate DATE DEFAULT SYSDATE ,
> SetupBy VARCHAR2 (30) DEFAULT USER ,
> LastModifiedDate DATE NULL ,
> LastModifiedBy VARCHAR2 (30) NULL ,
> ServerFlag NUMBER(3) NULL,
> RecordInputType NUMBER(3) NULL
> )
> ;
>
> CREATE UNIQUE INDEX PKEmployee ON Employee(EmployeeID)
> ;
>
> CREATE UNIQUE INDEX AK1Employee ON Employee(DefaultLoginName)
> ;
>
> drop sequence emp_seq;
> create sequence emp_seq;
>
> CREATE OR REPLACE TRIGGER EmpID_TR
> BEFORE INSERT ON employee
> FOR EACH ROW
> WHEN (NEW.EmployeeID IS NULL)
> BEGIN
> SELECT emp_seq.NEXTVAL INTO :NEW.EmployeeID FROM DUAL;
> END;
>
> /
>
> declare
> y int;
> begin
> for y in 1..5000 loop
> INSERT INTO Employee
> (
> FirstName,
> LastName,
> MiddleInitial,
> DefaultLoginName,
> EmailAddress,
> ServerFlag,
> RecordInputType
> )
> VALUES
> (
> 'User'||y,
> 'User'||y,
> null,
> 'User'||y,
> null,
> 1,
> 2
> );
> commit;
> end loop;
> commit;
> end;
>
> /

--
Vitaly Sikolenko                  Senior Sales Consultant
                                  Server & Gateways
ORACLE C.I.S.
Moscow Russia                      E-mail: vsikolen_at_ru.oracle.com

The statements and opinions expressed here are my own and do not
necessarily represent those of Oracle Corporation.