Oracle扩展的统计信息

最新推荐文章于 2023-10-18 16:09:18 发布

cuiwangxie1183

最新推荐文章于 2023-10-18 16:09:18 发布

阅读量269

点赞数

文章标签：数据库

Oracle扩展的统计信息

--性能优化和故障处理小组

我们在收集列的统计信息与直方图时，往往都是对某一列的收集。当谓词使用多个相关列时，会导致约束条件的冗余。这几个相关的列也被称作关联列。出现这种情况时，查询优化器也会做出不准确的判断。所以我们必须对这些相关列收集统计信息或直方图来描述这种依赖关系。

从Oracle11g开始，数据库可以收集基于表达式或者一组列上的对象统计信息和直方图，从而解决这种问题。这种新的统计叫做扩展的统计信息（extension statistics）

这种技术实际上是基于表达式或一组列创建一个隐藏列，叫做扩展（extension），再在扩展列上收集统计信息与直方图。

（一） 多列统计信息：

我们看下面的例子：

create table t2 (c1 varchar2(22),c2 varchar2(22),n1 number,n2 number);

insert into t2 select 'a','a',trunc(dbms_random.value(0,20)),trunc(dbms_random.value(0,25))

from dba_objects where rownum<10001;

commit;

select * from t2;

begin

dbms_stats.gather_table_stats('DSG','T2',cascade=>true,estimate_percent=>100 );

end;

Table Number Empty Average Chain Average Global User Sample Date

Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY

--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------

T2 10,000 28 0 0 0 10 YES NO 10,000 01-13-2016

Column Column Distinct Number Number Global User Sample Date

Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY

------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------

C1 VARCHAR2(22) 1 1 1 0 YES NO 10,000 01-13-2016

C2 VARCHAR2(22) 1 1 1 0 YES NO 10,000 01-13-2016

N1 NUMBER(22) 20 0 1 0 YES NO 10,000 01-13-2016

N2 NUMBER(22) 25 0 1 0 YES NO 10,000 01-13-2016

从上述统计信息我们可以发现，表T2的数量为1w，数据块的数量为28，列N1的distinct值为20，n2的distinct 值为25,C1 C2的distinct均为1。

执行sql：select* from t2 where n1=1 and n2=1 and c1='a';

执行计划如下：

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20 | 200 | 9 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T2 | 20 | 200 | 9 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("N2"=3 AND "N1"=1 AND "C1"='a')

当目标sql语句where条件中出现的各个列之间是相互独立，没有关系时，如果各列之间是以and组合的话，那么整个sql的where条件可选择率就等于各个条件可选择率的乘积。

根据统计信息我们知道上述sql结果集的cardinality=T2的记录数*组合选择率=10000*（1/20）*(1/25)*1=20

这个结果和执行计划中的基数20是一致的。因此我们认为这个执行计划是没有问题的。

上面的例子是符合大众情况的，那么下面这个特例似乎就不行了：

现在我们强制让N1和N2列产生关联关系：

update t2 set n2=n1;

commit;

begin

dbms_stats.gather_table_stats('DSG','T2',cascade=>true,estimate_percent=>100 );

end;

现在的统计信息如下：

Table Number Empty Average Chain Average Global User Sample Date

Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY

--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------

T2 10,000 28 0 0 0 10 YES NO 10,000 01-13-2016

Elapsed: 00:00:00.04

Column Column Distinct Number Number Global User Sample Date

Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY

------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------

C1 VARCHAR2(22) 1 0 1 0 YES NO 10,000 01-13-2016

C2 VARCHAR2(22) 1 1 1 0 YES NO 10,000 01-13-2016

N1 NUMBER(22) 20 0 20 0 YES NO 10,000 01-13-2016

N2 NUMBER(22) 20 0 20 0 YES NO 10,000 01-13-2016

从结果我们可以看出N2的distinct值由之前的25变为现在的20

继续执行sql：select* from t2 where n1=1 and n2=1 and c1='a';

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 24 | 240 | 9 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T2 | 24 | 240 | 9 (0)| 00:00:01 |

--------------------------------------------------------------------------

cardinality=T2的记录数*组合选择率=10000*（1/20）*(1/20)*1=25

在N1和N2列有关联关系的情况下还用默认的方式去计算，显然不合适，优化器估算结果集位24，而实际返回将近500，结果有数量级的偏差，估算结果已经严重不准。

可能有人会想，估算的结果集不准就不准了呗，反正都是全表扫描，执行的路径，逻辑读，物理读都一样，效率也都一样，这样说似乎也不是没有道理，但是不要忘了，如果这个情况发生在多表关联之间，情况恐怕就很糟糕了，每一步骤的结果集，将严重影响多表之间表的关联方式，比如：驱动结果集比较少的话，oracle可能会优先考虑循环嵌套。

