select * from (  select rownum num , a.*  from ( SELECT * FROM (  SELECT  rowset1.* FROM (
 SELECT
 contents_id, bbs_seq,    yn_msg, idx, re_seq,
 re_level, sort_seq, user_id, name, password,
 title, content, e_mail, phone1, phone2, phone3,
 input_dt, input_ip, read_cnt, file_cnt, board_kind,
 majorname, homepage, read_file, read_file_day, chk_schedule, gubun
 FROM HPGDM702
 WHERE rownum > 0
  AND bbs_seq = 414
 ORDER BY INPUT_DT ASC ) rowset1 START WITH ( RE_SEQ = 0
) CONNECT BY PRIOR IDX=RE_SEQ ORDER SIBLINGS BY INPUT_DT DESC
 )  ) a )
where 1 = 1
and num > 0
and num <= 10

쿼리는 위와 같습니다. 그런데 PLAN이 한쪽 서버에서는 (1초 걸림 - 풀스캔 1번)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   VIEW  (cr=4036 pr=0 pw=0 time=510527 us)
  49424    COUNT  (cr=4036 pr=0 pw=0 time=708207 us)
  49424     VIEW  (cr=4036 pr=0 pw=0 time=658780 us)
  49424      CONNECT BY NO FILTERING WITH START-WITH (cr=4036 pr=0 pw=0 time=658775 us)
  49456       VIEW  (cr=4036 pr=0 pw=0 time=294069 us)
  49456        SORT ORDER BY (cr=4036 pr=0 pw=0 time=244604 us)
  49456         COUNT  (cr=4036 pr=0 pw=0 time=148692 us)
  49456          FILTER  (cr=4036 pr=0 pw=0 time=99236 us)
  49456           TABLE ACCESS FULL HPGDM702 (cr=4036 pr=0 pw=0 time=99233 us)

이렇게 양호하게 나오는 반면 다른 서버에서는 (5초 걸림 - 풀스캔 3번)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   VIEW  (cr=36324 pr=0 pw=0 time=4815380 us)
  49424    COUNT  (cr=36324 pr=0 pw=0 time=5359020 us)
  49424     VIEW  (cr=36324 pr=0 pw=0 time=5359017 us)
  49424      CONNECT BY WITH FILTERING (cr=36324 pr=0 pw=0 time=5309588 us)
  25728       FILTER  (cr=4036 pr=0 pw=0 time=569911 us)
  49456        COUNT  (cr=4036 pr=0 pw=0 time=583329 us)
  49456         VIEW  (cr=4036 pr=0 pw=0 time=534338 us)
  49456          SORT ORDER BY (cr=4036 pr=0 pw=0 time=484582 us)
  49456           COUNT  (cr=4036 pr=0 pw=0 time=148688 us)
  49456            FILTER  (cr=4036 pr=0 pw=0 time=148687 us)
  49456             TABLE ACCESS FULL HPGDM702 (cr=4036 pr=0 pw=0 time=99226 us)
  23696       HASH JOIN  (cr=32288 pr=0 pw=0 time=3024895 us)
  49424        CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=486 us)
 395648        COUNT  (cr=32288 pr=0 pw=0 time=3833641 us)
 395648         VIEW  (cr=32288 pr=0 pw=0 time=3828086 us)
 395648          SORT ORDER BY (cr=32288 pr=0 pw=0 time=3432395 us)
 395648           COUNT  (cr=32288 pr=0 pw=0 time=1585557 us)
 395648            FILTER  (cr=32288 pr=0 pw=0 time=1189897 us)
 395648             TABLE ACCESS FULL HPGDM702 (cr=32288 pr=0 pw=0 time=794193 us)
      0       COUNT  (cr=0 pr=0 pw=0 time=0 us)
      0        VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0         SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)
      0          COUNT  (cr=0 pr=0 pw=0 time=0 us)
      0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0            TABLE ACCESS FULL HPGDM702 (cr=0 pr=0 pw=0 time=0 us)

이렇게 나오네요 -_-

플랜을 보면 connect by (with filtering / no filtering) 에 따라 차이가 있는 것 같은데요

테이블 건수도 양쪽 다 같으며 인덱스도 그렇고 양쪽 다 CBO에 특별히 환경적으로 틀린 것이 없는것 같구요

다만 위의 서버는 10.2.0.4 아래는 10.1.0.4 입니다만 이 영향은 아닐 것 같은데

고수님들 조언 부탁드립니다 ;;