| Performance tuning [message #356122] |
Wed, 29 October 2008 12:10  |
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  |
joy_division
Messages: 4963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
