RE: temp table location
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 May 2004 22:24:06 +0100
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).
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of A Joshi
Sent: 06 May 2004 20:07
To: oracle-l@xxxxxxxxxxxxx
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.
---------------------------------
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
