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

On Mon, 27 Oct 2003 17:29:29 -0500, "Scott Watson" <nospam_at_hotmail.com> wrote:

>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
>
>

I would rather
a learn sql
b tune the statement
c make sure you have appropiate histograms

From the execution path it is quite clear that for every row returned from the inline view, you get an index range scan on the table, and you don't use the inline view at all.
You should revise the statement to transform the inline view in a proper where exists query.
What you see is what you get, and you should blame yourself, instead of the disks.

--
Sybrand Bakker, Senior Oracle DBA