Re: How fast should an IO be on a SYMMETRIX?

Hi Scott

Agree with the comment made elsewhere that with a Symm you do need to look beyond the marketting. So many of the usual rules still apply - get more and smaller form factor disks if poss and go for mirroring rather than RAID 5 (RAID S in EMC speak IIRC).

I believe we had mirroring in the EMC and striping at the HP LVM level at one of my previous sites which seemed successfull.

It is possible to mis-configure and EMC and get really sluggish performance on some areas of "disk". We resorted to writing a program in C that did simple reads/writes (sequential and random) as a means of testing and identifying these disk hotspots which once identified were resolved by our EMC eggheads. Also meant that being a few lines of C they couldn't blame Oracle!

Have been impressed with the write speed of EMC's (and all the HA features) but have never been blown away with random read speed.

KJP Scott Watson wrote:

> 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 Wed Oct 29 2003 - 22:43:44 CST