PX and system allocation

A few years ago (2007) I wrote about a problem that could appear when you mixed parallel execution with system managed extent allocation. A couple of years later I added a note that Christian Antognini had observed a patch in 11.1.0.7 that addressed the specific issue I had raised. Today, thanks to an email exchange with Christo Kutrovsky of Pythian, I can report that there is a variation of this issue still available even in 11.2.0.3.

The basic problem is that you can end up with a very large number of very small extents, leading to poor performance in parallel queries and a significant waste of space in a data segment. Here’s a simple, though not particularly realistic, way to demonstrate the problem.

rem
rem     Script:         autoallocate_bug.sql
rem     Author:         J P Lewis / Christo Kutrovsky
rem     Dated:          June 2012
rem
rem     Last Tested
rem             11.2.0.3
rem             11.1.0.7
rem

drop table t3;
drop table t2;
drop table t1;

create table t1
as
select * from all_objects where rownum <= 50000;

create table t2
as
select * from all_objects where rownum <= 50000;

create table t3
as
select * from all_objects where rownum <= 5000;

alter table t1 parallel(degree 2);
alter table t2 parallel(degree 2);
alter table t3 parallel(degree 2);

alter session force parallel dml;

insert into t1 select * from t3;
commit;

insert into t2 select * from t3;
commit;

-- Now repeat the last 4 statements a couple of dozen times.

It’s not visible in the create table statement, but tablespace I’m using has an 8KB blocksize, uses ASSM (automatic segment space management) and system-managed extent allocation. I’ve set the session to force parallel DML, but only because my objects are very small, and you might find that if you’ve previously enabled automatic parallel tuning then the optimizer will decide the objects are too small to bother running in parallel. (In Christo’s case the relevant SQL was handling large objects, but producing small result sets.) After executing the insert statements 15 times I ran a simple query against view user_extents, with the following results:

break on segment_name skip 1

select
        segment_name, extent_id, blocks
from
        user_extents
where
        segment_name in ('T1','T2')
order by
        segment_name, extent_id
;

SEGMENT_NAME              EXTENT_ID     BLOCKS
------------------------ ---------- ----------
T1                                0          8
                                  1          8
                                  2         16
                                  3          8
                                  4         16
                                  5         16
                                  6         16
...
                                 58         16
                                 59         16
                                 60          8
                                 61         16

SEGMENT_NAME              EXTENT_ID     BLOCKS
------------------------ ---------- ----------
T2                                0          8
                                  1          8
                                  2          8
                                  3         16
                                  4         16
                                  5          8
                                  6         16
...
                                 58         16
                                 59         16
                                 60          8
                                 61          8

124 rows selected.

Normally you expect to stop generating very small (i.e. 64KB) extents after the first 16 and carry on at 1MB at a time. In my case though, I’ve got a problem because I’ve inserted data in parallel. When I first raised this issue it related to the fact that even when inserting a large volume of data the parallel execution slaves still started by working through 16 small extents – and Christian Antognini’s article described the change that had appeared in 11.1.0.7 to address this issue. The difference in the latest example is that my parallel query slaves are only inserting a small amount of data anyway, so each slave generates a couple of small extents for that data.

The circumstances in which you might get into this state are, perhaps, fairly rare; but there are scenarios where you could end up with a very large number of these small extents. For example, if at the end of each day you merge a fairly large amount of data into a very big table you might run a parallel merge statement – because you’re processing a lot of data – that does a lot of updates and a small number of inserts. Every time you run the merge you could be adding another batch of small extents without realising what’s happening unless you happen to look.

Unfortunately there’s no (good) way to work around this problem – if you do a parallel insert you end up with each PX slave working on its own private extents, and when the query coordinator finally connects all those extents to the main data segment you end up with a certain amount of messiness and empty space below the high water mark. The degree to which you waste space and introduce small extents is then a matter of luck (and the degree of parallelism).

Update:

The space management code behaves differently for partitioned tables – but I believe Christo will be writing something about this on the Pythian blog in the near future, and will link to it when is appears.