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

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 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