Re: count(*) to get timing on tuned SQL

"rohit" <rohitk1973_at_yahoo.com> wrote in message news:74353ecc.0307161027.59d8c62c_at_posting.google.com...
> One of the things I try while tuning is to do a count(*) over the
> tuned SQL, this saves me the full result dump, and doesnt lose the
> query execution timing while doing IO. But I am not very sure if
> thats a right thing to do.
> For example to get timing on a query like--
>
> select /*+ USE_HASH(t2) */
> from t1,t2
> where t1.a = t2.a
> and t2.b = 'xyz';
>
> I would run a SQL like:
>
> select count(*) from
> (select /*+ USE_HASH(t2) */
> from t1,t2
> where t1.a = t2.a
> and t2.b = 'xyz');
>
> and I may try different combinations of hints that look good in
> explain plan outputs.

I'd definitely look at the explain, count(*) often will favour index range scans over he actual access plan for the query.

Another thing you can try is to replace

select col1,col2.....
from
where......

with

select AGGREGATE_FUNCTION(col2)
from
....

where AGGREGATE_FUNCTION is an aggregate function (doh!) other than count(*) on an unindexed colum AVG is quite good because it rarely changes execution plans.

This will eliminate returning the large resultset (thus eliminating display time) but usually give the same access path as the actual query.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************