Sunday, October 31, 2010

This is just a short note that some of the new features added in 11.2 have some subtle restrictions that are not documented properly.

1. Hybrid Columnar Compression and Function-Based Indexes

The Hybrid Columnar Compression (HCC) introduced in 11.2 that is only enabled in tablespaces residing on Exadata Storage in 11.2.0.1 has the subtle restriction that the mere presence of a function-based index either explicitly created or indirectly via an index on a virtual column disables HCC completely on that segment with direct path / parallel DML inserts. It silently falls back to no compression at all, rather than using for example basic or advanced oltp compression instead.

-- So the mere existence of a FBI, no matter if usable or unusable will prevent HCC with direct-path inserts / parallel DML-- It is however fully supported to have them on HCC data, but you need to drop / re-create them in order to load the data-- via direct-path inserts / parallel DML with HCC enabled

I've been told that this restriction is going to be lifted in some future version, but I haven't had the chance yet to test this with the 11.2.0.2 patch set, in particular because the patch set is not yet released for existing Exadata installations as upgrade - it is only available for new Exadata deployments.

2. Deferred Segment Creation and Parallel DML

11.2 introduced the deferred segment creation that interestingly is enabled by default when doing a fresh installation. I haven't done a migration from previous versions to 11.2 yet so I don't know if this is also enabled in migrated environments but I assume so.

Note that the 11.2.0.2 patch set extends the deferred segment creation to partitioned objects which wasn't supported in the initial 11.2.0.1 release.

The deferred segment creation has some subtle side effects, for example sequences seem not to start with the defined START WITH value because the recursive transaction fails initially, the segment gets created and the next sequence value will be used for the actual DML insert operation.

Another side effect that is not properly documented is the fact that parallel DML is not supported on "segment-less" objects.

So if you have a freshly created segment that is supposed to be populated via parallel DML inserts then this will silently fall back to serial direct-path inserts for the first time executed.

Once the segment has been created from the next time on parallel DML is going to be used which might help to confuse the issue since it is only reproducible under particular circumstances.

So if you have migrated an application to 11.2 and wonder why sometimes load operations take significantly longer than before but mostly do not then this might be a viable explanation.

This behaviour is not part of the deferred segment creation restrictions described in the official documentation, but it is mentioned towards the end of the MOS note "11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]"

According to the mentioned MOS note this still seems to apply to 11.2.0.2, but I haven't tested this yet myself. So it looks like this significant restriction is not yet lifted with the current patch set.

Update July 2011: See comment below by Radoslav, the problem seems to be fixed in 11.2.0.2.