技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置：首页 --> Oracle --> EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能

EXPDP:使用ESTIMATE_ONLY参数评估ESTIMATE性能

浏览:1763次  出处信息
在使用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=statistics

    

Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 29 December, 2010 12:29:14

    

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

    

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=statistics 
Estimate 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 KB
Total estimation using STATISTICS method: 20.39 GB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:29:19

    

[oracle@oracle ~]$ expdp \\\'/ as sysdba\\\' ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=blocks

    

Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 29 December, 2010 12:29:29

    

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

    

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" ESTIMATE_ONLY=y schemas=EYGLE ESTIMATE=blocks 
Estimate 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 KB
Total estimation using BLOCKS method: 24.33 GB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:29:35

    

测试数据，仅供参考！

建议继续学习：

  1. ORACLE 12C可以通过expdp导出view数据    (阅读：3874)
  2. cursor_sharing参数对于expdp的性能影响    (阅读：1540)
  3. EXPDP 过程中的 SYS_XMLGEN 性能影响    (阅读：1529)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：Oracle数据库恢复:存储故障导致的数据损坏
后一篇：cursor_sharing参数对于expdp的性能影响 >>
文章信息
© 2009 - 2024 by blogread.cn 微博：@IT技术博客大学习

京ICP备15002552号-1