Oracle11.2开始,可以使用下面方式搜集统计信息:
1、搜集统计信息完成后,自动发布(默认方式PUBLISH)
2、保存新的统计信息,并且待定发布
以待定方式保存新的统计信息,允许你检验新的统计信息,如果统计信息满足需求,再将其发布。
为了检查统计信息搜集完成后是否自动发布,使用DBMS_STATS,如下:
SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISHFROM DUAL;
查询结果返回TRUE或FALSE。TRUE表明搜集统计信息完成后自动发布,而FALSE表明搜集统计信息完成后将待定。
提示:
数据库存储发布的统计信息到数据字典视图比如USER_TAB_STATISTICS 和USER_IND_STATISTICS。数据库存储待定的统计信息到视图比如USER_TAB_PENDING_STATS和USER_IND_PENDING_STATS。
你可以在用户或者表级修改PUBLISH设置。例如,修改用户SH的customers表的PUBLISH设置:
execdbms_stats.set_table_prefs('SH', 'CUSTOMERS', 'PUBLISH','false');
随后,当搜集customers表的统计信息,搜集完成后,统计信息将不会自动发布,取而代之的是,数据库存储新搜集的统计信息到USER_TAB_PENDING_STATS。
缺省情况下,优化器使用存在在数据字典视图中已发布的统计信息。如果希望优化器使用新搜集的待定统计信息,设置初始化参数OPTIMIZER_USE_PENDING_STATISTICS的值为TRUE(缺省值为FALSE),并基于表或者用户运行一个工作负载。
altersession set optimizer_use_pending_statistics = TRUE;
当编译sql语句,优化器将使用待定的统计信息替代发布的统计信息。如果待定的统计性能是有效的,可以使用执行下面的语句将它发布:
Execdbms_stats.publish_pending_stats(null, null);
你可以为一个特定的数据对象发布待定统计信息。例如,使用下面的语句:
Execdbms_stats.publish_pending_stats('SH','CUSTOMERS');
如果你不想发布待定的统计信息,执行下面的语句:
Execdbms_stats.delete_pending_stats('SH','CUSTOMERS');
你可以使用函数dbms_stats.export_pending_stats导出待定的统计,导出待定的统计到测试系统,使你可以运行一个完整的工作负责测试新的统计信息。
简单实验如下:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bitProduction
PL/SQL Release 11.2.0.3.0 - Production
CORE
11.2.0.3.0
Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISHFROM DUAL;
PUBLISH
----------------------
TRUE
SQL> show parameteroptimizer_use_pending_statistics
NAME
TYPE
VALUE
------------------------------------ -----------------------------------------
optimizer_use_pending_statistics
boolean
FALSE
SQL>
SQL> create table wxlun_obj as select * fromdba_objects where rownum<10;
Table created.
SQL> create index ind_obj_type onwxlun_obj(object_type);
Index created.
SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWSfrom user_tab_statistics where table_name='WXLUN_OBJ';
TABLE_NAME
NUM_ROWS
------------------------------ ----------
WXLUN_OBJ
9
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
no rows selected
SQL>
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133
--------------------------------------------------------------------------------------------
| Id |Operation
|Name
| Rows
| Bytes | Cost (%CPU)|Time
|
--------------------------------------------------------------------------------------------
|
0 | SELECTSTATEMENT
|
|
3 |
30|
2
(0)| 00:00:01 |
|
1 |
TABLEACCESS BY INDEX ROWID|WXLUN_OBJ
|
3 |
30|
2
(0)| 00:00:01 |
|* 2 |
INDEXRANGESCAN
| IND_OBJ_TYPE|
3|
|
1
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
597
bytes sent via SQL*Net to client
519
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
4
rows processed
SQL>
多次执行:insert into wxlun_obj select * from dba_objects;
SQL> execdbms_stats.set_table_prefs('wxlun','wxlun_obj','PUBLISH','false');
PL/SQL procedure successfully completed.
SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';
TABLE_NAME
NUM_ROWS
------------------------------ ----------
WXLUN_OBJ
9
------user_tab_statistics的统计信息未变
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
TABLE_NAME
NUM_ROWS
------------------------------ ----------
WXLUN_OBJ
1085609
------WXLUN_OBJ的统计信息放到了USER_TAB_PENDING_STATS
SQL> select count(*) from wxlun_obj;
COUNT(*)
----------
1085609
SQL> select count(*) from wxlun_obj whereobject_type='TABLE';
COUNT(*)
----------
90964
SQL>
因为wxlun_obj中object_type值为TABLE的数据量太多,按照分析下面的查询应该走全表扫描才合理,但是由于前面的设置,事与愿违:
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133
--------------------------------------------------------------------------------------------
| Id |Operation
|Name
| Rows
| Bytes | Cost (%CPU)|Time
|
--------------------------------------------------------------------------------------------
|
0 | SELECTSTATEMENT
|
|
3 |
30|
2
(0)| 00:00:01 |
|
1 |
TABLEACCESS BY INDEX ROWID|WXLUN_OBJ
|
3 |
30|
2
(0)| 00:00:01 |
|* 2 |
INDEX RANGESCAN
| IND_OBJ_TYPE|
3|
|
1
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
18283
consistent gets
0
physical reads
0
redo size
1592229
bytes sent via SQL*Net to client
67223
bytes received via SQL*Net fromclient
6066
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
90964
rows processed
SQL>
SQL> alter session setoptimizer_use_pending_statistics = TRUE;
Session altered.
SQL> set linesize 132
SQL> set autotrace traceonly
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 381774511
-------------------------------------------------------------------------------
| Id |Operation
|Name
| Rows
| Bytes | Cost (%CPU)|Time
|
-------------------------------------------------------------------------------
|
0 | SELECTSTATEMENT
|
| 90024 |
1142K|
4148
(1)| 00:00:50 |
|* 1 |
TABLE ACCESS FULL| WXLUN_OBJ | 90024|
1142K|
4148
(1)| 00:00:50 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
52
recursive calls
1
db block gets
20656
consistent gets
0
physical reads
0
redo size
1592229
bytes sent via SQL*Net to client
67223
bytes received via SQL*Net fromclient
6066
SQL*Net roundtrips to/from client
3
sorts (memory)
0
sorts (disk)
90964
rows processed
SQL>
SQL> Execdbms_stats.publish_pending_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';
TABLE_NAME
NUM_ROWS
------------------------------ ----------
WXLUN_OBJ
1085609
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
no rows selected
SQL>
SQL> alter session setoptimizer_use_pending_statistics = false;
Session altered.
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 381774511
-------------------------------------------------------------------------------
| Id |Operation
|Name
| Rows
| Bytes | Cost (%CPU)|Time
|
-------------------------------------------------------------------------------
|
0 | SELECTSTATEMENT
|
| 90024 |
1142K|
4148
(1)| 00:00:50 |
|* 1 |
TABLE ACCESS FULL| WXLUN_OBJ | 90024|
1142K|
4148
(1)| 00:00:50 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1
recursive calls
1
db block gets
20625
consistent gets
0
physical reads
0
redo size
1592229
bytes sent via SQL*Net to client
67223
bytes received via SQL*Net fromclient
6066
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
90964
rows processed
SQL>
提示:
数据库存储发布的统计信息到数据字典视图比如USER_TAB_STATISTICS 和USER_IND_STATISTICS。数据库存储待定的统计信息到视图比如USER_TAB_PENDING_STATS和USER_IND_PENDING_STATS。
简单实验如下:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bitProduction
PL/SQL Release 11.2.0.3.0 - Production
CORE
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISHFROM DUAL;
PUBLISH
----------------------
TRUE
SQL> show parameteroptimizer_use_pending_statistics
NAME
------------------------------------ -----------------------------------------
optimizer_use_pending_statistics
SQL>
SQL> create table wxlun_obj as select * fromdba_objects where rownum<10;
Table created.
SQL> create index ind_obj_type onwxlun_obj(object_type);
Index created.
SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL>
TABLE_NAME
------------------------------ ----------
WXLUN_OBJ
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
no rows selected
SQL>
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133
--------------------------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------------------------
|
|
|*
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
SQL>
多次执行:insert into wxlun_obj select * from dba_objects;
SQL> execdbms_stats.set_table_prefs('wxlun','wxlun_obj','PUBLISH','false');
PL/SQL procedure successfully completed.
SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';
TABLE_NAME
------------------------------ ----------
WXLUN_OBJ
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
TABLE_NAME
------------------------------ ----------
WXLUN_OBJ
SQL> select count(*) from wxlun_obj;
----------
SQL> select count(*) from wxlun_obj whereobject_type='TABLE';
----------
SQL>
因为wxlun_obj中object_type值为TABLE的数据量太多,按照分析下面的查询应该走全表扫描才合理,但是由于前面的设置,事与愿违:
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133
--------------------------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------------------------
|
|
|*
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
SQL>
SQL> alter session setoptimizer_use_pending_statistics = TRUE;
Session altered.
SQL> set linesize 132
SQL> set autotrace traceonly
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 381774511
-------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------
|
|*
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
SQL>
SQL> Execdbms_stats.publish_pending_stats('wxlun','wxlun_obj');
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';
TABLE_NAME
------------------------------ ----------
WXLUN_OBJ
SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;
no rows selected
SQL>
SQL> alter session setoptimizer_use_pending_statistics = false;
Session altered.
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';
90964 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 381774511
-------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------
|
|*
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
SQL>