Temp DB Space

How much temp db space is generally sufficient. I can understand it would depend on actual data size and the kind of queries, but is there a metric which can be used. My DB file size is 25 GB and there are lot of joins happening due to queries. What would be a good value for temp db space.

Gurpreet Straight joins would not normally need temp db space, only large sorts and temp table creation. Some features need logged temp space and some need unlogged (true temp) dbspace. Difficult to give recommendations without knowing your system, but I would suggest 2 x 2 Gb logged temp space and 2 x 2 Gb unlogged temp space to start with and see how it goes. You wil need to restart the engine after adding the space and modifying the onconfig file. Keith On 23 April 2014 12:23, GURPREET SACHDEVA <gusachde@cisco.com> wrote: > How much temp db space is generally sufficient. I can understand it would > depend on actual data size and the kind of queries, but is there a metric > which can be used. My DB file size is 25 GB and there are lot of joins > happening due to queries. > > What would be a good value for temp db space. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1136a95ab11da404f7b47077

For sorting efficiency you want to have between three and six temp dbspaces. Most joins do not require a temp table, but temp tables are generally partitioned across all of the temp dbspaces listed in DBSPACETEMP. Unless you have TEMPTABNOLOG set to 1 in your ONCONFIG file, you should have at least one low activity non-temp dbspace listed in DBSPACETEMP to hold logged temp tables so they are not written into the rootdb dbspace. As to how much space? Look at sysmaster:sysprofile for the size of the largest sort that went to disk to determine the space needed for sorting. For temp tables, you should get a better estimate from knowing your data. If you have large complex joins with GROUP BY clauses, joins involving views and other objects joined together, or ANSI style joins with filters in the WHERE clause; those are the queries that are most likely to create implicit temp tables. So you can run them under SET EXPLAIN to see if they do so and estimate the sizes of the intermediate and result sets. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 23, 2014 at 7:23 AM, GURPREET SACHDEVA <gusachde@cisco.com>wrote: > How much temp db space is generally sufficient. I can understand it would > depend on actual data size and the kind of queries, but is there a metric > which can be used. My DB file size is 25 GB and there are lot of joins > happening due to queries. > > What would be a good value for temp db space. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c36ca87093fc04f7b5f276