Re: SQL Tunning -- BIG difference in result

Allan, I do not consider myself a tuning expert, but I think I can see why you get the results you do. Your sub-select is a coordinated sub-query so it runs once for every row retrieved in the outer query. In the once case you are running it for every row in the table and the other your are running it only for those rows that are in department 10. Look at the difference in the number of disk reads.

I believe that you are missing statistics for some columns because statistics are not turned on at the database level in the init.ora file. Look in the Reference manual for a parameter like timed_statistics.

In article <01be61a5$d9418340$c5094f0c_at_syoffice.singstar.com>,   "Alan Tang" <nonfemett_at_worldnet.att.net> wrote:
> Hello there,
>
> Recently I was stuying SQL tuning and found the following two
> statements run at totally different speed with only a difference of
> one WHERE clause: deptno=10; the one with it runs almost 20 times
> faster than the one without this condition. The table has about
> 38000 rows and column "pinno" is unique. Here is the tkprof report
> (please maximize your email viewer):
>
> ***************************************************************************
> *****
>
> select pinno, name, sal, e.deptno from att e
> where sal > (select avg(sal) from att where e.deptno=deptno)
> and e.deptno=10
> and rownum<10
> order by pinno -- this one runs 1462 ms in sql/plus with timing on
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 0.00 0.00 762 763 7
> 9
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 0.00 0.00 762 763 7
> 9
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 20 (SCOTT)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 9 SORT (ORDER BY)
> 9 COUNT (STOPKEY)
> 17 FILTER
> 348 TABLE ACCESS (FULL) OF 'ATT'
> 2586 SORT (AGGREGATE)
> 38578 TABLE ACCESS (FULL) OF 'ATT'
>
> ***************************************************************************
> *****
>
> select pinno, name, sal, e.deptno from att e
> where sal>(select avg(sal) from att where e.deptno=deptno)
> and rownum<10 ------- without "deptno=10";
> order by pinno -- this one runs 27119 ms in sql/plus with timing on
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 0.00 0.00 19578 19605 107
> 9
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 0.00 0.00 19578 19605 107
> 9
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 20 (SCOTT)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 9 SORT (ORDER BY)
> 9 COUNT (STOPKEY)
> 26 FILTER
> 26 TABLE ACCESS (FULL) OF 'ATT'
> 66789 SORT (AGGREGATE)
> 1003028 TABLE ACCESS (FULL) OF 'ATT'
>
> ***************************************************************************
> *****
>
> They seem to have same execution plan but why such a big difference?
> And why are CPU and elapsed time showing zero?
> Could anyone give me a clue? Thanks a lot.
>
> Alan Tang
>
>

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Feb 27 1999 - 23:47:26 CET