Re: Use of TEMP tablespace other than sorting

Rick Denoire wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote:
>
> >I'd suggest a couple of things. The first is the you run explain plan to
> >see what is happening. The second is that you post the query and give us
> >some idea of how many rows are in the tables. At first blush it appears
> >that something is wrong. But without more background I wouldn't want to
> >speculate as to what.
> >
> >BTW: CBO or RBO and are statistics with DBMS_STATS current?
>
> Here it goes: (set fixed pitch font please)
>
> select vp.id, vv.gc_sample_version_id, vv.gc_proc_version_id,
> vv.gc_sample_id, s.gc_sequence_id,
> 351260229, ma.compound_id, a.met_chem_no,
> ma.dict_ms_match_id, pq.norm_rel_area,
> pq.rel_area, pq.norm_corr_rel_area
> from v_valid_peak vp,
> v_valid_sv_for_ev vv,
> mdb_val.t_ms_match ma,
> mdb_sys.t_gc_sample s,
> mdb_sys.t_analyte a,
> mdb_val.t_gc_peak_quant pq
> where vv.exp_version_id = 351260229 and
> vp.gc_sample_version_id = vv.gc_sample_version_id and
> s.id = vv.gc_sample_id and
> vp.id = ma.gc_peak_id and
> ma.dict_ms_match_id = 11 and
> vp.id = pq.id and
> ma.compound_id = a.id;
>
> SQL> @plan_temp_prob.sql
>
> OPERATION OPTIONS OBJECT_NAME ID PARENT_ID
> POSITION
> --------------- --------------- --------------- ---- ----------
> ----------
> INDEX RANGE SCAN PK_MS_MATCH 42 41
> 1
> SELECT STATEMEN 0
> 105
> T
>
> NESTED LOOPS 1 0
> 1
> NESTED LOOPS 2 1
> 1
> NESTED LOOPS 3 2
> 1
> NESTED LOOPS 4 3
> 1
> NESTED LOOPS 5 4
> 1
> HASH JOIN 6 5
> 1
> NESTED LOOPS 7 6
> 1
> NESTED LOOPS 8 7
> 1
> NESTED LOOPS 9 8
> 1
> NESTED LOOPS 10 9
> 1
> NESTED LOOPS 11 10
> 1
> NESTED LOOPS 12 11
> 1
> NESTED LOOPS 13 12
> 1
> NESTED LOOPS 14 13
> 1
> NESTED LOOPS 15 14
> 1
> TABLE ACCESS BY INDEX ROWID T_EXP_VERSION 16 15
> 1
> INDEX UNIQUE SCAN SYS_C0018898 17 16
> 1
> TABLE ACCESS BY INDEX ROWID T_EXP_VERSION_P 18 15
> 2
> V
>
> INDEX RANGE SCAN I_EXP_VERSION_P 19 18
> 1
> V_1
>
> TABLE ACCESS BY INDEX ROWID MV_LIMS_GC_SAMP 20 14
> 2
> LE
>
> INDEX RANGE SCAN I_LIMS_GC_SAMPL 21 20
> 1
> E_2
>
> TABLE ACCESS BY INDEX ROWID T_GC_SAMPLE_VER 22 13
> 2
> SION
>
> INDEX RANGE SCAN FK_GC_SAMPLE_VE 23 22
> 1
> RSION_1
>
> TABLE ACCESS BY INDEX ROWID T_VAL_SAMPLE_VE 24 12
> 2
> RSION
>
> INDEX RANGE SCAN FK_VAL_SAMPLE_V 25 24
> 1
> ERSION
>
> TABLE ACCESS BY INDEX ROWID T_GC_SAMPLE 26 11
> 2
> INDEX UNIQUE SCAN PK_GC_SAMPLE 27 26
> 1
> INDEX RANGE SCAN FK_GC_PEAK_1 28 10
> 2
> TABLE ACCESS BY INDEX ROWID T_VAL_PEAK 29 9
> 2
> INDEX RANGE SCAN FK_VAL_PEAK 30 29
> 1
> TABLE ACCESS BY INDEX ROWID T_GC_SAMPLE_VER 31 8
> 2
> SION
>
> INDEX UNIQUE SCAN PK_GC_SAMPLE_VE 32 31
> 1
> RSION_VAL
>
> TABLE ACCESS FULL T_ALL_PARAMS 33 7
> 2
> TABLE ACCESS FULL T_GC_SAMPLE_VER 34 6
> 2
> SION
>
> INDEX UNIQUE SCAN FK_GC_PEAK_1 35 5
> 2
> INDEX RANGE SCAN PK_MS_MATCH 36 4
> 2
> TABLE ACCESS BY INDEX ROWID T_GC_ANALYTE 37 3
> 2
> INDEX UNIQUE SCAN PK_GC_ANALYTE 38 37
> 1
> TABLE ACCESS BY INDEX ROWID T_GC_PEAK_QUANT 39 2
> 2
> INDEX UNIQUE SCAN PK_GC_PEAK_QUAN 40 39
> 1
> T
>
> INLIST ITERATOR 41 1
> 2
>
> SQL> @nr_rows.sql
>
> TABLE_NAME NUM_ROWS LAST_ANA
> ------------------------------ ---------- --------
> T_GC_PEAK_QUANT 11944095 12.01.03
> T_MS_MATCH 65717610 12.01.03
> T_GC_SAMPLE 148005 12.01.03
>
> SQL> spool query.txt
> SQL> select
> tablespace_name,contents,extent_management,initial_extent,allocation_type
> from dba_tablespaces where tablespace_name='TEMP';
>
> TABLESPACE_NAME CONTENTS EXTENT_MAN INITIAL_EXTENT
> ALLOCATIO
> ------------------------------ --------- ---------- --------------
> ---------
> TEMP TEMPORARY LOCAL 4194304
> UNIFORM
>
> SQL> select name,value from v$parameter where name in
> ('sort_area_size','hash_area_size');
>
> NAME
> ----------------------------------------------------------------
> VALUE
> --------------------------------------------------------------------------------
> sort_area_size
> 4194304
>
> hash_area_size
> 8388608
>
> The optimizer is set to choose.
>
> Regards
> Rick Denoire

Others are more qualified than I and I hope they weigh in with an opinion ... but this looks to me like a recipe for massive use of the temp tablespace.

You didn't post the cost. Are you sure you have current statistics created with DBMS_STATS? Daniel Morgan Received on Sat Mar 01 2003 - 01:01:27 CST