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

VC, Thanks for the info. I had observed similar results running a test on my laptop.

Sybrand, you have not read the explain plan correctly. The inner table of the NL join is the inline view ( I used a stop key to limit the number of rows in the inner table for my test). This in turn does a index range scan on the index (not the table) and then it will lookup the values using a rowid lookup. The outer table has 33million rows. What would you do to optimize this statement? Don't bother answering there is nothing you can do.

The problem is **not** the query statement. The statement is tuned. No histogram will make the query I provided any faster.

Scott Watson.

"VC" <boston103_at_hotmail.com> wrote in message news:2Pinb.44997$e01.106865_at_attbi_s02...
> Hello Scott,
>
> 1. 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 - 21:30:08 CST