eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« EXPDP测试:性能数据记录 | Blog首页 | 恩墨科技第一次年会顺利结束 »

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

在使用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

测试数据,仅供参考!


历史上的今天...
    >> 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 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com