温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

微课sql优化(5)、统计信息收集(3)-关于默认采样率

发布时间:2020-08-07 10:39:09 来源:ITPUB博客 阅读:206 作者:orastar 栏目:关系型数据库

1、关于默认采样率


在不同版本的Oracle上统计信息收集的默认值不一定相同,例如:
ESTIMATE_PERCENT :默认值:
 9i:100%
10g:DBMS_STATS.AUTO_SAMPLE_SIZE(使用非常小的估计百分比)
11g:DBMS_STATS.AUTO_SAMPLE_SIZE(使用较大的估算百分比 - 100%)
METHOD_OPT:默认值
9i:“FOR ALL COLUMNS SIZE 1”,实际上没有详细的列统计。
10g和11g:“FOR ALL COLUMNS SIZE AUTO” - 此设置意味着DBMS_STATS决定哪些列添加直方图,制定更好的计划。 
在11g中,使用ESTIMATE_PERCENT的默认值100%,统计信息收集尽可能准确。收集统计信息时间限制,在以前的版本中,100%的样本是不可能的,但是11g实现了一种新的哈希算法来计算统计数据而不是排序(在9i和10g中,“慢”部分通常是排序),这显着提高了收集时间和资源使用。

2、统计信息收集了什么,有什么作用

  • table statistics
微课sql优化(5)、统计信息收集(3)-关于默认采样率
NUM_ROWS:  NUM_ROWS是计算cardinality的基础,如果该表是嵌套循环联接(nested loops join)的驱动表,表的行数就特别重要,驱动表的行数,决定了被驱动表的扫描次数。
BLOCKS : 表使用的块数,该参数和DB_FILE_MULTIBLOCK_READ_COUNT一块使用,可以计算 全表扫描所需要的IO数量。
AVG_ROW_LEN :   表中记录的平均大小bytes
EMPTY_BLOCKS :   未使用的空数据块数据量,已使用的数据块与高水位之间的数据块数量。(ANALYZE)
AVG_SPACE :   平均空闲空间bytes(ANALYZE)
CHAIN_CNT :      行迁移或行链接
  • index statistics

微课sql优化(5)、统计信息收集(3)-关于默认采样率

如果需要使用索引,CBO 需要索引的前导列在查询条件中使用,如果没有查询条件或索引列包含所有查询结果,CBO更倾向使用全索引扫描或全表扫描。
BLEVEL: 从根结点到叶子结点的高度,用来计算叶子结点查找的成本。0表明根块和叶块是一样的。 
LEAF_BLOCKS:  用于计算 full index scane及index range scan的成本 。
CLUSTERING_FACTOR :  基于索引的值计算表中记录的顺序程度。如果该值接近块的数量,表中记录有序存储。在这种情况下,索引条目在单个叶块往往指向相同的数据块中的行。如果该值接近表的行数,表示记录无序存储。在这种情况下,不太可能在同一叶块索引条目指向相同的数据块中的行。
DISTINCT_KEYS  :  不同的索引值数量。在主键约束和唯一索引中,该值等于表记录数。
AVG_LEAF_BLOCKS_PER_KEY :   叶块的平均数量在每一个不同的值出现在索引中,四舍五入为最接近的整数。实施独特的索引和主键约束,这个值总是1(一个)。索引值在不同叶子结点出现的平均数量,在唯一索引和主键约束中,该值为总是这1。
AVG_DATA_BLOCKS_PER_KEY  :   表中数据块指向同一索引值的平均数量。
NUM_ROWS  :   索引中的行数
  • column statistics

微课sql优化(5)、统计信息收集(3)-关于默认采样率

NUM_DISTINCT : 计算选择性。(例: 1/NDV) 
LOW_VALUE and HIGH_VALUE: 对所有数据类型,CBO假设在高值和低值之间是均匀分布的。这些值用于计算范围扫描的选择性。 
NUM_NULLS:  对存在空值的列,使用is null 和is not null查询时,计算选择性 
DENSITY:   
未收集直方图
 Density = 1 / Number of distinct NON null values
The number of distinct NON-null values for a column (COL1) on table TABLE1 can be obtained as follows:
select distinct count(COL1)  from   TABLE1 where  COL1 is not null;
收集直方图
A non-popular value is one that does not span multiple bucket end points.
A popular value is one that spans multiple end points.
For non-popular values the density is calculated as the number of non-popular 
values divided by the total number of values. Formula:
    Density =  Number of non-popular values
               ----------------------------
                  total number of values
We only use the density statistic for non-popular values. 
Popular values calculate the selectivity of a particular column values by 
using histograms as follows:
The Selectivity for popular values is calculated as the number of end points 
spanned by that value divided by the total number of end points. Formula:
    Selectivity = Number of end points spanned by this value
                  ------------------------------------------
                         total number of end points

3、练习3:10g和11g自动采样率测试


exec DBMS_STATS.DELETE_TABLE_STATS('HT','A_AMT');
exec dbms_stats.gather_table_stats('HT','A_AMT');
set line 200
col owner for a10
col table_name for a20 
col LAST_ANALYZED for a30
SELECT owner,
table_name,
num_rows,
sample_size,
trunc(sample_size / num_rows * 100) estimate_percent,
to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi:ss') LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE owner='HT' and table_name='A_AMT';
oracle 10g测试结果
SQL> exec dbms_stats.gather_table_stats('HT','A_AMT');
PL/SQL procedure successfully completed.
SQL> set line 200
SQL> col owner for a10
col table_name for a20 
col LAST_ANALYZED for a30
SELECT owner,
table_name,
num_rows,
sample_size,
trunc(sample_size / num_rows * 100) estimate_percent,
to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi:ss') LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE owner='HT' and table_name='A_AMT';SQL> SQL> SQL>   2    3    4    5    6    7    8  
OWNER       TABLE_NAME      NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT LAST_ANALYZED
---------- -------------------- ---------- ----------- ---------------- ------------------------------
HT                   A_AMT         59968         59968        100                 2017-09-22 02:07:47
SQL> !date
Fri Sep 22 02:08:00 CST 2017
SQL> select version from v$instance;
VERSION
---------------------------------------------------
10.2.0.5.0
SQL> 
oracle 11g测试结果
OWNER       TABLE_NAME      NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
---------- -------------------- ---------- ----------- ----------------
HT            A_AMT      59968  59968        100

4、小结


1、 Oracle 10g可能 使用非常小的估计百分比,建议手工100%收集,至少30%。
2、 在11g中,使用ESTIMATE_PERCENT的默认值100%,统计信息收集尽可能准确。-- 11g:DBMS_STATS.AUTO_SAMPLE_SIZE(使用较大的估算百分比 - 100%)
3、直方图收集: METHOD_OPT:默认值 10g和11g:“FOR ALL COLUMNS SIZE AUTO” - 此设置意味着DBMS_STATS决定哪些列添加直方图,制定更好的计划。
4、了解 统计信息收集: table statistics index statistics column statistics,各指标内容及作用
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI