Sunday, October 30, 2011

Auto DOP And Direct-Path Inserts

This is just a short note about one of the potential side-effects of the new Auto Degree Of Parallelism (DOP) feature introduced in 11.2.

If you happen to have Parallel DML enabled in your session along with Auto DOP (and here I refer to the PARALLEL_DEGREE_POLICY = AUTO setting, not LIMITED) then it might take you by surprise that INSERT statements that are neither decorated with a parallel hint nor use any parallel enabled objects can be turned into direct-path inserts.

Now don't get me wrong - I think this is reasonable and in-line with the behaviour so far because you have enabled parallel DML and Auto DOP therefore is eligible to make use of that feature. According to the documentation the default mode of parallel inserts is direct-path, so Auto DOP simply follows the documented behaviour when deciding to use parallel DML. Note that depending on the data volume to insert you can even end up with a serial direct-path insert combined with a parallel query part.

It is just that you need to be aware of the fact that a simple INSERT INTO ... SELECT FROM on serial objects might turn into a direct-path insert.

The main caveat to watch out for is that the direct-path insert won't re-use any space available in the existing blocks of the segment but will always allocate blocks above the current High Water Mark (HWM).

So if you use this feature along with some application logic that deletes rows from a segment then by enabling Auto DOP you might end up with an unreasonable segment growth that can have all kinds of nasty side effects.

Another side effect of this is more obvious: An existing application logic might break because it attempts to re-access the object after the now direct-path insert within the the same transaction which will end up with an "ORA-12838: cannot read/modify an object after modifying it in parallel".

If you still want to make use of parallel DML but need to be able to re-use available space in existing blocks you can try to explicitly specify the NOAPPEND hint that still allows parallel AUTO to be used but will prevent the direct-path insert mode for both serial and parallel inserts - 11g introduced the parallel conventional insert, by the way.

2 comments:

Thankfully "alter session enable parallel dml;" would still be a pre-requisite.Therefore, the DBA and Developer would be able to isolate their batch jobs which suddenly change executions for certain Serial INSERTs to PARALLEL INSERTs.