在这种情况下可有2中方式解决：

（A）强制使用动态采样

select/*+ dynamic_sampling(t2 2) */* from t2 where n1=1 and n2=1 and c1='a';

488 rows selected.

Elapsed: 00:00:00.04

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 488 | 4880 | 9 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T2 | 488 | 4880 | 9 (0)| 00:00:01 |

--------------------------------------------------------------------------

从执行计划的反馈基数488 与实际488的结果还是非常接近的。

这是什么原因呢？这个原因不在我们本次讨论的重点范围之内，感兴趣的可以自己查阅资料。

（B）多列统计信息

多列统计信息的诞生就是为解决这个问题的。

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID SUBOBJECT_ OBJECT_TYP CREATED LAST_DDL_TIME STATUS

---------- -------------------- --------- -------------- ---------- ---------- --------------- --------------- --------

DSG T2 90587 90587 TABLE 160113 08:57:42 160113 22:18:53 VALID

没有创建前T2有4个列：

5:DSG@yzf> select t.col#,t.name from sys.col$ t where obj#=90587;

COL# NAME

---------- ------------------------------

1 C1

2 C2

3 N1

4 N2

创建多列统计信息：

SET SERVEROUTPUT ON

DECLARE

C_NAME VARCHAR2(2000);

BEGIN

C_NAME:= sys.DBMS_STATS.CREATE_EXTENDED_STATS('DSG','T2','(N1,N2)');

DBMS_OUTPUT.PUT_LINE(C_NAME);

END;

SYS_STUBZH0IHA7K$KEBJVXO5LOHAS

SYS@yzf>select * from dba_stat_extensions where table_name='T2';

OWNER TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO

---------- ------------------------------ ------------------------------ --------------- ------ ---

DSG T2 SYS_STUBZH0IHA7K$KEBJVXO5LOHAS ("N1","N2") USER YES

创建后T2有5个列：

5:DSG@yzf> select t.col#,t.name from sys.col$ t where obj#=90587;

COL# NAME

---------- ------------------------------

0 SYS_STUBZH0IHA7K$KEBJVXO5LOHAS

1 C1

2 C2

3 N1

4 N2

从上面的查询可以看出：所谓的组合列其实就是在表T2中增加了一列，类似有oracle里面的函数索引。

收集统计信息：

begin

dbms_stats.gather_table_stats('DSG','T2',cascade=>true,estimate_percent=>100 );

end;

从列统计信息的数据字典基表hist_head$中可以看出oracle对上述组合列收集的多列统计

信息的详情：

5:DSG@yzf> select t.col#,t.distcnt,t.minimum,t.maximum,t.density from sys.hist_head$ t where obj#=90587;

COL# DISTCNT MINIMUM MAXIMUM Density

---------- ---------- ---------- ---------- -------

0 20 1.2949E+17 1.6885E+19 0

1 1 5.0365E+35 5.0365E+35 0

2 1 5.0365E+35 5.0365E+35 1

3 20 0 19 0

4 20 0 19 0

执行sql：select* from t2 where n1=1 and n2=1 and c1='a';

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 488 | 4880 | 9 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T2 | 488 | 4880 | 9 (0)| 00:00:01 |

--------------------------------------------------------------------------

实际结果也为488，和oracle执行计划估算的一致。

因此可以使用多列统计信息，来解决where条件后有关联关系的列。

（二）函数列统计信息：

如果where条件类似于function_name(table_name.column_name)=’XXX’时，则优化器在估计这样的where条件的selectivity时，总是会假设其selectivity为1％，也就是该where条件将返回table_name里总记录行数的1％的记录行数。很明显的，这种假设肯定是错误的，从而可能导致优化器产生了不够优化的执行计划。

create table t1 as select * from dba_objects;

begin

dbms_stats.gather_table_stats('DSG','T1',cascade=>true,estimate_percent=>100 );

end;

统计信息：

Table Number Empty Average Chain Average Global User Sample Date

Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY

--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------

T1 NO NO

Elapsed: 00:00:00.02

Column Column Distinct Number Number Global User Sample Date

Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY

------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------

OWNER VARCHAR2(30) NO NO

OBJECT_NAME VARCHAR2(128) NO NO

SUBOBJECT_NAME VARCHAR2(30) NO NO

OBJECT_ID NUMBER(22) NO NO

DATA_OBJECT_ID NUMBER(22) NO NO

OBJECT_TYPE VARCHAR2(19) NO NO

CREATED DATE NO NO

