Re: Is it me or is Oracle just slow?

Let's examine table/index structures between the two products as well as the default settings for both databases. Comments will be embedded in the original text.

In article <8h4f6n$1s1$1_at_slb0.atl.mindspring.net>,   "Newbie" <nospam_at_please.com> wrote:
> Hi,
> I have Oracle 8i and SQL Server 7.0 installed on identical hardware
 (PIII
> 733 with 256M memory and a single disk). I have not tuned either
 database
> (both are just simple installs.)
>
> I am accessing the SQL Server 7.0 database via Microsofts ODBC for SQL
> Server and Oracle 8i via Oracles ODBC driver.
>
> The time for an insert into SQL Server is approximately 1/20th the
 time for
> an insert into Oracle 8i.
>
> A sample Object, called Example would be set up in SQL Server via the
> command:
>
> CREATE TABLE dbo.ExampleObject (
> ID int IDENTITY (1, 1) NOT NULL ,
> Name varchar(50) NOT NULL ,
> Status bit NOT NULL,
> Reason varchar(255) NOT NULL ,
> ConcurrencyID int NOT NULL
> )
>
> With an index on the ID column created by the command:
>
> Create Unique Clustered Index IX_ExampleObject ON
 dbo.ExampleObject(ID)
>

A clustered index in MS SQLServer uses the table data as the leaf nodes of the index, unlike Oracle where the index is an entirely separate entity. With small tables a clustered index can outperform a non-clustered configuration.

> The audit table for this object is created by the command:
>
> CREATE TABLE dbo.ExampleObjectAudit (
> ID int IDENTITY (1, 1) NOT NULL ,
> Name varchar(50) NOT NULL ,
> Status bit NOT NULL,
> Reason varchar(255) NOT NULL ,
> StampID int NOT NULL ,
> StampAction char (1) NOT NULL ,
> StampDateTime datetime NOT NULL ,
> StampUser char (15) NOT NULL)
>
> With an index on its ID column created by the command:
>
> Create Unique Clustered Index IX_ExampleObjectAudit ON
> dbo.ExampleObjectAudit(ID)
>
> The table in Oracle 8i is created by the command:
>
> CREATE TABLE ExampleObject (
> ID integer NOT NULL,
> Name varchar2(50),
> Status integer NOT NULL,
> Reason varchar2(255),
> PRIMARY KEY(ID))
>
> The index for the ID column should automatically be created because of
 the
> Primary Key constraint.
>

This is true, but the index is located in the same tablespace as the table, not the ideal configuration for Oracle. If the inserted data is in sequential order then you could create an index-organized table which would more closely mimic the table/clustered index configuration in SQLServer. However, with in index-oragnized table no additional indexes can be created and the efficiency of the insert/select statements is compromised when large numbers of non-sequential data rows exist.

> The table has an associated sequence for assigning ID numbers created
 by the
