这篇文章主要讲解了“oracle统计信息查看与收集的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle统计信息查看与收集的方法是什么”吧！
创新互联建站致力于互联网品牌建设与网络营销，包括网站设计、网站制作、SEO优化、网络推广、整站优化营销策划推广、电子商务、移动互联网营销等。创新互联建站为不同类型的客户提供良好的互联网应用定制及解决方案，创新互联建站核心团队十余年专注互联网开发，积累了丰富的网站经验，为广大企业客户提供一站式企业网站建设服务，在网站建设行业内树立了良好口碑。
一、查看表统计信息
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统计信息查看与收集的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
