cursor_sharing参数对于expdp的性能影响

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的又一重要不利影响。