Re: query tuning

rpr,

Read Richard's post more closely. v$sql_plan is one possible view which tells you sql plans ... One of those columns will tell you whether a full scan was in the explain plan.

Now, yes these sql's get aged out.

However, you should concentrate more on queries which generate a lot of I/O ... or in other words .. a lot of buffer gets/disk reads and strive to reduce that to a minimum.

statspack would help in your efforts.

Anurag

"rpr" <renu_p_r_at_yahoo.com> wrote in message news:71802af1.0311071444.6ca525c9_at_posting.google.com...
> Thank you Richard and Anurag for your help.
>
> v$sqlarea gives the sql text and optimizer mode, PARSE_CALLS,
> DISK_READS, BUFFER_GETS. Based on that I can get some info. However
> there is no info about full table scan etc. I understand that full
> table scan is not necessarily bad but I am trying to start from there
> in my tuning approach. Also I think v$sqlarea gets overwritten with
> time.
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:<XrMqb.916$aT.288_at_news-server.bigpond.net.au>...
> > "rpr" <renu_p_r_at_yahoo.com> wrote in message
> > news:71802af1.0311061555.634549fd_at_posting.google.com...
> > > Hello,
> > > Is there init param setting that will help me identify queries doing
> > > full table scans. Is there a way to filter these. say by tables. For
> > > example full table scan on small tables is fine. Thanks
> >
> > Hi rpr,
> >
> > You would also need to filter them based of selectivity as the CBO quite
> > correctly favours FTS if the "costs" of doing so are less than any available
> > indexes and the FTS can take advantage of only reading each block only the
> > once, of performing multiblock reads, of performing the access in parallel,
> > etc. The selectivity of the query in relation to the table is hence a vital
> > consideration.
> >
> > You would also need to identify queries that use indexes inappropriately as
> > such queries can often be far far far worse than their inefficient FTS
> > cousins.
> >
> > In short, what you really need to identify are inefficient queries. Period.
> >
> > And prior to 10g, there's really no parameter as such. Statspack though is
> > probably a good place to start and then look at documented views such as
> > v$sql, v$sqlarea, v$sqltext and v$sql_plan (closest to what you asked).
> >
> > Don't limit your search to just queries that perform FTS, they might
> > actually be your efficient ones ...
> >
> > Cheers
> >
> > Richard
Received on Fri Nov 07 2003 - 23:40:20 CST