偶遇Oracle 11g 的DBMS_TABCOMP_TEMP_UNCMP和DBMS_TABCOMP_TEMP_CMP

这两天有个系统上线，刚好在晚上10点多的时候准备数据库环境，结果发现当时数据库很繁忙，日志的生成量竟然比白天交易时间产生的日志量还大。最开始还以为是新上的系统起来以后在执行什么操作呢，后来一看发现时SYS用户在执行一些很耗时耗资源的操作，摘录如下：

超级耗时的作业，可以看到这些SQL都是SYS用户发起的，而且每个的执行时间都特别长：

再来看看这些SQL是什么，已经他们的执行计划，它在创建临时表：

CREATE TABLE "ZFPT"DBMS_TABCOMP_TEMP_CMP organization heap TABLESPACE "ZFPT_CBLOB" compress FOR ALL operations nologging ASSELECT * FROM "ZFPT".DBMS_TABCOMP_TEMP_UNCMP mytab

CREATE TABLE "ZFPT" DBMS_TABCOMP_TEMP_UNCMP TABLESPACE "ZFPT_CBLOB" nologging ASSELECT        /*+ FULL("ZFPT"."DBTSEND_LIST") */        *FROM "ZFPT"."DBTSEND_LIST" partition("P201208") sample block( 65.1) mytab

原来这些作业都是11g里面自我诊断产生的，在MOS上可以找到一篇文章关于怎么暂停ASA（Automatic Segment Advisor）的：

 

 

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP [ID 1326118.1]

转到底部

---

修改时间:2012-5-16类型:PROBLEM状态:PUBLISHED优先级:3

注释 (0)

In this Document

Symptoms

 

Cause

 

Solution

 

References

---

Applies to:

Oracle Server -Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

After upgrading to11g, during running of the Automatic Segment Advisor, the tabledms_tabcomp_temp_uncmp is being created and is taking up lots of space.

Note: You may seeORA-1652 error: Unable to extend temp segment.

Cause

Solution

The followingsolutions are available:

1. Apply one-off Patch 8896202 for unpublished Bug 8896202, if available.
2. Apply 11.2.0.2 patchset where fix is included.
3. Workaround: Disable the Automatic Segment Adviser, as this is what makes the call to the Compression Advisor in 11.2.  These steps can be found in the "Configuring the Automatic Segment Advisor" section of the Oracle Database Administrator's Guide 11g Release 2 (11.2).  Here is the excerpt from the guide:

Configuring theAutomatic Segment Advisor 
The Automatic Segment Advisor is an automated maintenance task. As such, youcan use Enterprise Manager or PL/SQL package procedure calls to modify when(and if) this task runs. You can also control the resources allotted to it bymodifying the appropriate resource plans. 

You can call PL/SQL package procedures to make these changes, but the easierway to is to use Enterprise Manager.

To configure the Automatic Segment Advisor task with Enterprise Manager: 

1. Log in to Enterprise Manager as user SYSTEM. 

2. On the Database Home page, under the Space Summary heading, click thenumeric link next to the label Segment Advisor Recommendations.  TheSegment Advisor Recommendations page appears. 

3. Under the Related Links heading, click the link entitled AutomatedMaintenance Tasks. The Automated Maintenance Tasks page appears. 

4. Click Configure. The Automated Maintenance Tasks Configuration page appears. 

5. To completely disable the Automatic Segment Advisor, under Task Settings,select Disabled next to the Segment Advisor label, and then click Apply.

 

【延伸阅读】

Slow Logical apply, due to objects: DBMS_TABCOMP_TEMP_UNCMP OR DBMS_TABCOMP_TEMP_CMP [ID 1114000.1]

转到底部

---

修改时间:2012-10-12类型:PROBLEM状态:PUBLISHED优先级:3

注释 (0)

In this Document

Symptoms

 

Cause

 

Solution

 

References

---

Applies to:

Oracle Server -Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [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 becreated 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 underthe same schema name.

These tables are used to determine the level of compression level can beachieved. 


Bug.9595358 SLOW LOGICAL STANDBY APPLY,APPLYING CHANGES TO DBMS_TABCOMP_TEMP_UNCMP 
This bug is fixed in 11.2.0.2 and should not occur in this release of DB.

Solution

Workaround:
========

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

1. Stop SQL Apply:

SQL> ALTERDATABASE STOP LOGICAL STANDBY APPLY;

2. Register the SKIP rules:

SQL> EXECUTEDBMS_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> ALTERDATABASE START LOGICAL STANDBY APPLY IMMEDIATE;