« EXPDP测试：性能数据记录 | Blog首页 | 恩墨科技第一次年会顺利结束 »
EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2010/12/estimate_only_estimate.html
链接：https://www.eygle.com/archives/2010/12/estimate_only_estimate.html
在使用Expdp进行导出时，Expdp需要计算导出数据大小容量，Oracle可以通过两种方式进行容量估算，一种是通过数据块数量、一种是通过统计信息中记录的内容估算。两者在不同版本中，可能有巨大的性能差异，尤其是在Oracle 10g的早期版本中，一些Bug严重影响了性能。
我们可以通过expdp的参数ESTIMATE_ONLY和ESTIMATE来评估导出的性能，ESTIMATE_ONLY仅作评估不会导出数据，通过ESTIMATE参数指定statistics和blocks参数来测试两者的差异。
以下是Oracle 10.2.0.5中的测试数据输出，在这一版本中，并无明显的性能差异，评估时间在5~6秒左右.
测试主要的两条命令如下：
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=statistics
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=blocks
测试数出如下：
[oracle@oracle ~]$ expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=statisticsExport: Release 10.2.0.5.0 - 64bit Production on Wednesday, 29 December, 2010 12:29:14Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=statisticsEstimate in progress using STATISTICS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. estimated "EYGLE"."EYGLEE_ISMG_CURRENT" 3.504 GB. estimated "EYGLE"."EYGLEE_ISMG":"M07" 1.702 GB. estimated "EYGLE"."EYGLEE_ISMG":"M06" 1.491 GB. estimated "EYGLE"."EYGLEE_ISMG":"M03" 1.377 GB. estimated "EYGLE"."EYGLEE_ISMG":"M05" 1.306 GB. estimated "EYGLE"."EYGLEE_ISMG":"M12" 1.296 GB. estimated "EYGLE"."EYGLEE_ISMG":"M08" 1.292 GB. estimated "EYGLE"."EYGLEE_ISMG":"M04" 1.290 GB. estimated "EYGLE"."EYGLEE_ISMG":"M01" 1.200 GB. estimated "EYGLE"."EYGLEE_ISMG":"M02" 1.187 GB. estimated "EYGLE"."EYGLEE_ISMG":"M11" 1.147 GB. estimated "EYGLE"."EYGLEE_ISMG":"M09" 1.132 GB. estimated "EYGLE"."EYGLEE_ISMG":"M10" 1.086 GB. estimated "EYGLE"."MONTH_BILLS" 162.1 MB. estimated "EYGLE"."EYGLEE_ISMG2":"M07" 158.9 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M09" 138.6 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M10" 92.45 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M12" 88.28 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M01" 87.56 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M03" 83.47 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M11" 83.26 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M02" 83.14 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M08" 77.55 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M04" 75.46 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M07" 72.32 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M06" 71.94 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M05" 67.50 MB. estimated "EYGLE"."EYGLEE_ISMG2":"M06" 26.27 MB. estimated "EYGLE"."SERVICE_USERS" 15.97 MB. estimated "EYGLE"."BLACK_MDNS" 11.19 MB. estimated "EYGLE"."SERVICE_USER_ACTIONS" 10.13 MB. estimated "EYGLE"."MDNSEGMENTS" 1.766 MB. estimated "EYGLE"."MDNNEW" 1.384 MB. estimated "EYGLE"."MDNSEGINFO" 1.075 MB. estimated "EYGLE"."THREE_PHASE_BILLS" 1019. KB. estimated "EYGLE"."BILL_BLOCK_MDNS" 202.5 KB. estimated "EYGLE"."TMPSX" 34.12 KB. estimated "EYGLE"."TMPGX" 21.30 KB. estimated "EYGLE"."SERVICES" 19.17 KB. estimated "EYGLE"."QTTMP" 12.57 KB. estimated "EYGLE"."SERVICE_PROVIDERS" 9.048 KB. estimated "EYGLE"."ISMG_PROFILES" 8.504 KB. estimated "EYGLE"."ADMIN_USERS" 0 KB. estimated "EYGLE"."ALLOWED_IPS" 0 KB. estimated "EYGLE"."BILL_BLOCK_MDN_SEGMENTS" 0 KB. estimated "EYGLE"."REPORT_FROM_ISMG" 0 KB. estimated "EYGLE"."SMS_FROM_ISMG":"M13" 0 KB. estimated "EYGLE"."SMS_FROM_SERVICE" 0 KB. estimated "EYGLE"."EYGLEE_ISMG":"M13" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M01" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M02" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M03" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M04" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M05" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M08" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M09" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M10" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M11" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M12" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M13" 0 KBTotal estimation using STATISTICS method: 20.39 GBJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:29:19[oracle@oracle ~]$ expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=blocksExport: Release 10.2.0.5.0 - 64bit Production on Wednesday, 29 December, 2010 12:29:29Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=blocksEstimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. estimated "EYGLE"."EYGLEE_ISMG_CURRENT" 4.187 GB. estimated "EYGLE"."EYGLEE_ISMG":"M07" 2 GB. estimated "EYGLE"."EYGLEE_ISMG":"M06" 1.75 GB. estimated "EYGLE"."EYGLEE_ISMG":"M03" 1.625 GB. estimated "EYGLE"."EYGLEE_ISMG":"M04" 1.562 GB. estimated "EYGLE"."EYGLEE_ISMG":"M05" 1.562 GB. estimated "EYGLE"."EYGLEE_ISMG":"M08" 1.562 GB. estimated "EYGLE"."EYGLEE_ISMG":"M12" 1.5 GB. estimated "EYGLE"."EYGLEE_ISMG":"M02" 1.437 GB. estimated "EYGLE"."EYGLEE_ISMG":"M01" 1.375 GB. estimated "EYGLE"."EYGLEE_ISMG":"M09" 1.375 GB. estimated "EYGLE"."EYGLEE_ISMG":"M10" 1.312 GB. estimated "EYGLE"."EYGLEE_ISMG":"M11" 1.312 GB. estimated "EYGLE"."MONTH_BILLS" 232 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M09" 168 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M12" 168 MB. estimated "EYGLE"."EYGLEE_ISMG2":"M07" 168 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M01" 112 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M10" 112 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M02" 104 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M03" 104 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M11" 104 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M08" 96 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M04" 88 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M06" 88 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M07" 88 MB. estimated "EYGLE"."SMS_FROM_ISMG":"M05" 80 MB. estimated "EYGLE"."EYGLEE_ISMG2":"M06" 28 MB. estimated "EYGLE"."SERVICE_USERS" 26 MB. estimated "EYGLE"."BLACK_MDNS" 22 MB. estimated "EYGLE"."SERVICE_USER_ACTIONS" 14 MB. estimated "EYGLE"."MDNSEGMENTS" 3 MB. estimated "EYGLE"."QTTMP" 3 MB. estimated "EYGLE"."THREE_PHASE_BILLS" 3 MB. estimated "EYGLE"."MDNNEW" 2 MB. estimated "EYGLE"."MDNSEGINFO" 2 MB. estimated "EYGLE"."BILL_BLOCK_MDNS" 384 KB. estimated "EYGLE"."ISMG_PROFILES" 128 KB. estimated "EYGLE"."SERVICES" 128 KB. estimated "EYGLE"."SERVICE_PROVIDERS" 128 KB. estimated "EYGLE"."TMPGX" 128 KB. estimated "EYGLE"."TMPSX" 128 KB. estimated "EYGLE"."ADMIN_USERS" 0 KB. estimated "EYGLE"."ALLOWED_IPS" 0 KB. estimated "EYGLE"."BILL_BLOCK_MDN_SEGMENTS" 0 KB. estimated "EYGLE"."REPORT_FROM_ISMG" 0 KB. estimated "EYGLE"."SMS_FROM_ISMG":"M13" 0 KB. estimated "EYGLE"."SMS_FROM_SERVICE" 0 KB. estimated "EYGLE"."EYGLEE_ISMG":"M13" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M01" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M02" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M03" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M04" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M05" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M08" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M09" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M10" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M11" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M12" 0 KB. estimated "EYGLE"."EYGLEE_ISMG2":"M13" 0 KBTotal estimation using BLOCKS method: 24.33 GBJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:29:35
测试数据，仅供参考！
历史上的今天...
>> 2015-12-30文章:
>> 2009-12-30文章:
>> 2008-12-30文章:
>> 2007-12-30文章:
>> 2006-12-30文章:
>> 2005-12-30文章:
>> 2004-12-30文章:
By eygle on 2010-12-30 08:41 | Comments (0) | Backup&Recovery | 2690 |