Re: Strange perf.

"astalavista" <nobody_at_nowhere.com> wrote in message news:46319a65$0$3633$426a74cc_at_news.free.fr...
> Hi,
>
> I have something weird with a query :
> with 2 columns => 25 s
> with * => less 1 s
>
> how do you explain that ?
>
> Thanks for your lights
>
>
> select idper, idetot
> from vue_idetot_per
> where idper in (select idperinf from prjexr)
> order by idetot
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 72 146
> SORT ORDER BY 72 6 K 146
> HASH JOIN 72 6 K 132
> VIEW SYS.VW_NSO_1 84 1 K 59
> SORT UNIQUE 84 504 59
> TABLE ACCESS FULL ZA.PRJEXR 6 K 38 K 20
> NESTED LOOPS 30 K 2 M 72
> REMOTE 30 K 2 M 72 CBDSRV_SRVCNS.WORLD SERIAL
> REMOTE 8 48 1 CBDSRV_SRVCNS.WORLD SERIAL
>
>
> select *
> from vue_idetot_per
> where idper in (select idperinf from prjexr)
> order by idetot
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 72 148
> SORT ORDER BY 72 8 K 148
> HASH JOIN 72 8 K 134
> VIEW SYS.VW_NSO_1 84 1 K 59
> SORT UNIQUE 84 504 59
> TABLE ACCESS FULL ZA.PRJEXR 6 K 38 K 20
> HASH JOIN 30 K 3 M 74
> REMOTE 8 64 1 CBDSRV_SRVCNS.WORLD SERIAL
> REMOTE 30 K 2 M 72 CBDSRV_SRVCNS.WORLD SERIAL
>
>

Your vue_idetot_per seems to be
a join of two remote tables, which
isn't executing as a remote join for
some reason.

In the quick query the join is a hash join, which means you haul the "small" data set across the db_link, then haul the "large" data set across the link once each

In the slow query, the join is a nested loop join, which means for each row in the first set, you execute a remote query to get
matching row in the second set. If the
optimizer's stats are right in this case, you will be executing about 30,000 queries
to the remote database - which could be
enough to account for 25s of network time.

1. When looking at execution plans, use dbms_xplan so that you get all the useful information out
2. When testing, start a session, run the query, then query v$session_event to see what events you were waiting on. (and v$mystat to see how much work you did). My guess is that you will see lots of time spent waiting on 'SQL*Net message from dblink'

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html