How fast should an IO be on a SYMMETRIX?

From: Scott Watson <nospam_at_hotmail.com>
Date: Mon, 27 Oct 2003 17:24:58 -0500
Message-ID: <0Ugnb.31717$Pt3.792770@weber.videotron.net>

Group,

I have the following query.

select a.*
from

     golfowner.scott_match_data a,
     (select * from golfowner.t_mtch_subj_id where rownum < 50) b
where
     a.wb_ctry_cd = 785

and a.subj_id = b.subj_id

Using tkprof to collect waits for the above query I found that the read time is what is impacting performance of this query. I would like to know if these times are realistic for a lightly loaded server.

The database is a 2node RAC cluster running HP-UX 11.11 with raw partitions on
a Symmetrix. Does my Symm need a check up or is there some setting I can change
to increase the read IO. ( Async IO is already set up )

Thanks,
Scottt Watson.

select /* new */ a.*
from

     golfowner.scott_match_data a,
     (select * from golfowner.t_mtch_subj_id where rownum < :"SYS_B_0") b
where
     a.wb_ctry_cd = :"SYS_B_1"

and a.subj_id = b.subj_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 33 0.06 2.50 254 736 0 473
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 35 0.06 2.50 254 736 0 473

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: SYS

Rows Row Source Operation

-------  --------------------------------------------------- 

    473 TABLE ACCESS BY INDEX ROWID SCOTT_MATCH_DATA (cr=736 r=254 w=0 time=2497285 us)

    573 NESTED LOOPS (cr=299 r=36 w=0 time=239305 us)

     99    VIEW  (cr=36 r=0 w=0 time=660 us)
     99     COUNT STOPKEY (cr=36 r=0 w=0 time=527 us)
     99      TABLE ACCESS FULL T_MTCH_SUBJ_ID (cr=36 r=0 w=0 time=396 us)

    473 INDEX RANGE SCAN SCOTT_MATCH_DATA_IX01 (cr=263 r=36 w=0 time=237472 us)(object id 45838)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

---

 Waited ---------- ------------

  SQL*Net message to client                      33        0.00
0.00
  global cache cr request                       108        0.00
0.03
  SQL*Net message from client                    33        0.00
0.03
  db file sequential read                       254        0.45
2.42     <<<<< ALL THE WAITING IS HAPPENEING HERE.

Received on Mon Oct 27 2003 - 16:24:58 CST