理解 Balanced Histogram 与Height Balanced Histogram
在数据查询中，对于有谓词的操作，优化器需要根据数据的分布情况，计算是走索引还是全表扫描，实现数据的Access方法的选择。所以就需要一种方法使得优化器知道该如何选择。直方图是统计学中的一个概念，通过数据的条块分割，统计出不同值的数据分布，比如某个列有10个不同的值，通过直方图可以计算（估计出）每个值的数据分布，1 有100个，2 有20个，4有15个等待，Oracle根据自己的算法计算Density和selectivity，为计算Cost值提供依据，从而决定是否走索引。这是直方图的好处，帮助优化器做快速判断，但是有时候由于字段长度问题导致某种类型的直方图统计的列虽然NDV很好，依然会走全表扫描，这我们在后面文章会分析，这里我们讨论两种类型的直方图（Density如何计算在后续文件再做测试）
这里强调下，如果实际工作中有这种场景性能问题，我们务必抓住这样的机会，对问题深入分析，系统全面的了解知识点，做出测试，给自己一个知识点总结，日积月累就会形成系统的思维方式，对Oracle一些“伎俩”也会有敏感度，后面的一个案例我会详细分析。
我们先分析直方图，这个例子来自MOS的一篇文章Interpreting Histogram Information (Doc ID 72539.1)
创建测试表。
drop table HTAB1;
create table HTAB1 (a number, b number);
Insert into HTAB1 ( A,B) values ( 1,1);
Insert into HTAB1 ( A,B) values ( 2,2);
Insert into HTAB1 ( A,B) values ( 3,3);
Insert into HTAB1 ( A,B) values ( 4,4);
Insert into HTAB1 ( A,B) values ( 9996,9996);
Insert into HTAB1 ( A,B) values ( 9997,9997);
Insert into HTAB1 ( A,B) values ( 9998,9998);
Insert into HTAB1 ( A,B) values ( 9999,9999);
Insert into HTAB1 ( A,B) values ( 10000,10000);
commit;
begin
for i in 5 .. 9995 loop
Insert into HTAB1 ( A,B)
values ( i,5);
if (mod(i,100) = 0) then
commit;
end if;
end loop;
commit;
end;
/
commit;
create index HTAB1_B on HTAB1(b);
exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'HTAB1', method_opt => 'FOR ALL COLUMNS SIZE 1'); <<<<< 不收集直方图
alter session set OPTIMIZER_DYNAMIC_SAMPLING = 0; <<<<屏蔽动态采样。
下面看数据分布情况：
SCOTT@orcl1> select distinct B , count(*)
2 from HTAB1
3 group by B
4 order by B
5 ;
B COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 9991
9996 1
9997 1
9998 1
9999 1
10000 1
10 rows selected.
可以看出，该表字段B的数据分布很不均匀。我们在上面的设置中将动态统计信息功能关闭，测试没有直方图时，Oracle如何选择执行计划，已经在B字段创建了索引。
为了显示友好，我们创建一个raw data 到number data的转换函数。
SCOTT@orcl1> create or replace function raw_to_number(my_input raw)
2 return number
3 as
4 my_output number;
5 begin
6 dbms_stats.convert_raw_value(my_input,my_output);
7 return my_output;
8 end;
9 /
Function created.
Elapsed: 00:00:00.11
查询表直方图相关统计信息
SCOTT@orcl1> column COLUMN_NAME format a5 heading COL
SCOTT@orcl1>column NUM_DISTINCT format 99990
SCOTT@orcl1>column LOW_VALUE format 99990
SCOTT@orcl1>column HIGH_VALUE format 99990
SCOTT@orcl1>column DENSITY format 99990
SCOTT@orcl1>column NUM_NULLS format 99990
SCOTT@orcl1>column NUM_BUCKETS format 99990
SCOTT@orcl1>column SAMPLE_SIZE format 99990
SCOTT@orcl1>select COLUMN_NAME,NUM_DISTINCT,raw_to_number(LOW_VALUE) Low,raw_to_number(HIGH_VALUE) High,DENSITY,NUM_NULLS,
2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM
3 from user_tab_columns
4 where table_name = 'HTAB1';
COL NUM_DISTINCT LOW HIGH DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE HISTOGRAM
----- ------------ ---------- ---------- ------- --------- ----------- --------- ----------- ---------------
A 10000 1 10000 0 0 1 20-SEP-20 10000 NONE
B 10 1 10000 0 0 1 20-SEP-20 10000 NONE
这个统计信息是准确的，因为我们关闭了直方图统计。
下面我们查询B = 5的数据，明显这里应该走全表扫描，走索引会效率很差。我们看Oracle如何选择。
Execution Plan
----------------------------------------------------------
Plan hash value: 3130945556
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HTAB1 | 1000 | 7000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HTAB1_B | 1000 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
recursive calls
db block gets
1370 consistent gets
physical reads
redo size
244152 bytes sent via SQL*Net to client
7850 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
9991 rows processed
这里走了差的执行计划，竟然走索引，B5的记录应该有9991条，这里评估有1000条，没有直方图，Oracle会默认用 all _rows / ndv = 10000/10 =1000,所以这里走了索引
我继续查询只有一条记录的b=3
SCOTT@orcl1> select * from htab1 where b=3;
A B
---------- ----------
3 3
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3130945556
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HTAB1 | 1000 | 7000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HTAB1_B | 1000 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=3)
Statistics
----------------------------------------------------------
1 recursive calls
db block gets
4 consistent gets
physical reads
redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
1 rows processed
注意这里虽然走对了索引，但是Rows为1000，也是不准确的，
这两个例子要正确且得到相对准确的统计信息，就需要同直方图信息。
method_opt => 'FOR ALL COLUMNS SIZE AUTO');<<<<自动收集执行过的SQL谓词中列的直方图
SCOTT@orcl1>exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'HTAB1', method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.71
再次查看统计信息的情况
SCOTT@orcl1>set autotr off;
SCOTT@orcl1>column COLUMN_NAME format a5 heading COL
SCOTT@orcl1>column NUM_DISTINCT format 99990
SCOTT@orcl1>column LOW_VALUE format 99990
SCOTT@orcl1>column HIGH_VALUE format 99990
SCOTT@orcl1>column DENSITY format 99990
SCOTT@orcl1>column NUM_NULLS format 99990
SCOTT@orcl1>column NUM_BUCKETS format 99990
SCOTT@orcl1>column SAMPLE_SIZE format 99990
SCOTT@orcl1>select COLUMN_NAME,NUM_DISTINCT,raw_to_number(LOW_VALUE) Low,raw_to_number(HIGH_VALUE) High,DENSITY,NUM_NULLS,
2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM
3 from user_tab_columns
4 where table_name = 'HTAB1';
COL NUM_DISTINCT LOW HIGH DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE HISTOGRAM
----- ------------ ---------- ---------- ------- --------- ----------- --------- ----------- ---------------
A 10000 1 10000 0 0 1 20-SEP-20 10000 NONE
B 10 1 10000 0 10 20-SEP-20 10000 FREQUENCY
Elapsed: 00:00:00.01
此时B列生成了FREQUENCY直方图，因为NUM_BUCKETS数量>=NDV（B）的值，我们看下直方图信息
SCOTT@orcl1> select lpad(TABLE_NAME,10) TAB, lpad(COLUMN_NAME, 10) COL,
2 ENDPOINT_NUMBER, ENDPOINT_VALUE
3 from user_histograms
4 where table_name='HTAB1'
5 order by COL, ENDPOINT_NUMBER;
TAB COL ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ---------------------------------------- --------------- --------------
HTAB1 A 1
HTAB1 A 1 10000
HTAB1 B 1 1
HTAB1 B 2 2
HTAB1 B 3 3
HTAB1 B 4 4
HTAB1 B 9995 5
HTAB1 B 9996 9996
HTAB1 B 9997 9997
HTAB1 B 9998 9998
HTAB1 B 9999 9999
HTAB1 B 10000 10000
12 rows selected.
Elapsed: 00:00:00.02
说明： 这里ENDPOINT_VALUE说明是列值，ENDPOINT_NUMBER行的累积值，所以计算某个一值出现次数，需要用ENDPOINT_NUMBER做减法计算，例如B=5 其对应ENDPOINT_NUMBER=9995- B=4对应的ENDPOINT_NUMBER=4 其出现行数=9995 - 4 =9991次。
我们再次执行上述两条SQL
1 select * from htab1 where b=5 <<<<<此时该SQL执行全表扫描，执行计划优
Execution Plan
----------------------------------------------------------
Plan hash value: 2049148369
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 69937 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HTAB1 | 9991 | 69937 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=5)
Statistics
----------------------------------------------------------
21 recursive calls
db block gets
735 consistent gets
23 physical reads
redo size
212182 bytes sent via SQL*Net to client
7849 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
7 sorts (memory)
sorts (disk)
9991 rows processed
2 select * from htab1 where b=3; <<<<该执行计划继续走索引
Execution Plan
----------------------------------------------------------
Plan hash value: 3130945556
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HTAB1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HTAB1_B | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=3)
Statistics
----------------------------------------------------------
1 recursive calls
db block gets
4 consistent gets
5 physical reads
redo size
589 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
1 rows processed
但是从这次的优化器评估的ROWS看，此时走索引只返回一行数据，数据统计部分跟没有收集直方图时是一样的。
2 Height Balanced Histograms
此时字段B有10个不同的值，我们只需要指定8个buckets .
SCOTT@orcl1>exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'HTAB1', method_opt => 'FOR COLUMNS B SIZE 8');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.11
查询直方图信息
SCOTT@orcl1>column COLUMN_NAME format a5 heading COL
SCOTT@orcl1>column NUM_DISTINCT format 99990
SCOTT@orcl1>column LOW_VALUE format 99990
SCOTT@orcl1>column HIGH_VALUE format 99990
SCOTT@orcl1>column DENSITY format 99990
SCOTT@orcl1>column NUM_NULLS format 99990
SCOTT@orcl1>column NUM_BUCKETS format 99990
SCOTT@orcl1>column SAMPLE_SIZE format 99990
SCOTT@orcl1>select COLUMN_NAME,NUM_DISTINCT,raw_to_number(LOW_VALUE) Low,raw_to_number(HIGH_VALUE) High,DENSITY,NUM_NULLS,
2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM
3 from user_tab_columns
4 where table_name = 'HTAB1';
COL NUM_DISTINCT LOW HIGH DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE HISTOGRAM
----- ------------ ---------- ---------- ------- --------- ----------- --------- ----------- ---------------
A 10000 1 10000 0 0 1 20-SEP-20 10000 NONE
B 10 1 10000 0 0 8 20-SEP-20 10000 HEIGHT BALANCED
查看直方图数据分布
SCOTT@orcl1> select lpad(TABLE_NAME,10) TAB, lpad(COLUMN_NAME, 10) COL,
2 ENDPOINT_NUMBER, ENDPOINT_VALUE
3 from user_histograms
4 where table_name='HTAB1'
5 order by COL, ENDPOINT_NUMBER;
TAB COL ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ---------- --------------- --------------
HTAB1 A 1
HTAB1 A 1 10000
HTAB1 B 1
HTAB1 B 7 5
HTAB1 B 8 10000
Elapsed: 00:00:00.00
分析。。。再添加
下面执行刚才两个SQL。
SCOTT@orcl1>set autot on;
SCOTT@orcl1>select * from htab1 where B=3; <<<<此时优化器评估的ROWS为139
A B
---------- ----------
3 3
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3130945556
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 139 | 973 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HTAB1 | 139 | 973 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HTAB1_B | 139 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=3)
Statistics
----------------------------------------------------------
1 recursive calls
db block gets
4 consistent gets
physical reads
redo size
589 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
1 rows processed
下面执行第二个语句。
select * from htab1 where b=5 <<<<<此时走全表扫描
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8750 | 61250 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HTAB1 | 8750 | 61250 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=5)
Statistics
----------------------------------------------------------
recursive calls
db block gets
688 consistent gets
physical reads
redo size
212182 bytes sent via SQL*Net to client
7849 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
9991 rows processed
我们再将height balanced histogram的信息放在下面
TAB COL ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ---------- --------------- --------------
HTAB1 A 1
HTAB1 A 1 10000
HTAB1 B 1
HTAB1 B 7 5
HTAB1 B 8 10000
我们收集下字段B的数据数量和分布
SCOTT@orcl1> select B,count(*) from htab1 group by B order by count(*);
B COUNT(*)
---------- ----------
1 1
9997 1
9999 1
10000 1
9996 1
2 1
4 1
9998 1
3 1
5 999
在HEIGHT BALANCED直方图中，ENDPOINT_NUMBER 是桶号，ENDPOINT_VALUE是基于字段值的endpoint value。对于字段B，一共有8个桶，桶1-桶7的endpoint都是5，Oracle将具有相同ENDPOINT_VALUE的桶存储到一个桶中，所以，这里只有两个桶，一个存储了1 到5之间的值，一个存储了5到10000之间的值日.
由于这个直方图的存在，Oracle E-ROWS 就基于这个统计选择优的执行计划，也就是Oracle知道了谓词中B=number，具体数据的分布情况了。其实也有一种情况正是由于收集了直方图导致执行计划走了不想要的全表扫描，比如数据自由三条，而ORacle走索引竟然评估的有与全表扫一样的行数，这个就是另个一问题，也就是索引存储字符值时会有32字节的限制，这个要具体问题具体分析了。
相关文章