Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unhappy Unanswered: TEMP tablespace efficient usage

    temp tablespace space is not reused efficiently. new sort process keep using new space. had to keep adding new space to it. after normal DB restart, temp tablespace high water mark not drop to 0. droped tablespace, re-created. it happened again. This is as displayed in DB-Studio. How do I fix this? Is there any query that I can from SQL/Plus to check HWM for TEMP tablespace? I know SQL for table HWM but not tablespace. Any help is appreciated.

    Thanks, Vinnie

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    temp tablespace use is not released to save on expensive extent allocation operations. Query v$sort_usage to get an idea of which sessions are allocating temp and not releasing the temp extents for other sessions.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    unless you are getting temp errors like this:
    PHP Code:
    20060915 1625 ORA-1652unable to extend temp segment by 256 in
     tablespace TEMP_01 
    then you are fine. temp will always look to be full but oracle will reuse those
    used blocks unless another process is using up all the sort area.
    Here is a query for looking up sorts (unknown author):
    PHP Code:
    set linesize 250
    col tablespace                 format a10
    col username                   format a8
    col osuser                     format a6
    col sid_pid                    format a10
    col machine                    format a10
    col sid                        format 9990
    col serial
    #                    format 99990
    col spid                       format a5

     compute sum label 
    'TOTAL' of blocks_mb on report;
     break 
    on report
    select t1
    .tablespace
    sumt1.blocks to_numbert3.value ) ) / 1024 1024 blocks_mb
    sumt1.extents tot_extents
    t2.username
    t2.osuser
    t2.status
    t2.SID ||','|| t2.serial# SID_PID
    t4.spid
    t2.sql_address
    t5.hash_value
    from v$sort_usage t1
    v$session t2
    v$parameter t3
    v$process t4
    v$sqltext t5
    where t1
    .SESSION_ADDR t2.SADDR
    and t3.name 'db_block_size'
    and (t2.PROCESS=t4.SPID or t2.paddr t4.addr)
    and 
    t2.sql_address t5.address
    and t5.piece 0
    group by
    t1
    .tablespace
    t2.username
    t2.osuser
    t2.machine
    t2.status
    t2.schemaname
    t2.program
    t2.SID ||','|| t2.serial#
    t4.spid
    t2.sql_address
    t5.hash_value
    order by 2

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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