Sign in or 
Location: Performance Tuning
Discussion: Multiple temp files
Category:
Discussion Forum /
General Discussion
Share this
Showing 6 posts
|
redferrariteam |
Multiple temp files
Dec 27 2007, 5:58 AM EST
Hi Experts,We have a four disk solaris production server without RAID configuration. Many SQL queries of the application would perform huge sort operations. In order to use the available I/O bandwidth optimally we created 3 temp files on different disks for our TEMP tablespace. While monitoring the I/O performance we realized that only one tempfile is used extensively though the data being sorted is too huge compared to the extent size. In case of data files(multiple datafiles per TS) Oracle would allocate extents in a round ronbin fashion. Would like to know why the same seems to be not happening in our case. Any pointers? 2 out of 13 found this valuable. Do you?
Keyword tags:
Oracle Performance Tuning
Oracle Tuning
Performance Tuning
PT
Tuning
|
HayrolR |
1. RE: Multiple temp files
Dec 28 2007, 7:46 PM EST
"In case of data files(multiple datafiles per TS) Oracle would allocate extents in a round ronbin fashion" <--- this is a "would" comming from you not from Oracle. So, you have nothing to do with performance when speaking about multiple datafile per TS, and it's worst when you refer to TEMP TS.TS and datafiles per each don't have a significant influence about PERFORMANCE, and if you refer to raw I/O performance then... neither you will see an improvement in it. If you think about performance, then you can spread out the I/O load by placing your datafiles on diferents disk units (as you are trying), no matter if multiples files per TS or not, and of course, better if you use some RAID striping. It's a common practice to use one datafile per TS in a large DB, of course you need to keep in mind the Oracle max datafile size (acording to your block size, 8K = max of 32GB, etc) and the OS max file size. Also, if you have too much datafiles (in a large DB) you need to worry about checkpoint process, it's longer acording to your numbers of datafiles. Everything can be aplied to the TEMP TS and the only important thing with a TS with multiple datafiles is a data recovery ... and I can bet that you don't think a seriuos recovery for your TEMP TS. 1 out of 7 found this valuable. Do you? |
wongyc2k |
2. RE: Multiple temp files
Dec 31 2007, 9:51 AM EST
Unless you are using temporary tablespace groups (Oracle10g feature), the database will not be writing to multiple temp data files (from different temp tablespaces) simultaneously. With a single temp tablespace the database will write to one data file at a time. Without RAID you are doing the right thing in trying to spread the IO load around using multiple data files to separate disk, however, the benefits wont necessarily be as great as you might think if the setup is not maintained. Advice: Use RAID, much easier, greater benefits and will protect against disk loss.HayrolIR, I'm not fully understanding what you are saying, but in a VLDB, the use of a single datafile is more for ease of administration having to deal with less data files. The gains from reduced checkpoints to more files is marginal in such environments and in using a single data file (large file tablespace) the underlying file system MUST be using RAID to spread the IO load to different spindles. Otherwise, IO would be a major issue. The temporary tablespace is not recovered or restored (it's simply rebuilt following such operations) so that doesn't really matter, but I will agree that multiple data files in a tablespace may give more 'uptime' and recovery options. 0 out of 4 found this valuable. Do you? |
|
HayrolR |
3. RE: Multiple temp files
Dec 31 2007, 12:15 PM EST
wongyc2k, I agree (and I said that alreay) with you about the RAID use (and everyone in the world), and about the new TEMP TS group feature, by the way, not used for me right now. As you said: using Temporary tablespace group is the only way to have multiple datafiles receiving temp data, of course, each tablespace in the group has one (or more) datafile.And about the checkpoints, I think you misunderstand me, what I mean is that when a checkpoint occurs (I'm not trying to reduce them), the database writes changed blocks in the buffer cache to their datafiles, this process also updates all datafiles with the latest SCN of the checkpoint, even if that file had no changed blocks, so the more datafiles you have, the longer it will take CKPT to complete this process. That is not a serious problem at the end, but if you have several datafile per TS just for pleasure then you are giving a aditional load to your DB without any benefit. So, if your database is very large, you could consider use the max datafile size your tablespace can support. And I don't think that this is only for easy of adminstration becouse it's a topic to check on the VLDB Oracle support. It's the why of the new Oracle 10g improvement of BIGFILE TABLESPACE feature also. Btw, Oracle have a limit of 64,000 datafile per DB, and it's another thing to keep as small as you can the number of datafiles in a VLDB (I know 64,000 is a big number :-) ). 0 out of 5 found this valuable. Do you? |
|
redferrariteam |
4. RE: Multiple temp files
Jan 2 2008, 6:01 AM EST
""you have nothing to do with performance when speaking about multiple datafile per TS.I can see you contradicting your own statement. Yes the "would" in "In case of data files(multiple datafiles per TS) Oracle would allocate extents in a round ronbin fashion" <--- this is a "would" comming from you not from Oracle. " comes from me only as the round-robin extent allocation method is not documented. But that is purely based on experiment and observation. The above method of creating multiple datafiles per TS is used at many shops where RAID is not a feasible solution, though the performance improvement will not be that great. 0 out of 2 found this valuable. Do you? |
|
HayrolR |
5. RE: Multiple temp files
Jan 2 2008, 12:00 PM EST
Exactly, that was a try to say you that you won't get a significant improvement in performance.Regards 0 out of 3 found this valuable. Do you? |
