sort_area_size in oracle 10g
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?