Thanks Tom for explaining with clarity. Especially the following drove home the point very well.
"Now, when the sort is done and before the client gets the first row, only
SARS bytes of storage will be used to cache the sorted answer (the rest just
stays in temp in its pieces, we'll pull from it as needed).
"
It tells that even if the sort_area_size is large, if SARS is not large enough to hold result set, we will have I/O to temp segments.

In dedicated server mode, since UGA is part of PGA, and SARS is in UGA and SAS - SARS in PGA, the PGA can expand by SAS. After the sort is over since it is not released to O/S, PGA will remain at the expanded level. Is this right ? Agreed, O/S may page out some PGA memory based on LRU.

In case SARS=SAS, then all the sort is happening in UGA. Which implies in shared server mode, all the sort is forced to happen in SGA in SARS=SAS. Is this right ?

Some documents indicate that a SQL statement may need multiple sort_Area_RETAINED_SIZE for concurrent sorts. Is a sort-merge join one of them ? Since it has to sort two row sources and hold the results of two sorted row sources it needs two SARS ?
Regarding SORT alogorithm used by Oracle, ist something internal and proprietary info ?

Hi Tom,

While understanding concepts of SORT from your book 'expert-one-on-one' referring page 71, 72 ..etc I have performed following steps

1 select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4* and a.name like '%ga%'
14:06:12 system:infod@bnpinfo> /

NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 53504
session uga memory max 53504
session pga memory 146576
session pga memory max 146576
calls to kcmgas 1

14:06:13 system:infod@bnpinfo> show parameter sort_area

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 1048576

select * from all_objects order by 1, 2, 3, 4; ( Done Control-C after 2 pages )

14:21:30 system:infod@bnpinfo> select a.name, b.value
14:21:37 2 from v$statname a, v$mystat b
14:21:37 3 where a.statistic# = b.statistic#
14:21:38 4 and a.name like '%ga%';


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 53324
session uga memory max 714452
session pga memory 810512
session pga memory max 810512
calls to kcmgas 1

In your book page no 72 is written that after sort operation UGA memory is going to increased by sort_area_retained_size and PGA is going to increase little up.

But in this case it is not the case. Can you explain by what amount UGA memory increased where sort_area_retained size is 0 and Secondly why PGA has increased so much ?

regards & thanks
pjp

SQL> SELECT NAME, VALUE
  2    FROM V$SYSSTAT
  3   WHERE NAME IN ('sorts (memory)', 'sorts (disk)');

NAME                                VALUE
-------------------------------- ----------
sorts (memory)                        54450
sorts (disk)                              0

SQL> 
SQL> select * from v$pgastat;

NAME                                             VALUE UNIT
------------------------------------------- ---------- ------------
aggregate PGA target parameter              2621440000 bytes
aggregate PGA auto target                   2054356992 bytes
global memory bound                          104857600 bytes
total PGA inuse                              338819072 bytes
total PGA allocated                           68583680 bytes
maximum PGA allocated                        441916416 bytes
total freeable PGA memory                      1900544 bytes
PGA memory freed back to OS                 9.9736E+10 bytes
total PGA used for auto workareas                    0 bytes
maximum PGA used for auto workareas               8192 bytes
total PGA used for manual workareas          326594560 bytes
NAME                                             VALUE UNIT
------------------------------------------- ---------- ------------
maximum PGA used for manual workareas        397923328 bytes
over allocation count                                0
bytes processed                             5654026240 bytes
extra bytes read/written                    2147482624 bytes
cache hit percentage                             72.47 percent

16 rows selected.

SQL> SELECT NAME, VALUE
  2    FROM V$SYSSTAT
  3   WHERE NAME IN ('sorts (memory)', 'sorts (disk)');

NAME                                      VALUE
------------------------------------ ----------
sorts (memory)                            54498
sorts (disk)                                  0


There are no sorts done on disk, then why
cache hit percentage = 72.47 it should be 100 percent.

Thanks 

In auto workarea_size_policy, we know that
formula for sort_area_size (that 5% one), but what
is the formula for hash_area_size and sort_area_retained_size in AUTO mode.

Thanks

OK, but what about the formula for hash_area_size
and sort_area_retained_size in AUTO workarea_size_policy

Hi Tom,
What happens when sort_area_size equals sort_area_retained_size??