FAQ
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
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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 3 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 23, '05 at 1:03p
activeJun 23, '05 at 1:59p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase