Re: 10gR2: Why TEMP TS & disk sorts if pga_aggregate_target = 700mb?

vlegaspi wrote:
> Hello,
>
> I'm sorry if this was covered in a different post, but I didn't see
> anything. Just posts about what extent size should be for TEMP TS when
> workarea_size_policy=AUTO. Here goes...
>
> This is on 10.2.0.3 on Solaris 9 SPARC 64-bit. 4 Gig Physical RAM.
>
> I'm trying to understand why my TEMP tablespace is being used (and I
> get disk sorts) when I have:
>
> workarea_size_policy=AUTO
> pga_aggregate_target = 700m
>
> My TEMP space was created during CREATE DATABASE:
> ...
> default temporary tablespace temp
> tempfile '/oradata/lab10g/temp_lab10g.dbf' size 50m
> autoextend on
> next 128k
> extent management local
> uniform size 128k
> ...
>
> It has grown from 50m to 597mb.
>
> select name, value/1024/1024 "MBytes" from v$pgastat where unit =
> 'bytes' gives me:
> NAME MBytes
> -------------------------------------- ----------
> aggregate PGA target parameter 700
> aggregate PGA auto target 605.126953
> global memory bound 100
> total PGA inuse 27.6318359
> total PGA allocated 60.3808594
> maximum PGA allocated 228.076172
> total freeable PGA memory 7.1875
> PGA memory freed back to OS 6296.9375
> total PGA used for auto workareas 0
> maximum PGA used for auto workareas 116.631836
> total PGA used for manual workareas 0
> maximum PGA used for manual workareas .512695313
> bytes processed 7072.63867
> extra bytes read/written 2068.05469
>
> select name, value from v$sysstat where name='sorts (disk)' gives:
> NAME VALUE
> --------------- ----------
> sorts (disk) 5
>
>
> Trying to understanding all these statistics... it seems to me the max
> PGA ever used was ~228mb (or is that ~117mb?), and I only had 5 disk
> sorts. So my questions are:
> 1) How come Oracle didn't use all my 700mb of my PGA_AGGREGATE_TARGET
> to avoid a disk sort?
> 2) How come only 5 disks sorts autoextended my 50mb TEMP TS to 597mb?
>
> TIA,
> -Verna

Limits are placed on the percentage of the PGA_AGGREGATE_TARGET that may be used by any one session, roughly 5% of the PGA target, although that percentage may be somewhat lower for large values of PGA_AGGREGATE_TARGET. One wild Cartesian join between two tables can cause the temp tablespace to grow significantly, just as a sort on a single large table can cause the temp tablespace to grow.

If the sorts to disk bother you, set SORT_AREA_SIZE to a value to specify the smallest maximum amount of memory that can be used during a single sort - Oracle can automatically use more memory than what is specified. Care must be taken if you chose to specify SORT_AREA_SIZE, if there are many sessions that will be connected to the database at the same time.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jan 22 2007 - 19:54:08 CST