技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> cursor_sharing参数对于expdp的性能影响

cursor_sharing参数对于expdp的性能影响

浏览:1539次  出处信息
    客户的数据库使用了cursor_sharing=similar参数,经过测试,发现这一参数极大影响了expdp的性能。

    其本质原因是SQL的执行计划发生了改变。

    在正常情况下28分钟完成的EXPDP操作,在similar模式下用了整整1个小时:

    [oracle@stat backup]$ expdp smg/smg directory=backup dumpfile=sms2.dmp schemas=SMGSTAT

    Export: Release 10.2.0.2.0 - Production on Wednesday, 29 December, 2010 15:14:30

    Copyright (c) 2003, 2005, Oracle.  All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

    With the Partitioning, OLAP and Data Mining options

    Starting "SMGSTAT"."SYS_EXPORT_SCHEMA_02":  smg/******** directory=backup dumpfile=sms2.dmp schemas=SMG

    Estimate in progress using BLOCKS method...

    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 22.57 GB

    Processing object type SCHEMA_EXPORT/USER

    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

    Processing object type SCHEMA_EXPORT/ROLE_GRANT

    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

    Processing object type SCHEMA_EXPORT/DB_LINK

    Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

    Processing object type SCHEMA_EXPORT/TABLE/TABLE

    Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

    Processing object type SCHEMA_EXPORT/TABLE/COMMENT

    Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

    Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

    Processing object type SCHEMA_EXPORT/VIEW/VIEW

    Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

    Processing object type SCHEMA_EXPORT/JOB

    . . exported "SMGSTAT"."MM_HIS"                          3.238 GB 15465541 rows

    . . exported "SMGSTAT"."MM_HIS"                          1.317 GB 16579390 rows

    .......

    . . exported "SMGSTAT"."TEMP_SMSSTAT_MOMT_HOUR"              0 KB       0 rows

    . . exported "SMGSTAT"."T_BMS_U2R"                           0 KB       0 rows

    Master table "SMGSTAT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

    ******************************************************************************

    Dump file set for SMGSTAT.SYS_EXPORT_SCHEMA_02 is:

     /data3/backup/sms2.dmp

    Job "SMGSTAT"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:17:55

    由于cursor_sharing是一个动态参数,所以可以在执行expdp之前进行修改,然后执行导出:

    SQL> show parameter cursor

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    cursor_sharing                       string      EXACT

    cursor_space_for_time                boolean     FALSE

    open_cursors                         integer     300

    session_cached_cursors               integer     20

    SQL> alter system set cursor_sharing=similar scope=memory;

    System altered.

    这是最近遇到的cursor_sharing的又一重要不利影响。

建议继续学习:

  1. ORACLE 12C可以通过expdp导出view数据    (阅读:3873)
  2. EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能    (阅读:1762)
  3. EXPDP 过程中的 SYS_XMLGEN 性能影响    (阅读:1527)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1