Nasty bug introduced with patch set 10.2.0.3 regarding SORT_AREA_SIZE

While testing an database application that is being migrated from 9.2 to 10.2 we came across a quite nasty bug that was introduced by a "bug fix" which was meant to speed up the "ALTER SESSION" command when propagating parameter changes to the parallel slaves. Unfortunately the "fix" introduced another severe issue regarding the handling of the SORT_AREA_SIZE parameter.

Metalink has documented the issue in the document 452466.1 and the corresponding bug 6113096.

Our own tests have shown that the bug description unfortunately is incomplete resp. inaccurate. It is not only that the propagation of the via ALTER SESSION modified SORT_AREA_SIZE setting to the parallel slaves is not working, but the modified SORT_AREA_SIZE setting of the session is not used at all, even in serial execution.

So in case you have a 9i application that is mainly batch driven and therefore has control over the concurrent usage of the database, you can speed up large sorts and joins by using the WORKAREA_SIZE_POLICY setting of MANUAL and setting SORT_AREA_SIZE and HASH_AREA_SIZE to suitable large values. Indications of a good setting can be obtained e.g. by looking at V$SQL_WORKAREA or V$SQL_WORKAREA_ACTIVE. Since in 9i the upper limit of the automatic tuning for the sort area size using PGA_AGGREGATE_TARGET is fixed to 100M (if you do not start to fiddle around with undocumented parameters, e.g. _pga_max_size or _smm_max_size), you can sometimes achieve significant benefits when using the MANUAL setting for large sorts and joins. Note that starting with 10gR2 a more aggressive approach is used when performing automatic tuning and the upper limit of 100M does not apply anymore (when using a PGA_AGGREGATE_TARGET > 1000M, for a detailed description see e.g. Joze Senegacnik's PGA memory management presentation) . So in 10gR2 and higher the automatic tuning can also be used for mainly batch driven processes, but still there are cases where the MANUAL setting performs better if you know your data well and do not overallocate memory.

Now if you perform in a 10.2.0.3 session an ALTER SESSION SET SORT_AREA_SIZE = x, this does not take any effect and the actual value used will be the value of the instance setting, either from pfile or spfile, regardless of the individual value set in the session. This applies also to serial execution, not only parallel executions as described in the Metalink documents.

This affects all sort-related operations, for instance, creation or rebuild of an index, ORDER BY sorts, unions, intersects, etc.

You can confirm this by monitoring a sort operation e.g. using V$SQL_WORKAREA_ACTIVE. In MANUAL mode the WORK_AREA_SIZE column shows always 0 in >= 10.2.0.3 (in 9i the actual SORT_AREA_SIZE setting was shown) and the MAX_MEMORY_USED column will correspond to the instance setting of SORT_AREA_SIZE in case a sufficient large sort operation takes place. SORT_AREA_SIZE has a quite low default setting of 64k in 10gR2 if not stated otherwise in the instance configuration. You'll probably also detect quite a lot usage of the temporary tablespace since most sort operations won't fit into memory when using the default setting and therefore will spill to disk.

It is even does not work to use an ALTER SYSTEM SET SORT_AREA_SIZE = x DEFERRED which is possible since 10g. Although the V$PARAMETER view shows the modified value set for SORT_AREA_SIZE when establishing a new session (similar as it does when using ALTER SESSION) the actual value used is still the one from the instance wide setting.

The only known workaround so far is to set the instance setting of SORT_AREA_SIZE to a sufficient high value, although this is in my opinion not a really neat solution as you have to live with this "one size fits all" approach. So in case you sometimes want to allocate less memory since multiple processes will run at the same time and sometimes really use a huge allocation for a single process this is not possible in 10.2.0.3 and later. You have to use either the automatic tuning or live with the restriction of a single possible value used as instance setting.

UPDATE: As shown e.g. by Jonathan Lewis here there seems to be a kind of workaround at least when executing in serial mode, by simply issuing the ALTER SESSION twice with the value for SORT_AREA_SIZE requested. This doesn't seem to apply to the propagation of the value to parallel slaves, though.

Further tests using release 11.1.0.6.0 on Windows have shown that - as described in the document - the bug is still not fixed in 11g and according to the documentation it won't be fixed in the 1o.2.0.4 patch set either. A one-off patch does not seem to be available either. You can only use the workarounds offered so far.

So if you do something like that using MANUAL workarea_size_policy:

alter index cbo_test.test_mem rebuild nologging;

you will be affected by the bug, or more precisely the sort operation required to rebuild the index will be affected. It doesn't matter if the operation is executed in serial or parallel mode.

If you do something like that using MANUAL workarea_size_policy:

insert /*+ append noparallel(x) */ into sh.sales x
select /*+ noparallel(a) noparallel(b) */ a.* from sh.sales a, sh.sales b
where a.prod_id = b.prod_id
and a.cust_id = b.cust_id
and a.time_id = b.time_id
and a.channel_id = b.channel_id
and a.promo_id = b.promo_id;

you can also be affected, depending on if indexes do exist and the kind of join operation. In case the table being populated has indexes that need to be maintained the corresponding sort operation will be affected. The join operation itself will be able to use the MANUAL settings when using a HASH JOIN as the HASH_AREA_SIZE parameter seems not to be affected by the bug (the WORK_AREA_SIZE column shows the expected value of HASH_AREA_SIZE in MANUAL mode), whereas a SORT MERGE JOIN will be affected since it in most cases requires a SORT operation that again will be affected. Again it does not matter if serial or parallel execution is used, the serial execution will be affected as well.

So the following operation represents basically a worst case as it is affected by the bug in several ways:

insert /*+ append noparallel(x) */ into sh.sales x
select /*+ noparallel(a) noparallel(b) use_merge(a, b) */ a.* from sh.sales a, sh.sales b
where a.prod_id = b.prod_id
and a.cust_id = b.cust_id
and a.time_id = b.time_id
and a.channel_id = b.channel_id
and a.promo_id = b.promo_id
order by a.prod_id, a.cust_id, a.time_id, a.channel_id, a.promo_id;

1. It needs to maintain indexes on the SALES table (provided the demo indexes are in place and usable) by sorting
2. It needs to sort to perform the SORT MERGE JOIN
3. It needs to sort the result set

Of course there might be even worse scenarios, e.g. additional sorts caused by analytic functions etc.

So in summary check thoroughly any application that is supposed to use MANUAL workarea size policy before applying 10.2.0.3 patchset or even using 11g.