Oracle-SQL性能优化（2）-收集统计信息

原创大柏树 2022-10-26

748

优化器执行步骤中最为关键的就是估算（Estimator），评估的主要内容主要包括基数、选择性和成本。成本还与数据访问方式、表连接方式等有关。

1.基数

基数可以理解为某一列上不同值的个数（distinct column）。主键或唯一值列的基数等于总行数。

--创建测试表
SQL> create table  scott.jason as select * from dba_objects where 1=0;
Table created.
SQL> alter table scott.jason add constraint pk_object_id primary key (OBJECT_ID);
Table altered.
SQL> insert into scott.jason select * from dba_objects;
86981 rows created.
SQL> commit;
Commit complete.
--查看基数
SQL> select count(*) CNT,
  2         count(distinct owner) owner,
  3         count(distinct object_name) obj_name,
  4         count(distinct object_id) obj_id,
  5         count(distinct object_type) obj_type
  6  from scott.jason;
       CNT	OWNER	OBJ_NAME     OBJ_ID   OBJ_TYPE
---------- ---------- ---------- ---------- ----------
     86981	   29	   52423      86981	    45
--owner因为同一用户下有大量对象比如sys用户，所以基数很低。
--object_id是主键，所以基数等于总行数。
SQL> 
复制

从基数我们可以得到以下“相对”的结论：

基数小：数据重复值高、数据分布不均匀，适合采用全表扫描的方式。
基数大：数据重复值低、数据分布均匀，适合采用索引扫描的方式。

2.选择性

选择性可以理解为满足条件的行数与数据集的比值。
selectivity=numbers of rows satisfying a predicate/total number of rows

通过选择性判断是否适合使用索引，我们同样得到以下“相对”的结论：(适合使用索引)

小表（1000行以内）:selectivity 为 1%
大表（超过1000行）：selectivity 为4% - 5%

因为选择性会涉及很多相关联的表，很难做出判断，所以我们一般可以用列选择性来判断。
以jason表为例，计算OWNER,OBJECT_NAME,OBJECT_ID,OBJECTTYPE的选择性：

SQL> select round(owner/cnt * 100,2) owner_pec,
  2         round(obj_name/cnt * 100,2) objname_pec,
  3         round(obj_id/cnt * 100,2) objid_pec,
  4         round(obj_type/cnt * 100,2) objtype_pec
  5  from        
  6         (select count(*) cnt,
  7                 count(distinct owner) owner,
  8                 count(distinct object_name) obj_name,
  9                 count(distinct object_id) obj_id,
 10                 count(distinct object_type) obj_type
 11          from scott.jason);

 OWNER_PEC OBJNAME_PEC	OBJID_PEC OBJTYPE_PEC
---------- ----------- ---------- -----------
       .03	 60.27	      100	  .05

SQL> 
复制

一般我们认为列选择性大于15%的时候说明数据分布较为均匀。数据分布均匀我们可以这样理解：以单表查询为例，当where条件中谓词固定，但是不同的值返回的行数不同，返回结果相差在百倍以内。如果大于百倍，我们可以说该列数据分布不均匀。

3.收集数据库统计信息

我们上面看了基数和选择性，优化器肯定不是按照上面的方法来进行统计的。数据库内部的定时任务会进行统计信息收集，我们也可以手动收集统计信息。
统计信息收集主要包括以下内容：

表/列的统计信息
索引的统计信息
用户的统计信息
系统的统计信息
数据字典的统计信息
动态性能视图基表的统计信息

--数据库定时任务是通过DBMS_STAT包进行收集
--使用DBMS_STAT包对表进行统计信息收集：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'JASON',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                no_invalidate    => FALSE,
                                cascade          => TRUE);
END;
/

--estimate_percent 采样百分比：推荐5g以内的表，设置100%。大于5g的表，设置20%。
--method_opt 表示是否收集列的直方图信息。
--no_invalidate 表示收集完统计信息相关游标是否失效，建议设置为false 立即生效。
--cascade 表示与表相关联的对象，例如索引等。
复制

我们收集统计信息之后，这些信息存放在什么位置呢 ? 我们可以通过以下视图查看。

