oracle 04023,生产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/，如需转载，请注明出处，否则将追究法律责任。