IO performance question

Hi guys,

I'm trying to investigate performance of one our db's where db file sequential read are 90% of waits
Oracle 9.2.0.6, on a RHEL 3, dual Xeon box Everything's on a raid 5, 7+1, Hitachi SAN

So with a few test's here what I came up with I can do a full table scan I can read 225,525 disk reads in 40 seconds

Select count(*) from test;

Elapsed: 00:00:40.79

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'TEST' Statistics

          0  recursive calls
          0  db block gets
     297115  consistent gets
     225525  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

then to force disk reads from index

select sum(1) from (
select 1 from test where CAL_YEAR in (2000,2001,2002,2003,2004,2005));

Elapsed: 00:02:36.21

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (AGGREGATE)

   2    1     CONCATENATION
   3    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)
   5    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)
   6    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)
   8    2       INDEX (RANGE SCAN) OF 'TEST_INDEX1' (NON-UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
      94530  consistent gets
      92538  physical reads
          0  redo size
        380  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So to read less then half as much data from disk it took me 6 times as long

Ok, multiblock reads vs single block reads, so it must the db_file_multiblock_read_count, I thought

So I should be able to simulate single block reads with this alter session set db_file_multiblock_read_count = 0; from the default of 8

SQL> select count(*) from test1;

Elapsed: 00:01:18.11

So its still took twice as long to read half as much data

What would explain this difference?? Or is this normal?? Shouldn't I get the same io performance from a full table scan with db_file_multiblock_read_count disabled as index reads from disk?

Both objects are in the same tablespace on the same lun The db setup is pretty vanilla, nothing exotic. 8k block size Sysadmin's not here this week, so I'm not sure of the exact setup for raid. Could this be due to my raid stripe size?

Can anyone point me in the right direction???

Thanks
David Hill
DBA CONFIDENTIALITY NOTICE
This message contains confidential information intended only for the use of the individual or entity named as recipient. Any dissemination, distribution or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify us and delete your copy. Thank you.

AVIS DE CONFIDENTIALITÉ
Les informations contenues aux présentes sont de nature privilégiée et confidentielle. Elles ne peuvent être utilisées que par la personne ou l'entité dont le nom paraît comme destinataire. Si le lecteur du présent message n'est pas le destinataire prévu, il est par les présentes prié de noter qu'il est strictement interdit de divulguer, de distribuer ou de copier ce message. Si ce message vous a été transmis par mégarde, veuillez nous en aviser immédiatement et supprimer votre copie. Merci.

--
http://www.freelists.org/webpage/oracle-l