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

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 Received on Mon Jan 22 2007 - 18:04:50 CST