甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

QQ :532337155
电话：028-86922220
 
成都网站建设设计

将想法与焦点和您一起共享

微课sql优化(7)、统计信息收集(5)-关于直方图

1、直方图介绍

    你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。
Oracle Database为提供2种类别的列统计信息直方图：
  • Height-Balanced Histograms
  • Frequency Histograms
    数据库存储直方图信息*TAB_COL_STATISTICS视图(用户和DBA)。列值范围：HEIGHTBALANCED, FREQUENCY, or NONE。
2、Height-Balanced Histograms
    在height-balanced histogram中，列值被划分为桶，使得每个桶包含大致相同数量的行。直方图显示端点在值范围内的位置。
考虑一个my_col值为1到100之间的列，以及一个10个桶的直方图。如果数据my_col均匀分布，则直方图看起来与图13-1类似，其中数字是端点值。例如，第七个桶具有值在60到70之间的行。
图13-1具有均匀分布的高度平衡直方图

微课sql优化(7)、统计信息收集(5)-关于直方图
    每个桶中的行数为总行数的10％。在这个均匀分布的例子中，40％的行的值在60到100之间。
如果数据不均匀分布，则直方图可能如图13-2所示。在这种情况下，大多数行的列的值为5。只有10％的行的值在60到100之间。
图13-2具有非均匀分布的高度平衡直方图
    您可以使用USER_TAB_HISTOGRAMS表格查看高度平衡的直方图，如示例13-1所示。
  1. BEGIN
  2.   DBMS_STATS . GATHER_table_STATS  (  
  3.     OWNNAME  = >   'OE' ,  
  4.     TABNAME  = >   'INVENTORIES' ,  
  5.     METHOD_OPT  = >   'FOR COLUMNS SIZE 10 quantity_on_hand'   ) ;
  6. END ;
  7. /

  9. SELECT  COLUMN_NAME ,  NUM_DISTINCT ,  NUM_BUCKETS ,  HISTOGRAM 
  10. FROM  USER_TAB_COL_STATISTICS
  11. WHERE  TABLE_NAME  =   'INVENTORIES'   AND  COLUMN_NAME  =   'QUANTITY_ON_HAND' ;

  13. COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
  14. ------------------------------ ------------ ----------- ---------------
  15. QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

  17. SELECT  ENDPOINT_NUMBER ,  ENDPOINT_VALUE 
  18. FROM  USER_TAB_HISTOGRAMS
  19. WHERE  TABLE_NAME  =   'INVENTORIES'   AND  COLUMN_NAME  =   'QUANTITY_ON_HAND'
  20. ORDER   BY  ENDPOINT_NUMBER ;

  22. ENDPOINT_NUMBER ENDPOINT_VALUE
  23. --------------- --------------
  24.               0 0
  25.               1 27
  26.               2 42
  27.               3 57
  28.               4 74
  29.               5 98
  30.               6 123
  31.               7 149
  32.               8 175
  33.               9 202
  34.              10 353
       在示例13-1查询输出中，一行（1-10）对应于直方图中的每个桶。Oracle数据库向该直方图添加了特殊的第0个数据桶，因为第1个数据桶（27）中的值不是quantity_on_hand列的最小值。第0个桶的最小值为0 quantity_on_hand。

3、 frequency histogra m

    在 frequency histogram中，列的每个值对应于直方图的单个桶。每个桶包含此单个值的出现次数。例如，假设36行包含列的值1 warehouse_id。端点值1具有端点号36。
数据库在以下条件下自动创建频率直方图，而不是高度平衡的直方图：
  • 不同值的数量小于或等于指定的直方图桶数（最多254个）。
  • 每个列值重复一次。
