Temporary tablespace concerns...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Temporary tablespace concerns...

  1. #1
    Join Date
    Feb 2001
    Posts
    39

    Red face

    Hi All,
    I was wondering why my temporary tablespace is growing so fast. Is there anything I can do about it? I think our application is opening a lot of cursors and that's causing the problem. Is there a way to free up the space on the temp tablespace? At the moment the size has grown to about 300 Megs.

    Thanks,
    Dee.
    God Bless

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well turn the autoextend off then

    cursor does not cause temp tablespace to grow, it's your SQL statements that cause them with ORDER BY, GROUP BY or MERGE JOINs

  3. #3
    Join Date
    Feb 2001
    Posts
    39
    Thanks Pando, but what will be the impact of turning off autoextend? Will it affect performance in anyway?

    Dee.
    God Bless

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you turn autoextend off, the users who are using all your TEMP space will start complaining because their queries fail.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    but then again considering 300mb for a temp tablespace is not much anyways

    and you have not said what kind of database is it, oltp or dwh

  6. #6
    Join Date
    Feb 2001
    Posts
    39
    That's what I thought. I can't have our developers breath down my neck. By the way, I am not running an OLTP, it more or less a DWH. I am not used to seeing that much growth on the TEMP tablespace, but they are developing a GUI tool that I think does a lot of order by and sorting. Is there anything I should do or should be aware of at this point?

    Thanks,
    Dee
    God Bless

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    i think u can consider creating some indexes based on the explain plan of a particular query .
    siva prakash
    DBA

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if it's DWH then 300MB is nothing

    in our DWH we have like 15GB temp tablespaces

  9. #9
    Join Date
    Feb 2001
    Posts
    39
    Thanks guys, now I can sleep in peace.

    Dee.
    God Bless

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