统计信息收集不完的解决

早上收到邮件，某库的一节点报错ora-7445。


alert告警：
Sat Nov 21 03:30:54 2015
ARC2: Completed archiving thread 1 sequence 20859 (8430321499688-8430321499835) (c2db71)
Sat Nov 21 06:00:01 2015
Errors in file /app/oracle/admin/c2db7/bdump/c2db71_j003_9243.trc:
ORA-07445: exception encountered: core dump [pfrgnc()+29] [SIGSEGV] [Address not mapped to object] [0x0000000B0] [] []
Sat Nov 21 06:00:04 2015
Trace dumping is performing id=[cdmp_20151121060004]




在MOS上查询，符合文档描述：
Bug 12368527 - DBMS_STATS_JOB may hit ORA-7445[pfrgnc] (文档 ID 12368527.8)


在10.2.0.4版本数据库上进行统计信息收集，job的从属进程可能在执行dbms_stats_job时触发ora-7445[pfrgnc]。
该bug在11.1.0.6版本中解决。


该bug本身是由于统计信息收集的job在维护窗口时间内没有执行完成。
解决的方法是增大维护窗口的时间范围，或者使dbms_stats_job的执行更快。




查看当前维护窗口：
sys@c2db71> select ds.last_start_date,ds.last_run_duration
  2  from dba_scheduler_jobs ds
  3  where job_name='GATHER_STATS_JOB';


LAST_START_DATE                                                             LAST_RUN_DURATION
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
20-NOV-15 10.00.00.600794 PM +08:00                                         +000000000 08:01:00.300930




sys@c2db71> select log_date,status,additional_info
  2  from dba_scheduler_job_log
  3  where job_name='GATHER_STATS_JOB'
  4  order by log_id;


LOG_DATE                               STATUS       ADDITIONAL_INFO
-------------------------------------- ------------ --------------------------------------------------------------------------------
24-OCT-15 06.00.00.721145 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
27-OCT-15 06.00.00.864309 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
28-OCT-15 06.00.00.933990 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
29-OCT-15 06.00.01.074298 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
30-OCT-15 06.00.00.173108 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
31-OCT-15 06.00.00.199287 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
03-NOV-15 06.00.00.374654 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
04-NOV-15 06.00.00.439645 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
05-NOV-15 06.00.00.513895 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
06-NOV-15 06.00.00.500967 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
07-NOV-15 06.00.00.622089 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
10-NOV-15 06.00.00.700254 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
13-NOV-15 06.00.01.175444 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
14-NOV-15 06.00.00.410782 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
11-NOV-15 06.00.00.822476 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
12-NOV-15 06.00.00.925706 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
17-NOV-15 06.00.00.280864 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
18-NOV-15 06.00.00.327630 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
19-NOV-15 06.00.00.502065 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
20-NOV-15 06.00.00.510377 AM +08:00    STOPPED      REASON="Stop job called because associated window was closed"
21-NOV-15 06.01.00.902501 AM +08:00    STOPPED      REASON="Job slave process was terminated"


21 rows selected.


sys@c2db71> col repeat_interval for a50                                                                                                                            
sys@c2db71> select window_name,repeat_interval,duration,enabled                                                                                                    
  2   from dba_scheduler_windows;                                                                                                                                  
                                                                                                                                                                   
WINDOW_NAME                    REPEAT_INTERVAL                                    DURATION                                                                    ENABL
------------------------------ -------------------------------------------------- --------------------------------------------------------------------------- -----
WEEKNIGHT_WINDOW               freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;bym +000 08:00:00                                                               TRUE 
                               inute=0; bysecond=0                                                                                                                 
                                                                                                                                                                   
WEEKEND_WINDOW                 freq=daily;byday=SAT;byhour=0;byminute=0;bysecond= +000 05:00:00                                                               TRUE 
                               0                                                                                                                                   
                               
                               
每个工作日晚上十点开始，持续八小时，到早上六点结束
每个周六零点开始，持续五小时


查看三个月没有收集统计信息的表：
sys@db1_terry> select table_name,last_analyzed,num_rows from dba_tables
  2  where owner='TERRY'
  3  and last_analyzed<=trunc(sysdate-90)
  4  order by num_rows desc;


