什么是直方图?
发布网友
发布时间：2022-04-23 14:00
共4个回答
懂视网
时间：2022-05-04 04:42
1. 示例说明直方图的作用。 初始化数据 dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_objects ; Table created. dexter@STARTREK select count(*
1. 示例说明直方图的作用。
初始化数据
dexter@STARTREK> select count(*) fromall_objects ;
COUNT(*)
----------
72642
dexter@STARTREK> create table tuning4_tabnologging as select * from all_objects ;
Table created.
dexter@STARTREK> select count(*) fromall_objects ;
COUNT(*)
----------
72643
dexter@STARTREK> create indexidx_tuning4_tab_owner on tuning4_tab (owner) ;
Index created.
dexter@STARTREK> @gather_tab
Enter value for tbname: tuning4_tab
PL/SQL procedure successfully completed.
在这里碰到了一个小问题，因为数据的倾斜比较严重，而且oracle数据库在执行gather_table_stats的时候没有收集owner列的统计信息，这里优化器选择了错误的执行计划。下面记录了完整的处理过程。
出现错误的执行计划
dexter@STARTREK> select* from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 989038285
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("OWNER"='PUBLIC' OR"OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9581 consistent gets
0 physical reads
0 redo size
6805858 bytes sent via SQL*Net to client
43970 bytes received via SQL*Net from client
3952 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59253 rows processed
下面的输出可以看到，其实使用全表扫描的效率要高于indexrange scan。
dexter@STARTREK> select /*+full(tuning4_tab)*/ *from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1641193091
---------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 4687 | 443K| 290 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 4687 | 443K| 290 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4927 consistent gets
0 physical reads
0 redo size
3035580 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK>
trace 一下
dexter@STARTREK> alter session settracefile_identifier=histogram ;
Session altered.
dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';
Session altered.
dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 989038285
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -access("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9581 consistent gets
0 physical reads
0 redo size
6805858 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK> alter session set events '10053trace name context off ';
Session altered.
dexter@STARTREK>
从10053中看到
Access path analysis for TUNING4_TAB
***************************************
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]
Column(#1): OWNER(
AvgLen: 6NDV: 31 Nulls: 0 Density: 0.032258
Table:TUNING4_TAB Alias: TUNING4_TAB
Card:Original: 72643.000000 Rounded: 4687 Computed: 4686.65 Non Adjusted: 4686.65
Rounded:
4687
实际：
59253
明显是由于统计信息不准确造成的。我们看一下它的直方图信息。
其实从执行计划
| 0 | SELECTSTATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
也可以看到它的统计信息不准确。
确定问题根源
dexter@STARTREK> select* from user_tab_histograms where table_name='TUNING4_TAB' andcolumn_name='OWNER' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------------------------------------ --------------- --------------------------------------------
TUNING4_TAB OWNER 0 3.3913E+35
TUNING4_TAB OWNER 1 4.5831E+35
dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------------------------------------ ---------------
TUNING4_TAB OWNER NONE
TUNING4_TAB OBJECT_NAME NONE
TUNING4_TAB SUBOBJECT_NAME NONE
TUNING4_TAB OBJECT_ID NONE
TUNING4_TAB DATA_OBJECT_ID NONE
TUNING4_TAB OBJECT_TYPE NONE
TUNING4_TAB CREATED NONE
TUNING4_TAB LAST_DDL_TIME NONE
TUNING4_TAB TIMESTAMP NONE
TUNING4_TAB STATUS NONE
TUNING4_TAB TEMPORARY NONE
TUNING4_TAB GENERATED NONE
TUNING4_TAB SECONDARY NONE
TUNING4_TAB NAMESPACE NONE
TUNING4_TAB EDITION_NAME NONE
15 rows selected.
没有直方图信息。
我们来直接查看表中数据的分布情况
dexter@STARTREK> select owner,count(*) fromtuning4_Tab group by owner order by 2 ;
OWNER COUNT(*)
------------------------------ ----------
OWBSYS 2
APPQOSSYS 5
SCOTT 6
SI_INFORMTN_SCHEMA 8
OUTLN 8
ORACLE_OCM 8
BI 8
ORDPLUGINS 10
PM 10
FLOWS_FILES 11
OWBSYS_AUDIT 12
DEXTER 25
APEX 33
HR 35
IX 48
DBSNMP 57
OE 112
ORDDATA 239
SH 299
EXFSYS 308
WMSYS 312
CTXSYS 384
SYSTEM 516
XDB 517
OLAPSYS 717
MDSYS 1545
APEX_030200 2251
ORDSYS 2512
SYSMAN 3392
PUBLIC 28027
SYS 31226
31 rows selected.
倾斜很严重，并且因为gather_table_stats的时候默认没有收集直方图信息，导致优化器没有选择正确的执行计划，我们来收集一下它的直方图。
默认为FOR ALL COLUMNS SIZEAUTO没有收集直方图。
dexter@STARTREK> selectdbms_stats.get_param('METHOD_OPT') from dual ;
DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
收集列的直方图信息
dexter@STARTREK> execdbms_stats.gather_table_stats(user,'tuning4_tab',cascade=>true,method_opt=>'FORALL columns size skewonly') ;
PL/SQL procedure successfully completed.
dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------------------------------------ ---------------
TUNING4_TAB OWNER FREQUENCY
TUNING4_TAB OBJECT_NAME HEIGHT BALANCED
TUNING4_TAB SUBOBJECT_NAME FREQUENCY
TUNING4_TAB OBJECT_ID NONE
TUNING4_TAB DATA_OBJECT_ID HEIGHT BALANCED
TUNING4_TAB OBJECT_TYPE FREQUENCY
TUNING4_TAB CREATED HEIGHT BALANCED
TUNING4_TAB LAST_DDL_TIME HEIGHT BALANCED
TUNING4_TAB TIMESTAMP HEIGHT BALANCED
TUNING4_TAB STATUS FREQUENCY
TUNING4_TAB TEMPORARY FREQUENCY
TUNING4_TAB GENERATED FREQUENCY
TUNING4_TAB SECONDARY FREQUENCY
TUNING4_TAB NAMESPACE FREQUENCY
TUNING4_TAB EDITION_NAME NONE
15 rows selected.
owner为频率直方图，比较正确。
dexter@STARTREK> select * from user_tab_histograms wheretable_name='TUNING4_TAB' and column_name='OWNER' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------------------------------------ --------------- -------------------------------------
TUNING4_TAB OWNER 1 3.3913E+35
TUNING4_TAB OWNER 179 3.3913E+35
TUNING4_TAB OWNER 206 3.4959E+35
TUNING4_TAB OWNER 212 3.5442E+35
TUNING4_TAB OWNER 215 3.5448E+35
TUNING4_TAB OWNER 244 3.6006E+35
TUNING4_TAB OWNER 246 3.7551E+35
TUNING4_TAB OWNER 249 3.8082E+35
TUNING4_TAB OWNER 370 4.0119E+35
TUNING4_TAB OWNER 383 4.1159E+35
TUNING4_TAB OWNER 422 4.1174E+35
TUNING4_TAB OWNER 423 4.1186E+35
TUNING4_TAB OWNER 436 4.1186E+35
TUNING4_TAB OWNER 636 4.1186E+35
TUNING4_TAB OWNER 2824 4.1711E+35
TUNING4_TAB OWNER 2855 4.3242E+35
TUNING4_TAB OWNER 5199 4.3277E+35
TUNING4_TAB OWNER 5455 4.3277E+35
TUNING4_TAB OWNER 5500 4.3277E+35
TUNING4_TAB OWNER 5525 4.5330E+35
TUNING4_TAB OWNER 5567 4.5831E+35
21 rows selected.
再次测试
已经收集好了直方图，我们再来看一下执行计划以及10053事件。
set autotrace traceonly
alter session set tracefile_identifier=histogram ;
alter session set events '10053 trace name contextforever ,level 12';
select * from tuning4_Tab where owner='SYS' orowner='PUBLIC' ;
alter session set events '10053 trace name contextoff ';
已经选择了正确、最优的执行计划。
dexter@STARTREK> set autotrace traceonly
dexter@STARTREK> alter session settracefile_identifier=histogram ;
Session altered.
dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';
Session altered.
dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1641193091
---------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 59137 | 5601K| 291 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 59137 | 5601K| 291 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4927 consistent gets
0 physical reads
0 redo size
3035580 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK> alter session set events '10053trace name context off ';
Session altered.
dexter@STARTREK> exit
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]
Column(#1):
NewDensity:0.000090, OldDensity:0.000007 BktCnt:5567, PopBktCnt:5565,PopValCnt:19, NDV:31
Column(#1): OWNER(
AvgLen: 6NDV: 31 Nulls: 0 Density: 0.000090
Histogram: Freq #Bkts: 21 UncompBkts: 5567 EndPtVals: 21
Table:TUNING4_TAB Alias: TUNING4_TAB
Card:Original: 72643.000000 Rounded: 59137 Computed: 59137.43 Non Adjusted: 59137.43
Rounded: 59137
比较正确了。也选择了全表扫描作为最佳的accesspath。
热心网友
时间：2022-05-04 01:50
直方图(Histogram)又称质量分布图。是一种统计报告图，由一系列高度不等的纵向条纹或线段表示数据分布的情况。 一般用横轴表示数据类型，纵轴表示分布情况。
作用：
(1)显示质量波动的状态；
(2)较直观地传递有关过程质量状况的信息；
(3)通过研究质量波动状况之后，就能掌握过程的状况，从而确定在什么地方集中力量进行质量改进工作。
热心网友
时间：2022-05-04 03:08
直方图是用面积表示各组频数的多少，矩形的高度表示每一组的频数或频率，宽度则表示各组的组距，因此其高度与宽度均有意义。
其次，由于分组数据具有连续性，直方图的各矩形通常是连续排列，而条形图则是分开排列。
最后，条形图主要用于展示分类数据，而直方图则主要用于展示数据型数据
请看下面的示例, 虽然我们可以用条形图来近似地模拟直方图, 但由于条形图的x轴是分类轴, 不是刻度轴, 因此, 它不是严格意义上的直方图.
参考资料：http://club.excelhome.net/dispbbs.asp?boardid=6&id=191193
热心网友
时间：2022-05-04 05:16
就是用一个长矩形块来表示数量的多少
比如说 92年到2000年的人口数量 每年都用一个竖条来表示 那个竖条越高就表示越多
