Never ending activity in temp file

Recently started to rewrite slowly performing reports using some
analytic function stuff etc.
But now I'm stuck on a rather simple select (see below) that works as
expected without the final where flag = 1, and works forever (at least
for more than 14 hours) with the final where clause. Without this
clause it takes ~10 minutes.
V$session_longops shows long operation "Sort output" with constantly
increasing sofar value. Tracefile generates in big amounts and shows
constant information like
WAIT #1: nam='direct path read' ela= 171 p1=203 p2=451896 p3=1
WAIT #1: nam='direct path read' ela= 168 p1=203 p2=451897 p3=1
WAIT #1: nam='direct path read' ela= 167 p1=203 p2=451898 p3=1
WAIT #1: nam='direct path read' ela= 196 p1=203 p2=451899 p3=1
WAIT #1: nam='direct path read' ela= 170 p1=203 p2=451900 p3=1
I assume that it is tempfile, because db_files = 200 and there are 3
tempfiles on this box.

So the question is - have you ever encountered something like this? I
think it smells like a bug? Of course I can probably make a workaround
- create some global temporary table to store intermeddiate results
and select final grouping from it.

And one more thing the result with simple count is ~2.5M. When I
simply restrict it to some small number for example using rownum <
5000 in the first join it works as expected even with final where
clause.

System uses automatic work area size policy and pga_aggregate_target
is set to 600M. Overall memory is 4G.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

OS : AIX 5.2 Oslevel 04
HW : pSeries 650M2 (2 CPU 1,45 GHz POWER4+; 4 G RAM)

Yea and this is RAC.


The bad select follows
select count(*) FROM (
  SELECT 
      valsts, 
      decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA',
0, 1), 1) flag
    FROM (
      SELECT 
        rvpd_rrpr_id, 
        kvls_kods,
        valsts, 
        lag(rvpd_rrpr_id, 1, -1) over (order by rvpd_rrpr_id, rn) iepr_rrpr_id, 
        lag(kvls_kods, rn - 1, 'AAA') over (order by rvpd_rrpr_id, rn)
pirm_kvls_kods
      FROM ( 
        SELECT /*+ FULL (rpst1) FULL(rpst2) FULL(krfk) FULL(kvls) FULL(rvpd) 
                   INDEX_FFS(rdad) INDEX_FFS(rdzv) FULL(kadr)*/
          rvpd_rrpr_id, 
          kvls.kvls_kods,
          kvls.kvls_vp_nosaukums||' '||krfk_nozime_viriesiem valsts, 
          row_number() OVER (PARTITION BY rvpd_rrpr_id ORDER BY
decode(kvls.kvls_kods, 'LVA', 0, 1)) rn
        FROM ira_valst_piederibas rvpd
          ,kls_valstis kvls
          ,kls_ref_kodi krfk
          ,ira_pers_statusi rpst1
          ,ira_pers_statusi rpst2
          ,ira_dzivesvietas rdzv
          ,ira_dzv_adreses rdad
          ,kls_adreses kadr
          ,kls_valstis kvls1
        WHERE krfk_nosaukums = 'VALSTISKAS_PIEDERIBAS_VEIDS'
          AND krfk_min_vertiba = rvpd_krfk_min_vertiba
          AND kvls.kvls_id = rvpd_kvls_id
          AND rpst1.rpst_rrpr_id = rvpd_rrpr_id
          AND rpst1.rpst_veids = 2 AND rpst1.rpst_vertiba = 1
          AND rpst1.rpst_sakuma_datums <= sysdate AND
rpst1.rpst_beigu_datums >= sysdate
          AND rpst2.rpst_rrpr_id = rvpd_rrpr_id
          AND rpst2.rpst_veids = 3 AND rpst2.rpst_vertiba = 1
          AND rpst2.rpst_sakuma_datums <= sysdate AND
rpst2.rpst_beigu_datums >= sysdate
          AND rvpd_beigu_datums > sysdate
          AND rvpd_sakuma_datums <= sysdate
          AND rvpd_ir_nosledzoss = 0
          AND rdzv_rrpr_id = rvpd_rrpr_id
          AND rdzv_id = rdad_rdzv_id
          AND kadr_id = rdad_kadr_id
          AND kvls1.kvls_id = kadr_kvls_id
          AND rdzv_sakuma_datums <= sysdate
          AND rdzv_beigu_datums > sysdate
          AND rdad_ir_pamata = 1
          AND kvls1.kvls_icao_kods = 'LVA'
          -- with this rownum everything works even _with_ final where clause
          --AND rownum < 5000
      ) 
    )
  )
-- this is the final where clause. Without it everything works. With
it runs forever.
WHERE flag = 1  

Gints Plivna
--
http://www.freelists.org/webpage/oracle-l

Other related posts: