ITPub博客

首页 > 数据库 > Oracle > Oracle 性能优化-EXPDP备份速度优化01

Oracle 性能优化-EXPDP备份速度优化01

原创 Oracle 作者:chenoracle 时间:2018-11-10 23:17:09 0 删除 编辑


Oracle 性能优化-EXPDP备份速度优化01

 

问题现象:

数据库每天两次expdp备份,中午一次,晚上一次,最近连续两天发现,中午备份正常,耗时 2个小时,晚上备份确需要 5小时;

环境:

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

问题分析:

怀疑晚上备份时间段,数据库内部或数据库操作系统存在大的作业,导致资源争用;

生成晚上备份时间段AWR报告,通过 TOP SQL可以看到,除了第一个 expdp作业特别耗时以外,第二和第三也特别耗时;

详细SQL如下:

SQL一:执行 3个小时没有执行完成;

call dbms_space.auto_space_advisor_job_proc ( )

SQL二:执行 4个小时没有执行完成, Oracle通过 CTAS方式自动备份了数据库里最大的一张表,占用大量资源,并产生大量归档文件;

create table "CHENJCH".DBMS_TABCOMP_TEMP_UNCMP tablespace "CHENJCH_TBS_STANDARD" nologging as select /*+ FULL("CHENJCH"."T_BAS_XXX") */ * from "CHENJCH"."T_BAS_XXX" sample block( 99) mytab


二个耗时SQL 都是由 DBMS_SCHEDULER auto_space_advisor 产生的;

可以看到最近几天auto space advisor执行时间突然增加到 4个小时;

select  client_name ,  job_start_time ,  job_duration

   from  dba_autotask_job_history

  where  client_name =   'auto space advisor'

  order   by   2   desc ;

 

解决方案:禁用auto space advisor

select  client_name , status from  dba_autotask_client ;

begin

  DBMS_AUTO_TASK_ADMIN.DISABLE ( client_name =>   'auto space advisor' ,

                               operation    =>   NULL ,

                               window_name =>   NULL );

end ;

禁用后备份时间缩短到两个小时;

  欢迎关注我的微信公众号"IT小Chen",共同学习,共同成长!!!



How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? (文档 ID 1326118.1)

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? ( 文档 ID 1326118.1)


 

修改时间:2018-8-4 类型:PROBLEM  

 



In this Document

 Symptoms

 

 Cause

 

 Solution

 

 References

 

APPLIES TO:

Oracle Database - 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 to 11g, during running of the Automatic Segment Advisor, the table dms_tabcomp_temp_uncmp is being created and is taking up lots of space.

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

CAUSE

The issue is related to unpublished Bug 8896202: "ENABLE COMPRESSION ADVISOR TO ESTIMATE EXADATA HCC COMPRESSION RATIOS."

SOLUTION

The following solutions are available:

1. Apply one-off   for unpublished Bug 8896202, if available.
This fix will stop excessive redo log from being generated due to this table.

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 the Automatic Segment Advisor 
The Automatic Segment Advisor is an automated maintenance task. As such, you can 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 by modifying the appropriate resource plans. 

You can call PL/SQL package procedures to make these changes, but the easier way 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 the numeric link next to the label Segment Advisor Recommendations.  The Segment Advisor Recommendations page appears. 

3. Under the Related Links heading, click the link entitled Automated Maintenance 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.

REFERENCES

NOTE:19047.1  - OERR: ORA-1652 "unable to extend temp segment by %s in tablespace %s" Reference Note


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2219526/,如需转载,请注明出处,否则将追究法律责任。

上一篇: expdp ORA-01555(一)
请登录后发表评论 登录
全部评论
Oracle ACE Associate、OCMU 用户组成员、Oracle 11g OCM、微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    601
  • 访问量
    1303782