Temporary tablespace concerns...

[smallpin]

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.

[pando]

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

[smallpin]

Thanks Pando, but what will be the impact of turning off autoextend? Will it affect performance in anyway?

Dee.

[marist89]

If you turn autoextend off, the users who are using all your TEMP space will start complaining because their queries fail.

[pando]

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

[smallpin]

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

[prakashs43]

i think u can consider creating some indexes based on the explain plan of a particular query .

[pando]

if it's DWH then 300MB is nothing

in our DWH we have like 15GB temp tablespaces

[smallpin]

Thanks guys, now I can sleep in peace.

Dee.