TABLE_NAME                     LAST_ANALYZED         NUM_ROWS
------------------------------ ------------------- ----------
PGW_ORDERINFO                  2014-09-20 05:50:47  237054847
PGW_PUFR_ORDERINFO             2014-12-10 02:50:53  224347096
PGW_BANKLOG                    2013-04-25 23:15:20  148955033
PGW_PRE_DEPOSIT                2013-04-26 05:31:23   25396472
PGW_ORDER_CHECK                2013-04-25 22:34:51    9959539
PGW_LOCKCARDINFO               2013-04-27 00:13:38    7765113
PGW_ORDER_CHECK_DETAIL         2013-04-25 22:45:14    7650236
PGW_CPFAILD_COLLECTION         2013-04-25 22:05:03    4617426
NAGIOS_ALERT                   2013-04-25 22:00:54    1140808
MAN_CHANNELSER_DAYSTAT         2013-04-25 22:19:28    1062011


……


202 rows selected.




对比一个其他服务器的配置：
sys@db2_terry> select window_name,repeat_interval,duration,enabled                                                                                                    
  2  from dba_scheduler_windows; 


WINDOW_NAME
------------------------------
REPEAT_INTERVAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DURATION                                                                    ENABL
--------------------------------------------------------------------------- -----
MONDAY_WINDOW
freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
+000 04:00:00                                                               TRUE


TUESDAY_WINDOW
freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
+000 04:00:00                                                               TRUE


WEDNESDAY_WINDOW
freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
+000 04:00:00                                                               TRUE


THURSDAY_WINDOW
freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
+000 04:00:00                                                               TRUE


FRIDAY_WINDOW
freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
+000 04:00:00                                                               TRUE


SATURDAY_WINDOW
freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
+000 20:00:00                                                               TRUE


SUNDAY_WINDOW
freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
+000 20:00:00                                                               TRUE


WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00                                                               FALSE


WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00                                                               FALSE




9 rows selected.


当前启用的窗口是前7个，也就是说周一到周五的维护窗口时长为4小时，周六、周日为20小时。


查看该服务器周末的负载，发现几乎没有业务。因此可以将WEEKEND_WINDOW的duration改为40小时。




修改方法：
BEGIN
dbms_scheduler.disable(name => 'WEEKEND_WINDOW');
dbms_scheduler.set_attribute(name => 'WEEKEND_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'day'));
dbms_scheduler.enable(name => 'WEEKEND_WINDOW');
END;
/




修改后确认：
sys@db1_terry> col repeat_interval for a50                                                                                                                            
sys@db1_terry> select window_name,repeat_interval,duration,enabled                                                                                                    
  2  from dba_scheduler_windows;  


WINDOW_NAME                    REPEAT_INTERVAL                                    DURATION                                                                    ENABL
------------------------------ -------------------------------------------------- --------------------------------------------------------------------------- -----
WEEKNIGHT_WINDOW               freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;bym +000 08:00:00                                                               TRUE
                               inute=0; bysecond=0


WEEKEND_WINDOW                 freq=daily;byday=SAT;byhour=0;byminute=0;bysecond= +002 00:00:00                                                               TRUE
                               0


查看负载发现统计信息收集时，大量空闲的系统资源没有利用。
可以将大表的统计信息收集放到存储过程里面定期执行，并分配合理地收集粒度等参数。
此外，还可以考虑将变化量比较大，但占用空间、数据特性变化不大的表锁定统计信息，使查询所涉及的执行计划固定下来。


=========================================================================================================


关于统计信息收集相关的视图包括：
ALL_IND_STATISTICS Optimizer statistics for all indexes on tables accessible to the user
ALL_PART_COL_STATISTICS
ALL_REPRESOLUTION_STATISTICS Statistics for conflict resolutions for replicated tables which are accessible to the user
ALL_SUBPART_COL_STATISTICS
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_STATISTICS Optimizer statistics for all tables accessible to the user


对应有DBA_族的视图。
比较常用的是ALL_(DBA_)TAB_STATISTICS：




查看哪些表的统计信息过旧了：
select * from dba_tab_statistics
where owner='TERRY'
and stale_stats='YES';  --表示统计信息过期了，需要收集


查看哪些表的统计信息被锁住了：
select * from dba_tab_statistics
where owner='TERRY'
and stattype_locked is not null; 


通过user_stats、global_stats判断统计信息是否是用户指定的、是否将分区合并统计。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1844518/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1844518/