这篇文章主要讲解了“oracle统计信息查看与收集的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle统计信息查看与收集的方法是什么”吧！
一、查看表统计信息
SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SQL> 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>
感谢各位的阅读，以上就是“oracle统计信息查看与收集的方法是什么”的内容了，经过本文的学习后，相信大家对oracle统计信息查看与收集的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是云搜网，小编将为大家推送更多相关知识点的文章，欢迎关注！