Temp tablespace constantly blowing out
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Temp tablespace constantly blowing out

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    94

    Angry Temp tablespace constantly blowing out

    We have a 2GB temp tablespace and I'm trying to find out what user is running the offending sort. Are there any scripts out there to find what this out.? We have added more space to the tablespace to 4GB and it blew out again. We have a 2mb sort area size and 2mb retained size.
    Initial and next on the temp tablespace is 2mb.

    any help would be grateful.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Code:
    col sid format 999999
    col tablespace format a10
    col username format a25
    col noexts format 9999 head EXTS
    col proginfo format a30 trunc
    col mbused format 999,999.90
    set verify off
    select s.sid,s.serial#,
    s.sql_hash_value sesshash,
    u.SQLHASH sorthash,
    s.username,
    u.tablespace,
    sum(u.blocks*p.value/1024/1024) mbused ,
    sum(u.extents) noexts,
    u.segtype,
    s.module || ' - ' || s.program proginfo
    from v$sort_usage u, v$session s, v$parameter p
    where u.session_addr = s.saddr
    and p.name = 'db_block_size'
    group by sid,serial#,sql_hash_value,sqlhash,s.username,tablespace,
    u.segtype,
    s.module || ' - ' || s.program
    order by 6;
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    And also tell all oracle users to set their session sort_area_size=32MB beforing they executed sql's that do heavy sorting operation as this would reduce the temp tablespace usage and imporve the I/O

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    Re: Temp tablespace constantly blowing out

    Originally posted by rgaskell
    We have a 2GB temp tablespace and I'm trying to find out what user is running the offending sort. Are there any scripts out there to find what this out.? We have added more space to the tablespace to 4GB and it blew out again. We have a 2mb sort area size and 2mb retained size.
    Initial and next on the temp tablespace is 2mb.

    any help would be grateful.
    I'm also experienceing the same problem on 8i(8173)/AIX, and found that its bug. Not sure whats your platform and version of oracle.
    Reddy,Sam

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by anandkl
    And also tell all oracle users to set their session sort_area_size=32MB beforing they executed sql's that do heavy sorting operation as this would reduce the temp tablespace usage and imporve the I/O

    regards
    anandkl
    Correct me if I am wrong but isn't this a really bad idea? I would think that if every user used 32MB for sorts it would wreak havok with you server RAM and/or SGA, no?


    Also, does the transaction fail? Often TEMP will look to be 100% used but it is recycled. Your "used" percent will not free up until you re-boot.

    Tech the damn developers to use a commit in their SQL, or just shoot them!!



    MH
    I remember when this place was cool.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    rgaskell,

    As long as your end users are NOT making any complaint, you are ok.
    The "TEMP Tablespace" blow out problem is on the every oracle instance. Even if you add another 20 GB space, you will still find it again becuase of the SQL statement executed by the application that does cartesian product of 2 big tables.

    You need to talk to the developers/end users to correct the SQL statement.

    Tamil

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Mr.Hanky
    Tech the damn developers to use a commit in their SQL, or just shoot them!!

    MH
    Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by abhaysk
    Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS?
    That's easy to explain: it won't solve the problem, therefore Mr H. will have a pretext for shooting them

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Mr.Hanky,
    I dont think all the users would be using the full 32 MB all the time,So i most of the time recommend my users to use this only when they are runing heavy SQL with sorting operations.

    regards
    anandkl
    anandkl

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    That's easy to explain: it won't solve the problem, therefore Mr H. will have a pretext for shooting them
    Yeah thas right, then whom shud we shoot now?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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