（Oracle）SQL优化基础（二）：统计信息

姜豆豆耶

已于 2024-03-10 16:44:16 修改

阅读量1.2k

点赞数 33

分类专栏： Oracle之SQL优化文章标签： oracle sql 数据库

于 2024-03-10 16:17:52 首次发布

本文链接：https://blog.csdn.net/m0_58872140/article/details/136581972

版权

Oracle之SQL优化专栏收录该内容

7 篇文章 5 订阅

订阅专栏

前言

Oracle在选择最优执行计划时，是其CBO优化器依据统计信息进行判断的。所以统计信息这块内容就超级重要了，这有点像情报部门，情报工作搞得好，干仗才利索；情报部门出问题了，那就只有挨揍的份了。强如李云龙，当年也派了侦察兵去楚云飞的营地蹲点，随后就帮楚云飞干掉了钱伯钧。

闲篇少叙，开始介绍。下面的一个导图就是对统计信息的一个基本描述。大家可以结合此图来阅读后面的具体内容。

一、查看统计信息

查看表的统计信息

SELECT 
owner,
table_name,
num_rows,
blocks,
avg_row_len
FROM dba_tables
WHERE owner = '&schema'
AND table_name = '&表名'

查看列的统计信息

SELECT
tcs.column_name,
dt.num_rows,
tcs.num_distinct 基数,
round(tcs.num_distinct / dt.num_rows *100,2)  选择性,
tcs.num_nulls,
tcs.num_buckets,
tcs.histogram
FROM dba_tab_col_statistics tcs,dba_tables dt
WHERE tcs.owner = dt.owner 
AND tcs.table_name = dt.table_name 
AND tcs.owner = '&schema'
AND tcs.table_name = '&表名' ;

查看索引的统计信息

SELECT
dic.table_owner||'.'||dic.table_name,
dic.column_name,
di.index_name,
di.blevel,
di.leaf_blocks,
di.clustering_factor,
di.status
FROM dba_indexes di,dba_ind_columns dic
WHERE di.index_name = dic.index_name
AND di.owner = '&schema'
AND di.table_name = '&表名' ;

二、统计信息参数设置

BEGIN
  dbms_stats.gather_table_stats(
                      ownname             => '&schema',
                      tabname             => '&表名',
                      estimate_percent    => 100,
                      method_opt          => 'for all columns size auto',
                      no_invalidate       => FAlSE,
                      degree              => 1,
                      cascade             =>TRUE  
                       );
END;

ownname

表的拥有者，也就是schema；不区分大小写。

tabname

表名，不区分大小写。

estimate_percent

采样率。范围是0.000001~100。采样率根据表的大小情况进行设置。如果是小表，比如1G大小以下的表，建议采样率设置为100。如果是大表，比如1G以上的表，采样率可以设置区间建议为[30,50]。如果是超大表，几十上百GB的表，那么官方建议是先分区，再分别对每个区收集统计信息。

因为表中的某些列数据分布不一定均匀，所以采样率尽量越高越好；实在是超大表，采样成本太高的话，也尽量不低于30。

查看表的统计信息采样率，SQL代码如下。

SELECT 
owner,
table_name,
num_rows,
sample_size,
ROUND(sample_size / num_rows * 100) 采样率
FROM dba_tab_statistics
WHERE owner = '&schema'
AND table_name = '&表名' ;

method_opt

该参数表示收集直方图策略。收集直方图的策略有以下五种。

'for all columns size 1'
'for all columns size skewonly'
'for all columns size auto'
'for all columns size repeat'
'for columns 列名 size skewonly'

现在对以上的直方图策略分别做下描述：

（1）'for all columns size 1'

该策略表示对表中所有列都不收集直方图。

（2）'for all columns size skewonly'

该策略表示对表中所有列收集直方图，Oracle会自动判断是否收集直方图。例如Oracle会将选择性较高、或者null值较多的列排除，对于这些列不会收集直方图。在实际生成中，不需要对所有列收集直方图，因为不是所有列都会作为where条件，对where条件之外的列收集直方图没有意义。

（3） 'for all columns size auto'

该策略表示对于出现在where条件中的列，Oracle会自动判断是否收集直方图。对于选择性较低，数据分布不均匀且出现在where条件中的列，Oracle会判断其应该收集直方图；如果某列出现在where条件中，但该列的选择性很高，数据分布很均匀，那么Oracle的算法也会判定此列不需要收集直方图。

