Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is it me or is Oracle just slow?
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