RE: IO performance question

Hi David,

Are you sure 90% waits on 'db file sequential read' is a "bad" thing? 90% of your waits will have to be on something - what would you prefer for them to be on?

How does you wait time compare to service time?

What is your average service time (ms/Read) on these files?

I'm not sure if dbfmrc=0 works, or if Oracle will still do multiblock reads on its own. You could do a level 12 trace and check the output for sure to see how many blocks it's reading at a time. Also, the trace file (after tkprof-ing it) will show you how much CPU time each query took - perhaps the index range scans, concatenation and sort are taking a significant amount of time?

Regards,
Brandon

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of david hill Sent: Thursday, June 23, 2005 9:59 AM
To: oracle-l_at_freelists.org
Subject: 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

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 23 2005 - 13:15:33 CDT