【效能優化】Oracle 效能優化：行比例偏差大 建立柱狀圖

海星星hktk發表於2014-09-04
優化Oracle

行比例偏差大 建立柱狀圖


當一個表中資料分佈不均，某一個查詢條件對應的結果比例較大時，通過索引訪問代價可能比全表掃描還要高很多。通過實驗，模擬表中資料航比例偏差大、但優化器仍然選擇索引掃描的情況。通過建立柱狀圖來影響優化器選擇執行計劃。


【實驗環境】

作業系統：RHEL 5.5

資料庫：Oracle 10.2.0


【實驗過程】

1、環境部署

1.1、建立表、新增索引

 SCOTT@ prod>create table test2 as select * from emp;

SCOTT@ prod>create index ind_test2_empno on test2(empno);

1.2、分析表

 SCOTT@ prod>analyze table test2 compute statistics;

1.3、使用索引列進行查詢

SCOTT@ prod>set autotrace traceonly

SCOTT@ prod>select * from test2 where empno=7788;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

 

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |    87 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2          |     1 |    87 |     2   (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TEST2_EMPNO|     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed


1.4、插入資料

SCOTT@ prod>begin

    for i in 1..10000 loop

    insert into test2 values(7788,'WARD','SALESMAN',7839,sysdate,3000,2000,20);

    end loop;

    end;

    /

PL/SQL procedure successfully completed.

SCOTT@ prod>commit;

 

SCOTT@ prod>select count(*) from test2;

 

  COUNT(*)

----------

     10014

SCOTT@ prod>select count(*) from test2 where empno=7788;

 

  COUNT(*)

----------

     10001

SCOTT@ prod>


1.5、分析表、檢視執行計劃

SCOTT@ prod>analyze table test2 estimate statistics;

SCOTT@ prod>select * from test2 where empno=7788;

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |   715 | 23595 |    8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2           |   715 | 23595 |    8   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_TEST2_EMPNO |   715 |       |    3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

SCOTT@ prod>


1.6、檢視集簇因子

SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR

    from dba_tables t,dba_indexes i

    where t.table_name=i.table_name

    and t.owner='SCOTT'

    and t.table_name='TEST2';

 

TABLE_NAME   NUM_ROWS  BLOCKS AVG_ROW_LEN INDEX_NAME      CLUSTERING_FACTOR

---------- ---------- ------- ----------- --------------- -----------------

TEST2           10014     68          42 IND_TEST2_EMPNO               63


1.7、檢視EMPNO列柱狀圖

SCOTT@ prod>select column_name,num_distinct,num_buckets,histogram

    from user_tab_col_statistics

    where table_name='TEST2' and column_name='EMPNO';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

EMPNO                                    14           1 NONE


通常當查詢結果小於3%-5%時，oracle會選擇走索引。

本次查詢全表資料10014行，查詢結果資料10001行，屬於行比例偏差較大的情況，以empno=7788條件查詢時，走索引反而代價更高。需要為列建立柱狀圖。

2、建立柱狀圖

2.1、使用包建立柱狀圖

SYS@ prod>exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TEST2', method_opt => 'FOR COLUMNS empno');

PL/SQL procedure successfully completed.

2.2、檢視EMPNO列柱狀圖

 SCOTT@ prod>select column_name,num_distinct,num_buckets,histogram

    from user_tab_col_statistics

    where table_name='TEST2' and column_name='EMPNO';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

EMPNO                                    14          14 FREQUENCY

2.3、檢視執行計劃

 SCOTT@ prod>select * from test2 where empno=7788;

 

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 300966803

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       | 10001 |   332K|    18   (6)| 00:00:01 |

|*  1 | TABLE ACCESS FULL| TEST2 | 10001 |   332K|    18   (6)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("EMPNO"=7788)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

 

SCOTT@ prod>


2.4、檢視其他查詢條件時執行計劃

 SCOTT@ prod>select * from test2 where empno=7499;

Execution Plan

----------------------------------------------------------

Plan hash value: 3801234157

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |     1 |    34 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2           |     1 |    34 |    2   (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TEST2_EMPNO |     1 |       |    1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7499)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

          0  bytes sent via SQL*Net to client

          0  bytes received via SQL*Net from client

          0  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

這次執行計劃選擇了索引

【實驗總結】

1、通常當查詢結果小於3%-5%時，oracle會選擇走索引。當行比例偏差較大的時，需要為列建立柱狀圖。建立柱狀圖後，查詢結果行比例較大時，會走全表掃描。查詢結果比例小於3%-5%時，仍然走索引。

2、為列建立柱狀圖的另一種方法

SCOTT@ prod>analyze table test2 estimate statistics;

SCOTT@ prod>analyze table test2 estimate statistics for columns empno;


呂星昊
2014.9.4

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

相關文章