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

Direct path loads via SQL*Loader avoid even the log problems.

Vitaly Sikolenko wrote:

> 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.
Received on Tue Sep 16 1997 - 00:00:00 CEST