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

One can never tell what these optimizers are capable of, so I would not bet the inner query gets fully executed and counted. Whenever I want to get an idea of how much improvement I got, I usually get statistics for the following PL/SQL block:

DECLARE
    v_counter decimal (15,0) := 0;
BEGIN
    FOR REC IN
        (your query goes here)
    LOOP
        v_counter := v_counter + 1;
    END;     dbms_output.put_line(v_conter);
END; This way I make sure every line of the resultset will be obtained and fetched.

"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.
>
> My assumption here is the nested query would have to run to
> completition and then a select count(*) would happen. So, if the
> select count(*) query took longer with Plan1 than Plan2, then nested
> query for Plan1 would run longer than the same for Plan2.
>
> Question is, am I right in making this assumption.
>
> Thanks,
>
> Rohit
Received on Wed Jul 16 2003 - 20:29:03 CDT