Re: How to size, calculate the temporary tablespace based on database total size OR database growth?

Hello again,

sorry I sent an incomplete message. :)

TEMP space usage is not directly dependent on number of open cursors. You
can have a lot of cursors that use no TEMP space at all or you can have one
cursor that utilizes all the TEMP space. The main question here is what are
your DB sessions doing.

There are 2 typical cases when TEMP space is used:
1) sort operations that don't fit into PGA
2) storing data in temp tablespaces

You should investigate what's the cause of all temp space being used.
Quick google search gave me this select (by Jony Safi) (
http://www.oracle.com/technology/oramag/code/tips2004/110104.html)
it shows the temp space used by sort operations

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace
"Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in
MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size"
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;

and this one will show temp space usage for other activities.

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace
"Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in
MB", q.sql_text "SQL TEXT"
from v$tempseg_usage u, v$session s, v$sql q
where s.SADDR=u.SESSION_ADDR and q.ADDRESS=u.SQLADDR

When you've checked what's being stored in temp, you can work on sizing or
tuning the temp usage. e.g., if there are many sessions doing large sorts in
temps space, you may be looking into tuning the SQLs eating your temp space,
etc..


For your data warehouse loads, you can probably create a separate temp
tablespace and a separate DB user having the new tablespace as default temp
tablespace. This way it would not interfere with the other sessions.

best regards,
Maris


> On Sat, Oct 25, 2008 at 3:47 PM, Shastry(DBA) <shastry17@xxxxxxxxx> wrote:
>
>> Hello Gurus,
>>
>> How to size, calculate the temporary tablespace based on database total
>> size OR database growth?, One of our application support raised a tar on
>> high open cursor count and they inturn asked the DBA's to size appropriately
>> the TEMP tablespace. Could anyone please advice on this? I am mainly looking
>> out scenarios to handle the TEMP issues and one of the reason is high open
>> cursor count which is causing the performance issue.
>>
>> Also we have a dataware house database where they run data to load every 2
>> hours. Even their process got failed because of TEMP space unable to extend,
>> and had to restart the job post temp space addition. I have least idea on
>> how to size the TEMP space required for a particular activity.
>> Please let me know any links to refer as well( Metalink notes). Thanks in
>> anticipation.
>>
>> Thanks,
>> Ann
>>
>
>

Other related posts: