理解 Balanced Histogram 与Height Balanced Histogram

2020-09-21 00:00:00 索引 数据 字段 执行 直方图

在数据查询中,对于有谓词的操作,优化器需要根据数据的分布情况,计算是走索引还是全表扫描,实现数据的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

注意这里虽然走对了索引,但是Rows1000,也是不准确的,

这两个例子要正确且得到相对准确的统计信息,就需要同直方图信息。

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数量>=NDVB)的值,我们看下直方图信息

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


此时字段B10个不同的值,我们只需要指定8buckets .

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;      <<<<此时优化器评估的ROWS139


         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-7endpoint都是5Oracle将具有相同ENDPOINT_VALUE的桶存储到一个桶中,所以,这里只有两个桶,一个存储了1 5之间的值,一个存储了510000之间的值日.

由于这个直方图的存在,Oracle E-ROWS 就基于这个统计选择优的执行计划,也就是Oracle知道了谓词中B=number,具体数据的分布情况了。其实也有一种情况正是由于收集了直方图导致执行计划走了不想要的全表扫描,比如数据自由三条,而ORacle走索引竟然评估的有与全表扫一样的行数,这个就是另个一问题,也就是索引存储字符值时会有32字节的限制,这个要具体问题具体分析了。









相关文章