SQL> set line 200
set pages 9999
select a.column_name,
	   b.num_rows,
	   a.num_distinct Cardinality,
	   round(a.num_distinct / b.num_rows * 100, 2) selectivity,
	   a.histogram,
	   a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner = b.oSQL> SQL>   2    3    4    5    6    7    8  wner
and a.table_name = b.table_name
and a.owner='SCOTT'
and a.table_name = 'JASON';  9   10   11  

COLUMN_NAME			 NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM	  NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER				    86981	   29	      .03 NONE			    1
OBJECT_NAME			    86981	52423	    60.27 NONE			    1
SUBOBJECT_NAME			    86981	  151	      .17 NONE			    1
OBJECT_ID			    86981	86981	      100 NONE			    1
DATA_OBJECT_ID			    86981	 9094	    10.46 NONE			    1
OBJECT_TYPE			    86981	   45	      .05 NONE			    1
CREATED 			    86981	  920	     1.06 NONE			    1
LAST_DDL_TIME			    86981	 1020	     1.17 NONE			    1
TIMESTAMP			    86981	 1059	     1.22 NONE			    1
STATUS				    86981	    1		0 NONE			    1
TEMPORARY			    86981	    2		0 NONE			    1
GENERATED			    86981	    2		0 NONE			    1
SECONDARY			    86981	    2		0 NONE			    1
NAMESPACE			    86981	   21	      .02 NONE			    1
EDITION_NAME			    86981	    0		0 NONE			    0

15 rows selected.
--histogram：NONE表示没有收集直方图。直方图就是反应数据分布的值，能够帮助优化器选择更合理的执行计划。
SQL>
复制

4.实用脚本

我们通过了解基数和选择性得到一些“相对的”结论，那如果现在有一个单表，走的是全表扫描，我们希望创建索引能够加快查询速度，怎么用之前的结论判断怎么创建索引呢？

4.1.判断是否需要加索引的脚本

--模拟查询object_name为筛选列
SQL> select object_id,owner,object_type 
  2  from scott.jason
  3  where object_name='C_COBJ#';

 OBJECT_ID OWNER			  OBJECT_TYPE
---------- ------------------------------ -------------------
	29 SYS				  CLUSTER
SQL>   
--手动刷新系统监控
SQL> begin
  2  dbms_stats.flush_database_monitoring_info;
  3  end;
  4  /

PL/SQL procedure successfully completed.
--查询需要创建索引的列：基数较大、选择性大于15%，且谓词条件中包含了该列
SQL> select owner,
	   column_name,
	   num_rows,
	   Cardinality,
	   selectivity,
	   'Need index' as notice
from (select b.owner,
          a.column_name,
	  b.num_rows,
	  a.num_distinct Cardinality,
	  round(a.num_distinct / b.num_rows * 100,2) selectivity
	  from dba_tab_col_statistics a,dba_tables b
	  where a.owner=b.owner
	  and a.table_name=b.table_name
	  and a.owner='SCOTT'
	  and a.table_name='JASON')
where selectivity >= 20
and column_name not in (select column_name
						from dba_ind_columns      
						where table_owner = 'SCOTT'
						and table_name = 'JASON')
and column_name in (select c.name
					from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
					where o.obj# = u.obj#
					and c.obj# = u.obj#
					and c.col# = u.intcol#
					and r.name = 'SCOTT'
					and o.name = 'JASON');   

OWNER			       COLUMN_NAME			NUM_ROWS CARDINALITY SELECTIVITY NOTICE
------------------------------ ------------------------------ ---------- ----------- ----------- ----------
SCOTT			       OBJECT_NAME			   86981       52423	   60.27 Need index

SQL> 
复制

4.2.怎么判断统计信息是否过期？

我们在进行sql优化的时候，第一件事是看统计信息，因为如果统计信息过期，那生成的执行计划是不准确的。
如果表中有大量数据发生了变化，而统计信息未能及时收集，那么就会引起统计信息过期的问题，可能会走不合理的执行计划。
DBA_TAB_STATISTICS视图中STALE_STATS 列代表了统计信息的状态，如果为YES表示需要收集统计信息。为NO表示不需要重新收集。
一般认为当DML对表的操作达到10%以上的时候统计信息是过期陈旧的，这个变化记录在DBA_TAB_MODIFICATIONS中。

