Re: Why does adding simple grouping slow dblink query to a crawl?

willhandley_at_comcast.net (Will Handley) wrote in message news:<d208cf6e.0211271848.1d0da581_at_posting.google.com>...
> I know there are problems with a DBLINK, but this query should'nt have
> them. Yet, while it runs instantly on the machine where the data
> resides, it takes over 2 hours when going through a dblink.
>
> The inner select is where all of the heavy work takes place, multiple
> joins, grouping, counting, etc. Yet, if I run the inner query alone
> through the dblink, it comes back as quickly as when run on the remote
> machine. All of the tables reside on the remote database-I think
> Oracle is smart enough to run the query there. BUT, and here is the
> puzzling part, when I include the outer select, it takes hours to run.
> Yet, the outer select is merely grouping the few hundred rows returned
> by the complex inner select, so you wouldn't
> think adding it would have such a detremental effect. I've tried all
> combinations of hints, to no avail.
>
> So the question is, why does the addition of this simple outer select
> have such an effect when going through a dblink? Oracle 8.1.7, 64-bit
> servers, same versions on both servers.
>
>
> SELECT sys_user_id, vote_rqmt_cd, crer_mgmt_ctrl_cd,
> count(CASE WHEN TRUNC(vote_date)=TRUNC(SYSDATE) THEN vote_date End)
> as votes_today,
> count(vote_date) as total_votes
> FROM (
> SELECT DISTINCT v.sys_user_id,
> c.vote_rqmt_cd,
> c.crer_mgmt_ctrl_cd,
> c.cand_xref_seq_nr,
> max(v.bd_mbr_vote_dtm) as vote_date
> FROM os3_vote_t v,
> os3_candidate_t c,
> os3_board_results_t b,
> os3_voter_sel_bd_assoc_t a
> WHERE v.sel_bd_id = ? and
> v.sys_user_id = a.sys_user_id and
> v.sel_bd_id = a.sel_bd_id and
> v.sel_bd_id = c.sel_bd_id and
> v.cand_seq_nr = c.cand_seq_nr and
> c.cand_cons_stat_cd <> 'D' and
> (c.vote_rqmt_cd <> v.vote_rqmt_cd OR
> v.vote_rqmt_cd IS NULL ) and
> DECODE(c.vote_rqmt_cd,'H',DECODE(TO_CHAR(v.hard_vote_rtg_nr),NULL,'Z','X'),'S',DECODE(v.screen_vote_cd,NULL,'Z','X'),'Z')
> = 'X' and "+
> c.sel_bd_id = b.sel_bd_id and
> c.cand_seq_nr = b.cand_seq_nr and
> (b.bd_action_cd not in ('M','N') or b.bd_action_cd IS
> NULL)
> GROUP BY v.sys_user_id,
> c.vote_rqmt_cd,
> c.crer_mgmt_ctrl_cd,
> c.cand_xref_seq_nr
> HAVING COUNT(c.cand_xref_seq_nr) = (SELECT count(*)
> FROM os3_candidate_t c1, os3_board_results_t b1
> WHERE c1.sel_bd_id = ? and
> c1.cand_xref_seq_nr = c.cand_xref_seq_nr
> and
> c1.cand_cons_stat_cd <> 'D' and
> c1.sel_bd_id = b1.sel_bd_id and
> c1.cand_seq_nr = b1.cand_seq_nr and
> (b1.bd_action_cd <> 'N' OR b1.bd_action_cd
> is null)
> GROUP BY c1.crer_mgmt_ctrl_cd ))
> GROUP BY crer_mgmt_ctrl_cd,
> sys_user_id,
> vote_rqmt_cd;

Will, since I do not understand your data I am offering only general advise and observations:

Run an explain plan on your queries and you should be able to see the answer to your question. When working with remote objects the CBO has less information than when working with a local object. I wouldn't be surprised to see the remote obect being full table scaned in a loop.

Remember that a group by clause generally requires a sort and before Oracle can sort data it usually has to get all related data so that it can group it properly. Adding another group by to your original query therefore completely changed the query and the new plan my be radically different that the old access plan.

When dealing with remote queries you often convert joins from a local table to a remote table into coordinated subqueries where each fetch accross the link is a keyed query.

Getting Oracle to full scan the remote object and then hash join to it might also be an option.

If the local tables are all small and the remote table is large and is the best driving table then the driving_site hint (check manual for spelling) might be an option to get Oracle to run the query at the remote site.

In general converting a local SQL statement to a distributed SQL statement requires tuning it differently than tuning it for all local objects.

Note - having clauses are a potential source of slowing the query down. Check to see if any of the having clause requirements can be pushed up into the where clause to eliminate rows from the equation.

HTH -- Mark D Powell -- Received on Thu Nov 28 2002 - 11:32:23 CST