DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: 16GB Temp Tablespace not enough

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    16GB Temp Tablespace not enough

    The hash join in the following query is requiring more than 16GB of temp tablespace. All the tables/indexes are analyzed everyday and I am running Oracle 9.2.0.6 on Linux. Here is the query with explain plan.
    Code:
    SELECT COUNT(DISTINCT car.assessment_locator) AS assessments_mapped 
    FROM      CLG_ASSESSMENT_RESULT        car,      CLG_ITEM_RESULT              cir,      CLG_ITEM                     ci,      CLG_CORRELATION              corr,      CB_STANDARD                  cbs,   CLG_ASSESSMENT_INSTANCE cai,   CLG_USER u  
    WHERE        u.org_unit_id = :1        
    AND u.user_id = cai.assignor_id        
    AND car.ASSESSMENT_locator = :2       
    AND car.completed_date >= :3        
    AND car.completed_date <= :4        
    AND cir.ASSESSMENT_RESULT_ID = car.ASSESSMENT_RESULT_ID        
    AND (ci.LOCATOR = corr.CONTENT_ID OR ci.ancestor_locator = corr.content_id)        
    AND corr.ASSET_TYPE_ID = 113        
    AND corr.cbs_id = cbs.CBS_ID       
    AND ci.LOCATOR = cir.item_locator
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=1 Bytes=208 )
       1    0   SORT (GROUP BY)
       2    1     HASH JOIN (Cost=120 Card=6846 Bytes=1423968)
       3    2       MERGE JOIN (CARTESIAN) (Cost=107 Card=320 Bytes=64960)
       4    3         NESTED LOOPS (Cost=65 Card=1 Bytes=193)
       5    4           NESTED LOOPS (Cost=64 Card=1 Bytes=189)
       6    5             NESTED LOOPS (Cost=15 Card=1 Bytes=154)
       7    6               NESTED LOOPS (Cost=13 Card=1 Bytes=88)
       8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ASSESSMENT_RESULT' (Cost=10 Card=1 Bytes=45)
       9    8                   BITMAP CONVERSION (TO ROWIDS)
      10    9                     BITMAP AND
      11   10                       BITMAP CONVERSION (FROM ROWIDS)
      12   11                         SORT (ORDER BY)
      13   12                           INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_COMPDATE' (NON-UNIQUE) (Cost=2 Card=184)
      14   10                       BITMAP CONVERSION (FROM ROWIDS)
      15   14                         INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_ASMNTLOC' (NON-UNIQUE) (Cost=4 Card=184)
    
      16    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM_RESULT' (Cost=3 Card=12 Bytes=516)
    
      17   16                   INDEX (RANGE SCAN) OF 'PK_ITEM_RESULT' (UNIQUE) (Cost=2 Card=12)
    
      18    6               TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM' (Cost=2 Card=1 Bytes=66)
    
      19   18                 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)
    
      20    5             TABLE ACCESS (BY INDEX ROWID) OF 'CLG_CORRELATION' (Cost=64 Card=1 Bytes=35)
    
      21   20               BITMAP CONVERSION (TO ROWIDS)
      22   21                 BITMAP OR
      23   22                   BITMAP CONVERSION (FROM ROWIDS)
      24   23                     SORT (ORDER BY)
      25   24                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)
    
      26   22                   BITMAP CONVERSION (FROM ROWIDS)
      27   26                     SORT (ORDER BY)
      28   27                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)
    
      29    4           INDEX (UNIQUE SCAN) OF 'PK_CB_STANDARD' (UNIQUE)
      30    3         BUFFER (SORT) (Cost=107 Card=435 Bytes=4350)
      31   30           TABLE ACCESS (BY INDEX ROWID) OF 'CLG_USER' (Cost=42 Card=435 Bytes=4350)
    
      32   31             INDEX (RANGE SCAN) OF 'IDX_USER_ORGUNITID' (NON-UNIQUE) (Cost=2 Card=435)
    
      33    2       INDEX (FAST FULL SCAN) OF 'IDX_ASMNTINST_ASSIGNORID' (NON-UNIQUE) (Cost=9 Card=35353 Bytes=176765)
    Any suggestions/ideas on what I can do to improve this ?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    there was really no need to open a seprate thread for this.

    anyway, YOU need to compare test and prod to see where the differ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width