|
Sort
|
In this issue, I will continue talking about possible solutions to prevent
ORA-04031 error.
(Solution 3)
Set Sort_Area_Retained_Size.
In shared server mode, the shared memory is allocated to a certain area based on
the following formula.
sort_area_size - sort_area_retained_size = PGA
sort_area_retained_size = SGA
(Test environment)
OS:Red Hat Linux release 7.1
Oracle:9.2.0.1
Check the current set value.
SQL> select name,value,isdefault from v$parameter
where name in ('workarea_size_policy','pga_aggregate_target')
or name like 'sort%';
NAME VALUE ISDEFAULT
------------------------------ ------------------------- ---------
sort_area_size 104857600 FALSE
sort_area_retained_size 0 TRUE
pga_aggregate_target 4294967296000 FALSE
workarea_size_policy AUTO TRUE
|
Sort_Area_Size is about 100MB and Sort_Area_Retained_Size is 0.
I apply these values to the formula above.
sort_area_size - sort_area_retained_size = shared memory allocated to PGA
100MB - 0 = 100MB
|
According to the formula, there should be no shared memory to be
allocated to SGA.
Now, I will actually summarize the data.
Check the session information.
SQL> select sid,serial#,server from v$session
where username = 'XPRT';
SID SERIAL# SERVER
---------- ---------- ------------------
10 2105 SHARED
|
The server is in shared mode.
Summarize the data.
SQL> select id,sum(cost) from code group by id;
ORA-04031: Unable to allocate 8192 bytes of shared memory ("shared pool",
"unknown object","sort subheap","sort key")
|
This must be wrong.
All shared memory is supposed to be allocated to PGA, but actually it attempts to be allocated
to SGA (i.e. shared pool). Oracle documents clearly explain this contradiction.
Excerpts from Oracle9i Database Reference Release9.2
The default value as reflected in the V$PARAMETER dynamic performance view is 0. However,
if you do not explicitly set this parameter, Oracle actually uses the value of the Sort_Area_Size
parameter.
This means that the shared memory specified in Sort_Area_Size attempts to be allocated
to SGA. What happens if I set Sort_Area_Retained_Size explicitly?
Before analyzing, I set Shared_Pool_Size to smaller, otherwise,
paging will occur due to insufficient free space.
1.Set sort_area_size to smaller.
SQL> alter system set shared_pool_size=200M;
System altered
2.Set sort_area_retained_size to 0.
SQL> alter session set sort_area_retained_size=0;
Session altered
SQL> select id,sum(cost) from code group by id;
ORA-04031: Unable to allocate 8192 bytes of shared memory ("shared pool",
"unknown object","sort subheap","sort key")
|
ORA-04031 does occur even though I set Sort_Area_Retained_Size to 0.
1.Set sort_area_retained_size to 1.
SQL> alter session set sort_area_retained_size=1;
Session altered
SQL> select id,sum(cost) from code group by id;
1000000 rows selected
|
The process is completed without any failure.
(Solution 4)
Set Sort_Area_Size.
Since there is not sufficient free space in memory, I set Sort_Area_Size to
smaller to perform disk sort.
1.Set sort_area_size.
SQL> alter session set sort_area_size=1;
Session altered
SQL> select id,sum(cost) from code group by id;
1000000 rows selected
|
The process is completed without any failure.
That's the end of the analysis of Sort.
I will start a new topic next week.
Takuya Kishimoto
|
|
