Oracle7 Benchmarks, Evaluations

The benchmarks I have so far for Oracle7 follow. Some important points that have been shown here:  

Scalability up to 26 Processors. Very large databases, up to 127 gigabytes, large numbers of users, up to 6050 (using /T as a concentrator).

Model            CPU's  Memory    Type    TPS      $/TPS    other info 
DEC VAX 6660       6     256M     TPC-B   560.87   2970   
DEC VAX 6560       6     256M     TPC-B   315      4725
DG AViiON 4625     2      64M     TPC-B   100.85   1588
Sequent S-?       26     256M     TPC-A   605.27   10,919   6050 users, 
					                    127 Gig Disk
Sun 690MP 	   4     256M     TPC-A   107.28   12,604

I will post additional benchmark results as they come in. Full disclosures are available from Oracle or the hardware vendors.

Other things besides performance which I think you should find important in ANY comparison of products, are key differences which are often lost in "check off the feature" comparisons.

Because a product can "check off" Two Phase Commit and Referential Integrity, for example, does NOT imply that its easy, productive, or automatic to use these features. Each of the independant RDBMS's is good doing at something - the following some people won't like because it looks like a marketing piece but is clearer than something I can write and will give you some good questions to ask the various vendors.

(Several vendors besides Oracle DO have great, robust implementations
of the features below, so take it easy on the flames please) - It's up to the buyer to figure out what they're really getting with each package.

  Automatic Data Access

