如何不影响生产库性能的情况下评估整库的容量

柔情只为你懂 2023-06-26 12:11 5阅读 0赞

最近有位朋友，要做一个T级别的数据迁移工作，打算使用数据泵，这个工具提供了非常多的参数，为的就是控制导入导出的过程。

碰巧，老杨前两天发了一篇文章《[【精品篇】\_如何在不影响Oracle生产库性能的评估整库大小][Oracle]》，就介绍了一个数据泵非常方便的参数。

【引言】

最近碰到一个小问题：一TB级的Oracle生产库，因为要走数据迁移，需要先行评估整个库的迁移数据量大小，但又不得影响生产库运行性能。如何搞？大家都知道，expdp数据泵有两个很好用的参数ESTIMATE和ESTIMATE\_ONLY，此两个参数可以保证在不真正发起逻辑备份的情况下评估整个迁移生产库的大小。今天念叨下这个小问题。

这里使用$ expdp -help先看expdp的ESTIMATE和ESTIMATE\_ONLY两个参数的介绍：

ESTIMATE
    Calculate job estimates.
    Valid keyword values are: [BLOCKS] and STATISTICS.
    
    
    ESTIMATE_ONLY
    Calculate job estimates without performing the export.

ESTIMATE

默认：blocks  
指定计算每张表使用磁盘空间的方法  
ESTIMATE=\[BLOCKS | STATISTICS\]  
1.BLOCKS - 通过块数和块大小计算  
2.STATISTICS -每张表的统计信息计算

Expdp可计算导出数据大小容量，一种是通过数据块数量、一种是通过统计信息中记录的内容估算。通过expdp的参数ESTIMATE\_ONLY和ESTIMATE来评估导出的性能，ESTIMATE\_ONLY仅作评估不会导出数据，通过ESTIMATE参数指定statistics和blocks参数来测试两者的差异。

以下是Oracle 11.2.0.4中的测试数据输出，在此版本中，我们来看下ESTIMATE的statistics和blocks两个参数各自评估大小和用时。

两条命令如下：

$expdp \'/ as sysdba\' ESTIMATE_ONLY=y FULL=y ESTIMATE=blocks
    $expdp \'/ as sysdba\' ESTIMATE_ONLY=y FULL=y ESTIMATE=statistics

具体执行如下：

$ expdp \'/ as sysdba\' ESTIMATE_ONLY=y FULL=y ESTIMATE=blocks
    
    
    Export: Release 11.2.0.4.0 - Production on Thu Dec 12 16:57:57 2019
    
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" ESTIMATE_ONLY=y FULL=y ESTIMATE=blocks
    Estimate in progress using BLOCKS method...
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    .  estimated "BIDW"."W_LOY_ACCRUAL_ITEM_F"               834.1 GB
    .  estimated "BIDW"."W_LOY_ACTIVITY_F"                   261.6 GB
    .
    .
    .
    .  estimated "S_NQ_SCHED"."S_NQ_JOB"                         0 KB
    .  estimated "S_NQ_SCHED"."S_NQ_JOB_PARAM"                   0 KB
    Total estimation using BLOCKS method: 2599. GB
    Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Dec 12 17:00:48 2019 elapsed 0 00:02:50

如上可以看出，使用ESTIMATE=blocks评估出来的大小为2599.GB，耗时：00:02:50；

接下来再看ESTIMATE=statistics方式。命令如下：

$expdp \'/ as sysdba\' ESTIMATE_ONLY=y FULL=y ESTIMATE=statistics
    
    
    Export: Release 11.2.0.4.0 - Production on Thu Dec 12 16:37:08 2019
    
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" ESTIMATE_ONLY=y FULL=y ESTIMATE=statistics
    Estimate in progress using STATISTICS method...
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    .  estimated "BIDW"."W_LOY_ACCRUAL_ITEM_F"               676.2 GB
    .  estimated "BIDW"."W_LOY_ACTIVITY_F"                   226.4 GB
    .
    .
    .
    .  estimated "S_NQ_SCHED"."S_NQ_JOB"                         0 KB
    .  estimated "S_NQ_SCHED"."S_NQ_JOB_PARAM"                   0 KB
    Total estimation using STATISTICS method: 2132. GB
    Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Dec 12 16:41:55 2019 elapsed 0 00:04:40

如上，使用ESTIMATE=STATISTICS评估出来的大小为2132.GB，耗时：00:04:40；

可以看出，两种方式统计的大小不一样，为何？

分析推断：

