Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is it me or is Oracle just slow?

Re: Is it me or is Oracle just slow?

From: <tandym_at_hotmail.com>
Date: 2000/06/05
Message-ID: <8hgdis$271$1@nnrp1.deja.com>

Hi,

a couple of ideas -
since you are using a base oracle install, you will need to go back through the initorcl.ora parameter file and increase such things as your buffer cache, shared pool and log buffers. Then, make sure you are not running in archive log mode (this will slow you down too). Then, Get rid of ODBC - instead, try using the migration workbench or sqlloader (with no logging which will skip the rbs etc, decreasing the number of writes). Then, buy another couple of harddisks and move your tablespaces around so that they are not all residing in one place. Pin your package/procedure to the SGA. After all of that, you should see a big improvement.

sonya

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)
>
> 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.
>
> 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...
>
>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US