Temp Tablespace Usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Temp Tablespace Usage

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

    Temp Tablespace Usage

    I was told that when a particular query is run in production, it is erroring out without space in temp tablespace (temp tablespace already has 16 GB). I then had production data copied into a different envrionment and when I run the same query, I see that it is using up only 9MB of temp space (the blocks column in V$TEMPSEG_USAGE shows value 1152). Am I seeing anything wrong here ?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    wy not trace the query in production, see what is actually happening

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    I have found the query whose Hash Join is asking for more than 16GB of temp tablespace. Here is the query in with the explain plan. The tables are all analyzed everyday and I am running Oracle 9.2.0.6 on Linux.
    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)

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    Is the explain plan the same in the "different" environment? Are the init parameters different, any hardware differences, etc?

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    The query and explain plan are from production.

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    I understand. But if you would like answers to your questions, then maybe you should answer the questions we ask you.

  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    I am going to rephrase my original question in a new thread as it is confusing. So closing this one.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The cost and card values shown in the execution plan are not NOT very high. Hence, no need to fill out TEMP tablespace.

    Are you sure that tables and indexes are analyzed with proper estimate?

    Take a extended trace data for this query. It will show you the actual temp usage.

    Tamil

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Tamil,
    I am doing compute stats, not estimate.
    I say that this query is using 16GB of temp space by looking at V$TEMPSEG_USAGE and v$sql.
    I do not have the extended trace data. Will work on getting that.

    Thanks.

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