生产awr报告出现ORA-04023错误，竟是oracle的bug

大学同学在生产awr时，遇到ORA-04023错误，awr正式内容开始之前出现大量如下信息，并重复多次
WARNING (-4023) ORA-04023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER), SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could not be validated or authorized

参考网上部分文章的说法，尝试alter system flush shared_pool;
在清理的shared pool之后，确实得到了解决，但是过一段时间，还可能会出现同样的问题


总不能每次都去flush一下shared pool吧，后来我在metalink上查到，这是一个bug
Bug 9843251 : ORA-04023 ERROR OCURRED WHEN GENERATING AWR REPORT

官方给出的解释为：

bug1、

Hdr: 9843251 11.2.0.1 RDBMS 11.2.0.1 DICTIONARY PRODID-5 PORTID-23
Abstract: ORA-4023 ERROR OCURRED WHEN GENERATING AWR REPORT *** 06/23/10 01:38 am *** *** 06/23/10 02:09 am *** *** 06/23/10 02:09 am *** (CHG: Sta->16) *** 07/15/10 07:27 am *** (CHG: Sta->10 Asg->BDUIJNEN SubComp->DICTIONARY) *** 07/15/10 07:27 am *** (ADD: Impact/Symptom->NON-INTERNAL ERROR ) *** 07/15/10 07:27 am *** The ORA-4023 error means that we could not parse the statement even when
trying 100 times. This used to be kksfbc-reparse-infinite-loop.
 
The typical reason is that timestamps for valid objects are wrong
somewhere.
 
Please do an alter view compile for:
DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME
And check if the problem is resolved.
 
Looks to be the same problem as in bug 9863554. *** 08/02/10 10:15 pm *** *** 08/02/10 10:16 pm *** (CHG: Sta->16) *** 08/02/10 11:58 pm *** (CHG: Sta->91) *** 08/02/10 11:58 pm *** Closing as could not reproduce. *** 08/03/10 12:32 am *** *** 03/28/13 10:21 am *** *** 03/02/15 06:31 am ***

bug2、

Hdr: 9863554 11.2.0.1 RDBMS 11.2.0.1 PERF TOOLS PRODID-5 PORTID-226 ORA-4023
Abstract: LIBRARY CACHE: MUTEX X EVENT AND ORA-4023 OCCURED *** 06/29/10 09:40 pm ***  *** 06/29/10 09:40 pm *** (ADD: Impact/Symptom->FEATURE UNUSABLE ) *** 06/29/10 09:40 pm ***   
  
  BUG TYPE CHOSEN
  ===============
  Supportability
  
  SubComponent: Performance Tools
  ===============================
  DETAILED PROBLEM DESCRIPTION
  ============================
  Customer encountered intermittent performance issue when generating an AWR
  report during a schedule job with high waits on 'library cache: mutex X'
  event and the AWR report error with ORA-4023.
  
  Below error in the AWR report :- 
  
  WARNING (-4023) ORA-4023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER),
  SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND
  SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could
  not be validated or authorized WARNING: Since the DB Time is less than one
  second, there was minimal foreground activity in the snapshot period. Some
  of the percentage values will be invalid.
  
  DIAGNOSTIC ANALYSIS
  ===================
  Expect no issue during AWR generation.
  
  WORKAROUND?
  ===========
  No
  
  TECHNICAL IMPACT
  ================
  Performance issue impacting the DB when generating AWR report via cronjob.
  
  RELATED ISSUES (bugs, forums, RFAs)
  ===================================
  Bug 9843251 and Bug 8992081
  
  HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
  ====================================================
  Intermittent
  
  DOES THE ISSUE REPRODUCE INTERNALLY?
  ====================================
  Never
  
  IS A TESTCASE AVAILABLE?
  ========================
  No
  
  Link to IPS Package:
  ====================
  NA
  *** 06/29/10 09:42 pm *** *** 07/22/10 07:54 am ***  *** 07/22/10 07:56 am *** *** 07/22/10 07:56 am *** (CHG: Sta->10) *** 08/10/10 01:06 am *** (CHG: Sta->16) *** 08/10/10 01:06 am *** *** 08/10/10 09:42 am *** *** 08/10/10 09:42 am *** (CHG: Sta->10) *** 08/10/10 03:05 pm *** (CHG: Sta->16) *** 08/10/10 03:05 pm *** *** 08/10/10 04:37 pm *** *** 08/10/10 04:37 pm *** (CHG: Sta->10) *** 08/10/10 10:14 pm *** (CHG: Sta->16) *** 08/10/10 10:14 pm *** *** 08/10/10 10:14 pm *** *** 08/11/10 03:28 pm *** *** 08/11/10 03:29 pm *** (CHG: Sta->10) *** 10/08/10 01:58 pm *** (CHG: Sta->33) *** 10/08/10 01:58 pm *** *** 12/14/14 06:15 pm *** *** 12/14/14 06:21 pm ***

我同学没有打补丁，通过重新编译 DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME解决了这个问题
希望遇到同样问题的人，看到了会有所帮助。

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

转载于:http://blog.itpub.net/30202921/viewspace-1695708/