Re: TPC-A and -B Benchmarks

In <21e3gcINN5q_at_emory.mathcs.emory.edu> SNIEUWEN_at_nl.oracle.com ("Sandor (Mr. Oracle7)") writes:

>In-Reply-To: NLUNIX:rmy.emory.edu!ilist_at_sun4nl.nl.oracle.com's message of 07-06-93 21:11
 

>Just for your information:
 

>Oracle (v6 and Oracle7) *DO* support repeatable reads, *WITHOUT* locking
>whatsoever. By default (so without setting any parameters or changing any
>settings), Oracle implements statement level multi-version read-consistency.
>This advanced technique will always return correct data for a query, making
>sure there are no inconsistencies because of changes made by other users during
>the query. Changes made by other users *ARE* allowed however, you won't see
>them as the result of the query when they are commited AFTER the start of
>executing of the query.

But this is Read Consistency, *NOT* Repeatable Read. Repeatable Read requires that other users *ARE NOT* allowed to change the rows in your cursor, nor are they allowed to insert new rows that would have been included by your cursor.

>Multiple versions of rows can be reconstructed very easy, as this information
>is contained in the rollback data.
 

>This works out very efficiently for multi-user systems, but, which is also
>quite interesting, it also has effect within one statement. Consider the
>following update statement:
 

> UPDATE employees e1
> SET salary =
> (
> SELECT avg(e2.salary)
> FROM employees e2
> WHERE e1.department = e2.department
> )
 

>The purpose of this statement is to set each employee's salary to the
>average of all salaries in his/her department. When read-comsistency is not
>correctly implemented, you might get the wrong result, i.e. changing averages,
>or at least an unpredictable result.

Yet another reason why it is *NOT* Repeatable Read, which requires *transaction* level consistency, not statement level consistency.

>After using 'SET TRANSACTION READONLY' in a session, Oracle will also enforced
>read-consistency over multiple statements, allowing constructions like:
 

> SELECT region, sum(salary)
> FROM employees
> GROUP BY region;
 

> SELECT department, sum(salary)
> FROM employess
> GROUP BY department;
 

>both statements will have to return the same total when run consecutively, even
>if some salaries are changed in between. Session level read consistency will do
>the job, again *WITHOUT* locking (in Oracle)

This satisfies the consisency requirements of Repeatable Read, but *NOT* the locking requirements.

Oracle *can* do Repeatable Read, but (IMHO) *only* by setting the init.ora parameter SERIALIZABLE to TRUE (which gives ANSI compatibility).

When SERIALIZABLE is set to TRUE, the ORACLE RDBMS schedules all concurrently running transactions to execute such that the result is the same as if each transaction were run in serial, one at a time. Users gain the ability to query one table repeatedly throughout one transaction and know they are always seeing the same data (i.e., transaction-level read consistency) but at considerable concurrency costs. Although multiple transactions can query the same data, only the transaction that first accesses the table (even with a query) can update the data. -- *That* is Repeatable Read!

When SERIALIZABLE is set to TRUE, all locks (and resources) are held at the table-level; for example, if you lock one row in a table (with or without the intention of updating it), no one else can modify *any* rows in that table.

>One note: I'm pretty sure Oracle will come up with TPC-C results some time. I

I believe that Pyramid and Oracle plan to run TPC-C with ORACLE7 on Pyramid later this year.

>am not aware of any time scale, but I think TPC-A is still the most accepted at
>this time. One of the purposed of benchmarks is that you can compare (allthough
>very carefully) figures. If almost nobody is running TPC-C's (at this moment
>only IBM as far as I know, both on AS/400 and RS/6000 with Informix), what can
>customers do with these figures, as long as there is nothing to compare it
>with. That's why I think a lot of DB vendors are a bit hesitant to immediately
>start doing TPC-C's.

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.