My Oracle Support Banner

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

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - 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 <TABLE_NAME> move subpartition <SUBPARTITION_NAME1> compress for oltp;
alter table <TABLE_NAME> move subpartition <SUBPARTITION_NAME2> compress for oltp;
alter table <TABLE_NAME> move subpartition <SUBPARTITION_NAME3> compress for oltp;
alter table <TABLE_NAME> move subpartition <SUBPARTITION_NAME4> compress for oltp;
  Rebuild the UNUSABLE indexes:
alter index <INDEX_NAME> rebuild subpartition <SUBPARTITION_NAME1> online;
alter index <INDEX_NAME> rebuild subpartition <SUBPARTITION_NAME2> online;
alter index <INDEX_NAME> rebuild subpartition <SUBPARTITION_NAME3> online;
alter index <INDEX_NAME> rebuild subpartition <SUBPARTITION_NAME4> 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.

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 having a big number of chained rows.

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

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.