（4）'for all columns size repeat'

该策略表示目前表中有哪些列已经收集了直方图，这次就会继续对这些列收集直方图。当应用系统达到稳定时，是应该采取该策略的。

（5） 'for columns 列名 size skewonly'

该策略表示对单独列收集直方图，但如果表中的其他列之前也收集过直方图，那么这次也会继续收集直方图。

（6）for columns 列名 size 1

该策略表示删除某列的直方图信息收集

下面是我个人的直方图思路，朋友们可以参考。如果您有其他的见解也可以留言或者私信。

在实际生产中：

一个应用系统如果上线不久，涉及的优化工作太多，我们可以使用'for all columns size auto'策略，节省精力，也避免了不必要的列直方图收集。
系统上线一段时间后，我们已经比较深度的了解业务和数据分布情况，可以对某些列删除和收集直方图信息。
当应用系统已经稳定后，我们可以使用'for all columns size repeat'策略收集直方图信息，因为已经稳定，所以只需要沿用之前的策略即可。

no_invalidate

该参数表示共享池中涉及到该表的游标是否立即失效。

默认值是dbms_stats.auto_invalidate，表示让Oracle判断是否立即失效。建议是将该参数设置为FALSE，立即失效。很多的SQL执行缓慢是统计信息过期导致，重新收集了统计信息后执行计划还是没有改变；原因就是没有将该参数设置为FALSE。

degree

该参数表示收集统计信息的并行度，默认是null。

如果表没有设置degree，收集统计信息的时候就不会开并行。如果表设置了degree，那么收集统计信息的时候，就按照表的degree数值来进行并行收集。可以查询dba_tables.degree来查看表的degree。一般情况下，表的degree都为1。建议是根据实际生产中的资源情况来设置并行度。

cascade

该参数表示在收集表的统计信息时，是否级联收集索引的统计信息。建议设置为TRUE，表示在收集表的统计信息时，级联收集索引的统计信息。

三、统计信息是否过期

先用统计信息过期问题的实验复现，来强调统计信息过期的严重性。

创建一张测试表

create table scott.统计信息测试表 as select * from dba_objects

在owner列上创建一个索引

CREATE INDEX  IDX_OWNER ON scott.统计信息测试表(OWNER);

查看以owner作为WHERE条件的SQL执行计划

此时发现执行计划中的行数是61，通过count查询， owner = ‘SCOTT’ 的数量确实是61，此时的统计信息是准确的。

SQL> SELECT * FROM scott.统计信息测试表 WHERE owner = 'SCOTT';

已选择61行。


执行计划
----------------------------------------------------------
Plan hash value: 2962945524

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    61 |  5917 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| 统计信息测|    61 |  5917 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OWNER |    61 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       7799  bytes sent via SQL*Net to client
        567  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         61  rows processed

SQL> set autot off
SQL> SELECT count(*) FROM scott.统计信息测试表 WHERE owner = 'SCOTT';

  COUNT(*)
----------
        61

将表中object_id<8888的owner都更新为SCOTT

UPDATE scott.统计信息测试表 SET owner = 'SCOTT' WHERE object_id < 20000;

再次查看同一SQL的执行计划

此时 owner = ‘SCOTT’ 的数量已经变成了19575，但其执行计划中的ROWS却依然是61，统计信息已经过期了，不准确了。

SQL> SELECT count(*) FROM scott.统计信息测试表 WHERE owner = 'SCOTT';

  COUNT(*)
----------
      19575

SQL> set autot trace
SQL> SELECT * FROM scott.统计信息测试表 WHERE owner = 'SCOTT';

已选择8783行。


执行计划
----------------------------------------------------------
Plan hash value: 2962945524

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    61 |  5917 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| 统计信息测|    61 |  5917 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OWNER |    61 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1318  consistent gets
          0  physical reads
          0  redo size
     930520  bytes sent via SQL*Net to client
       6958  bytes received via SQL*Net from client
        587  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8783  rows processed

收集测试表的统计信息

BEGIN
  dbms_stats.gather_table_stats(
                      ownname             => 'SCOTT',
                      tabname             => '统计信息测试表',
                      estimate_percent    => 100,
                      method_opt          => 'for all columns size auto',
                      no_invalidate       => FAlSE,
                      degree              => 1,
                      cascade             =>TRUE  
                       );
END;

再次查看统计信息