ESTIMATE的默认方式是blocks。个人理解为：Oracle的块大小默认为8KB，直接对库使用的块数计算使用量，简单明了，但考虑到块有高水位线、碎片等因素，故统计不精确。而STATISTICS 方式因计算的是对每张表的实际使用情况，故更为精确。

但STATISTICS的方式也非很精确，原因为：1. 该方式只是对表做统计，没有对索引、列、系统做统计，2. 一个表中被修改的行数超过stale\_percent(缺省值10%)时才会认为这个表的统计数据过时，需要重新搜集。

注意：

如果压缩了表，那么使用ESTIMATE=BLOCKS计算的值时不准确的，这个时候就应该使用ESTIMATE=STATISTICS。

推荐：

使用ESTIMATE的默认方式blocks进行估算，原因很简单，估值按最大值估算申请空间更靠谱。

# 为了加深理解，这里介绍下Oracle统计信息收集。 #

大家都知道，Oracle较优执行计划的挑选是基于CBO(cost based optimized)判断，而CBO对哪个执行计划较优的判断是基于统计信息。优化器统计范围包含：

1. 表统计：行数，块数，行平均长度；

all\_tables：NUM\_ROWS，BLOCKS，AVG\_ROW\_LEN；

2. 列统计：列中唯一值的数量（NDV），NULL值的数量，数据分布；

DBA\_TAB\_COLUMNS：NUM\_DISTINCT，NUM\_NULLS，HISTOGRAM；

3. 索引统计：叶块数量，等级，聚簇因子；

DBA\_INDEXES：LEAF\_BLOCKS，CLUSTERING\_FACTOR，BLEVEL；

4. 系统统计：

I/O性能与使用率；

CPU性能与使用率；

存储在aux\_stats$中，需要使用dbms\_stats收集，I/O统计在X$KCFIO中；

查询表上一次收集统计信息的时间：

SQL> select owner,table\_name,last\_analyzed from dba\_tables where owner='SCOTT';

Oracle中如何搜集统计信息？ 主要有2种方法：

方式1： analyze语句

analyze可以用来收集表，索引，列以及系统的统计信息和直方图，以下为一些典型用法：

analyze table scott.emp compute statistics; 
    --收集所有的统计信息和直方图信息，包括表、列、索引。
    analyze table scott.emp compute statistics for table; 
    --收集emp表的统计信息，不含列、索引统计信息和直方图。
    analyze table scott.emp compute statistics for all columns; 
    --收集所有列的统计信息和直方图（超大表较耗资源，因为只要列中有非空值，那么就会收集这个列的统计信息和直方图）。
    analyze table scott.emp compute statistics for all indexed columns;  
    --收集所有索引列的统计信息和直方图。
    analyze table scott.emp compute statistics for all indexes; 
    --收集所有索引统计信息，不含列的统计信息和直方图。
    analyze table scott.emp compute statistics for columns 列1,列2；
    --收集2个列的统计信息和直方图。
    analyze index idx_ename delete statistics; 
    --删除索引idx_ename的统计信息。
    analyze table scott.emp delete statistics; 
    --删除表t1所有的表，列，索引的统计信息和列直方图。
    analyze table scott.emp estimate statistics sample 15 percent for table; 
    --收集emp表的统计信息，以估算模式采样比例为15%进行收集，不含列、索引统计信息和直方图。

从语法可以看出，只有指定列统计信息收集时，才会收集相关列的直方图，此外收集直方图时for子句还可以加size子句，size的取值范围是1-254，默认值是75，表示直方图的buckets的最大数目。而dbms\_stats包的size选择则有：数字|auto|repeat|skewonly选项，但analyze的size只能是数字。

关于直方图介绍：

A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets.

直方图就是一种特殊的列统计信息，只有列才有直方图。

用于查看analyze后统计信息的SQL；

select t.num_rows as num_rows_in_table, i.index_name, i.num_rows as num_rows_in_index, co.num_analyzed_cols,ch.histogram_cnt
    from (select num_rows from user_tables where table_name ='EMP') t,
         (select index_name,num_rows from user_indexes where table_name = 'EMP') i,
         (select count(*) as num_analyzed_cols from user_tab_columns where table_name='EMP' and num_distinct is not null) co,
         (select count(distinct column_name) histogram_cnt from user_tab_histograms where table_name = 'EMP' ) ch;

需要注意的一点是for table选项并不只收集表统计信息，将列和索引的统计信息一块收集了。

oracle会监控所有表的DML活动并在SGA中进行记录。监控的信息会定时的刷新到磁盘且可以通过\*\_tab\_modifications视图来查看。

