| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Never ending activity in temp file
On Apr 12, 2005 2:33 PM, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
> If I were you I would rework the inner query. I am always uncomfortable at
> long lists of hints, and even if my not understanding what column and table
> names are about doesn't help :-)
Oh yea but the reason for hints was only to ensure that DB read all
table with full table scan or probably only index (for some others)
and to prevent sing nested loops, because it really needs to scan all
tables to get the result of join. Plan shows that generaly Oracle
reads tale one by one and performs ash joins, v$session_longops shows
the same thing.
BUT innrmost query isn't the bad one, the problem seems to be in some
sorting or temp space assignment or deficiency, because as you can see
below I created table innermost_join containing the result of join.
Then doing my superselect and increasing volume using rownum show that
time consumption isn't linear:
For 40k rows 0:54
50k 1:27 60k 2:19 70k 2:52
Who knows how much for 3M...
See below for details:
SQL> ed
Wrote file afiedt.buf
1 CREATE TABLE innermost_join as
2 SELECT /*+ FULL (rpst1) FULL(rpst2) FULL(krfk) FULL(kvls)
FULL(rvpd)
3 INDEX_FFS(rdad) INDEX_FFS(rdzv) FULL(kadr)*/ 4 rvpd_rrpr_id, 5 kvls.kvls_kods, 6 kvls.kvls_vp_nosaukums||' '||krfk_nozime_viriesiem valsts, 7 row_number() OVER (PARTITION BY rvpd_rrpr_id ORDER BY decode(kvls.kvls_kods, 'LVA', 0, 1) 8 FROM ira_valst_piederibas rvpd 9 ,kls_valstis kvls 10 ,kls_ref_kodi krfk 11 ,ira_pers_statusi rpst1 12 ,ira_pers_statusi rpst2 13 ,ira_dzivesvietas rdzv 14 ,ira_dzv_adreses rdad 15 ,kls_adreses kadr 16 ,kls_valstis kvls1 17 WHERE krfk_nosaukums = 'VALSTISKAS_PIEDERIBAS_VEIDS' 18 AND krfk_min_vertiba = rvpd_krfk_min_vertiba 19 AND kvls.kvls_id = rvpd_kvls_id 20 AND rpst1.rpst_rrpr_id = rvpd_rrpr_id 21 AND rpst1.rpst_veids = 2 AND rpst1.rpst_vertiba = 1 22 AND rpst1.rpst_sakuma_datums <= sysdate AND rpst1.rpst_beigu_datums >= sysdate 23 AND rpst2.rpst_rrpr_id = rvpd_rrpr_id 24 AND rpst2.rpst_veids = 3 AND rpst2.rpst_vertiba = 1 25 AND rpst2.rpst_sakuma_datums <= sysdate AND rpst2.rpst_beigu_datums >= sysdate 26 AND rvpd_beigu_datums > sysdate 27 AND rvpd_sakuma_datums <= sysdate 28 AND rvpd_ir_nosledzoss = 0 29 AND rdzv_rrpr_id = rvpd_rrpr_id 30 AND rdzv_id = rdad_rdzv_id 31 AND kadr_id = rdad_kadr_id 32 AND kvls1.kvls_id = kadr_kvls_id 33 AND rdzv_sakuma_datums <= sysdate 34 AND rdzv_beigu_datums > sysdate 35 AND rdad_ir_pamata = 1 36* AND kvls1.kvls_icao_kods = 'LVA'SQL> / Table created.
SQL> select count(*) from innermost_join;
COUNT(*)
2990577
SQL> ed
Wrote file afiedt.buf
1 select count(*) FROM (
2 SELECT
3 valsts, 4 decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA', 0, 1), 1) flag 5 FROM ( 6 SELECT 7 rvpd_rrpr_id, 8 kvls_kods, 9 valsts, 10 lag(rvpd_rrpr_id, 1, -1) over (order by rvpd_rrpr_id, rn) iepr_rrpr_id, 11 lag(kvls_kods, rn - 1, 'AAA') over (order by rvpd_rrpr_id, rn) pirm_kvls_kods 12 FROM innermost_join 13 WHERE rownum < 40000 14 )
32686
Elapsed: 00:00:54.03
SQL> select count(*) FROM (
2 SELECT
3 valsts, 4 decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA', 0, 1), 1) flag 5 FROM ( 6 SELECT 7 rvpd_rrpr_id, 8 kvls_kods, 9 valsts, 10 lag(rvpd_rrpr_id, 1, -1) over (order by rvpd_rrpr_id, rn) iepr_rrpr_id, 11 lag(kvls_kods, rn - 1, 'AAA') over (order by rvpd_rrpr_id, rn) pirm_kvls_kods 12 FROM innermost_join 13 WHERE rownum < 60000 14 )
COUNT(*)
48236
Elapsed: 00:02:19.04
SQL> select count(*) FROM (
2 SELECT
3 valsts, 4 decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA', 0, 1), 1) flag 5 FROM ( 6 SELECT 7 rvpd_rrpr_id, 8 kvls_kods, 9 valsts, 10 lag(rvpd_rrpr_id, 1, -1) over (order by rvpd_rrpr_id, rn) iepr_rrpr_id, 11 lag(kvls_kods, rn - 1, 'AAA') over (order by rvpd_rrpr_id, rn) pirm_kvls_kods 12 FROM innermost_join 13 WHERE rownum < 100000 14 )
COUNT(*)
79793
Elapsed: 00:05:57.03
SQL> select count(*) FROM (
2 SELECT
3 valsts, 4 decode(rvpd_rrpr_id, iepr_rrpr_id, decode(pirm_kvls_kods, 'LVA', 0, 1), 1) flag 5 FROM ( 6 SELECT 7 rvpd_rrpr_id, 8 kvls_kods, 9 valsts, 10 lag(rvpd_rrpr_id, 1, -1) over (order by rvpd_rrpr_id, rn) iepr_rrpr_id, 11 lag(kvls_kods, rn - 1, 'AAA') over (order by rvpd_rrpr_id, rn) pirm_kvls_kods 12 FROM innermost_join 13 WHERE rownum < 140000 14 )
COUNT(*)
110414
Elapsed: 00:11:55.09
SQL>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 12 2005 - 08:37:37 CDT
 |
 |