--SQL语句查看该语句所涉及表格统计信息是否准确，可以通过如下语句直接获取表的统计信息： 
var sql_id varchar2(40);
exec :sql_id :='dhtz3uadcwdag';
SELECT /*+ NO_MERGE LEADING(pt s t m) */
 s.owner,
 s.table_name,
 t.partitioned,
 S.partition_name,
 t.degree,
 t.temporary,
 s.num_rows,
 TO_CHAR(s.last_analyzed, 'YYYY-MM-DD/HH24:MI:SS') last_analyzed,
 s.stattype_locked,
 s.stale_stats,
 CASE
   WHEN s.num_rows > 0 THEN
    TO_CHAR(ROUND((m.inserts + m.updates + m.deletes) * 100 / s.num_rows, 1),
            '99999990D0')
 END stale_stats_perc
  FROM gv$sql_plan               pt,
       dba_tab_statistics        s,
       dba_tables                t,
       sys.dba_tab_modifications m
 WHERE pt.object_type = 'TABLE'
   AND pt.object_owner = s.owner
   AND pt.object_name = s.table_name
   AND pt.object_type = s.object_type
   AND pt.sql_id = :sql_id
   AND s.table_name not like 'BIN$%'
   AND s.stale_stats = 'YES'
   AND s.stattype_locked is null 
   AND s.owner = t.owner
   AND s.table_name = t.table_name
   AND t.owner = m.table_owner(+)
   AND t.table_name = m.table_name(+)
   AND m.partition_name IS NULL
 ORDER BY s.table_name, s.owner;
复制

4.3.直方图

SQL> select object_type,count(*) from scott.jason group by object_type order by 2;

OBJECT_TYPE	      COUNT(*)
------------------- ----------
EDITION 		     1
......
SYNONYM 		 34104
45 rows selected.
复制

例如上面的查询，如果不收集直方图，那么优化器会认为数据分布均匀，会走同一个执行计划。
示例如下

SQL> select count(*) from scott.jason where object_type='EDITION';
Execution Plan
----------------------------------------------------------
Plan hash value: 1343065172
---------------------------------------------------------------------------
| Id  | Operation	  | Name	| Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  ||1 |	  9 | 6  (0)| 00:00:01 |
|   1 | SORT AGGREGATE   ||	  1 |	  9 |||
|*  2 | INDEX RANGE SCAN| IDX_OBJECT_TYPE |  1933 | 17397 |6 (0)| 00:00:01 
---------------------------------------------------------------------------
SQL> select count(*) from scott.jason where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 1343065172
---------------------------------------------------------------------------

| Id  | Operation| Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 | 1 |	 9 |	6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	 |1 |	  9 |		 ||
|*  2 |   INDEX RANGE SCAN| IDX_OBJECT_TYPE |  1933 | 17397|6 (0)|00:00:01|
---------------------------------------------------------------------------
复制

收集直方图

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'JASON',
                                estimate_percent => 100,
                                method_opt       => 'for columns object_type size skewonly',
                                no_invalidate    => FALSE,
                                cascade          => TRUE);
END;
/

--method包含的参数：
--for all columns:统计所有列的直方图
--for all indexed columns: 统计所有索引列的直方图
--for columns column [size_clase]
--size_clase的定义值为{integer|REPEAT|AUTO|SKEWONLY}
--integer指定直方图的桶数，范围为1-254，设置为1表示不收集直方图（通常用于删除直方图）
--REPEAT：仅在已经有的直方图的列上收集
--AUTO：oracle自行判断哪些列需要
--SKEWONLY：指定列收集直方图

SQL> set line 200
set pages 9999
select a.column_name,
	   b.num_rows,
	   a.num_distinct Cardinality,
	   round(a.num_distinct / b.num_rows * 100, 2) selectivity,
	   a.histogram,
	   a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner = b.oSQL> SQL>   2    3    4    5    6    7    8  wner
and a.table_name = b.table_name
and a.owner='SCOTT'
and a.table_name = 'JASON';  9   10   11  

