with oneday as (select /*+ materialize */ * from v$diag_alert_ext where ORIGINATING_TIMESTAMP > systimestamp - 1) select to_char(ORIGINATING_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')|| ' ' || message_text from oneday where ORIGINATING_TIMESTAMP > systimestamp - 301/(24 * 60 * 60) and message_text like '%ORA-%' and message_text not like '%result of ORA-609%' and message_text not like '%result of ORA-28%' and message_text not like '%(ORA-3136)%' and message_text not like '%ORA-01013%'
11.2.0.4.0 存在bug Bug 14368995 - Dump in memcpy() under qerfxFetch() from query on fixed view, FIX 在11.2.0.4.6.0 版本上。
workaroud 有隐患，建议打补丁（for linux 11.2.0.4.0,no aix patch in 11.2.0.4.0）或者升级- 11.2.0.4.6 (Apr 2015) Database Patch Set Update (DB PSU) (for aix and linux,)
To solve the issue, use any of below alternatives:
Upgrade to an Oracle version where the base bug is fixed:
- 12.1.0.1 (Base Release) - 11.2.0.4.6 (Apr 2015) Database Patch Set Update (DB PSU) - 11.2.0.4 Bundle Patch 13 for Exadata Database (Nov 2014) - 11.2.0.4 Patch 15 on Windows Platforms
- OR -
Apply interim patch 14368995, if available for your platform and Oracle version.
To check for conflicting patches, please use the MOS Patch Planner Tool Please refer to Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
If no patch exists for your version, please contact Oracle Support for a backport request.
- OR - 规避方法：
As a work around, please execute any of the below alternatives:
Set the 10235 event to level 2, as in:
SQL> ALTER SYSTEM SET EVENT="10235 trace name context forever, level 2" SCOPE=SPFILE;
Then restart the instance. Since the patch is available, the best solution is to apply the patch.
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
SYMPTOMS
Selects from v$diag_alert_ext take a long time
Selects read the whole table whatever the where clause:
select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT WHERE ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '60' minute;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 87m9dp1xgwv44, child number 0
-------------------------------------
select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT WHERE
ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '60' minute
Plan hash value: 116576056
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU) |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$DIAG_ALERT_EXT | 5 | 5245 | 0 (0)|
---------------------------------------------------------------------------
CHANGES
none
CAUSE
The v$diag_alert_ext view is based upon the fixed table x$DIAG_ALERT_EXT which contains information from the XML-based alert log (log.xml). This log can become large and thus selects can take some time.
SOLUTION
The easiest solution is to use an inline view and making sure no merging or predicate pushing is taking place.
With the with clause and a hint a real-life monitoring, query looks like this:
with oneday as (select /*+ materialize */ * from v$diag_alert_ext where ORIGINATING_TIMESTAMP>systimestamp-1) select to_char(ORIGINATING_TIMESTAMP,’YYYY-MM-DD HH24:MI:SS’) || ‘ ‘ || message_text from oneday where ORIGINATING_TIMESTAMP>systimestamp-301/(24*60*60) and message_text like ‘%ORA-%’ and message_text not like ‘%result of ORA-609%’ and message_text not like ‘%result of ORA-28%’ and message_text not like ‘%(ORA-3136)%’ and message_text not like ‘%ORA-01013:%’;"
