統計資訊收集不完的解決
早上收到郵件，某庫的一節點報錯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判斷統計資訊是否是使用者指定的、是否將分割槽合併統計。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DBMS_STATS收集統計資訊的問題及解決
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- 收集全庫統計資訊
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- 收集統計資訊的簡單操作
- ORACLE 統計資訊的收集與管理Oracle
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- MySQL系統如何收集統計資訊MySql
- Nebula Graph 特性講解——RocksDB 統計資訊的收集和展示
- Oracle統計資訊自動收集Oracle
- oracle收集統計資訊job停止Oracle
- oracle統計資訊--直方圖的收集：Oracle直方圖
- 重新收集oracle表的統計資訊Oracle
- Oracle統計資訊的收集和維護Oracle
- oracle 統計資訊檢視與收集Oracle
- 6 收集資料庫統計資訊資料庫
- Fixed Objects Statistics統計資訊收集 - 2Object
- oracle 11g統計資訊收集Oracle
- EMD_MAINTENANCE 引起統計資訊收集AINaN
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- [Oracle] Oracle收集統計資訊的取樣比例Oracle
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- Oracle10g 統計資訊的自動收集Oracle
- Oracle運維指令碼-收集統計資訊Oracle運維指令碼
- 關於oracle自動收集統計資訊Oracle
- 最佳實踐：解讀GaussDB(DWS) 統計資訊自動收集方案
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- Greenplum自動統計資訊收集-暨統計資訊不準引入的broadcastmotion一例AST
- Oracle 11g手工收集表統計資訊Oracle
- Oracle 11g 統計資訊收集指令碼Oracle指令碼