測試直方圖的用法

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

oracle直方圖histogram小記(一)

相關文章