客户的数据库使用了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数据    (阅读：4021)
2. EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能    (阅读：1885)
3. EXPDP 过程中的 SYS_XMLGEN 性能影响    (阅读：1663)