| Temp Tablespace usage [message #396172] |
Sat, 04 April 2009 09:50  |
dbcop
Messages: 37
Registered: September 2006
Location: india
|
Member |
|
|
Hi,
In our production box I find whenever there are huge sort operations during peak hours only any particular tempfile is being used and the system becomes exceptionally slow.
I have 4 tempfiles attached to the temp tablespace .From the statspack report
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
-------------- ------- --------- ------- ------------ -------- ------------
Av Buf
Wt(ms)
---------
PSAPTEMP /oracle/MP1/saptemp1/temp_1/temp.data1
7 0 0.0 1.0 0 0 0
/oracle/MP1/saptemp1/temp_3/temp.data3
7 0 0.0 1.0 0 0 0
/oracle/MP1/saptemp2/temp_2/temp.data2
7 0 0.0 1.0 0 0 0
/oracle/MP1/saptemp2/temp_4/temp.data4
64,844 4 123.4 19.1 133,981 7 0
it reveals temp.data4 is being used. I understand that only if tempfile space is being used completely then only the operation would move to the next available tempfile.
Maybe the RPM s of the disk drives are not up to the mark.
I am taking it up with the h/w guys but menawhile.
If I want all the tempfiles to be used then I need to make the tempfiles shorter in size (compared to 10gb each currently).
Does any one of you have any different suggestion to give.
Thanks
[Updated on: Sat, 04 April 2009 10:10] by Moderator Report message to a moderator |
|
|
|
| Re: Temp Tablespace usage [message #396199 is a reply to message #396172] |
Sat, 04 April 2009 19:18  |
oraxperts
Messages: 5
Registered: March 2009
Location: Melbourne
|
Junior Member |
|
|
Noticed that average read time for the forth tempfile is 123 ms, which is too high. It should not more than 15ms in SAN arrays. I think issue lies under the storage system rather than, which tempfile Oracle is using. You should move the tempfiles to file system, which had got better throughput. You can use the Oracle's ORION tool to check the IO throughput.
Cheers
ORAxperts
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
