SAS Bug

I see that Tom Kyte has found a nasty little bug waiting to trap a few unlucky people as they patch to 10.2.0.4, or upgrade to 11g.

By now, you should probably be using the pga_aggregate_target rather than the sort_area_size to control the memory allocation for sorting – especially from 10g onwards – but you may still have a few special tasks in big batch jobs where you have code that switches the workarea_size_policy to manual and sets a specific (large) sort_area_size. Perhaps something like:

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 104857600;
--execute big job here
alter session set workarea_size_policy = auto;

If this is the case, be careful when you patch to 10.2.0.4 or upgrade to 11.1.0.6, as the code may suddenly slow down quite dramatically.

There seems to be a bug with this particular ‘alter session’ command (and possibly with others – I haven’t yet tested) that “delays” the change. It’s quite easy to see this if you enable event 10032 to get the “sort parameters” dump. Here’s a sample of code I’ve run against a small 11g system (11.1.0.6) to demonstrate the issue.

create table t1
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects 
	where	rownum <= 3000
)
select
	rownum				id,
	lpad(rownum,10,'0')		small_vc,
	lpad(rownum,100,'0')		big_vc
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

-- gather statistics here

I’ve created a table with 10,000 rows and a couple of character columns that I’m now going to select and sort. The small_vc column is only 10 characters, so I shouldn’t need more than a couple of hundred KB to select and sort this column; and the big_vc column is only 100 bytes, so 4MB should be plenty to select and sort that column:

alter session set events '10032 trace name context forever';
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 262144;


-- first the small one

select 
	count(*)
from
	(
		select 
			/*+ 
				qb_name(ordered) 
				no_merge
				no_eliminate_oby(@ordered)
			*/
			small_vc
		from
			t1
		order by
			small_vc
	)
;

-- then the big one

alter session set sort_area_size = 4194304;

select 
	count(*)
from
	(
		select 
			/*+ 
				qb_name(ordered) 
				no_merge
				no_eliminate_oby(@ordered)
			*/
			big_vc
		from
			t1
		order by
			big_vc
	)
;

I’ve used the /*+ no_merge */ hint in the SQL, with an inline view that contains the select and “order by” so that I can force Oracle to sort the data but only output a single row. Then I’ve used the /*+ no_eliminate_oby() */ hint to make sure that the optimizer doesn’t eliminate the (apparently) redundant “order by” clause in the in-line view (a trick that it learnt to do in 10gR2 – see the notes towards the end of this article).

The trace files dumped by event 10032 show the following results.

From the first query:

---- Sort Parameters ------------------------------
sort_area_size                    65536
sort_area_retained_size           65536
sort_multiblock_read_count        1
max intermediate merge width      3
---- Sort Statistics ------------------------------
Initial runs                              1
Input records                             10000
Output records                            10000
Disk blocks 1st pass                      20
Total disk blocks used                    22
Total number of comparisons performed     10103
  Comparisons performed by in-memory sort 10099
  Comparisons while searching for key in-memory 4
Temp segments allocated                   1
Extents allocated                         1
Uses version 2 sort
Uses asynchronous IO

Despite my call to set the sort_area_size to 256KB, the operation has used 64KB (the system default), and dumped about 160KB of data to disk.

Then, for the second query:

---- Sort Parameters ------------------------------
sort_area_size                    262144
sort_area_retained_size           262144
sort_multiblock_read_count        2
max intermediate merge width      7
---- Sort Statistics ------------------------------
Initial runs                              1
Input records                             10000
Output records                            10000
Disk blocks 1st pass                      134
Total disk blocks used                    136
Total number of comparisons performed     10103
  Comparisons performed by in-memory sort 10099
  Comparisons while searching for key in-memory 4
Temp segments allocated                   1
Extents allocated                         2
Uses version 2 sort
Uses asynchronous IO

I set the sort_area_size to 4MB, and the operation has used 256KB – which is the value I set on the previous sort. We can also see that we’ve dumped about 1.1MB of data to disk.

So, if you’ve got code that tries to set the sort_area_size before running a big job, it’s possible that that job will run with the wrong value for the parameter. The effects could be dire.

Fortunately the workaround is simple (and carries no side-effects) – just edit the code to do the ‘alter session’ twice in a row. The second call will ensure that the value you want actually gets used.