可调用dbms\_stats.flush\_database\_monitoring\_info过程来手动刷新这些数据。如果想在查询时得到最新信息(在所有统计数据收集之前内部监控数据会被刷新)。可通过查询user\_tab\_statistics视图中的stale\_stats列来查看哪个表的统计数据过时。表的stale\_stats被设置为NO,统计数据是最新的。表的stale\_stats被设置为YES,统计数据是过时的，表的stale\_stats没有被设置说明丢失统计数据。

** **

方式2：调用dbms\_stats包

dbms\_stats中负责收集统计信息的几个存储过程：

GATHER\_DATABASE\_STATS

--This procedure gathers statistics for all objects in the database.

GATHER\_DICTIONARY\_STATS

--This procedure gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components.

GATHER\_FIXED\_OBJECTS\_STATS

--This procedure gathers statistics for all fixed objects (dynamic performance tables).

GATHER\_INDEX\_STATS

--This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity and no\_invalidate arguments are not relevant to these types of indexes.

GATHER\_SCHEMA\_STATS

--This procedure gathers statistics for all objects in a schema.

GATHER\_SYSTEM\_STATS

--This procedure gathers system statistics.

GATHER\_TABLE\_STATS

--This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.

具体使用案例：

1.EXEC DBMS\_STATS.GATHER\_SCHEMA\_STATS('SCOTT',estimate\_percent=>80,method\_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

2.EXEC DBMS\_STATS.GATHER\_TABLE\_STATS('SCOTT','EMP',estimate\_percent=>80,

method\_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

3. EXEC DBMS\_STATS.GATHER\_INDEX\_STATS('SCOTT','PK\_EMP',estimate\_percent=>80,degree=>4);

** **

dbms\_stats与analyze的区别：

1.analyze收集系统内部对象会报错，而dbms\_stats不会；

2.analyze不能正确的收集分区表的统计信息，而dbms\_stats可以通过指定粒度来实现(granularity)。

3.analyze不能并行的收集统计信息，而dbms\_stats可以(可以加上degree=>4来实现并行度为4的收集)。

4.Oracle推荐使用dbms\_stats来收集统计信息，analyze将会被逐渐抛弃。

【总结】

1. 本文介绍了在不影响生产库运行性能的前提下。使用expdp数据泵参数ESTIMATE和ESTIMATE\_ONLY，在不真正发起逻辑备份的情况下，可以评估整个迁移生产库大小的用法和差别及分析；

2. 同时介绍了Oracle的CBO，统计信息、直方图，如何收集统计的两种方法调用dbms\_stats包和analyze两种方式，推荐使用调用dbms\_stats包；

3. 使用expdp评估库大小时，推荐使用ESTIMATE的默认方式blocks进行估算，原因很简单，估值按最大值估算申请空间更靠谱。

【参考】

https://docs.oracle.com/cd/B19306\_01/appdev.102/b14258/d\_stats.htm\#CIHBIEII

【参考】

https://docs.oracle.com/cd/B12037\_01/server.101/b10759/statements\_4005.htm\#i2150533

【参考】

https://docs.oracle.com/database/121/TGSQL/tgsql\_histo.htm\#TGSQL36

【参考】

https://www.cnblogs.com/leohahah/p/9413513.html

曾经写过的一些和导入导出相关的文章，

《[导入导出的两个小错误][Link 1]》

《[解决导入过程中出现的ORA-02289错误][ORA-02289]》

《[Oracle导入导出的常见错误][Oracle 1]》

《[生产数据导入测试环境碰见的一些问题][Link 2]》

《[imp/exp导入导出的一些错误][imp_exp]》

《[使用exp导出报错EXP-00091][exp_EXP-00091]》

[Oracle]: http://mp.weixin.qq.com/s?__biz=Mzg2NTI0NDE1Mg%3D%3D&chksm=ce5c4263f92bcb75e290c44de15f5a68569c00c02a56e7bd83095e5bb66e800992e6df8204a3&idx=1&mid=2247484660&scene=21&sn=557832fe7fcaae8b272e9147640b23c0#wechat_redirect
[Link 1]: https://blog.csdn.net/bisal/article/details/98814290
[ORA-02289]: https://blog.csdn.net/bisal/article/details/77890461
[Oracle 1]: https://blog.csdn.net/bisal/article/details/84938206
[Link 2]: https://blog.csdn.net/bisal/article/details/76560331
[imp_exp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814df4ddf6c4e2266e8b5a5945b10a66c8c88a399eb45f9503980be4c5e838c8fe6ac96fd6&idx=1&mid=2247485447&scene=21&sn=aec596757a180e6eb34f92a2cffceed1#wechat_redirect
[exp_EXP-00091]: https://blog.csdn.net/bisal/article/details/49592973