eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 顽童仍未老 词曲尽锋芒 - 李宗盛《给自己的歌》 | Blog首页 | EXPDP 过程中的 SYS_XMLGEN 性能影响 »

cursor_sharing参数对于expdp的性能影响

客户的数据库使用了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的又一重要不利影响。



历史上的今天...
    >> 2007-01-01文章:
           2007年 新的开端
    >> 2006-01-01文章:
           English Blog Starting ...
           Google and Baidu:Happy New Year!
    >> 2005-01-01文章:
           新的一年-辛苦的开端

无觅

By eygle on 2011-01-01 08:16 | Comments (1) | Backup&Recovery | 2691 |

1 Comment

元旦,就开始调试!
加油,预祝新年生意红火


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com