An oddity here is that your logical I/O count is in the
tens of thousands (from your earlier post), but the
optimizer is indicating 1M rows as the output from
the second table access. The two numbers could
only be consistent if you have a VERY large number
for 'buffer is pinned count' when the query runs. This
statistic is not reported by autotrace, but is a measure
of visits to buffers. Rerun the queries but take a
snapshot of the session's v$mystat values before
and after each run.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Adon Keber" <adon_at_cool.iskon.local> wrote in message
news:slrnbmdp2m.1pc.adon_at_cool.iskon.local...
> On Tue, 16 Sep 2003 13:08:25 +0300, Tanel Poder wrote:
> >Btw, is your query running parallel or serial?
>
> My first thought, but query is running in serial.
>
> And here are execution plans:
>
> Slower:
>
> Operation Object Name Rows
Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=ALL_ROWS 22
446
> SORT ORDER BY 22 1 K
446
> FILTER
> NESTED LOOPS 22 1 K
431
> TABLE ACCESS BY INDEX ROWID USERS 1 14
1
> INDEX UNIQUE SCAN USR_UK 1
1
> TABLE ACCESS BY INDEX ROWID PASTCONN 1 M 91 M
430
> INDEX RANGE SCAN PCONN_USR_FK_I 1 M
> INDEX RANGE SCAN PCD_I 2 12
3
>
>
> Faster:
>
> Operation Object Name Rows
Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=ALL_ROWS 22
31845
> FILTER
> NESTED LOOPS 22 1 K
31845
> TABLE ACCESS BY INDEX ROWID USERS 1 14
1
> INDEX UNIQUE SCAN USR_UK 1
1
> TABLE ACCESS BY INDEX ROWID PASTCONN 1 M 91 M
31844
> INDEX RANGE SCAN PCONN_CEND_I 1 M
3355
> INDEX RANGE SCAN PCD_I 2 12
3
>
>
>
> As I said...only one difference...
>
> a.
Received on Wed Sep 17 2003 - 15:18:11 CDT