LAST_DDL_TIME DATE NO NO

TIMESTAMP VARCHAR2(19) NO NO

STATUS VARCHAR2(7) NO NO

TEMPORARY VARCHAR2(1) NO NO

GENERATED VARCHAR2(1) NO NO

SECONDARY VARCHAR2(1) NO NO

NAMESPACE NUMBER(22) NO NO

EDITION_NAME VARCHAR2(30) NO NO

统计信息内容不解释，直接看下面的sql：

22:47:12 5:DSG@yzf>select * from t1 where object_name='gaotuan';

Elapsed: 00:00:00.02

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 196 | 306 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T1 | 2 | 196 | 306 (1)| 00:00:04 |

--------------------------------------------------------------------------

实际返回0， oracle执行计划估算结果集2，和实际结果比较接近。

22:47:40 5:DSG@yzf>select * from t1 where upper(object_name)='gaotuan';

Elapsed: 00:00:00.03

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 767 | 75166 | 307 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T1 | 767 | 75166 | 307 (1)| 00:00:04 |

--------------------------------------------------------------------------

实际返回0， oracle执行计划估算结果集767(总是会假设其selectivity为1％，也就是该where条件将返回table_name里总记录行数的1％的记录行数)，和实际结果比较相差很远。

有时候这样类似的函数我们又不得不使用，oracle的拓展的统计信息，在这个情况下就用上了，（当然我们一般不建议在列上使用函数）

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID SUBOBJECT_ OBJECT_TYP CREATED LAST_DDL_TIME STATUS

---------- -------------------- --------- -------------- ---------- ---------- --------------- --------------- --------

DSG T1 90618 90618 TABLE 160113 22:45:45 160113 22:45:45 VALID

还是类似实验一的操作：

创建函数列统计信息：

SET SERVEROUTPUT ON

DECLARE

C_NAME VARCHAR2(2000);

BEGIN

C_NAME:= sys.DBMS_STATS.CREATE_EXTENDED_STATS('DSG','T1','(upper(object_name))');

DBMS_OUTPUT.PUT_LINE(C_NAME);

END;

SYS_STUBZH0IHA7K$KEBJVXO5LOHAS

SYS@yzf>select * from dba_stat_extensions where table_name='T1';

OWNER TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO

---------- ---------- ------------------------------ ------------------------------ ------ ---

DSG T1 SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y (UPPER("OBJECT_NAME")) USER YES

创建后T1多了一个列：

5:DSG@yzf> select t.col#,t.name from sys.col$ t where obj#=90618;

COL# NAME

---------- ------------------------------

0 SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y

收集统计信息：

begin

dbms_stats.gather_table_stats('DSG','T1',cascade=>true,estimate_percent=>100 );

end;

从列统计信息的数据字典基表hist_head$中可以看出oracle对上述函数列收集的统计信息的详情中多了如下信息：

5:DSG@yzf> select t.col#,t.distcnt,t.minimum,t.maximum,t.density from sys.hist_head$ t where obj#=90618;

COL# DISTCNT MINIMUM MAXIMUM DENSITY

---------- ---------- ---------- ---------- ----------

0 46985 2.4504E+35 4.9500E+35 .000021283

再次执行sql：

23:03:20 5:DSG@yzf>select * from t1 where upper(object_name)='gaotuan';

Elapsed: 00:00:00.04

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 122 | 307 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 122 | 307 (1)| 00:00:04 |

--------------------------------------------------------------------------

实际返回0， oracle执行计划估算结果集1，和实际结果比较接近。

通过上述两个真实的案例，想必大家对Oracle扩展的统计信息，使用场景，使用方法都了然于胸。

最后我们介绍下Oracle扩展的统计信息如何去维护:

创建：

SET SERVEROUTPUT ON

DECLARE

C_NAME VARCHAR2(2000);

BEGIN

C_NAME:= DBMS_STATS.CREATE_EXTENDED_STATS('DSG','T1','(upper(object_name))');

DBMS_OUTPUT.PUT_LINE(C_NAME);

END;

查询：

select * from dba_stat_extensions where table_name='T1';

删除：

BEGIN

DBMS_STATS.DROP_EXTENDED_STATS('DSG','T2','(N1,N2)');

END;

----gt

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

转载于:http://blog.itpub.net/30109892/viewspace-1976697/

cuiwangxie1183

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle扩展的统计信息

Oracle扩展的统计信息 --性能优化和故障处理小组我们在收集列的统计信息与直方图时，往往都是对某一列的收集。当谓词使用多个相关列时，...
复制链接

扫一扫

Oracle扩展的统计信息

Oracle扩展的统计信息

“相关推荐”对你有帮助么？