Oracle-L: RE: Transactions per second

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 09 May 2002 14:53:19 -0800
Message-ID: <F001.0045DC3F.20020509145319@fatcity.com>

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:

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-----
Sent: Thursday, May 09, 2002 4:08 AM
To: Multiple recipients of list ORACLE-L

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." <ian_at_SLAC.Stanford.EDU> Sent by: root_at_fatcity.com

05/09/02 04:53 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
        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
<mailto:ian_at_SLAC.Stanford.edu> ian_at_SLAC.Stanford.edu -----Original Message-----
Sent: Wednesday, May 08, 2002 11:39 AM To: Multiple recipients of list ORACLE-L

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 -----
To: <mailto:ORACLE-L_at_fatcity.com> Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 11:30 AM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 09 2002 - 17:53:19 CDT