RE: using a lot of temporary tablespace with large sort area size

[Paula_Stankus]

Title: RE: using a lot of temporary tablespace with large sort area size

Man significantly improved perf. by setting hash_area_size for session.  Okay so guys have read in metalink says hash_area_size is 2* sort_area_size by default - is this not true?  Most be the case that it isn't true.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: using a lot of temporary tablespace with large sort area size

Guys,

Note:
Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility.  Any opinions on how well PGA_AGGRE... works versus using specific parameters?

 

-----Original Message-----
From: Jack Silvey [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 11:38 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: using a lot of temporary tablespace with large sort area
size

Paula,

With so much data, Oralce (or Oracle, if you prefer)
might be hash joining your tables and writing temp
hash segments.

What is your hash_area_size?

jack silvey

--- [EMAIL PROTECTED] wrote:
> Guys,
>
> Have 20Gb and 16CPUS available on host.  Need to do
> large full-table
> scans/joins to create materialized view.  Since I
> have to do the full-table
> scans of large tables - decided to use parallel
> query option.  Eliminated
> significant I/O contention by using DIRECT IO.
> Using very very large
> sort_area_size, however, still writing out
> significant segments to temporary
> tablespace which doesn't make sense to me.  Any
> ideas?
>
>

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).