| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: temp table location
A GT table is created in the temporary segment in the temporary tablespace
(assuming you have a temporary tablespace that is of type temporary).
v$sort_usage will report on all temporary objects (sort and hash objects as well as GT tables) that are in use, and how many extents and blocks they occupy in the temporary segment.
If a sort requires less that the sort_area_size it will be performed in memory. If a sort or hash takes place in memory it will not be reported on v$sort_usage. If a sort is larger that the sort_area_Size you will see a sort object in v$sort_usage that will be at least as large as the sort_area_size. Similarly with hash operations.
If you reduce the sort area size then fewer sorts will fit in memory and more will be sorted to disk. Unless you are reducing it from an excessively high value, this is not usually beneficial. Remember that hash_area_size defaults to 2*sort_area_Size. My understanding is that the values of these parameter affect the costing of sort and hash operations in the optimiser. If you use Oracle 9 you might want to consider using pga_aggregate_target.
GT tables are not cached in PGA. Their blocks are treated like those of any other table, they will be cached in the block buffer cache in the usual way.
If a GT table is in use it always appears in v$sort_usage, even if it contains only 1 row (although if you are using multiple GT tables you can't tell which row relates to which table)
Sort_area_size has no bearing on the operation of GT tables (unless you need to sort them).
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of A Joshi
Sent: 06 May 2004 20:07
To: oracle-l_at_freelists.org
Subject: temp table location
Hi,
I am using a global temp table in my application and doing a sort on that.
Generally the temp table size is small (within 100 rows) but in really
exceptional once in a month cases it can go up to thousands. This is causing
problems and before I can reduce it I am trying to understand how it can
affect the database.
1) Where does the temp table and sort info get stored in memory.
2) I have reduced sort_area_size to reduce the PGA requirement allcation. Is
that the right course of action.
Thanks for your help.
 |
 |