DBMS_TABCOMP_TEMP_UNCMP OR DBMS_TABCOMP_TEMP_CMP导致逻辑应用慢

郑全

Slow Logical apply, due to objects: DBMS_TABCOMP_TEMP_UNCMP OR DBMS_TABCOMP_TEMP_CMP (文档 ID 1114000.1)

郑全

In this Document

Symptoms

Cause

Solution

References


Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 12-OCT-2012***
Symptoms

Find the log apply is slow, and the objects involved are <schema_name>.DBMS_TABCOMP_TEMP_CMP and/or <shcmea_name>.DBMS_TABCOMP_TEMP_UNCMP
Cause

Two tables (DBMS_TABCOMP_TEMP_UNCMP & DBMS_TABCOMP_TEMP_CMP) could be created because of a 11.2 new feature, Compression Advisory.

These two temporary tables  are created during the execution of package - DBMS_COMPRESSION, while doing the analyze of a table. They are created under the same schema name.

These tables are used to determine the level of compression level can be achieved.
Solution

Workaround:
========

Set up the following SKIP rules in the logical standby DB:

1. Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;


2. Register the SKIP rules:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','%','DBMS_TABCOMP_TEMP_CMP');
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','%','DBMS_TABCOMP_TEMP_UNCMP');

SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','%','DBMS_TABCOMP_TEMP_CMP');
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','%','DBMS_TABCOMP_TEMP_UNCMP');



3. Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

References
BUG:9595358 - SLOW LOGICAL STANDBY APPLY, APPLYING CHANGES TO DBMS_TABCOMP_TEMP_UNCMP