Re: benchmark to disprove myths

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 5 Dec 2002 10:15:24 -0000
Message-ID: <3def273d$0$238$ed9e5944@reading.news.pipex.net>

"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3dee8a26.83981499_at_ausnews.austin.ibm.com...
> My partner and I are still disagreeing on two prime issues covered in this
> summer's famous "Oracle Myths" discussion.

This will run and run I suspect.

> We're also disagreeing on the value
> of separating redo logs, archive log files, and other data. All of this
has to
> do with placement of different files on available disk arrays and how to
best
> configure the disks in a new server.
>
> I am proposing a simple benchmark test.
>
> Have a PL/SQL to implement this pseudo-code:
>
> Create table my_test
> (empno number,
> last_name char(10)
> first_name char(10)
> street char(10)
> city char(10)
> state char(2)
> zip char(5)
>
>
> for x = 1 to 100,000
> insert row using x as the empno value
> commit
> next x
>
> for x = 1 to 100,000
> update my_test
> set last_name = 'xxxxxx',
> first_name = 'xxxxx'
> street = 'xxxxxx'
> city = 'xxxxxxx'
> state = 'xx'
> zip = 'xxxxx'
> where empno = x
> commit
> next x
>
> what I propose is running this with various placements of data, index,
redo,
> rbs, and archive logs. What I hope to demonstrate is the value (or lack
> thereof) of
>
> 1 - separating index and data
> 2 - giving redo its own raid set
> 3 - giving archive logs their own set
>
> Does this sound like a resonable means of proving/disproving long held
> assumptions and the assurances of our hardware guys who insist that drives
are
> now so fast that we can ignore these kinds of considerations?

It certainly looks pretty reasonable to me. Like Stephan I'd probably loop a million times rather than a hundred thousand, bigger numbers always look more impressive.

The other test I'd suggest you also run which goes to sql efficiency is to consider

SQL> Create table my_test

  2      (empno number,
  3         last_name char(10),
  4         first_name char(10),
  5         street char(10),
  6         city char(10),
  7          state char(2),
  8         zip char(5));

Table created.

Elapsed: 00:00:00.00
SQL> begin
2 for x in 1..100000 loop

  3      insert into my_test (empno) values(x);
  4      commit;

  5 end loop;
  6 end;
  7 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:15.07
SQL> ed
Wrote file afiedt.buf

  1 begin
  2 for x in 1..100000 loop
  3 insert into my_test (empno) values(x);   4 end loop;
  5 commit;
  6* end;
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:19.09
SQL>

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Dec 05 2002 - 04:15:24 CST