CBO優化器基于三個方面評估SQL cost:selectivity,cardinality and cost(CPU&MEM&I/O),利用dbms_stats收集column柱狀圖資訊,CBO依據柱狀圖可以得到column的資料分布情況：
SQL> create table t1 as select 1 c1,rpad('*',200,'*') c2 from all_objects;
Table created.
SQL> insert into t1 values(2,rpad('*',200,'*'));
1 row created.
SQL> commit;
Commit complete.
SQL> create index idx_t1_01 on t1(c1);
Index created.
SQL> select * from user_tab_histograms where table_name = 'T1' and column_name='C1' order by 4;
no rows selected
SQL> select table_name,
2 column_name,
3 num_distinct,
4 num_nulls,
5 num_buckets,
6 density,
7 low_value,
8 high_value
9 from user_tab_col_statistics
10 where table_name = 'T1'
11 and column_name = 'C1';
no rows selected
SQL> explain plan for select * from t1 where c1=1;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76264 | 8564K| 558 (1)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T1 | 76264 | 8564K| 558 (1)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("C1"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> explain plan for select * from t1 where c1=2;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3018735338
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 115 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("C1"=2)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
沒有收集統計資訊時的計劃是正确的,FTS for 1,index for 2;
現在收集統計資訊:
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',method_opt => 'for all columns');
PL/SQL procedure successfully completed.
SQL> select * from user_tab_histograms where table_name = 'T1' and column_name='C1' order by 4;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPO
------------ ------------- --------------- -------------- -----
T1 C1 5584 1
SQL> select table_name,
2 column_name,
3 num_distinct,
4 num_nulls,
5 num_buckets,
6 density,
7 utl_raw.cast_to_number(low_value) low_value,
8 utl_raw.cast_to_number(high_value) high_value
9 from user_tab_col_statistics
10 where table_name = 'T1'
11 and column_name = 'C1';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY LOW_VALUE HIGH_VALUE
------------ ------------- ------------ ---------- ----------- ---------- --------- ----------
T1 C1 2 0 1 7.2698E-06 1.00 2.00
SQL> explain plan for select * from t1 where c1=2;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33872 | 6747K| 558 (1)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T1 | 33872 | 6747K| 558 (1)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
1 - filter("C1"=2)
13 rows selected.
SQL>
這樣的收集沒有顯示出資料的傾斜度,結果得出了錯誤的計劃；
重新收集:
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',estimate_percent=>100,method_opt => 'FOR COLUMNS SIZE 5 c1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats(ownname => user,indname => 'IDX_T1_01');
PL/SQL procedure successfully completed.
SQL> select * from user_tab_histograms where table_name = 'T1' and column_name='C1' order by 4;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPO
------------ ------------- --------------- -------------- -----
T1 C1 67742 1
T1 C1 67743 2
SQL>
SQL> explain plan for select * from t1 where c1=1;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67742 | 6946K| 558 (1)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T1 | 67742 | 6946K| 558 (1)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
1 - filter("C1"=1)
13 rows selected.
SQL> explain plan for select * from t1 where c1=2;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 3018735338
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 105 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("C1"=2)
14 rows selected.
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/45259/viewspace-662126/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/45259/viewspace-662126/