使用待定的统计信息



众所周知，统计信息会影响oracle的执行计划，但是搜集统计信息后是否会真的提高数据库的执行效率，这个谁也不能肯定，为了保险期间，可以先测试待定的统计信息，
确定能提供高效的执行计划是在应用。
建立测试表
create table t(iid int,sname varchar2(100));

begin
for i in 1..1000 loop
insert into t values(i,'a');
end loop;
end;
/

begin
for i in 1001..1010 loop
insert into t values(i,'b');
end loop;
end;
/
建立索引
SQL> create index sname_idx on t(sname);

SQL> set autotrace on explain
SQL> select count(sname) from t where sname='a';

COUNT(SNAME)
------------
1000

执行计划
----------------------------------------------------------
Plan hash value: 1417815139

-----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:0
1 |

| 1 | SORT AGGREGATE | | 1 | 52 | |
|

|* 2 | INDEX FAST FULL SCAN| SNAME_IDX | 1000 | 52000 | 3 (0)| 00:00:0
1 |

-----------------------------------------------------------------------------------

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

2 - filter("SNAME"='a')

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> select count(sname) from t where sname='b';

COUNT(SNAME)
------------
10

执行计划
----------------------------------------------------------
Plan hash value: 16299662

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | INDEX RANGE SCAN| SNAME_IDX | 10 | 520 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("SNAME"='b')

Note
-----
- dynamic sampling used for this statement (level=2)

添加测试数据
begin
for i in 1010..2010 loop
insert into t values(i,'b');
end loop;
end;
/

再查查数据，此时执行计划为全表扫描
SQL> select count(sname) from t where sname='b';

COUNT(SNAME)
------------
1011

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | TABLE ACCESS FULL| T | 1011 | 52572 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("SNAME"='b')

Note
-----
- dynamic sampling used for this statement (level=2)

对表进行设置，不立即应用统计信息
begin
dbms_stats.set_table_prefs(
ownname => user,
tabname => 't',
pname => 'publish',
pvalue => 'false'
);
end;
/

重新搜集统计信息
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size 1',
cascade => TRUE
);
end;
/

设置optimizer_use_pending_statistics参数，临时应用新的统计信息看看结果
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
再看看执行计划，可见执行计划改为了INDEX RANGE SCAN

SQL> select count(sname) from t where sname='b';

COUNT(SNAME)
------------
10

执行计划
----------------------------------------------------------
Plan hash value: 16299662

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | INDEX RANGE SCAN| SNAME_IDX | 10 | 520 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("SNAME"='b')

Note
-----
- dynamic sampling used for this statement (level=2)

很明显， INDEX RANGE SCAN比全表扫描高效，手动应用新的统计信息。
execute dbms_stats.publish_pending_stats(ownname => user, tabname => 'T');