Re: Strange perf.


"astalavista" <nobody@xxxxxxxxxxx> wrote in message
news:46319a65$0$3633$426a74cc@xxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Query In BE database
    ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ... queries and that is remote queries. ...
    (microsoft.public.access.queries)
  • Re: Strange perf.
    ... HASH JOIN 72 6 K 132 ... REMOTE 30 K 2 M 72 CBDSRV_SRVCNS.WORLD SERIAL ... In the quick query the join is a hash join, ... will be executing about 30,000 queries ...
    (comp.databases.oracle.server)
  • Re: Query In BE database
    ... In the table is a list of all the queries that are available to be selected. ... When selected from the drop-down list box, I what the the query that is being ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: Query In BE database
    ... As additional queries are requested I ... All I would do is create the query, name it, and add the query name ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: Query In BE database
    ... I used VBA to grab the SQL statement of the query for the ... I have created several queries that they can run and output to MS Excel ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
Loading