测试直方图的用法
1,直方图的概念
直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
何谓倾斜列(或者说是列值偏差较大):例如一个表TAB有10000行记录,列A前10行的值分别1-10,
剩下的9900行值全部都为999,则该列称为倾斜列,意思就是列的值分布不均匀。
2,直方图的示例
SQL> create table t_zhifang(id int,status1 int);
直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
何谓倾斜列(或者说是列值偏差较大):例如一个表TAB有10000行记录,列A前10行的值分别1-10,
剩下的9900行值全部都为999,则该列称为倾斜列,意思就是列的值分布不均匀。
2,直方图的示例
SQL> create table t_zhifang(id int,status1 int);
表已创建。
SQL> insert into t_zhifang select level,level from dual connect by level<=10;
已创建10行。
SQL> commit;
提交完成。
SQL> insert into t_zhifang select level+10,level+10 from dual connect by level<=9990;
已创建9990行。
SQL> commit;
提交完成。
SQL> update t_zhifang set status1=88 where status1>=11;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index idx_t_zhifang on t_zhifang(status1);
索引已创建。
SQL> set autot trace
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot trace exp stat
SQL> select count(status1) from t_zhifang where status1<=10;
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot trace exp stat
SQL> select count(status1) from t_zhifang where status1<=10;
执行计划
----------------------------------------------------------
Plan hash value: 1509418136
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:0
1 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX_T_ZHIFANG | 10 | 130 | 2 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS1"<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
29 consistent gets
1 physical reads
0 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(status1) from t_zhifang where status1>10;
执行计划
----------------------------------------------------------
Plan hash value: 237896220
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T_ZHIFANG | 9990 | 126K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS1">10)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
54 consistent gets
4 physical reads
0 redo size
419 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> desc user_indexes;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
VISIBILITY VARCHAR2(9)
DOMIDX_MANAGEMENT VARCHAR2(14)
SEGMENT_CREATED VARCHAR2(3)
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
VISIBILITY VARCHAR2(9)
DOMIDX_MANAGEMENT VARCHAR2(14)
SEGMENT_CREATED VARCHAR2(3)
SQL> set autot off
SQL> select index_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_indexes where table_name='T_ZHIFANG';
SQL> select index_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_indexes where table_name='T_ZHIFANG';
INDEX_NAME TO_CHAR(LAST_ANALYZ
------------------------------ -------------------
IDX_T_ZHIFANG 2012-11-24 18:43:51
------------------------------ -------------------
IDX_T_ZHIFANG 2012-11-24 18:43:51
SQL> desc user_tab_histograms;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(1000)
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(1000)
SQL> select table_name,column_name,endpoint_number,endpoint_value,endpoint_actual_value from user_tab_histograms where table_name='T_ZHIFANG';
未选定行
SQL> SET AUTOT TRACE EXP STAT
SQL> select count(status1) from t_zhifang where status1=88;
SQL> select count(status1) from t_zhifang where status1=88;
执行计划
----------------------------------------------------------
Plan hash value: 237896220
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T_ZHIFANG | 9990 | 126K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS1"=88)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--收集直方图信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T_ZHIFANG',cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T_ZHIFANG',cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL 过程已成功完成。
SQL> SET AUTOT OFF
SQL> DESC user_tab_histograms;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(1000)
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(1000)
SQL> col table_name for a30
SQL> col column_name for a30
SQL> col endpoint_number for 9999999
SQL> col endpoint_value for 9999999
SQL> col endpoint_actual_value for 9999999
SQL> col column_name for a30
SQL> col endpoint_number for 9999999
SQL> col endpoint_value for 9999999
SQL> col endpoint_actual_value for 9999999
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
T_ZHIFANG STATUS1 1 1
T_ZHIFANG STATUS1 2 2
T_ZHIFANG STATUS1 3 3
T_ZHIFANG STATUS1 4 4
T_ZHIFANG STATUS1 5 5
T_ZHIFANG STATUS1 6 6
T_ZHIFANG STATUS1 7 7
T_ZHIFANG STATUS1 8 8
T_ZHIFANG STATUS1 9 9
T_ZHIFANG STATUS1 10 10
T_ZHIFANG STATUS1 10000 88
T_ZHIFANG STATUS1 1 1
T_ZHIFANG STATUS1 2 2
T_ZHIFANG STATUS1 3 3
T_ZHIFANG STATUS1 4 4
T_ZHIFANG STATUS1 5 5
T_ZHIFANG STATUS1 6 6
T_ZHIFANG STATUS1 7 7
T_ZHIFANG STATUS1 8 8
T_ZHIFANG STATUS1 9 9
T_ZHIFANG STATUS1 10 10
T_ZHIFANG STATUS1 10000 88
3,直方图的小结
1,可能数据量太小,oracle在未收集直方图情况下依然会对高重复列的记录进行选择全表扫描
2,user_tab_histograms直方图字典的列ENDPOINT_NUMBER是此列唯一值的最大终点值,即这样重复记录有多少条,
列endpoint_value是此列唯一值的实际列值
比如 10000 88 ,各表示10000为此列的最大终点值是10000,88表示此列的唯一值是88
而用上述的10000-10(列是endpoint_number)就是这个桶总共存储的重复值的记录数为9990条记录,的列值是88
1,可能数据量太小,oracle在未收集直方图情况下依然会对高重复列的记录进行选择全表扫描
2,user_tab_histograms直方图字典的列ENDPOINT_NUMBER是此列唯一值的最大终点值,即这样重复记录有多少条,
列endpoint_value是此列唯一值的实际列值
比如 10000 88 ,各表示10000为此列的最大终点值是10000,88表示此列的唯一值是88
而用上述的10000-10(列是endpoint_number)就是这个桶总共存储的重复值的记录数为9990条记录,的列值是88
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-749877/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-749877/