Sizing DBSPACETEMP

Any one have any suggestions on sizing the database temp spaces? What option for onstat or Unix command do you use to monitor db temp space? thanks in advance!! Val Valerie H. Webber Valerie.H.Webber@m1.irs.gov 704-569-1002 x107

It needs to be as big as it needs to be! Seriously, you need to have some knowledge of your applications. Are they liable to use many temporary tables, and are they likely to require many rows to be sorted before presentation to the client? As a rule of thumb, I generally configue dev. servers with around 50MBytes. Then, if users get "no space for sort" messages, we can review the application to see if it can be made more efficient, rather than relying on a larger space in production. onstat -d and onstat -D will allow you to see the amount of temp (and anyother) dbspace free at the time; and the total number of writes since stats zeroing respecively. Both can in their own way aid the cofiguration of its size. Neil Truby Londis Stores Hampton Hill, UK Webber Valerie H wrote in message <7pgu92$ro3$1@news.xmission.com>... > >Any one have any suggestions on sizing the database temp spaces? What option >for onstat or Unix command do you use to monitor db temp space? > >thanks in advance!! >Val > >Valerie H. Webber >Valerie.H.Webber@m1.irs.gov >704-569-1002 x107 >

In addition to Neil's reply on determining DBSPACETEMP sizing based on the use of temp tables, explicit and implicit, do not forget to take account of the temp table use that archives need. This can be calculated. The archive threads copy physical log records of pages modified since the beginning of the archive to a set of temp tables one for each dbspace on your system. The total used will depend on the IDS version. The versions before 7.30 use more space so I'll describe that and add a comment about how 7.30 reduces the requirement. Versions before 7.30 created one temp table for each dbspace living in one of the tempdb spaces and created them across all of the tempdb spaces round robin, ie the second dbspace's temp table in the second tempdb space and the third in the third etc. The tables are dumped to tape when the archive of the dbspace is completed but the temp table for that dbspace is not dropped until the entire archive is finished. Also since there may be more activity in some dbspaces than in others one tempdb space may fill up while there is still space left in the others and the archive will fail. For these reasons before 7.3x you want to have fewer larger tempdb spaces, one or two work best. Note that for sort efficiency you want more smaller tempdb spaces, at least three or four for best sort performance, so there is a problem here! In 7.30 the engine will fragment ALL of the temp tables built for the archive across ALL of the tempdb spaces and drops each one after it is dumped to tape. Therefore you need less temp space to begin with and the need to keep the number of tempdb spaces small disappears. "So!", you ask, "How much space?" Maximum space needed is one page for each page that might be modified during the duration of the archive. For 7.1x and 7.2x this is the number. For 7.3x wellllll, if your busiest dbspace is one of the first you can get away with much less, the number of pages modified before this one dbspace is archived. If the busiest is one of the last and the largest dbspace is first you will likely need close to the maximum. So say your archive never takes longer than 10 hours and during those overnight hours you average 100 10 page transactions per minute then you will need room for 600,000 pages of temp storage assuming no page is updated more than once (if some of those transactions modify the same pages as others then the need is smaller since a physical log record is generated only once per checkpoint period and the archive will ignore any additional ones created from checkpoint to checkpoint anyway). For 7.1x/7.2x, if you have more than one tempdb space, add some more to allow for the uneven temp table growth. Whew, that's enough. Art S. Kagel Webber Valerie H wrote: > > Any one have any suggestions on sizing the database temp spaces? What option > for onstat or Unix command do you use to monitor db temp space? > > thanks in advance!! > Val > > Valerie H. Webber > Valerie.H.Webber@m1.irs.gov > 704-569-1002 x107