REPOST (line wrap): How fast should an IO be on a SYMMETRIX?

From: Scott Watson <nospam_at_hotmail.com>
Date: Mon, 27 Oct 2003 17:29:29 -0500
Message-ID: <%Xgnb.31720$Pt3.794791@weber.videotron.net>

Sorry the orignal post was wrapping lines at char 76...

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

Received on Mon Oct 27 2003 - 16:29:29 CST