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

From: Will Handley <willhandley_at_comcast.net>
Date: 27 Nov 2002 18:48:42 -0800
Message-ID: <d208cf6e.0211271848.1d0da581@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;

Received on Wed Nov 27 2002 - 20:48:42 CST