Re: query tuning

"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 - 06:43:35 CST