EXPDP dumpfile和parallel的关系
作者：ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
同事晚上值班使用expdp备份1TB量的DB，“开并行”备份了5个小时。
命令：expdp "'/ as sysdba'" dumpfile=all.dmp directory=expdp schemas=用户 parallel=8 compression=all
原因：
导出文件数量少于并发数时，多于并发将不会工作，也就是说导出文件dumpfile的个数就是有效的parallel并行个数。
dumpfile=file_name.%U.dmp 文件将按需要创建n+1(通配符 %U )
【如果dumpfile 指定一个文件，并发设置过大，在导出过程中可能直接报错（ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes）
导入是paralle要小于dump文件数。 如果paralle 大于dump文件的个数，就会因为超过的那个进程获取不到文件，就不能对性能提高。】
演示：
命令：
expdp "'/ as sysdba'" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all
attach观察expdp导出情况：
$ expdp "'/ as sysdba'" attach=SYS_EXPORT_SCHEMA_05
Job: SYS_EXPORT_SCHEMA_05
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 44C8EA6252512B44E0530701F00AF814
Start Time: Thursday, 29 December, 2016 16:31:45
Mode: SCHEMA
Instance: test
Max Parallelism: 4
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "/******** AS SYSDBA" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all
COMPRESSION ALL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /data/auto/al1.dmp
bytes written: 4,096
Dump File: /data/auto/al2.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: ITEM
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 35
Total Objects: 35
Worker Parallelism: 1
Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_MOVEHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Completed Rows: 5,408,389
Worker Parallelism: 1
Worker 3 Status:
Process Name: DW02
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORYHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Completed Rows: 6,535,089
Worker Parallelism: 1
Worker 4 Status:
Process Name: DW03
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORY
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Worker Parallelism: 1
Export>
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /data/auto/al1.dmp
bytes written: 4,096
Dump File: /data/auto/al2.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: ITEM
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 35
Total Objects: 35
Worker Parallelism: 1
Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_MOVEHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Completed Rows: 26,723,846
Worker Parallelism: 1
Worker 3 Status:
Process Name: DW02
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORYHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Completed Rows: 34,458,978
Worker Parallelism: 1
Worker 4 Status:
Process Name: DW03
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORY
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 35
Worker Parallelism: 1
Export>
****** 只有2个进程(2个dumpfile)在工作(有completed rows:XXX)
后台数据库层查看正在执行的sql如下...event = direct path read 是2个工作进程，其他2个transceive_int 对应等待是wait for unread message on broadcast channel 空闲等待；
使用dumpfile=$bak_time.%U.dmp后台情况..
expdp "'/ as sysdba'" dumpfile=$bak_time.%U.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all logfile=$bak_time.log
.png)
【源于本人笔记】 若有书写错误，表达错误，请指正...
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28602568/viewspace-2133375/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle 跟踪/告警/监听日志的清理脚本
- Oracle JAVA变量类型与字段类型不一致，分区表全扫(INTERNAL_FUNCTION隐含转换)的优化
- Oracle not exist子查询全扫的优化
- Oracle 不知用户的密码时,如何获取dblink创建语句
- Oracle 快速将数据导出到CSV(Excel)文件的方法介绍
- Redis 基本搭建
- PLSQL Developer Profiler 使用 【存储过程调优 工具推荐】
- Oracle 针对子查询里有group by 表全扫的优化
- Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户
- MySQL 变量 、rownum 、 一行拆多行..小知识点
