Never ending activity in temp file

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Tue, 12 Apr 2005 13:01:35 +0300
Message-ID: <6e49b6d005041203015fbf7d75@mail.gmail.com>

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

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