oracle直方图histogram小记(一)

测试直方图的用法
1,直方图的概念
 直方图有两种类别,等频直方图与等高直方图。
  默认的,如果一个倾斜列上的唯一值超过了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;

执行计划
----------------------------------------------------------                     
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)
                                                   
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';
INDEX_NAME                     TO_CHAR(LAST_ANALYZ                             
------------------------------ -------------------                             
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)
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;

执行计划
----------------------------------------------------------                     
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');
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)
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
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 
 
3,直方图的小结
  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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值