一、查看表统计信息
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name='WOO';备注：通过脚本查看统计信息,参考MOS：SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)
二、查看表上的索引信息
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed
from user_indexes t
where table_name='DUMP_TABLE';
SQL> col table_name format a11
SQL> col index_name format a16
SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed
2 from user_indexes t
3 where table_name='DUMP_TABLE';
TABLE_NAME INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS LAST_ANAL
----------- ---------------- ---------- ---------- ----------- ---------
DUMP_TABLE WORNUM_IND 0 10 1 18-MAR-20
三、检查当前统计信息收集策略
set linesize 140
col WINDOW_NAME format a17
col REPEAT_INTERVAL for a55
col DURATION for a15
select t1.window_name,t1.repeat_interval,t1.duration from
dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name
and t2.window_group_name
in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
SQL> set linesize 140
SQL> col WINDOW_NAME format a17
SQL> col REPEAT_INTERVAL for a55
SQL> col DURATION for a15
SQL> select t1.window_name,t1.repeat_interval,t1.duration from
2 dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
3 where t1.window_name=t2.window_name
4 and t2.window_group_name
5 in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
----------------- ------------------------------------------------------- ---------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.3.1. 关闭自动统计信息收集
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SATURDAY_WINDOW"',
force => TRUE);
END;
/
3.2 修改自动统计信息持续时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(180,'minute'));
END;
/
3.3 修改自动统计信息开始时间，每周六22点开始
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0 ');
END;
/
3.4 开启自动统计信息收集
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SATURDAY_WINDOW"');
END;
/
3.5 再次检查策略是否正确
set linesize 200
col REPEAT_INTERVAL for a60
col DURATION for a30
select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name
and t2.window_group_name
in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ ------------------------------------------------------------ ------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.
四、手工收集统计信息
4.1 收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'WOO',indname => 'WORNUM_IND',estimate_percent => '10',degree => '4');
4.2 收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
4.3 收集表的统计信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',estimate_percent => 10,method_opt=> 'for all indexed columns');
4.4 收集分区表统计信息
exec dbms_stats.gather_table_stats(ownname => 'WOO',tabname => 'DUMP_TABLE',partname => 'p_20190318',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
4.5 收集某个用户的统计信息
exec dbms_stats.gather_schema_stats(ownname=>'WOO',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
4.6 收集整个数据库的统计信息
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
五、动态采样：
对于新创建的表，当访问此表时，oracle会动态的收集这个表的相关信息，等到晚上10点，再将其收集到数据字典中。
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select * from DUMP_TABLE;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1795212136
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20390 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUMP_TABLE | 10 | 20390 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1305 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed- dynamic sampling used for this statement (level=2) 表示动态采样，但是不记录数据字典，除非手动收集表的统计信息。
咱们通过user_tables查看结果也是一样的
SQL> set autotrace off;
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL>
六、统计信息收集完之后：
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select * from DUMP_TABLE;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1795212136
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 470 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUMP_TABLE | 10 | 470 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
1305 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> set autotrace off;
SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------------
10 4 2020-03-18 17:06:16
SQL>