合 Oracle多列统计信息
简介
Oracle优化器对于基数值的估算是否准确关系到能否生成最优的执行计划,而基数值估算的准确性又取决于SQL中各个对象的统计信息是否完整、是否能真实反映出对象的数据分布情况。因此使用何种方法收集统计信息是很有讲究的:对于数据倾斜度较大的表需要收集直方图,在此基础上如果有多个列存在相关性,那么多列统计信息(也叫扩展统计信息)收集又是一个更好的选择。
在一般情况下,SQL语句的WHERE子句后面针对单张表都有多个条件,也就是根据多列的条件筛选得到数据。默认情况下,Oracle会把多列的选择率(Selectivity)相乘从而得到WHERE语句的选择率,但是这样有可能造成选择率不准确,从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断,从而生成准确的执行计划,Oracle在11g数据库中引入了收集多列统计信息。多列统计信息包含列组统计信息(Column Group Statistics)和表达式的统计信息(Expression Statistics)。
使用程序包DBMS_STATS中的新函数CREATE_EXTENDED_STATS创建一个虚拟列,然后对表收集统计信息。如下所示,定义了两个扩展列:
1 2 3 4 5 6 7 | SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'TEST', TABNAME => 'T', EXTENSION => '(UPPER(PAD))'), DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'TEST', TABNAME => 'T', EXTENSION => '(VAL2,VAL3)') FROM DUAL; |
以上SQL是对TEST用户下的T表,分别基于表达式和基于多列创建虚拟列,下次再收集表的统计信息时,将会自动收集到多列统计信息。需要注意的是,不能对SYS用户下的表创建扩展的统计信息,否则会报错“ORA-20000: Unable to create extension: not supported for SYS owned table”。
使用Oracle自带的DBMS_STATS包提供的存储过程DROP_EXTENDED_STATS来删除扩展统计信息:
1 2 | EXEC DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME => 'TEST',TABNAME => 'T',EXTENSION => '(UPPER(PAD))'); EXEC DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME => 'TEST',TABNAME => 'T',EXTENSION => '(VAL2,VAL3)'); |
定义扩展统计信息也可以直接在包DBMS_STATS中指定METHOD_OPT,收集统计信息时,把列组合作为单独列使用,如下所示:
1 2 3 4 5 6 7 8 9 | BEGIN DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'SCOTT', TABNAME => 'BOOKS', ESTIMATE_PERCENT=> 100, METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (HOTEL_ID,RATE_CATEGORY)', CASCADE => TRUE ); END; |
在视图DBA_STAT_EXTENSIONS中,可以看到在数据库中定义的扩展统计信息:
1 2 3 4 5 6 | SQL> SELECT EXTENSION_NAME, EXTENSION 2 FROM DBA_STAT_EXTENSIONS 3 WHERE TABLE_NAME='BOOKS'; EXTENSION_NAME EXTENSION ------------------------------ ------------------------------ SYS_STUW3MXAI1XLZHCHDYKJ9E4K90 ("HOTEL_ID","RATE_CATEGORY") |
当不清楚需要创建哪些列的扩展统计信息时,可以针对一个表,基于特定的工作负荷,通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定需要哪些列组。需要注意的是,这种技术不适用于包含表达式列的统计工作。主要过程如下所示:
1 2 3 4 | EXEC DBMS_STATS.SEED_COL_USAGE(NULL,NULL,TIME_LIMIT=>100); EXPLAIN PLAN FOR SQL语句; SELECT DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'LHR',TABNAME=>'T_ES_20170601_LHR') FROM DUAL; SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'LHR',TABNAME=>'T_ES_20170601_LHR') FROM DUAL; |