cursor_sharing参数对于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的又一重要不利影响。
建议继续学习:
- ORACLE 12C可以通过expdp导出view数据 (阅读:3873)
- EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能 (阅读:1762)
- EXPDP 过程中的 SYS_XMLGEN 性能影响 (阅读:1527)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:eygle@eygle.com(eygle) 来源: Oracle Life
- 标签: cursor_sharing expdp
- 发布时间:2011-01-05 22:42:05
- [66] Oracle MTS模式下 进程地址与会话信
- [65] Go Reflect 性能
- [64] 如何拿下简短的域名
- [61] android 开发入门
- [59] 图书馆的世界纪录
- [59] 【社会化设计】自我(self)部分――欢迎区
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [54] 视觉调整-设计师 vs. 逻辑
- [48] 界面设计速成
- [48] 读书笔记-壹百度:百度十年千倍的29条法则