您可以使用USER_TAB_HISTOGRAMS视图查看频率直方图，如示例13-2所示。
  1. BEGIN
  2.   DBMS_STATS . GATHER_TABLE_STATS  (  
  3.     OWNNAME  = >   'OE' ,  
  4.     TABNAME  = >   'INVENTORIES' ,  
  5.     METHOD_OPT  = >   'FOR COLUMNS SIZE 20 warehouse_id'   ) ;
  6. END ;
  7. /

  9. SELECT  COLUMN_NAME ,  NUM_DISTINCT ,  NUM_BUCKETS ,  HISTOGRAM 
  10. FROM  USER_TAB_COL_STATISTICS
  11. WHERE  TABLE_NAME  =   'INVENTORIES'   AND  COLUMN_NAME  =   'WAREHOUSE_ID' ;

  13. COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
  14. ------------------------------ ------------ ----------- ---------------
  15. WAREHOUSE_ID 9 9 FREQUENCY

  17. SELECT  ENDPOINT_NUMBER ,  ENDPOINT_VALUE 
  18. FROM  USER_TAB_HISTOGRAMS
  19. WHERE  TABLE_NAME  =   'INVENTORIES'   AND  COLUMN_NAME  =   'WAREHOUSE_ID'
  20. ORDER   BY  ENDPOINT_NUMBER ;

  22. ENDPOINT_NUMBER ENDPOINT_VALUE
  23. --------------- --------------
  24.              36 1
  25.             213 2
  26.             261 3
  27.             370 4
  28.             484 5
  29.             692 6
  30.             798 7
  31.             984 8
  32.            1112 9
在例13-2中，第一个桶为warehouse_id1。该值在表中显示36次，如以下查询所证实：
oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1;
 
  COUNT(*)
----------
        36

5、 练习4 、直方图优化练习

