oracle统计信息查看与收集的方法是什么

这篇文章主要讲解了"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 a11SQL> col index_name format a16SQL> 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 140col WINDOW_NAME format a17col REPEAT_INTERVAL for a55col DURATION for a15select 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 140SQL> col WINDOW_NAME format a17SQL> col REPEAT_INTERVAL for a55SQL> col DURATION for a15SQL> 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:00TUESDAY_WINDOW    freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00WEDNESDAY_WINDOW  freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00THURSDAY_WINDOW   freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00FRIDAY_WINDOW     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00SATURDAY_WINDOW   freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00SUNDAY_WINDOW     freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:007 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 200col REPEAT_INTERVAL for a60col DURATION for a30select 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:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0         +000 20:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:007 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 200SQL> set autotrace traceonlySQL> 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 200SQL> set autotrace traceonlySQL> 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:16SQL>

感谢各位的阅读，以上就是"oracle统计信息查看与收集的方法是什么"的内容了，经过本文的学习后，相信大家对oracle统计信息查看与收集的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！