统计信息管理-动态采样

某些情况下，需要进行动态采样，比如已有的统计信息过旧，不能反应真实的情况，或者某些表和索引缺乏统计信息等

动态采样的特性是受OPTIMIZER_DYNAMIC_SAMPLING 参数控制，为了实现动态采样，该参数需要设置为2或更高。
默认值是2。当该值设为0时，表示禁用动态采样。
当OPTIMIZER_FEATURES_ENABLE 参数被设置为9.2.0以前的版本时，也将禁用动态采样。

下面看当OPTIMIZER_DYNAMIC_SAMPLING设置为2时，

SQL> select table_name,last_analyzed from user_tables     
  2  where table_name in('T1','T2');
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1
T2

可见在t1和t2表上均没有统计信息，此时进行查询：
SQL>  select t1.last_name,t1.salary from t1,t2
  2  where t1.employee_id=t2.customer_id and t2.order_id=100;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    66 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    66 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   107 |  4280 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."EMPLOYEE_ID"="T2"."CUSTOMER_ID")
   2 - filter("T2"."ORDER_ID"=100)
 
Note
-----
   - dynamic sampling used for this statement

发现使用了自动采样，只对t1表做统计信息收集后，再次执行查询：

SQL> exec dbms_stats.gather_table_stats(user,'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select t1.last_name,t1.salary from t1,t2
where t1.employee_id=t2.customer_id and t2.order_id=100;  2 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    42 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    42 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   107 |  1712 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."EMPLOYEE_ID"="T2"."CUSTOMER_ID")
   2 - filter("T2"."ORDER_ID"=100)
 
Note
-----
   - dynamic sampling used for this statement

仍然使用是自动采样，可见，对于没有分析过的表，还是要进行自动采样，接下来对t2进行统计信息收集，再次执行查询。

SQL> exec dbms_stats.gather_table_stats(user,'T2');
 
PL/SQL procedure successfully completed.
 
SQL> select t1.last_name,t1.salary from t1,t2
where t1.employee_id=t2.customer_id and t2.order_id=100;  2 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    24 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    24 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     8 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   107 |  1712 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."EMPLOYEE_ID"="T2"."CUSTOMER_ID")
   2 - filter("T2"."ORDER_ID"=100)

SQL> select table_name,last_analyzed from user_tables
  2  where table_name in('T1','T2');
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1                             13-FEB-09
T2                             13-FEB-09

再看optimizer_dynamic_sampling被设置为1的情况：

SQL> alter session set optimizer_dynamic_sampling=1;
 
Session altered.

SQL> exec dbms_stats.delete_table_stats(user,'T1');
 
PL/SQL procedure successfully completed.
 
SQL> set autot traceonly explain
SQL> select t1.last_name,t1.salary from t1,t2
where t1.employee_id=t2.customer_id and t2.order_id=100;  2 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    96 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     2 |    96 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     8 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   164 |  6560 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."EMPLOYEE_ID"="T2"."CUSTOMER_ID")
   2 - filter("T2"."ORDER_ID"=100)

SQL> select table_name,last_analyzed from user_tables
  2  where table_name in('T1','T2');
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1
T2                             13-FEB-09

SQL> exec dbms_stats.delete_table_stats(user,'T2');
 
PL/SQL procedure successfully completed.
 
SQL> set autot traceonly explain
SQL> select t1.last_name,t1.salary from t1,t2
where t1.employee_id=t2.customer_id and t2.order_id=100;  2 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    66 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    66 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   107 |  4280 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."EMPLOYEE_ID"="T2"."CUSTOMER_ID")
   2 - filter("T2"."ORDER_ID"=100)
 
Note
-----
   - dynamic sampling used for this statement

SQL> select table_name,last_analyzed from user_tables
  2   where table_name in('T1','T2');
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1
T2

可见，当optimizer_dynamic_sampling=1时，只有在2个表均没有统计信息时，才会使用动态采样。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-555614/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-555614/