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

  • Allen, Brandon at Jun 23, 2005 at 1:15 pm
    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
  • Mladen Gogala at Jun 23, 2005 at 1:59 pm

    david hill wrote:
    Hi guys,

    I'm trying to investigate performance of one our db's where db file
    sequential read are 90% of waits
    This means that your application is reading blocks from indexes. Is
    performance of the
    application unaccpetable? If so, you should set the event 10046, level 8
    in the session
    executing the application and see what took the longest (tkprof with
    sort=exeela will give you that)
    and tune that particular statement.
    Oracle 9.2.0.6, on a RHEL 3, dual Xeon box
    Everything's on a raid 5, 7+1, Hitachi SAN
    Wow! RAID-5. You should join the battle against any raid five
    (http://www.baarf.com). You should also
    read few articles from www.hotsos.com
    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

    Full table scans don't wait for db file sequential reads. They wait for
    db file scattered reads. In this case
    you may want to not use index.

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121

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

Related Discussions

Discussion Navigation
viewthread | post
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