Re: query tuning

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 - 16:44:02 CST