Re: Use of TEMP tablespace other than sorting

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sat, 01 Mar 2003 03:00:46 +0100
Message-ID: <9m006vcij6ap1tr9qkaftv0ut2en97go9f@4ax.com>

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 Received on Fri Feb 28 2003 - 20:00:46 CST