ALERT LOG Monitoring SQL 이 느릴때 조치 방법 ( 11G 이상 )

<<링크>>

1. http://www.koreaoug.org/administration/1030  ( 11g 이상, alert 로그 SQL 확인 하기  ) 

2. https://community.oracle.com/thread/604855  ( XML Alert log )

3. High CPU for Queries on X$DBGALERTEXT (문서 ID 2056666.1) ( XML Alert Log 성능 저하 해결 )

<< 환경 구성 >>

-- ALERT LOG VIEW 생성 후, 일반 유저에게 조회 권한 부여 

create view v_$alert_log as select * from x$dbgalertext;

create public synonym v$alert_log for sys.v_$alert_log;

grant select on v$alert_log to public;

-- ALERT LOG 조회 쿼리 

set linesize 180 pagesize 2000
col RECORD_ID FOR 9999999
col ALERT_DT FOR a20
col MESSAGE_T FOR a140
set timing on
 
SELECT RECORD_ID, TO_CHAR(originating_timestamp,'YYYY/MM/DD HH24:MI:SS') AS ALERT_DT, message_text AS MESSAGE_T
FROM   V$ALERT_LOG 
-- WHERE originating_timestamp > trunc(systimestamp -  1 )
WHERE originating_timestamp > systimestamp - 1/24
AND   REGEXP_LIKE(MESSAGE_TEXT,'(ORA-)');

2018/03/15

하루 2번 개발 서버 ALERT LOG CHECK

수행 시간에 따른 WHERE 절 분기

06 시는 전날 18시 부터 현재까지

18시는 당일 06시 부터 현재까지

SELECT RECORD_ID,
       TO_CHAR(originating_timestamp, 'YYYY/MM/DD HH24:MI:SS') AS ALERT_DT,
       message_text AS MESSAGE_T
FROM V$ALERT_LOG
WHERE 1=1
AND  1 = CASE WHEN TO_CHAR(SYSDATE, 'HH24')  = '6'  AND originating_timestamp > TRUNC(SYSTIMESTAMP-1)+18/24  AND REGEXP_LIKE(MESSAGE_TEXT, '(ORA-)')  THEN 1 
              WHEN TO_CHAR(SYSDATE, 'HH24')  = '18' AND originating_timestamp > TRUNC(SYSTIMESTAMP)+6/24     AND REGEXP_LIKE(MESSAGE_TEXT, '(ORA-)')  THEN 1
     ELSE NULL 
     END  ;
 

<<ALERT LOG Monitoring SQL 이 느릴때 조치 방법>>

<<원인>>

X$DBGALERTEXT is a fixed table populated from the XML alert log file situated in the ADR location. 

When the XML alert log is very large, it will take time to access.

--> 실제 alert 파일이 아닌, XML 파일을 참조하니, XML 파일을 PURGE 하라 .

--> 11g 는 alert log 를 text file 뿐만 아니라 XML-formatted file 로도 기록합니다

-- XML 파일이 276개 존재  
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/alert> ls -al *.xml |wc -l
276
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/alert> 
 
-- 참고, adrci purge 하여도, alert log 는 purge 되지 않는다. 
 
-- 참고 alert log 확인
-- alert log 를 작으나, 성능이 느림 ( 조회 9분 29초 )
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/trace> ls -al aler*-rw-rw-rw-   1 oracle     oinstall    224909 Mar 12 16:01 alert_TG.logSQL> select count(*) from x$dbgalertext;   
 
  COUNT(*)
----------
   9083715
Elapsed: 00:09:29.79
 
-- XML Purge  
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/trace> adrci
ADRCI: Release 11.2.0.3.0 - Production on Mon Mar 12 16:02:01 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/app/oracle"
adrci> show home
ADR Homes: 
diag/rdbms/TG/TG

-- ardci Home 설정 
adrci>set HOMEPATH diag/rdbms/TG/TG
-- PURGE 단위 분 - 1440 = 1 DAY 
adrci> purge -age 1440 -type alert
 
-- 참고, instance 여러 개일 경우, 개별로 지워야함 
-- Multipl ADR 환경에서 Purge 시 오류 발생 
adrci> purge -age 1440 -type alert
DIA-48448: This command does not support multiple ADR homes
adrci>
 
adrci> exit

-- XML Purge 결과 확인 
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/alert> ls -al *xml |wc -l
1
[NEW_TG]TGSVR:/app/oracle/diag/rdbms/TG/TG/alert>
 
-- XML Purge 에 따른 성능 향상 확인 
SQL> select count(*) from x$dbgalertext;   
  COUNT(*)
----------
     33131
 
Elapsed: 00:00:00.84
SQL> 
 

<< alert log 에 임의로 에러 찍기 >>

: alert log 에 vi를 통해서 에러 등록 시, xml 파일에는 반영이 안됨으로 

 아래와 같이 oracle 패키지를 통해서 alert log 에 write 할것

SQL>DBMS_SYSTEM.KSDWRT(2,'ORA-01536 Error !!!');
PL/SQL procedure successfully completed.