OraFAQ Forum: Performance Tuning » Temp Tablespace usage

Home » RDBMS Server » Performance Tuning » Temp Tablespace usage (Oracle 9.2.0.7 AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Previous Topic:	difference between analyze table and dbms_stats.gatherstatistic
Next Topic:	v$session output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:07:45 CST 2024