Re: Temp Tablespace very large oracle 9ir2

chase down the sort or hash_join usage.

v$sort_segment is an excellent place to start, followed by v$sort_usage.

if this is an oltp system, I seriously doubt if any statement should be using more than say 1 GB of sort space unless its a batch or maintenance task (such as gathering stats).

This smells of developers issuing bad code that is going the route of

MERGE JOIN CARTESIAN or something that has 32 levels of hash joins.

As you likely have sessions that are using the existing temp tablespace with its 62 GB tempfile (what was the extend size, one block?), you're probably best off going with creating a new temp tablespace with a maxsize on its tempfile(s), say 16 GB for starters, and assigning users to that temporary tablespace, making it the default temp tablespace.

Periodically check v$sort_segment, v$sort_usage for culprits. Also check v$sesstat for sessions that have large values for temp space usage, direct writes, etc.

you'll likely see a side-effect of better performance overall, after the temp tablespace resource hog code has been identified and fixed.

btw - you neglected to mention db server software version. are you using pga_aggregate_target > 0, or is workarea_policy=manual? perhaps allocating more memory to the pga_aggregate_target or sort_area_size, hash_area_size would reduce the demand for temp space.

-bdbafh Received on Wed Nov 30 2005 - 19:05:26 CST