此时可以发现统计信息已更新，得到的执行计划信息也是准确的。并且因为返回结果集变大，走全表扫描性能更高，所以根据正确的统计信息，已经不再走owner的索引。

SQL> SELECT * FROM scott.统计信息测试表 WHERE owner = 'SCOTT';

已选择19575行。


执行计划
----------------------------------------------------------
Plan hash value: 1021776639

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 19575 |  1854K|   290   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| 统计信息| 19575 |  1854K|   290   (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SCOTT')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2323  consistent gets
          0  physical reads
          0  redo size
     918495  bytes sent via SQL*Net to client
      14867  bytes received via SQL*Net from client
       1306  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      19575  rows processed

上文描述了统计信息过期的问题情况复现，那么接下来和大家分享下如何查询统计信息是否过期。

查看表的统计信息是否过期。

当stale_stats字段的值是NO时，统计信息并未过期；当 stale_stats字段值是YES时，代表统计信息已经过期。

SQL> SELECT
  2  owner,
  3  table_name,
  4  object_type,
  5  stale_stats,
  6  last_analyzed
  7  FROM dba_tab_statistics
  8  WHERE owner = '&schema'
  9  AND table_name = '&表名' ;
输入 schema 的值:  SCOTT
原值    8: WHERE owner = '&schema'
新值    8: WHERE owner = 'SCOTT'
输入 表名 的值:  统计信息测试表
原值    9: AND table_name = '&表名'
新值    9: AND table_name = '统计信息测试表'

OWNER      TABLE_NAME       OBJECT_TYPE              STALE_STATS         LAST_ANALYZED
---------- --------------- ------------------       -------------        --------------
SCOTT      统计信息测试表     TABLE                        NO               09-3月 -24

查看统计信息过期原因

此查询SQL可以显示出统计信息时因为insert、update、delete等操作导致的过期。

SELECT 
table_owner,
table_name,
inserts,
updates,
deletes,
timestamp
FROM all_tab_modifications
WHERE table_owner = '&schema'
AND table_name = '&表名' ;

四、动态采样

在第三节的实验中，SQL语句只有一个谓词条件，这种情况是比较简单的；在统计信息未过期的情况下，Oracle的CBO优化器很容易就可以统计出ROWS。但是实际生产中，太多的SQL查询语句是不止一个谓词过滤条件的，这些谓词条件彼此相关；所以即使统计信息是准确的，Oracle的CBO优化器也未必能准确估算出正确的行数。这个时候可以进行动态采样来解决此问题。

下面还是一个实验，给朋友们复现一下多个谓词条件时，优化器是如何估算ROWS。

创建一张测试表

create table scott.扩展统计信息测试表 as select * from dba_objects

为新表收集统计信息

BEGIN
  dbms_stats.gather_table_stats(
                      ownname             => 'scott',
                      tabname             => '统计信息测试表',
                      estimate_percent    => 100,
                      method_opt          => 'for all columns size auto',
                      no_invalidate       => FAlSE,
                      degree              => 1,
                      cascade             =>TRUE  
                       );
END;

当有2个谓词条件时，查看该SQL执行计划

朋友们可以看到下面的结果， SELECT * FROM scott.扩展统计信息测试表 KZ WHERE KZ.OWNER = 'SYS' AND KZ.NAMESPACE =1 这个SQL语句其实返回了27863行数据，但优化器只估算出了157条。

SQL> SELECT *  FROM scott.扩展统计信息测试表 KZ WHERE KZ.OWNER = 'SYS' AND KZ.NAMESPACE =1;

已选择27863行。


执行计划
----------------------------------------------------------
Plan hash value: 3249381568

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   157 | 15229 |   291   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| 扩展统计信|   157 | 15229 |   291   (2)| 00:00:04 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("KZ"."OWNER"='SYS' AND "KZ"."NAMESPACE"=1)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2869  consistent gets
          0  physical reads
          0  redo size
    1385729  bytes sent via SQL*Net to client
      20950  bytes received via SQL*Net from client
       1859  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      27863  rows processed

SQL> set autot off
SQL> SELECT count(*) FROM scott.扩展统计信息测试表 KZ WHERE KZ.OWNER = 'SYS' AND KZ.NAMESPACE =1 ;

  COUNT(*)
----------
     27863

那么这157是怎么估算出的呢？

Oracle的CBO优化器并不知道谓词条件中的两个谓词字段之间的关系，那么此时优化器的算法就变成了这样：

$totalnums\tfrac{1}{ab}$

totalnums是表的总行数，a、b是谓词字段的基数。如果有更多个谓词条件，那么就是多个谓词条件的基数相乘。

朋友们可以看下面的列统计信息，测试表的总行数是72335，谓词条件owner列的基数是23，谓词条件NAMESPACE的基数是20。根据上面的公式，得出的结果就是上面执行计划中的157。优化器已经估算错了。但这也不能怪人家优化器，人家也不知道这俩谓词条件啥关系。

SQL> SELECT
  2  tcs.column_name,
  3  dt.num_rows,
  4  tcs.num_distinct 基数,
  5  round(tcs.num_distinct / dt.num_rows *100,2)  选择性,
  6  tcs.num_nulls,
  7  tcs.num_buckets,
  8  tcs.histogram
  9  FROM dba_tab_col_statistics tcs,dba_tables dt
 10  WHERE tcs.owner = dt.owner
 11  AND tcs.table_name = dt.table_name
 12  AND tcs.owner = 'SCOTT'
 13  AND tcs.table_name = '扩展统计信息测试表' ;

COLUMN_NAME                                                    NUM_ROWS       基数     选择性  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------------------------------------ ---------- ---------- ---------- ---------- ----------- ------------------------------
OWNER                                                             72335         23        .03          0           1 NONE
OBJECT_NAME                                                       72335      44078      60.94          0           1 NONE
SUBOBJECT_NAME                                                    72335        194        .27      71822           1 NONE
OBJECT_ID                                                         72335      72335        100          0           1 NONE
DATA_OBJECT_ID                                                    72335       7626      10.54      64669           1 NONE
OBJECT_TYPE                                                       72335         43        .06          0           1 NONE
CREATED                                                           72335       1398       1.93          0           1 NONE
LAST_DDL_TIME                                                     72335       1451       2.01          0           1 NONE
TIMESTAMP                                                         72335       1518        2.1          0           1 NONE
STATUS                                                            72335          1          0          0           1 NONE
TEMPORARY                                                         72335          2          0          0           1 NONE
GENERATED                                                         72335          2          0          0           1 NONE
SECONDARY                                                         72335          2          0          0           1 NONE
NAMESPACE                                                         72335         20        .03          0           1 NONE
EDITION_NAME                                                      72335          0          0      72335           0 NONE

已选择15行。

SQL> SELECT ROUND(1/23/20*72335) FROM DUAL;

ROUND(1/23/20*72335)
--------------------
                 157

开启动态采样

当开启动态采样后，此时朋友们可以发现，优化器估算出了26704行数据，这样就比较精确了。当然因为这张测试表我并没有给它加索引，所以在执行计划上就没有什么变化，但朋友们记住CBO估算出的行数会影响执行计划，这个很重要，后面这个SQL优化专栏的其他文章我也会再提。

SQL> ALTER SESSION SET optimizer_dynamic_sampling=4;

会话已更改。

SQL> set autot trace
SQL> SELECT *  FROM scott.扩展统计信息测试表 KZ WHERE KZ.OWNER = 'SYS' AND KZ.NAMESPACE =1;

已选择27863行。


执行计划
----------------------------------------------------------
Plan hash value: 3249381568

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 26704 |  2529K|   291   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| 扩展统计信| 26704 |  2529K|   291   (2)| 00:00:04 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("KZ"."OWNER"='SYS' AND "KZ"."NAMESPACE"=1)

Note
-----
   - dynamic sampling used for this statement (level=4)


统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       2901  consistent gets
          0  physical reads
          0  redo size
    1385729  bytes sent via SQL*Net to client
      20950  bytes received via SQL*Net from client
       1859  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      27863  rows processed

SQL>

上面的实验做完了，那么下面就和朋友们一起来聊下动态采样这个事儿。

动态采样的目的是为了让优化器评估出较为准确的ROWS。一个表如果从来没有收集过统计信息，默认情况下Oracle会对表进行2级动态采样（level=2）。

动态采样的级别有11级

level 0：不启用动态采样
level 1：当表没有收集过统计信息，且这个表要和另外的表关联，且这个表没有索引，且表的block数大于32个。当满足这些条件时，Oracle会随机扫描表中的32个block。
level 2：对没有收集过统计信息的表启用动态采样，采样的block是64个。如果表的block数量小于64个，那就全部采样。
level 3：对没有收集过统计信息的表启用动态采样，采样的block是64个。如果已经收集过统计信息，但是优化器不能准确的估算出返回的rows，这是会随机扫描64个data block进行采样。
level 4：对没有收集过统计信息的表启用动态采样，采样的block是64个。如果已经收集过统计信息，但是表有2个或者2个以上的谓词过滤条件，这是会随机扫描64个data block进行采样。对于谓词较多的情况，level 4也是在遇到多个谓词条件时，至少要开启的动态采样级别。level 4 包含了 level 3的采样数据。
level 5：收集满足level 4 采样条件的数据，采集的block数量是128个
level 6：收集满足level 5 采样条件的数据，采集的block数量是256个
level 7：收集满足level 4 采样条件的数据，采集的block数量是512个
level 8：收集满足level 4 采样条件的数据，采集的block数量是1024个
level 9：收集满足level 4 采样条件的数据，采集的block数量是4086个
level 10：收集满足level 4 采样条件的数据，采集表中所有的block
level 11：Oracle自动判断如何采样，采样的block又Oracle自动决定。

启用动态采样方法

（1）修改optimizer_dynamic_sampling参数

ALTER SESSION SET optimizer_dynamic_sampling= [0,1,2,3,4....10,11];

（2）添加HINT

SELECT /*+ dynamic_sampling(3) */ FROM scott.扩展统计信息测试表 WHERE SUBSTR(OWNER,4,3) = 'LIC';

动态采样注意事项

启用动态采样不好使的原因

如果表已经收集过统计信息，且优化器能准确的算出返回的ROWS，那么此时即使修改参数或者添加HINT都不好使了，因为没有必要进行动态采样了。

什么时候启用动态采样呢？

除了对于新表会默认动态采样这个情况不考虑外。在执行计划中表的的rows估算有严重偏差时，例如相关列问题、两表关联的连接列太多、where谓词条件中使用了substr\instr\like、非等值过滤时可以考虑使用动态采样。建议动态采样的 level至少设置为 4。

不要在系统层面更改采样级别参数

修改会话级参数或者之间在SQL中添加HINT即可

五、定制收集策略

Oracle数据库自带有JOB每天会收集数据库中所有表的统计信息，但是当数据库特别大的时候，自带的JOB是没有办法完成全库的统计信息收集任务的。这种情况呢，我们可以自己根据实际情况定值一种策略，使其定期收集统计信息策略。

下面的PLSQL代码块用于收集SCOTT用户下，统计信息过期、未收集过统计信息的表。各位朋友可以进行参考。但不要直接拿来用，里面除了收集条件和采样率通用以外，其余的参数还是要根据实际情况修改。

declare
cursor sta_collect is 
select 
owner,
segment_name,
case when segment_size<1 then 100
       when segment_size>=1 and segment_size <=5 then 50
       when segment_size >5 then 30 
         end as percent
from(
select 
owner,segment_name,sum(bytes/1024/1024/1024) segment_size 
from DBA_SEGMENTS
where owner ='SCOTT' and  segment_name in(
                                    select table_name 
                                           from DBA_TAB_STATISTICS 
                                                where(last_analyzed is null or stale_stats ='YES') and owner ='SCOTT')
                                                                    group by owner,segment_name);
begin
         dbms_stats.flush_database_monitoring_info;

for sta in sta_collect loop
      dbms_stats.gather_table_stats(
                          ownname             => sta.owner,
                          tabname               => sta.segment_name,
                          estimate_percent   => sta.percent,
                          method_opt          => 'for all columns size repeat',
                          no_invalidate         => FAlSE,
                          degree                   => 1,
                          cascade                  =>TRUE  
                           );
end loop;
end;

❀❀❀❀❀END，拜拜，周末愉快！❀❀❀❀❀

姜豆豆耶

关注

33
点赞
踩
23

收藏

觉得还不错? 一键收藏
打赏
0
评论
（Oracle）SQL优化基础（二）：统计信息

Oracle在选择最优执行计划时，是其CBO优化器依据统计信息进行判断的。所以统计信息这块内容就超级重要了，这有点像情报部门，情报工作搞得好，干仗才利索；情报部门出问题了，那就只有挨揍的份了。强如李云龙，当年也派了侦察兵去楚云飞的营地蹲点，随后就帮楚云飞干掉了钱伯钧。
复制链接

扫一扫