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

Thanks Turk.

Issac:
I can follow your solution, but I am not sure what you mean by "statistics on PL/SQL block", do you mean time the block by setting timing on?

R

"Isaac Blank" <izblank_at_yahoo.com> wrote in message news:<zZmRa.119$Bq7.72_at_newssvr29.news.prodigy.com>...
> 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 Mon Jul 21 2003 - 10:17:14 CDT