Hello Scott,
Please ignore my previous message. I mis-placed the decimal point in the
tkprof output.
The Symmetrics timing seems to be fine, not 95 ms but rather 9.5 ms.
Rgds.
"Scott Watson" <nospam_at_hotmail.com> wrote in message
news:Nllnb.33335$Pt3.991245_at_weber.videotron.net...
> 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 Tue Oct 28 2003 - 09:24:47 CST
