Home » RDBMS Server » Performance Tuning » Temp Tablespace usage (Oracle 9.2.0.7 AIX)
Temp Tablespace usage [message #396172] Sat, 04 April 2009 09:50 Go to next message
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 Go to previous message
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
Previous Topic: difference between analyze table and dbms_stats.gatherstatistic
Next Topic: v$session output
Goto Forum:
  


Current Time: Sun Dec 04 19:11:18 CST 2016

Total time taken to generate the page: 0.07877 seconds