A cooperative-server database (ORACLE7) provides automatic access to all the data on all computers in the network. Automatic data access means that all the data on all the servers is queried and updated in exactly the same way and with the same ease as if all the data were stored on a single server. In other words, with a cooperative-server database, the same SQL query and update transactions that are used to access data on one computer are also used to access data on multiple computers.

  A banking application example illustrates the differences between an early client-server database and a cooperative-server database with automatic data sharing. The following industry-standard SQL query is used to retrieve the savings and checking account balances for a depositor.

            SELECT   S_BALANCE, C_BALANCE
        FROM     SAVINGS, CHECKING
            WHERE    DEPOSITOR_NAME = `JONES'
            AND      S_DEPOSITOR = C_DEPOSITOR;

  This is the query used when both the savings data and the checking data are on the same computer. But what happens if the savings data is moved to a computer in Los Angeles while the checking data remains on a computer in New York? With a cooperative-server database this same query is still used to retrieve the savings and checking account balances. With an early client-server database, on the other hand, a great deal of additional code must be written around every query and update transaction in every application that accesses the savings and checking data. This is because an early client-server database does not support standard SQL access to multiple server computers.

  The following SQL transaction subtracts $250.00 from a depositor's savings account and adds it to his checking account. The COMMIT statement indicates the end of the transaction and directs the changes to be indelibly recorded in the database.

        UPDATE   SAVINGS
        SET      S_BALANCE = S_BALANCE - 250.00
        WHERE    S_DEPOSITOR = `JONES';

        UPDATE   CHECKING
        SET      C_BALANCE = C_BALANCE + 250.00
        WHERE    C_DEPOSITOR = `JONES';

            COMMIT   WORK

  With a cooperative-server database the SQL transaction above is used to update the data regardless of whether the savings and checking data are on one computer in New York, or on multiple computers in New York and Los Angeles. With an early client-server database, however, this standard SQL transaction will not work if the data is on more than one computer.

  The first example above, showing the use of standard SQL to retrieve data from multiple computers, is called an automatic distributed join query. The second SQL example is called an automatic distributed update transaction with automatic two-phase commit. A cooperative-server database provides automatic access of data on multiple computers using industry-standard SQL. In contrast, an early client-server database allows no such automatic access, but instead provides a programmer's toolkit to help application programmers to write code to access data on multiple server computers.

  The early client-server programmer's toolkit includes a database remote procedure call facility for writing programs that access data on another machine, and a programmatic two-phase commit protocol to synchronize updates on multiple servers. To implement a distributed join query or the distributed update transaction using database remote procedure calls and a programmatic two-phase commit protocol requires that the application programmer write a large amount of very complex code.

  It is important to note that ORACLE7 does in fact support both database remote procedure calls and programmatic two-phase commit. However, these features do not and cannot provide automatic data sharing. Rather, they are used with ORACLE7's open gateway and programmable server described below.

  Open Gateway to other Vendors' Databases

  The ORACLE7 cooperative-server database provides two types of gateways to non-ORACLE data: automatic and programmable. The automatic gateway supports standard SQL operations on many non-SQL systems, such as IBM's IMS database and DEC's RMS file system, as well as most SQL systems, such as IBM's DB2 and DEC's Rdb databases. The programmable gateway supports native access to virtually any data source via database remote procedure calls.

  The automatic gateway supports automatic distributed queries and automatic distributed transactions that span ORACLE and non-ORACLE data sources, just as if all the data were stored in an ORACLE database. What happens in the previous banking application example if the savings data is moved to a DB2 database on an IBM mainframe in Los Angeles, while the checking data remains in an ORACLE database on a Sun server in New York? With the ORACLE7 automatic gateway, the same SQL query and update transactions that worked when all of the data was in an ORACLE database will continue to work without change even though part of the data is now in a DB2 database. In other words, the DB2 data is seamlessly integrated into a modern, open, cooperative-server network just as if it were ORACLE data.

  The programmable gateway is used when there is no automatic gateway available for a specific non-ORACLE data source -- for example, a file system on a microcomputer robot controller. In that case, the application developer uses tools provided as a part of the programmable gateway to write a program to access the data in the non-ORACLE data source. The programmable gateway's toolkit includes a database remote procedure call facility to simplify writing this program when the application program and the non-ORACLE data source are on different computers on the network.

  Early client-server databases do not provide an automatic gateway for accessing foreign data sources using industry-standard SQL. These firstgeneration  systems provide only a programmable gateway. That is, the user has to write a lot of additional code to access foreign data sources. In contrast, ORACLE7's automatic gateway provides open access to other vendors' databases without any programming whatsoever.

  Automatic Network Interchange

  Another challenge to accessing data, of course, is the fact that most organizations have multiple networks that communicate using different network protocols. ORACLE7's multiprotocol networking software allows any client computer to communicate with any server or group of server computers in the network, regardless of network protocol.

  What if we change our example configuration so that the IBM mainframe in Los Angeles communicates using the SNA APPC LU6.2 network protocol, while the Sun server in New York communicates using the TCP/IP network protocol? With ORACLE7's multiprotocol networking software the same SQL query and update transactions we used before continue to work with no change whatsoever. That is, with ORACLE7, the fact that the application is now running on a multiprotocol network is handled totally automatically.

  Programmable Server

  In addition to a powerful set of facilities that provide automatic access to data on multiple servers, the ORACLE7 cooperative-server database is completely user programmable as well. ORACLE7 fully supports stored procedures, triggers and database remote procedure calls. A powerful programming language, PL/SQL, is built into ORACLE7 so that complete database transactions can be stored and executed on the server. These stored procedures can be explicitly invoked by a single message from the client computer, or triggered by an event such as a database update.

  A programmable server, like any programming environment, is inherently powerful and flexible. Users can write programs to do virtually anything. However, a modern, cooperative-server database distinguishes itself from early client-server systems by making many frequently used functions available to users without requiring that they write programs. As already shown, ORACLE7 supports automatic distributed queries, automatic distributed updates and automatic access to non-ORACLE data sources using industrystandard  SQL. None of these functions require that the user program the server or write any other programs whatsoever.

  Early client-server databases do not support any of these functions except in the sense that they provide a programmable server; and a programmable server -- with a good deal of effort -- can be programmed to do almost anything. A clear example of how a programmable server can be misused is server-enforced referential integrity. An early client-server database's claim to support server-enforced referential integrity is based on the user's ability to write triggers and stored procedures to implement referential integrity. The next section examines this claim and serves as another example of the differences between an early client-server database and a modern, cooperative-server system.

  Server-Enforced Referential Integrity

  The ORACLE7 cooperative-server database provides automatic referential integrity using industry-standard SQL. All that is required to implement a referential integrity constraint is the addition of just four words to the standard SQL create table statement.

        CREATE TABLE       Department
            (Department_Number        INTEGER (2) PRIMARY KEY,
              Department_Name  CHAR (12));

        CREATE TABLE       Employee
            (Employee_Number   INTEGER (4) PRIMARY KEY,
              Employee_Name    CHAR (20),
               Manager_Number  INTEGER (4) REFERENCES Employee,
               Department_Number  INTEGER (2) REFERENCES Department);

  The department number field is identified as the primary key of the department table, and as a foreign key in the employee table. Whenever an employee record is entered into the employee table, ORACLE7 automatically checks to see if the department number in the new employee record matches
(that is, references with integrity) a department number in the department
table. If there is no match, ORACLE7 rejects the new employee record because it contains an invalid department number.

  Early client-server databases support programmatic referential integrity as opposed to the automatic referential integrity of ORACLE7. Programmatic referential integrity means that the user must write triggers and stored procedures to implement the server-enforced referential integrity function. The simple referential integrity constraints shown in the example above requires that the user of the programmable server write over 150 lines of code in the database vendor's proprietary programming language. A realworld  database, of course, has thousands of database tables with thousands of referential integrity constraints requiring hundreds of thousands of lines of code to implement. Early client-server databases' programmatic and proprietary approach to referential integrity is an obvious, clear contrast to the automatic referential integrity using industry standard SQL provided by a modern, cooperative-server database.

  Standards

  ORACLE7's implementation of distributed queries, distributed transactions, open access to non-ORACLE data and referential integrity is not only fully automatic; each of these features is implemented in complete conformance with the SQL standard from the American National Standards Institute (ANSI) and International Standards Organization (ISO). In contrast, as we have seen, early-client server databases require that users write programs to implement these same functions, and, moreover, often those programs must be written in the vendor's own proprietary language.

  ORACLE7 was the first DBMS to be tested and certified by the National Institute of Standards (NIST) to be 100 percent compliant with the ANSI SQL level 2 (the highest level) SQL standard and the Federal Information Processing Standard (FIPS).

• Dan

Daniel Druker
Senior Consultant
Oracle Corporation                    

| Dan Druker                    |  work 415.506.4803                          |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Wed Jun 17 1992 - 03:03:07 CEST