Oracle 锁定临时表统计信息及锁住后是否能用动态采集的hint

全局临时表的统计信息是不能被收集的，如果被收集，它的统计信息肯定为0，会造成执行计划不准，所以要锁定它的统计信息，禁止系统自动收集。--先解锁表的统计信息，然后删除表的统计信息，最后锁住表的统计信息declare v_sqlvarchar2（500）; cursor rowList1 is select"begin dbms_stats.unlock_table_stats（user,""" || table_name ||"""）; end;" from user_tables s where s.temporary = "Y"; cursor rowList2 is select"begin dbms_stats.delete_table_stats（user,""" || table_name ||"""）; end;" from user_tables s where s.temporary = "Y"; cursor rowList3 is select"begin dbms_stats.lock_table_stats（user,""" || table_name ||"""）; end;" from user_tables s where s.temporary = "Y"; begin open rowList1; open rowList2; open rowList3; loop fetch rowList1 into v_sql; executeimmediate v_sql; exitwhen rowList1%notfound; endloop; loop fetch rowList2 into v_sql; executeimmediate v_sql; exitwhen rowList2%notfound; endloop; loop fetch rowList3 into v_sql; executeimmediate v_sql; exitwhen rowList3%notfound; endloop; close rowList1; close rowList2; close rowList3;end;-- STATTYPE_LOCKED="ALL"意思是表的统计信息被锁select s.table_name,s.STATTYPE_LOCKED from user_TAB_STATISTICS s where s.STATTYPE_LOCKED="ALL";--当表的统计信息被锁后，用动态采集的hint是否生效，实验结果是hint是生效的SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> exec dbms_stats.lock_table_stats（user,"test"）;
SQL> select s.num_rows, s.last_analyzed
from user_tables s
where s.table_name = "TEST";
NUM_ROWS LAST_ANALYZED
---------- ----------------说明表被锁了
SQL> select s.STATTYPE_LOCKED from user_TAB_STATISTICS s
where s.table_name="TEST";
STATTYPE_L
----------
ALL
SQL> select count（*） from test;
COUNT（*）
----------
70384
SQL> set autotrace traceonly
SQL> select /*+ dynamic_sampling（test 1） */ * from test;
已选择70384行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 160K| 31M| 199 （3）| 00:00:03 |
| 1 | TABLE ACCESS FULL| TEST | 160K| 31M| 199 （3）| 00:00:03 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
5239 consistent gets
0 physical reads
0 redo size
3186713 bytes sent via SQL*Net to client
51949 bytes received via SQL*Net from client
4694 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
70384 rows processed
SQL> select /*+ dynamic_sampling（test 5） */ * from test;
已选择70384行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9747 | 1970K| 195 （0）| 00:00:03 |
| 1 | TABLE ACCESS FULL| TEST | 9747 | 1970K| 195 （0）| 00:00:03 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
5239 consistent gets
0 physical reads
0 redo size
3186713 bytes sent via SQL*Net to client
51949 bytes received via SQL*Net from client
4694 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
70384 rows processed
SQL> select /*+ dynamic_sampling（test 10） */ * from test;
已选择70384行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70384 | 13M| 197 （2）| 00:00:03 |
| 1 | TABLE ACCESS FULL| TEST | 70384 | 13M| 197 （2）| 00:00:03 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5165 consistent gets
0 physical reads
0 redo size
3186713 bytes sent via SQL*Net to client
51949 bytes received via SQL*Net from client
4694 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
70384 rows processedOracle 单实例从32位迁移到 64位方法 http://www.linuxidc.com/Linux/2012-03/55759.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle导入导出expdp IMPDP详解 http://www.linuxidc.com/Linux/2014-04/99932.htmOracle 10g expdp导出报错ORA-4031的解决方法 http://www.linuxidc.com/Linux/2014-04/99842.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址