> command:
>
> Create Sequence SEQExampleObject start with 1
>
> The ID's are assigned by a trigger created by the command:
>
> Create Trigger TRGExampleObject
> before insert on ExampleObject
> for each row
> begin
> if (:new.ID = 0) then
> select SEQExampleObject.NEXTVAL into :new.ID from DUAL;
> end if;
> end;
>
> Similarly, the Audit table in Oracle is created by the commands:
>
> CREATE TABLE ExampleObjectAudit (
> ID integer NOT NULL ,
> Name varchar2(50) ,
> Status integer NOT NULL ,
> Reason varchar2(255) ,
> StampID integer NOT NULL ,
> StampAction char(1) NOT NULL ,
> StampDateTime date NOT NULL ,
> StampUser char(15) NOT NULL ,
> PRIMARY KEY(ID))
>
> Create Sequence SEQExampleObjectAudit start with 1
>
> Create Trigger TRGExampleObjectAudit
> before insert on ExampleObjectAudit
> for each row
> begin
> if (:new.ID = 0) then
> select SEQExampleObjectAudit.NEXTVAL into :new.ID from DUAL;
> end if;
> end;
>
> The stored procedure called in SQL Server reads as:
>
> Create Procedure spExampleObjectInsert
> @StampUser varchar(10),
> @Name varchar (50) ,
> @Status bit ,
> @Reason varchar (255)
> As
> Declare @trncnt int,
> @ErrorNumber int,
> @ID int
>
> Select @ErrorNumber = -1
>
> Select @trncnt = @@trancount
>
> If @trncnt = 0
> Begin Transaction t1
> Else
> Save Transaction t1
>
> Insert ExampleObject
> (
> Name,
> Status,
> Reason,
> ConcurrencyID)
> Values
> (
> @Name,
> @Status,
> @Reason,
> 1)
>
> If @@Error <> 0 GoTo ErrorHandler
>
> Select @ID = @@Identity
>
> Insert ExampleObjectAudit
> (StampUser,
> StampDateTime,
> StampAction,
> StampID,
> Name,
> Status,
> Reason)
> Select @StampUser,
> GetDate() ,
> 'I',
> ID,
> Name,
> Status,
> Reason
> From ExampleObject
> Where ID = @ID
>
> If @@Error <> 0 GoTo ErrorHandler
>
> If @trncnt = 0
> Commit Transaction t1
>
> Select
> ID,
> Name,
> Status,
> Reason,
> ConcurrencyID As ConcurrencyID1
> From ExampleObject
> Where ID = @ID
>
> Return (0)
>
> ErrorHandler:
> Rollback Transaction t1
> Select ErrorCondition
> From Common..ErrorMessage
> Where ErrorNumber = @ErrorNumber
> Return (100)
>
> The Package and procedure in Oracle read:
>
> Create Package PKGExampleObject AS
> Type RT1 is RECORD
>
> ID ExampleObject.ID%type,
> Name ExampleObject.Name%type,
> Status ExampleObject.Status%type,
> Reason ExampleObject.Reason%type,"
> ConcurrencyID1 ExampleObject.ConcurrencyID%type );
> Type RCT1 is REF CURSOR RETURN RT1;
> End; "
>
> Create Procedure
> xampleObjectInsert(
> varStampUser IN ExampleObjectAudit.StampUser%type,
> varName IN ExampleObject.Name%type,
> varStatus IN ExampleObject.Status%type,
> varReason IN ExampleObject.Reason%type,
> RC1 IN OUT PKGExampleObject.RCT1)
> As
> varErrorNumber ErrorMessage.ErrorNumber%type;
> varErrorCondition ErrorMessage.ErrorCondition%type;
> varID ExampleObject.ID%type;
> Begin
> varErrorNumber := -1;
>
> Insert into ExampleObject
> (ID,
> Name,
> Status,
> Reason,
> ConcurrencyID)
> V
> alues
> (0,
> varName,
> varStatus,
> varReason,
> 1);
>
> Select SEQExampleObject.CURRVAL into varID From DUAL;
>
> Insert into ExampleObjectAudit
> (ID,
> StampUser,
> StampDateTime,
> StampAction,
> StampID,
> Name,
> Status,
> Reason)
> Select 0,
> varStampUser,
> SYSDATE ,
> 'I',
> ExampleObject.ID,
> ExampleObject.Name,
> ExampleObject.Status,
> ExampleObject.Reason
> From ExampleObject
> Where ExampleObject.ID = varID ;
>
> Commit;
>
> Open RC1 For
>
> Select
> ID,
> Name,
> Status,
> Reason,
> ConcurrencyID ""ConcurrencyID1""
> From ExampleObject
> Where ID = varID;
>
> Exception
> When Others then
> ROLLBACK;
> Select ErrorCondition into varErrorCondition From
> ErrorMessage
> Where ErrorNumber = varErrorNumber;
> RAISE_APPLICATION_ERROR(-20000,varErrorCondition);
> End;
>
> Any ideas as to the reason for the performance gap... Is the Oracle
> Procedure badly written? Is the problem the Trigger? The ODBC
 driver? Or
> do I need to "tune" the Oracle database? Any input would be
 appreciated...
>
>

The procedure looks fine, not badly written at all.

Oracle default values are notoriusly poor choices for database configuration. It seems to me that Oracle does this intentionally to force performance tuning of the database. On the other hand SQLServer defaults are fairly efficient for a simple install. For small to medium amounts of data SQLServer can run efficiently from the default values. Larger data volumes cause SQLServer to bog down, necessitating database and application tuning. I would check the default settings for the Oracle instance; many can be changed in the init<instance>.ora file and the results immediately seen when the database is shutdown and re-started. Additionally separation of the tables and indexes, preferably on separate disks, will help tremendously.

David Fitzjarrell
Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 06 2000 - 00:00:00 CDT