RE: Transactions per second

[MacGregor, Ian A.]

Your environment is much  different from our test which was one-tier, but did use Oracle 9i.  We had no indexes on the partition  in which the insert was taking place. We did no special tuning other than to balance the I/O load.  We employed Veritas and its direct IO capability.  That's direct not quick.  We used Oracle's OCiDirectPath capabilities.

 

The production system only has to do 500 tps routinely and 1000 tps peak in its initial configuration.    So when the Controls folks came back and  said they had achieved 15,000 tps.  I wasn't going to muck with the system.   

 

N.B.  There is a bug in the Oracle 9i version of the OCI direct path functions  which has additional requirements to  that 8i version. 

 

----------------------------------------------------------------------------------------------------------------

Here's some more information about it:

 

From: David Mitchell 04-Jan-02 00:23

Subject: Re : Crash in OCIDirPathPrepare on Solaris and Oracle9i

I'll present a few reasons why I believe this is a 9i regression bug:

* The Oracle8i client doesn't have this problem.

* The way it fails:

* If the schema is required, OCIDirPathPrepare() should fail with an error. A crash/core dump is an ... unconventional way of indicating an

error.

* Looking at the stack trace, the crash appears to occur in code where it's getting table-type info. Perhaps it's making the assumption that

there's always a schema name:

[12] sigacthandler(0xb, 0xf9f81dc0, 0xf9f7f220, 0xfdbec9ac, 0xf9f7f4d8, 0xf9f81dc0), at 0xfdbd8644

---- called from signal handler with signal 11 (SIGSEGV) ------

[13] kpudpxp_genCaseSensName(0x0, 0x0, 0x5734c8, 0xf9f7f778, 0x1f, 0x2), at 0xfadba9cc

[14] kpudpxp_setTblObjType(0x5724ac, 0x5ad050, 0x5ad0bc, 0xf9f81840, 0x48, 0x572fcc), at 0xfadbad58

[15] kpudpxp_ctxPrepare(0x573454, 0x5ad050, 0x5ad0bc, 0xfb4126d0, 0x5734c8, 0x5ad0bc), at 0xfadb1590

[16] OCIDirPathPrepare(0x573454, 0xfc8b5a30, 0x5ad0bc, 0xfc8b1720, 0x5ad0bc, 0x5ad050), at 0xfc87cd94

* Finally, there's the documentation (from

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/ociaahan.htm#453382):

OCI_ATTR_SCHEMA_NAME

Mode

READ/WRITE

Description

Name of the schema where the table being loaded resides. If not specified, the schema defaults to that of the connected user.

Attribute Datatype

text **/text *

I can work-around it by setting an empty ("") schema, but since the behavior is contrary to the documentation it seems to be a bug.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:08 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Transactions per second

Hi,

From the messages below I understand that some of you are having big transactions per second requirements in the application. We are also developing an application that requires 5000 TPS, can anybody suggest how to get the size of the TPS and also how I can test for the number of Transaction per second.

We are using a 3 tier architecture, Java client. EJB and Oracle 9i.

I would be very useful if some help is available in this regards

Regards
Prem Chandran N

"MacGregor, Ian A." <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/02 04:53 AM Please respond to ORACLE-L

To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>         cc:                 Subject:        RE: Transactions per second

Where  does that theoretical  limit come from?  We've done 15,000 tps here  using OCIDirectWrite calls on a machine with 4, 450 MHz CPU's  an  a-1000 and  some internal disks.  The transactions were small about  150 bytes max.   There was no network involved, and no queries were  being run against the database.   The 15,000 tps figure comes from our  accelerator controls department  which is  testing Oracle's feasibility to store information on  the accelerator's status.   
 
Ian  MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
From: Tim Gorman  [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 08, 2002 11:39  AM
To: Multiple recipients of list ORACLE-L
Subject: Re:  Transactions per second


Not that it's relevant to this question, but I  understand the theoretical limit is 16,384 tps.  This affects  the sizing of integers used for SCN base, wrap, seq# in the block headers, I  guess...
----- Original Message -----
From:  Rick  Stephenson
To: Multiple recipients of list ORACLE-L  
Sent: Wednesday, May 08, 2002 11:30  AM
Subject: FW: Transactions per  second

I have a developer that asked  "how many transactions per second can Oracle handle?"

I would assume that the number  of transactions depends on the size of the transactions, number of CPUs,  memory, etc.

Is there a guideline to follow  when guesstimating something like this, or is it just trial an error to find  out whether it can handle the new load?

 

I am running on Solaris 2.8,  Oracle EE 8.1.7

 

Thanks for any  help?

 

Rick Stephenson