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

From: VC <boston103_at_hotmail.com>
Date: Tue, 28 Oct 2003 00:33:02 GMT
Message-ID: <2Pinb.44997$e01.106865@attbi_s02>

Hello Scott,

The execution plan and the query efficiency aside, your HP I/O subsystem does appear to be sluggish. Assuming an 8K block size, average wait time was about 95 ms per each block (2.42s / 254). On my home PC with a single IDE disk, I got these numbers:

create table t1 as select * from all_objects; insert /*+ append */ into t1 select * from t1;

..................

commit;
select count(*) from t1;
count(*)

811040
create index t1_idx on t1(object_name) nologging; alter session set events '10046 trace name context forever, level 8'; select count(*) from t1 where object_name between 'B' and 'T';

select count(*)
from
t1 where object_name between 'B' and 'T'

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 2 0.27 0.54 375 376 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.27 0.54 375 376 0 1

--------------------------------------------------- 1 SORT AGGREGATE (cr=376 r=375 w=0 time=548486 us) 94016 INDEX RANGE SCAN OBJ#(48017) (cr=376 r=375 w=0 time=444504 us)(object id 48017)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

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

  SQL*Net message to client                       2        0.00
0.00
  db file sequential read                       375        0.03
0.29
  SQL*Net message from client                     2        5.31

5.31

Average wait time for a single 8K block was about 1 ms (0.29/375 ) . I do realize it's not quite apples-to-apples but surely a Symmetrics could do better than it did in your case.

2. On our HP machines with EMC Symmetrics arrays, we usually measure the 'avserv' time (in addition to a lot of other metrics). If the time exceeds approximately 10 ms, more often than not it's an indication of some problem with the array or the SAN. The command to get ten 'avserv' samples with an interval of one second is: 'sar -d 1 10'.

Rgds.

"Scott Watson" <nospam_at_hotmail.com> wrote in message news:%Xgnb.31720$Pt3.794791_at_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 - 18:33:02 CST