Re: How to figure out how many IOs a SQL costs ?

Hi Bass,

I think the reason why such statistics are not readily available is because it's not terribly meaningful when viewed from an individual SQL perspective. There are just too many variables. The exact same (as opposed to an exact different) SQL executed at different times could have vastly different I/O characteristics. I find the physical statistics useful not so much from a tuning perspective but from a "oh I see why it ran faster this time" perspective.

However, I must say a "logical" I/O statistic I think would be a nice thing to see (rather than the block level stats). Then the multiblock factor considered by the CBO would be more intuitive and comparisons between statistics more meaningful (execution plan A read more blocks but did so more efficiently than plan B).

Cheers

Richard

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:aolqne$d9b$2$8302bc10_at_news.demon.co.uk...
> Your comment about blocks and I/Os is correct -
>
> If you want the number of multiblock reads and single block reads
> then you could try looking at snapshots of v$session_event for
> the session in question - I haven't checked how good the correlation
> is, but in principle the physical reads (adjusted for direct reads) from
> v$sesstat should be represented by the 'db file scattered read' and
> ''db file sequential read' waits. There are also waits for direct reads
> and writes - which should match the v$sesstat figures IF you can
> adjust for the multi-block i/o size; but since that can vary from
> query to query, and depends on operation type, these is likely to
> be little obvious correlation.
>
>
> However, trying to get some 'total I/O' count is a bit difficult because
> DBWR and LGWR write in their own time, which need have very little
> to do with your session's activity on the system.
>
> DBWR will write the blocks you have updated only when
> there is pressure to clean some blocks.
>
> LGWR may write some redo that you generate because
> another user has committed - conversely, the redo written
> when you commit could have been generated largely by
> another user.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> Bass Chorng wrote in message ...
> >
> > I might be wrong here, but isn't v$sess_io and tkprof all showing
> > # of blocks, not # of IOs ? I am interested to know # of IOs, not
> > blocks, because blocks could be a combination of sequential read
> > and scattered read and the # of block/ per IO could be differnt.
> >
>
>
>
>
>
Received on Thu Oct 17 2002 - 08:39:04 CDT