Home » RDBMS Server » Server Administration » Performance tuning (Oracle 9.2)
Performance tuning [message #356122] Wed, 29 October 2008 12:10 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I have a query running indefinitely with distinct clause and when I remove the distinct it comes back almost immediately.

So I compared 2 databases database1(about to go to production) and database2(on production).
See the total blocks and used blocks comparison between the 2 databases from v$sort_segment.

database1
TOTAL_BLOCKS USED_BLOCKS
------------ -----------
128256 128256

database2
TOTAL_BLOCKS USED_BLOCKS
------------ -----------
14143488 40960

Is this a valid reason to have more space allocated to Temp tablespace.
Re: Performance tuning [message #356124 is a reply to message #356122] Wed, 29 October 2008 12:19 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Well, I don't think you have the whole picture. Your TEMP tablespace might be in autoextend mode and needed to be very large for a big sort or huge join at one time. Too many parameters to answer for sure why you are experiencing what you are, but the first place to start is to do explain plans and tkprof.
Previous Topic: session still KILLED in oracle, process don´t exist in SO
Next Topic: USERS01.dbf file size
Goto Forum:
  


Current Time: Tue Dec 06 02:50:42 CST 2016

Total time taken to generate the page: 0.08338 seconds