Performance decrease for parallel DML on compressed tables or regular tables after 11.2 Upgrade (Doc ID 1514011.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

After upgrading database 11.1.0.7 to 11.2.0.3 performance issues started to be seen on parallel DML on compressed tables and

- main part of elapsed time is caused by IO waits
- number of read request per execution have increased tremendously ( 5 to 10 times)
- as number of rows varying - number of buffer gets and read request per updated row have been increased by factors of  10 to 20


It was noticed that when following the steps on 11.2.0.3
-------------------
1. Update statement is slow (not only update, delete as well) 
2. Move the segments which are touched by the update:
alter table test_tpart move subpartition P58_SP1 compress for oltp;
alter table test_tpart move subpartition P58_SP2 compress for oltp;
alter table test_tpart move subpartition P58_SP3 compress for oltp;
alter table test_tpart move subpartition P58_SP4 compress for oltp;
  Rebuild the UNUSABLE indexes:
alter index INDX_test_tpart rebuild subpartition P58_SP1 online;
alter index INDX_test_tpart rebuild subpartition P58_SP2 online;
alter index INDX_test_tpart rebuild subpartition P58_SP3 online;
alter index INDX_test_tpart rebuild subpartition P58_SP4 online;
alter index INDX_test_tpart rebuild subpartition P58_SP1 online;
alter index INDX_test_tpart rebuild subpartition P58_SP2 online;
alter index INDX_test_tpart rebuild subpartition P58_SP3 online;
alter index INDX_test_tpart rebuild subpartition P58_SP4 online;
3. Retest the same update statement, and it works fine 

 

Changes

This is expected behaviour on 11.2 due to the changes which were made at the way the scan is performed.

See details about this in Bug 15997534 - PERFORMANCE DECREASE FOR DML OPERATIONS ON COMPRESSED TABLES AFTER UPGRADE 

This behaviour is not related to the compress feature and same behaviour can be seen for regular tables chaving a big number of chained rows.

See details in Bug 14165922 - SLOW PERFORMANCE FOR PARALLEL MERGE AGAINST PARTITIONED TABLE IN 11.2.0.2

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms