Thanks mister Jon ...
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de
news: pI-dnVsfS-EJAazbnZ2dnUVZ8qKvnZ2d_at_bt.com...
>
> "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.
>
> a) When looking at execution plans, use
> dbms_xplan so that you get all the useful
> information out
>
> b) 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
>
>
Received on Fri Apr 27 2007 - 01:50:28 CDT
