第十三章（二）

五、统计信息的管理

1.pending statistics

11.2版本开始，收集统计信息有了如下选项：
收集后自动显示；
Save the new statistics saved as pending（在需要统计信息时使用最新的）

通过如下方法确认统计信息是否自动publish：
select dbms_stats.get_prefs('PUBLISH') from dual；
true表示自动published

可以在用户或表的级别修改publish设置：
exec dbms_stats.set_table_prefs('SCOTT','EMP','PUBLISH','false');

当需要将pending statistics应用时，将optimizer_use_pending_statistics设置为true：
alter session set optimizer_use_pending_statistics=true；

You can also publish the pending statistics for a specific database object. For example, by using the following statement:
Exec dbms_stats.publish_pending_stats('SH','CUSTOMERS');

published statistics存储在数据字典的user_tab_statistics和user_ind_statistics中
pending statistics在user_tab_pending_stats和user_ind_pending_stats中

已经发布的统计数据可以在user_tab_statistics和user_ind_statistics中看到；
待用（pending）统计信息在user_Tab_pending_stats和user_ind_pending_Stats中看到；

如果统计信息自动发布，如果我们收集统计信息后查看，user_tab_statistics中有更新的数据，而user_Tab_pending_stats中是始终空的。

2.存储统计信息的前版本

应用新的统计信息后，旧的版本会被自动存储。可以用时间戳进行调用并比较。

dba_optstat_operations包含dbms_Stats操作的用户和数据库级别的统计信息历史记录。
*_TAB_STATS_HISTORY包含表统计信息修改的记录（只是表和时间的记录）

数据库中旧的统计信息会基于statistics history retention setting和最近系统分析的频率来定期清除。
可以通过dbms_stats.alter_stats_history_retention配置。默认存储31天；

3.统计信息导入导出
可以从数据字典导入用户表，也可以从一个数据库导入另一个。
需要首先创建一个表用于存储统计信息，然后导入：
SQL> exec dbms_stats.create_stat_table(ownname => 'SCOTT',stattab => 'STAT_TABLE');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'STAT_TABLE');

PL/SQL procedure successfully completed

SQL> select * from stat_table;

…………结果太长，省略。

可以是表级、schema级或数据库级。

然后可以将结果导入。

用户表中的统计信息是不会被数据库使用的。

导入实验：
SQL> exec dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'STAT_TABLE');

PL/SQL procedure successfully completed

SQL> delete from emp;

15 rows deleted

SQL> commit;

Commit complete

SQL> exec dbms_stats.import_table_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'STAT_TABLE');

PL/SQL procedure successfully completed

select * from stat_table t;
select * from user_tab_statistics where table_name='EMP';

可以看到统计信息中emp表还是15行数据，表示导入成功。

4.统计信息的存储
使用时机：
想要覆盖旧版本统计信息时
想清空历史统计信息时

而以下情况需要导入导出：
比较两个统计信息的变化
将一个数据库的统计信息移到另一个
想要长时间保留

5.统计信息的锁定

不想让统计信息变化时，可以将其锁定。方法为：dbms_stats.lock_schema/table_stats。对应也有解锁方法。只针对表和用户，不能对数据库作此操作

6.设置统计信息
通过set_*statistics过程可以设置表、列、索引和系统统计信息。不建议此操作

7.handling missing statistics
一般情况下，oracle在计算表的统计信息时，如果发现表没有必要的统计信息，会自动收集。但对于远程表和外部表不会有此操作。
这种情况以及动态收集被禁用时，优化器使用统计信息的默认值。
表的默认信息：
Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
Average row length：100 bytes
Number of blocks：100 or actual value based on the extent map
Remote cardinality：2000 rows
Remote average row length：100 bytes

索引的默认信息：
Levels：1
Leaf blocks：25
Leaf blocks/key：1
Data blocks/key：1
Distinct keys：100
Clustering factor：800

六、通过动态采样评估统计信息

1.动态采样的目的
Using dynamic sampling the optimizer can improve plans by making better estimates for predicate selectivity. Dynamic sampling can supplement statistics such as table block counts, applicable index block counts, table cardinalities (estimated number of rows), and relevant join column statistics.

2.动态采样
默认开启，可以通过optimizer_dynamic_sampling=0禁用

采样级别：
控制着触发的时机以及采样大小。
使用optimizer_dynamic_sampling进行设置。
共十个等级。

编译时，优化器基于一系列特性来决定是否动态采样。包括语句是否采用并行等。
对并行语句，优化器自动判断是否使用以及动态采样级别。基于表的大小和谓词的复杂程度判断。
优化器认为并行语句是资源敏感的（resorece-intensive)，因此编译时进行额外的操作来确保得到最优的计划。

对于串行之星的语句，动态采样的级别决定于optimizer_dynamic_sampling，由优化器自动触发。

无论串行还是并行语句，都是在如下情况下自动收集：
（1）没有统计信息
考虑到编译的时间，这时候自动收集的信息相对简单
（2）已收集的统计信息看上去不能用或者可能导致错误的评估
对于包含as of子句的sql，不会自动收集统计信息

3.设置动态采样级别
最好不要用到动态采样。动态采样在以下情况下有效：
由于复杂的谓词，你知道自己得到了错误的执行计划
动态采样的时间在整个查询的执行时间中所占比例很小
查询多次进行

七、统计信息查看

■DBA_TABLES and DBA_OBJECT_TABLES
■DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
■DBA_TAB_HISTOGRAMS
■DBA_TAB_COLS
■DBA_COL_GROUP_COLUMNS
■DBA_INDEXES and DBA_IND_STATISTICS
■DBA_CLUSTERS
■DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS
■DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS
■DBA_PART_COL_STATISTICS
■DBA_PART_HISTOGRAMS
■DBA_SUBPART_COL_STATISTICS
■DBA_SUBPART_HISTOGRAMS

1.柱状图信息的查看
高平衡柱状图height-balanced histograms
频率柱状图frequency histograms

存储在*tab_col_statistics的histogram列。

height-balanced histograms将结果平均分成若干块，根据每块的最大和最小值可以看到分布情况。

查看方法：
BEGIN
DBMS_STATS.GATHER_table_STATS (
OWNNAME => 'OE',
TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand' );
END;
/
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND 237 10 HEIGHT BALANCED
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

可以看到十段数据的分隔值：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 0
1 27
2 42
3 57
4 74
5 98
6 123
7 149
8 175
9 202
10 353

（2）frequency histograms

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-751578/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-751578/