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

Ann,

      You can also look for poorly writen queries using "DISTINCT" and lacking 
all the table relationships. Sometimes developers instead of correcting a 
cartesian product, simply add a DISTINCT clause to the query to hide the 
problem, and if the tables are large these will use a lot of temp space. These 
can be found using a explain plan and looking for full table scans, or by 
checking if all the tables that appear on the "FROM" clause have a relationship 
on the "WHERE" clause. Queries with large number of logical reads should be 
checked too.

     For me, for an OLTP database, to have about 10% of the database size as 
temp space  would be normal, more than that the application is likely to have a 
problem, either queries with cartesian products, lack of indexes, or large jobs 
that shoud be split into batches instead of processing the entire table at 
once. 

    On a datawarehouse I would expect the requirements to be larger, but I dont 
have much experience with this type of database.


    I worked in a place where the developer used a query account on the
production database to test his queries. He would first write a query
only with the "FROM" clause and no "WHERE" clause at all, and run it. After the
results came he would start the first "WHERE" clause. Of course we
started to get temp errors, and he requested that we increase the temp 
tablespace. I argued with him that I should actually reduce the temp space,
since it would make him more productive, as the query would error out
faster so he could fix it. Unfortunatelly Oracle has no temp tablespace quotas.

Regards,
Luis

--- On Mon, 10/27/08, Maris Elsins <elmaris@xxxxxxxxx> wrote:
From: Maris Elsins <elmaris@xxxxxxxxx>
Subject: Re: How to size, calculate the temporary tablespace based on database 
total size OR database growth?
To: ora-apps-dba@xxxxxxxxxxxxx
Date: Monday, October 27, 2008, 5:43 AM

Hello,

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 


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: