【效能優化】直方圖

SQL>
SQL> begin
  2         for i in 1..10000 loop
  3           insert into hist values (i, i);
  4         end loop;
  5         commit;
  6       end;
  7       /

PL/SQL 過程已成功完成。
SQL> update hist set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index i_hist_b on tab(b);
索引已建立。
然後分析表，強制使列B不產生直方圖。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => USER,
                                TABNAME    => 'HIST',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 1 ');
END;
檢視檢視USER_TAB_HISTOGRAMS，列B上只有最大值，最小值兩條記錄分別對應端點號（endpoint_number）0和1，這種顯示說明列B沒有直方

圖資訊。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ；

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
HIST                            B                                                      0              1
HIST                            B                                                      1          10000

在沒有直方圖的情況下，在B列上進行等值查詢的時候，都是索引範圍掃描。
-----------------------------------------------------------------
SQL> select * from hist where b =1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HIST     |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_HIST_B |  1000 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        570  bytes sent via SQL*Net to client
        492  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 * from hist where b =5;
9991 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HIST     |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_HIST_B |  1000 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=5)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1369  consistent gets
          0  physical reads
          0  redo size
     212165  bytes sent via SQL*Net to client
       7818  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9991  rows processed

-----生成直方圖 再次收集統計資訊時 務必清除之前的統計資訊。否則執行計劃會利用原來的統計資訊
SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => user,
  3  TABNAME    => 'HIST',
  4  CASCADE    => TRUE,
  5  METHOD_OPT => 'FOR ALL COLUMNS  SIZE AUTO ');
  6  END;
  7  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.28

SQL> select * from hist where b=1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HIST     |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_HIST_B |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=1)
Statistics
----------------------------------------------------------
        150  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        570  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from hist where b =5;
9991 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1745918543
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9991 | 59946 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HIST |  9991 | 59946 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=5)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        688  consistent gets
          0  physical reads
          0  redo size
     212165  bytes sent via SQL*Net to client
       7818  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9991  rows processed
-------------------------------------------
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
  2  WHERE TABLE_NAME = 'HIST';

TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
----------- ------------ --------------  -------------- ---
HIST         B                1              1      
HIST         B                2              2
HIST         B                3              3
HIST         B                4              4
HIST         B               9995              5
HIST         B               9996           9996
HIST         B               9997           9997
HIST         B               9998           9998
HIST         B               9999           9999
HIST         B               10000          10000
HIST         A                0              1
HIST         A                1              10000

12 rows selected.

Elapsed: 00:00:00.00

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-672508/，如需轉載，請註明出處，否則將追究法律責任。