用dbms_stats收集统计信息

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/