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

rohitk1973@yahoo.com (rohit) wrote:

>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
You may get better info with Autotrace:
-------------------------------------------------------------
SQL> set autotrace traceonly
SQL> select empl_nm from hr_cur_rptng where empl_nbr like '00%';

6052 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=8065 Bytes=
475835)

1 0 TABLE ACCESS (FULL) OF 'HR_CUR_RPTNG' (Cost=118 Card=8065
Bytes=475835)





Statistics
----------------------------------------------------------
442 recursive calls
39 db block gets
2308 consistent gets
1878 physical reads
0 redo size
397365 bytes sent via SQL*Net to client
49904 bytes received via SQL*Net from client
406 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
6052 rows processed

SQL> edit
Wrote file afiedt.buf

1* select empl_nm from hr_cur_rptng where empl_nbr= '0001111111' ( not real just wanted to show effect of using index)
SQL> /


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=118)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HR_CUR_RPTNG' (Cost=2 Ca
rd=2 Bytes=118)

2 1 INDEX (RANGE SCAN) OF 'I_HRCUR_EMPLID' (NON-UNIQUE) (Cos
t=1 Card=2)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
3 physical reads
0 redo size
415 bytes sent via SQL*Net to client
336 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
----------------------------------------------------------------------------------------------------------------------------------------------

It wil tell you how much improvement you get...

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@yahoo.com> wrote in message
news:74353ecc.0307161027.59d8c62c@posting.google.c om...
> 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

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@yahoo.com> wrote in message news:<zZmRa.119$Bq7.72@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@yahoo.com> wrote in message
> news:74353ecc.0307161027.59d8c62c@posting.google.c om...
> > 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

"rohit" <rohitk1973@yahoo.com> wrote in message
news:74353ecc.0307161027.59d8c62c@posting.google.c om...
> 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
******************************************

Very helpful, I learnt something. Thanks to you all!!

"Niall Litchfield" <niall.litchfield@dial.pipex.com> wrote in message news:<3f1c438b$0$15030$cc9e4d1f@news.dial.pipex.co m>...
> "rohit" <rohitk1973@yahoo.com> wrote in message
> news:74353ecc.0307161027.59d8c62c@posting.google.c om...
> > 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
> ******************************************