ITPub博客

首页 > 数据库 > Oracle > partition table with Pending statistics

partition table with Pending statistics

原创 Oracle 作者:warmbreeze 时间:2016-10-13 15:46:47 0 删除 编辑
用Pending statistics测试sql的执行计划时,如果是分区表,Pending statistics无效.

select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

--建分区表,收集统计信息
create table t(id number, name varchar2(10))
 partition by range(id)(
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values less than (30),
    partition p4 values less than (40),
    partition p5 values less than (50) )
;

insert into t 
select rownum*10-1, rownum
from dual connect by rownum<6 ;
commit;

 exec dbms_stats.gather_table_stats(user,'T');

select num_rows,blocks,last_analyzed
    from user_tab_statistics
     where table_name ='T';
  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         5        230 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16

--执行sql
 select count(*) from t where id = 9;
 select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ft9cu51yjfmh, child number 0
-------------------------------------
 select count(*) from t where id = 9

Plan hash value: 2993254470
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |    10 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      |     1 |     3 |    10   (0)| 00:00:01 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL    | T    |     1 |     3 |    10   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ID"=9)

select count(*) from t;
 select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cyzznbykb509s, child number 1
-------------------------------------
select count(*) from t
Plan hash value: 3225603066
-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |    43 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |     5 |    43   (0)| 00:00:01 |     1 |     5 |
|   3 |    TABLE ACCESS FULL | T    |     5 |    43   (0)| 00:00:01 |     1 |     5 |
-------------------------------------------------------------------------------------

--向某个分区插入数据
 insert into t select 9, '9' 
from dual connect by rownum<1e4 ;
commit;

 exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

--统计信息没变
exec dbms_stats.gather_table_stats(user,'T');
 select num_rows,blocks,last_analyzed
    from user_tab_statistics
     where table_name ='T';
 NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         5        230 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16
         1         46 27-SEP-16

--Pending statistics
select num_rows,blocks
    from user_tab_pending_stats
    where table_name ='T';
  NUM_ROWS     BLOCKS
---------- ----------
     10000         46
         1         46
         1         46
         1         46
         1         46
     10004        230

--使用Pending statistics
 alter session set optimizer_use_pending_statistics=true;

select count(*) from t where id = 9;


 select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1usps76kb8yz, child number 0
-------------------------------------
select count(*) from t where id = 9


Plan hash value: 2993254470


------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |    10 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      |     1 |     3 |    10   (0)| 00:00:01 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL    | T    |     1 |     3 |    10   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("ID"=9)






select count(*) from t;
 select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cyzznbykb509s, child number 1
-------------------------------------
select count(*) from t


Plan hash value: 3225603066


-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |    43 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |     5 |    43   (0)| 00:00:01 |     1 |     5 |
|   3 |    TABLE ACCESS FULL | T    |     5 |    43   (0)| 00:00:01 |     1 |     5 |
-------------------------------------------------------------------------------------


rows没变, 
需要invalid现存的cursor, Pending statistics才能生效





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

请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    42171