[Oracle] dbms_space.auto_space_advisor_job_proc 프로시저에 따른 서버 부하량 증가 이슈

환경
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production



이슈
AWR 리포트를 봤을 때 특정 시간대(14~19시)에 평균 CPU 가 80% 이상으로 사용량이 떨어지지 않고 유지가 되었다.

 

SELECT X.SQL_ID
      ,X.CPU_TIME
      ,X.EXECUTIONS
      ,X.CPU_TIME_PER_EXECUTIONS
      ,D.SQL_TEXT AS SQL_FULLTEXT
  FROM (  SELECT SQL.DBID
                ,SQL.SQL_ID
                ,SUM (SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
                ,SUM (SQL.EXECUTIONS_DELTA) AS EXECUTIONS
                ,ROUND ((SUM (SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM (SQL.EXECUTIONS_DELTA),0,1,SUM (SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA,0,1,SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
            FROM DBA_HIST_SQLSTAT SQL
                ,(SELECT MIN (SNAP_ID) AS START_SNAP_ID 
                        ,MAX (SNAP_ID) AS END_SNAP_ID
                        ,MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
                        ,MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
                  FROM DBA_HIST_SNAPSHOT
                 WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('201502011100','YYYYMMDDHH24MI')
                                               AND TO_DATE('201502011200','YYYYMMDDHH24MI')
                ) SNAP                                               
           WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
        GROUP BY SQL.DBID
                ,SQL.SQL_ID
                ,SQL.PX_SERVERS_EXECS_DELTA
          HAVING SUM (SQL.EXECUTIONS_DELTA) >= 0
        ORDER BY CPU_TIME_PER_EXECUTIONS DESC) X
      ,DBA_HIST_SQLTEXT D
 WHERE D.SQL_ID = X.SQL_ID
   AND D.DBID = X.DBID;

위의 쿼리를 사용하여 해당 시간대에 사용된 쿼리 데이터를 추출했다.
(http://apollo89.com/wordpress/?p=543 사이트 참고)




해당 날짜의 00시 부터 24시까지 1시간 간격으로 데이터를 뽑아서 분석을 해 보았다.





분석

1. 평소에는 보이지 않던 쿼리가 있다.
    - call dbms_space.auto_space_advisor_job_proc ()
    - call dbms_stats.gather_database_stats_job_proc ()


2. 'call dbms_space.auto_space_advisor_job_proc ()' 프로시저를 호출한 시간은 다음과 같다.
    1) 10 ~ 11시
    2) 14 ~ 15시
    3) 18 ~ 19시
    4) 22 ~ 23시


3. 평균 CPU사용량이 80%이상이 되는 시간이
'call dbms_space.auto_space_advisor_job_proc ()' 프로시저를 2번째 호출한 시점부터 3번째 호출한 시점까지 이다.


4. 데이터를 보면 해당 시간에 특출나게 CPU 자원을 많이 차지하는 쿼리는 없다.






추측

1. dbms_space.auto_space_advisor_job_proc 프로시저에 의한 Oracle Process 의 CPU 사용량 증가와 System CPU 사용량 증가

2. Oracle 퍼포먼스 이슈(버그)
  1) Oracle 10g 에서 해당 프로시저 사용시 퍼포먼스 이슈가 있었다.
    - http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=36819
    - 10.0.2.4 버전에서 이슈가 해결되었으며
      'execute dbms_scheduler.disable('sys.auto_space_advisor_job’);’ 명령어로 스케쥴러를 비활성화 시켜 문제해결이 가능

  2) Oracle 11g 또한 해당 프로시저 사용시 과도한 redo log 가 생성되는 문제가 발견되었다.
    - http://www.dbi-services.com/index.php/blog/entry/compression-advisor-excessive-redo-log-file-generation
    - 11.2.0.2 버전에서 이슈가 해결되었으나 같은 문제가 계속 발생하여 10g에서와 같이 스케쥴러를 비활성화 시켜 문제를 해결






해결방안!?

execute dbms_scheduler.disable('sys.auto_space_advisor_job’);
명령어를 이용하여 스케줄러를 비활성화 시킨 후 상황을 모니터링 한다.





PS.
아직 해결방안대로 할 수 없는 상황이다보니 시스템 팀에서 어떻게 조치를 취할지 알아보고 나중에 서버 부하 이슈가 없어졌는지 글을 다시 업데이트 해야 할 것 같다.

===============================
2015.02.04 추가
시스템팀에서 불가피한 AWR 통계수집이 아닌 auto_space_advisor, sql_tuning_advisor 를 비활성화 한다고 통보를 받았다.
앞으로 비슷한 문제가 발생을 하는지 모니터링 예정중..



===============================
2015.03.20 추가
1. AWR 그래프 추가
2. 그래프와 같이 지속적으로 부하가 올라가는 문제가 없어졌다.