全局临时表的统计信息是不能被收集的，如果被收集，它的统计信息肯定为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 (leve