sort_area_size in oracle 10g

Hello,
I'm working on oracle 10g,
In order to understand the effect of sort_area-size, I changed its value to 2048 bytes

alter system set SORT_AREA_SIZE = 2048 deferred;

and after some reading I saw that I should also change the value of workarea_size_policy parameter to manual in order to see the effects of changing the first parameter so I did:

alter system set workarea_size_policy= manual;

and I verified that the tow parameters have the new values.

then I ran a sort query from one session and I monitored the sort activity from another session, by running these scripts:

SELECT NAME,VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%';

select tablespace_name,total_extents,total_blocks, used_extents from v$sort_segment;

In fact, I couldn't really notice the differences after assigning the value 41943040 to the sort_area_size parameters!!

So I would really like to help me to understand the effect of this parameter and how I can verify this effect in practice?

thank you
Baraa

well, are you are on 10g why to worry with manual allocation when you have a automated option? check pga_aggregate_target parameter in 10g. It will do every thing for you.

Regards,

I want to understand the effect (the real use) of such parameters to understand really the use of each of them :)

Thanks to help me in that,
Regards
Baraa

Quote:

---

Originally Posted by Ro2a.M

I want to understand the effect (the real use) of such parameters to understand really the use of each of them :)

---

How about reading Oracle documentation? :) Let me quote: 'SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.' (*)

(*) http://docs.oracle.com/cd/B13789_01/...tparams200.htm