Slow performance fix oracle alert log v$diag_alert_ext x$diag_alert_ext_allway2’s blog

Slow performance fix oracle alert log v$diag_alert_ext x $diag_alert_ext v$diag_alert_ext aka x$diag_alert_ext should be useful for querying the oracle alert log. Unfortunately with common conditions in the where clause, queries against it run unusably slow. This is covered in Mos doc 1684140.1 “Selects from v$diag_alert_ext run slowly with large alert logs“, but that ends with the unhelpful “Solution: There is no solution. em>”. Bug 18643828 is an (unactioned) enhancement request from 2014 to add indexes to x$diag_alert_ext. But actually there is a solution already – the below script runs fast on all databases I’ve tested it on so far — 1) Initial setup, one-off. CREATE GLOBAL TEMPORARY TABLE gtt_diag_alert_ext ( host_id VARCHAR2(67) , originating_timestamp TIMESTAMP(9) WITH TIME ZONE , message_type VARCHAR2(14) , message_level VARCHAR2(9) , message_text VARCHAR2(2051) ) ON COMMIT DELETE ROWS ; — 2) Query script TRUNCATE TABLE gtt_diag_alert_ext t ; — only needed if not connecting as a new session. INSERT INTO gtt_diag_alert_ext t ( t.host_id , t.originating_timestamp , t.message_type , t.message_level , t.message_text ) SELECT d.host_id , d. originating_timestamp , CASE d. message_type WHEN 1 THEN ‘Unknown’ WHEN 2 THEN ‘Incident Error’ WHEN 3 THEN ‘Error’ WHEN 4 THEN ‘Warning’ WHEN 5 THEN ‘Notification’ WHEN 6 THEN…