Tales From A Lazy Fat DBA

Hi All,

Recently, one of our custom monitoring scripts, which comprises more than 2000 lines of PL/SQL code covering all possible performance areas of the Oracle database, started behaving strangely on some 19c (19.15.0) databases. Usually, the report completes within 10-12 minutes on databases running version 12.2 and above, but it began taking approximately 30 minutes for a single run, with worst times exceeding 40 minutes. This issue seems to occur specifically on our 19c instances.

During analysis, we identified one of the underlying SQL queries in our script that queries V$DIAG_ALERT_EXT as the culprit, consuming most of the time and significantly exceeding average execution times. We utilize V$DIAG_ALERT_EXT to access the XML-formatted alert log from the Automatic Diagnostic Repository (ADR), particularly in cases where we can only access the database through integrated development environments (IDEs) like SQL Developer, and direct access to the databases is unavailable.

The SQL queries are straightforward, one of the simple one we used is focusing on capturing INCIDENT_ERROR and ERROR type codes. We have implemented filter conditions to select rows where the message_type is either 2 or 3 and where the originating_timestamp is within the last 8 hours (sysdate – 8/24 represents the current date and time minus 8 hours).

SELECT TO_CHAR(originating_timestamp,'DD/MM/YYYY HH24:MI:SS') AS time, message_text 

FROM v$diag_alert_ext 

WHERE message_type IN (2, 3) AND originating_timestamp > sysdate - 8/24 

ORDER BY RECORD_ID;

Initially, we were perplexed by the problem but were confident that it was specific to 21c databases, as all other versions where we had this script scheduled were working perfectly. After exhausting all optimization attempts and with no insights into what was happening with this specific dynamic view on this database version, we decided to engage Oracle support. They promptly confirmed that the issue was due to a known bug in Oracle 19c database – Bug 33513906, which is resolved in the 19.16.0.0.220719 (July 2022) Database Release Update (DB RU).

Oracle Database support stands out as one of the best I have worked with. They possess extensive knowledge about their products, provide comprehensive private and public documentation, and, in the presence of all diagnostic files, swiftly identify issues. Moreover, they offer both temporary and permanent fixes to problems.

Hope It Helped!
Prashant Dixit