Slow performance fix oracle alert log v$diag_alert_ext x$diag_alert_ext

港控/mmm° 2023-10-18 23:55 86阅读 0赞

### [Slow performance fix oracle alert log v$diag\_alert\_ext x$diag\_alert\_ext][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 comon 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.*“. 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 'Trace'
              END AS message_type
         , CASE d.message_level
             WHEN 1 THEN 'Critical'
             WHEN 2 THEN 'Severe'
             WHEN 8 THEN 'Important'
             WHEN 16 THEN 'Normal'
             END AS message_level
         , d.message_text
      FROM v$diag_alert_ext d
     WHERE d.originating_timestamp > SYSTIMESTAMP - 1
    ;
    SELECT t.originating_timestamp
         , t.host_id
         , t.message_type
         , t.message_level
         , t.message_text
      FROM gtt_diag_alert_ext t
     WHERE t.message_text LIKE '%ORA-%'
     ORDER BY t.originating_timestamp DESC
    ;

A real table could be used instead of a global temporary table, especially if you wanted to persist the results or centralise them into a single remote database. From v18.1, a private temporary table could be used instead. A pl/sql array or even simple pl/sql loop would also work. I found that ordered\_predicates and with clause materialize hints did not fix performance in my testing.

Other columns in v$diag\_alert\_ext might be useful to output also, such as component\_id and module\_id.

Be aware v$diag\_alert\_ext is undocumented and hence unsupported, it relies on automatic diagnostic repository (ADR) working correctly, which might not always be the case especially in upgraded databases with non-standard directory locations or symbolic links – so worth checking there is data in v$diag\_alert\_ext before relying on this. Parsing alert log file on OS is likely a safer option, using old style cron scripts or newer filebeat/logstash for visualisations. Obtaining the data from the central OEM repository tables is another alternative.

August 23, 2018  
Posted in [Scripts][]

*  **RobK**says:
    
    [27 August 2018 at 15:09][27 August 2018 at 15_09]
    
    Hello!
    
    A had similar problem with our monitoring using v$diag\_alert\_ext.  
    Reading your article and testing your advice I realised that select on v$diag\_alert\_ext is fast when you have a simple filter like originating\_timestamp > SYSTIMESTAMP – 1
    
    It becomes slow when adding more complex criteria such as: originating\_timestamp > systimestamp-301/(24\*60\*60)  
    or and message\_text not like ‘%(ORA-3136)%’.
    
    So 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:%’;
    
    This is fast and quick, no object needs to be created.  
    I think it is an improved version of your original idea.
    
    Thanks for the hint. I’ve been struggling with this for quite some time.  
    Cheers,  
    Robert

[Slow performance fix oracle alert log v_diag_alert_ext x_diag_alert_ext]: https://andrewfraserdba.com/2018/08/23/slow-performance-fix-oracle-alert-log-vdiag_alert_ext-xdiag_alert_ext/
[Scripts]: https://andrewfraserdba.com/category/scripts/
[27 August 2018 at 15_09]: https://andrewfraserdba.com/2018/08/23/slow-performance-fix-oracle-alert-log-vdiag_alert_ext-xdiag_alert_ext/#comment-116436