Temporary dbspace
Many of my current systems have just one temporary dbspace. Is there any mileage to be gained by adding a second temporary dbspace on a multi-processor system but with only 2 disks which are mirrors of each other? Some reports and queries are not performing as well as they did when an earlier release of the application was running on SE and a zealous computer manager has suggested that we add more temp dbspace to improve performance. Needless to say I don't want to waste hos or my time. regards Malcolm
mweallans@panacea.co.uk wrote: > Many of my current systems have just one temporary dbspace. Is there > any mileage to be gained by adding a second temporary dbspace on a > multi-processor system but with only 2 disks which are mirrors of each > other? > > Some reports and queries are not performing as well as they did when an > earlier release of the application was running on SE and a zealous > computer manager has suggested that we add more temp dbspace to improve > performance. Needless to say I don't want to waste hos or my time. > > regards > > Malcolm > I always like 3 or more ... Sort algorithms like 3 or more places for their bits of sorting Creating temp tables across 3 dbspaces is easier for the engine than just one
Temp tables with no log are fragmented across the temp dbspaces as are other things (like sorting merge files, it seems like index builds and group by's use a merge sort algorithm). So I think it is a benefit to have more than one temp dbspace. I never thought of using an odd number of spaces though. I would be skeptical that 3 would be better than 4. I would of course agree if you told me 3 was better than 2. Skeptical of course means I reserve the right to say I agreed all along if given facts about why 3 is a magic number of dbspaces (3 in schoolhouse rock was a magic number). Since, disks are much slower than processors it seems like you should be able to service more than 1 tempdbs with 2 processors. So I am not sure what you were thinking about only one temp dbpace given that you have only 2 processors. We have our temp dbspaces on a ram disk, it seems improve latency and seek times ;-)
Hi Malcolm.
There is a "gotcha" here. I was involved with a single temp dbspace system (I
think you know which one!) which behaved differently if a second was added. To
illustrate, if you ran
create temp table temp_test (number integer);
insert into temp_test values (1);
insert into temp_test values (2);
insert into temp_test values (3);
insert into temp_test values (4);
select * from temp_test;
with one temp dbspace you got "1, 2, 3, 4", as you would expect, but with two
you got "1, 3, 2, 4", which totally confused the 4GL application! This is
presumably because it automatically striped across the two dbspaces.
--
Regards,
Doug Lawry
www.douglawry.webhop.org
<mweallans@panacea.co.uk> wrote in message
news:1131027585.032136.254600@o13g2000cwo.googlegroups.com...
> Many of my current systems have just one temporary dbspace. Is there
> any mileage to be gained by adding a second temporary dbspace on a
> multi-processor system but with only 2 disks which are mirrors of each
> other?
>
> Some reports and queries are not performing as well as they did when an
> earlier release of the application was running on SE and a zealous
> computer manager has suggested that we add more temp dbspace to improve
> performance. Needless to say I don't want to waste hos or my time.
>
> regards
>
> Malcolm
Doug Lawry wrote:
> Hi Malcolm.
>
> There is a "gotcha" here. I was involved with a single temp dbspace system (I
> think you know which one!) which behaved differently if a second was added. To
> illustrate, if you ran
>
> create temp table temp_test (number integer);With no log;
>
> insert into temp_test values (1);
> insert into temp_test values (2);
> insert into temp_test values (3);
> insert into temp_test values (4);>
> select * from temp_test;>
order is never guaranteed unless "order by" specified
> with one temp dbspace you got "1, 2, 3, 4", as you would expect, but with two
> you got "1, 3, 2, 4", which totally confused the 4GL application! This is
> presumably because it automatically striped across the two dbspaces.
>