ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 统计信息管理-动态采样

统计信息管理-动态采样

原创 Linux操作系统 作者:lsq_008 时间:2009-02-19 08:04:01 0 删除 编辑

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

动态采样的特性是受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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 直方图测试
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1254599