Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Never ending activity in temp file

Never ending activity in temp file

From: Gints Plivna <>
Date: Tue, 12 Apr 2005 13:01:35 +0300
Message-ID: <>

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;


Oracle9i Enterprise Edition Release - 64bit Production PL/SQL Release - Production
CORE Production
TNS for IBM/AIX RISC System/6000: Version - Production NLSRTL Version - 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 (

      decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA',
0, 1), 1) flag

    FROM (

        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)
      FROM ( 
        SELECT /*+ FULL (rpst1) FULL(rpst2) FULL(krfk) FULL(kvls) FULL(rvpd) 
                   INDEX_FFS(rdad) INDEX_FFS(rdzv) FULL(kadr)*/
          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

Received on Tue Apr 12 2005 - 06:05:34 CDT

Original text of this message