Performance decrease for parallel DML on compressed tables or regular tables after 11.2 Upgrade
(Doc ID 1514011.1)
Last updated on AUGUST 09, 2021
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Information in this document applies to any platform.
After upgrading database 184.108.40.206 to 220.127.116.11 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 18.104.22.168
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
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 22.214.171.124
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