COLUMN_NAME			 NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM	  NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER				    86981	   29	      .03 NONE			    1
OBJECT_NAME			    86981	52423	    60.27 NONE			    1
SUBOBJECT_NAME			    86981	  151	      .17 NONE			    1
OBJECT_ID			    86981	86981	      100 NONE			    1
DATA_OBJECT_ID			    86981	 9094	    10.46 NONE			    1
OBJECT_TYPE			    86981	   45	      .05 FREQUENCY		   45
CREATED 			    86981	  920	     1.06 NONE			    1
LAST_DDL_TIME			    86981	 1020	     1.17 NONE			    1
TIMESTAMP			    86981	 1059	     1.22 NONE			    1
STATUS				    86981	    1		0 NONE			    1
TEMPORARY			    86981	    2		0 NONE			    1
GENERATED			    86981	    2		0 NONE			    1
SECONDARY			    86981	    2		0 NONE			    1
NAMESPACE			    86981	   21	      .02 NONE			    1
EDITION_NAME			    86981	    0		0 NONE			    0

15 rows selected.
复制

然后我们再次执行上述查询：

SQL> select count(*) from scott.jason where object_type='EDITION';
Execution Plan
----------------------------------------------------------
Plan hash value: 1343065172
---------------------------------------------------------------------------
| Id  | Operation| Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	  1 |	  9 |	  1   (0)| 00:00:01|
|   1 |  SORT AGGREGATE   |	  |	  1 |	  9 |		 |	  
|*  2 |   INDEX RANGE SCAN| IDX_OBJECT_TYPE |	  1 |	  9 |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>  select count(*) from scott.jason where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 3630984780
---------------------------------------------------------------------------
| Id  | Operation	      | Name		| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 		|     1 |     9 |    66   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 		|     1 |     9 |	     |		|
|*  2 |   INDEX FAST FULL SCAN| IDX_OBJECT_TYPE | 34104 |   299K|    66   (0)| 00:00:01 |
---------------------------------------------------------------------------
复制

得到需要收集直方图的列的sql

select a.owner,
	   a.table_name,
	   a.column_name,
	   b.num_rows,
	   a.num_distinct,
	   trunc(num_distinct / num_rows * 100,2) selectivity,
	   'Need Gather Histogram' notice
from dba_tab_col_statistics a,dba_tables b
where a.owner = 'SCOTT'
and a.table_name = 'JASON'
and a.owner=b.owner
and a.table_name=b.table_name
and num_distinct / num_rows < 0.01
and (a.owner,a.table_name,a.column_name) in 
	(select r.name owner,o.name table_name,c.name column_name
     from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
     where o.obj# = u.obj#
     and c.obj# = u.obj#
     and c.col# = u.intcol#
     and r.name = 'SCOTT'
     and o.name = 'JASON')
and a.histogram = 'NONE';
复制

在SQL中使用绑定变量，可以减少硬解析，但问题是，如果该列数据倾斜严重，则可能会生成低效的执行计划。
oracle10g的时候引入了隐含参数_optim_peek_user_binds用于控制硬解析。但这个参数有个重大BUG，执行计划是根据硬解析窥视的绑定变量生成的，接下来会一直使用该执行计划，直到下次硬解析才改动，这会造成严重的性能问题，所以对于大部分Oracle 10g 环境，建议关闭绑定变量窥视的特性。

```sql
SQL> alter system set "_optim_peek_user_binds"=false;
复制

类似的性能问题跟参数有关的还有基数反馈
注：本文参考于：《DBA攻坚指南》

墨力计划 oracle

最后修改时间：2022-11-04 09:11:45

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle 性能优化（持续更新中）（共15篇）

性能优化

Oracle-SQL性能优化（2）-收集统计信息

1.基数

2.选择性

3.收集数据库统计信息

4.实用脚本

4.1.判断是否需要加索引的脚本

4.2.怎么判断统计信息是否过期？

4.3.直方图

文章被以下合辑收录

评论

相关阅读

Oracle-SQL性能优化（2）-收集统计信息

1.基数

2.选择性

3.收集数据库统计信息

4.实用脚本

4.1.判断是否需要加索引的脚本

4.2.怎么判断统计信息是否过期 ？

4.3.直方图

文章被以下合辑收录

评论

相关阅读

4.2.怎么判断统计信息是否过期？