Re: Tempspace planning
Your vendor recommendation seems to be valid for the cases where you have JBOD configuration (Just a Bunch Of Disks) as opposed to a RAID. This way effectively stripe your data across all disks, which allows the database manager to initiate parellel reads and writes.
[SIZE=1]Originally posted by bspiegel00
We've been using two TEMPSPACEs in our previous production environments, each using a separate array with a couple of containers each. I've been reading a lot about trying to use a number of TEMPSPACE containers equal to prefetch size/extent size, which is 25, per our vendor's recommendation.
Given the use of RAID 5, is it worthwhile to build a TEMPSPACE with 25 containers on the same RAID array?
With RAID you already have your data striped and you probably wouldn't want the database to mess with it.
Theoretically, I think that having JBODs for temporary spaces would be more efficient than RAID-5 since in that case you wouldn't have any overhead from parity calculation and update. It may seem insignificant but, given the nature of temp space usage, could have noticeable impact on performance. If your RAIDs allow it, you could also reconfigure them without parity (RAID-4).
People say you would want to have a separate temporary tablespace for each distinct pagesize used in data tablespaces. Say, you have a 4K TEMPSPACE (because it's a requirement to accomodate catalog tablespace, which always uses 4K pages). If you also have 16K page tablespace(s) for your data you would also define a 16K-page TEMPSPACE. I don't think there would be any benefit from additional TEMPSPACEs.
Likewise, is there value to having a second TEMPSPACE with 25 more containers on one of the other arrays?
And would there be additional value to adding a third or fourth similar TEMPSPACE area?
For what it's worth...
"It does not work" is not a valid problem statement.