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

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

From the tkprof summary, it seems the entire query took about 3 seconds, of which 0.5 second was spent on the nested loop join (including preparing the inline view and the index range scan), and 2.5 seconds were spent on the final table access by index rowid. Are you saying we should concentrate on optimizing the join, i.e. optimizing the step that took 0.5 second?

• Dave

"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message news:00srpvkpkhuguv7a0mq2nfv9al9r178toi_at_4ax.com...
> On Mon, 27 Oct 2003 22:30:08 -0500, "Scott Watson"
> <nospam_at_hotmail.com> wrote:
>
> >
> >The problem is **not** the query statement. The statement is tuned.
> >No histogram will make the query I provided any faster.
>
> You are wrong. The statement as it is doesn't make sense. You should,
> as I already pointed out, turn the inline view into a simple exists
> subquery. Problably you don't know the difference between inline views
> and subqueries, but that's your problem.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Tue Oct 28 2003 - 16:31:05 CST