统计已销户用户数量，请优化以下语句
select count(1) from ht.c_cons where status='close';
  1. SQL >   select   status , count ( 1 )   from   ht . c_cons   group   by   status ;

  3. STATUS                              COUNT ( 1 )
  4. ------------------------------------------------------------ ----------
  5. close                                 19
  6. open                                 9519
  7. creating                             462

  9. SQL >   create   index  ht . idx_c_cons_status  on  ht . c_cons ( status ) ;
  10. SQL > col  owner  for  a10
  11.   col  table_name  for  a20
  12. col  column_name  for  a20
  13. col  data_type  for  a30
  14. col  histogram  for  a20
  15. select  owner , table_name , column_name , data_type ,
  16. column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED          from  
  17. dba_tab_columns  where  table_name = 'C_CONS'   and  owner = 'HT'
  18. order   by  column_id ; SQL >  SQL >  SQL >  SQL >  SQL >  2 3 4 

  20. OWNER              TABLE_NAME        COLUMN_NAME     DATA_TYPE             COLUMN_ID NUM_DISTINCT HISTOGRAM         NUM_NULLS LAST_ANALYZED
  21. ---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
  22. HT              C_CONS        CONS_NO       NUMBER                  1          10000 NONE             0 20 - AUG - 17
  23. HT          C_CONS        CONS_NAME      VARCHAR2                  2          5057 NONE             0 20 - AUG - 17
  24. HT          C_CONS        ORG_NAME      VARCHAR2                  3             12 NONE             0 20 - AUG - 17
  25. HT          C_CONS        BUILD_DATE      DATE                  4          10000 NONE             0 20 - AUG - 17
  26. HT          C_CONS        STATUS          VARCHAR2                  5              3 NONE             0 20 - AUG - 17

  28. SQL >  exec DBMS_STATS . GATHER_TABLE_STATS ( ownname  = >   'HT' , tabname  = >   'C_CONS' , estimate_percent  = >  30 , method_opt  = >   'for columns size 50 status' , no_invalidate  = >   FALSE , degree  = >  4 , cascade   = >   TRUE ) ;
  29. PL / SQL procedure successfully completed .
  30. SQL >   col  owner  for  a10
  31. SQL >   col  table_name  for  a20
  32. col  column_name  for  a20
  33. col  data_type  for  a30
  34. col  histogram  for  a20
  35. select  owner , table_name , column_name , data_type ,
  36. column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED          from  
  37. dba_tab_columns  where  table_name = 'C_CONS'   and  owner = 'HT'
  38. order   by  column_id ; SQL >  SQL >  SQL >  SQL >  2 3 4 

  40. OWNER     TABLE_NAME        COLUMN_NAME     DATA_TYPE             COLUMN_ID NUM_DISTINCT HISTOGRAM         NUM_NULLS LAST_ANALYZED
  41. ---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
  42. HT     C_CONS        CONS_NO       NUMBER                  1     10000 NONE             0 20 - AUG - 17
  43. HT     C_CONS        CONS_NAME      VARCHAR2                  2     5057 NONE             0 20 - AUG - 17
  44. HT     C_CONS        ORG_NAME      VARCHAR2                  3         12 NONE             0 20 - AUG - 17
  45. HT     C_CONS        BUILD_DATE      DATE                  4     10000 NONE             0 20 - AUG - 17
  46. HT     C_CONS        STATUS          VARCHAR2                  5         3 FREQUENCY             0 20 - AUG - 17
  47. SQL >   select   count ( 1 )   from  ht . c_cons  where  status = 'open' ;
  48. Execution Plan
  49. ----------------------------------------------------------
  50. Plan hash  value :  2016425671

  52. -------------------------------------------------------------------------------------------
  53. |  Id  |  Operation      |  Name          |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time      |
  54. -------------------------------------------------------------------------------------------
  55. |  0  |   SELECT  STATEMENT  |            |     1  |     6  |     8  ( 0 ) |  00 : 00 : 01  |
  56. |  1  |  SORT AGGREGATE  |            |     1  |     6  |       |       |
  57. | *  2  |   INDEX  FAST FULL SCAN |  IDX_C_CONS_STATUS  |  9639  |  57834  |     8  ( 0 ) |  00 : 00 : 01  |
  58. -------------------------------------------------------------------------------------------

  60. Predicate Information  ( identified   by  operation id ) :
  61. ---------------------------------------------------
  62.    2  -  filter ( "STATUS" = 'open' )
  63. Statistics
  64. ----------------------------------------------------------
  65.      1 recursive calls
  66.      0 db block gets
  67.      28 consistent gets
  68.      0 physical reads
  69.      0 redo  size
  70.     527 bytes sent via SQL * Net  to  client
  71.     523 bytes received via SQL * Net  from  client
  72.      2 SQL * Net roundtrips  to / from  client
  73.      0 sorts  ( memory )
  74.      0 sorts  ( disk )
  75.      1  rows  processed
  76. SQL >  
  77. SQL >   select   count ( 1 )   from  ht . c_cons  where  status = 'close' ;
  78. Execution Plan
  79. ----------------------------------------------------------

  81. Plan hash  value :  2292286995

  83. ---------------------------------------------------------------------------------------
  84. |  Id  |  Operation      |  Name      |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time  |
  85. ---------------------------------------------------------------------------------------
  86. |  0  |   SELECT  STATEMENT  |           |  1  |  6  |  1     ( 0 ) |  00 : 00 : 01  |
  87. |  1  |  SORT AGGREGATE  |           |  1  |  6  |        |       |
  88. | *  2  |   INDEX  RANGE SCAN |  IDX_C_CONS_STATUS  |  24  |  144  |  1     ( 0 ) |  00 : 00 : 01  |
  89. ---------------------------------------------------------------------------------------

  91. Predicate Information  ( identified   by  operation id ) :
  92. ---------------------------------------------------

  94.    2  -   access ( "STATUS" = 'close' )


  97. Statistics
  98. ----------------------------------------------------------
  99.      1 recursive calls
  100.      0 db block gets
  101.      2 consistent gets
  102.      0 physical reads
  103.      0 redo  size
  104.     526 bytes sent via SQL * Net  to  client
  105.     523 bytes received via SQL * Net  from  client
  106.      2 SQL * Net roundtrips  to / from  client
  107.      0 sorts  ( memory )
  108.      0 sorts  ( disk )
  109.      1  rows  processed


分享标题：微课sql优化(7)、统计信息收集(5)-关于直方图
本文链接：http://chengdu.cdxwcx.cn/article/ipdppo.html

其他资讯