TEMP DBSPACE Usage:

Hi, IDS 11.10.FC3 on solaris 10. I have the DBSPACETEMP set to: DBSPACETEMP tempdbs1,tempdbs2 While running a SQL code which does a big selects with 'joins' and 'group by' on many columns and inserts the data in 3 temp tables. 2 out of these 3 temp tables hold where small data around 10M but the 3rd table hold data around 1GB. I find that the tempdbs2 is getting filled entirely when there is lots of free space in tempdbs1. I understand that due to round robin fragmentation strategy some how the Big temp table is getting created tempdbs2 only and causing us the problems. I dont want to change the code and make the big table reside in to tempdbs2. Other option would be "export DBSPACETEMP=tempdbs1" but then for all the tables created in that session would use just tempdbs1. And yes i can add more space to the tempdbs2 Is there any other way to do this? Question#2 Is it correct that for sorting operations the Sort Pool in Virtual segment of shared memeory will be used? Is there any scenario where the tempdbs would be used for Decision support queries? Regards Vikas

See my comments inline below: Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Sep 16, 2009 at 6:07 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hi, > > IDS 11.10.FC3 on solaris 10. > > I have the DBSPACETEMP set to: > > DBSPACETEMP tempdbs1,tempdbs2> > While running a SQL code which does a big selects with 'joins' and 'group > by' > on many columns and inserts the data in 3 temp tables. > > 2 out of these 3 temp tables hold where small data around 10M but the 3rd > table hold data around 1GB. > > I find that the tempdbs2 is getting filled entirely when there is lots of > free > space in tempdbs1. > > I understand that due to round robin fragmentation strategy some how the > Big > temp table is getting created tempdbs2 only and causing us the problems. > > I dont want to change the code and make the big table reside in to > tempdbs2. > You could fragment the three temp tables across the two temp dbspaces. > > Other option would be "export DBSPACETEMP=tempdbs1" but then for all the > tables created in that session would use just tempdbs1. > > And yes i can add more space to the tempdbs2 > > Is there any other way to do this? > > Question#2 > > Is it correct that for sorting operations the Sort Pool in Virtual segment > of > shared memeory will be used? Is there any scenario where the tempdbs would > be > used for Decision support queries? > That depends. If PDQPRIORITY is zero the DS_NONPDQ_QUERY_MEM controls the quantity of MGM memory used for sorting in memory and DS_TOTAL_MEMORY and DS_MAX_QUERIES controls the quantity of MGM memory that is available for sorting in memory when PDQPRIORITY is > 0 (DS_TOTAL_MEMORY / DS_MAX_QUERIES). If the amount of data exceeds these values the the sorts will spool sort-work files to disk keeping only the permitted amount of data in memory at any one time. This will require merging the sort-work files at the end of the sort operation. If PSORT_DBTEMP is set the sort-work files are written to the filesystem(s) listed there, otherwise they are written as unlogged temp files to the temp dbspaces in DBSPACETEMP. FYI, note that the default value for DS_NONPDQ_QUERY_MEM is 128(KB) which is REALLY tiny! I typically reset that to 51200 (50MB) on systems that I manage unless memory is an issue, then I set it to the old default for DBUPSPACE which was 15MB (DS_NONPDQ_QUERY_MEM overrides the older DBUPSPACE envirnoment setting). > > Regards > Vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0022158df8f77f28270473b3cc13