Techdata VINA 지식 공유

오라클 DB 서버의 CPU 소모의 원인...

1. CPU소모의 주원인

    - CPU소모의 주요인은 주로 잘못 짜여진 SQL로 인해 발생한다. 어떤 SQL이 CPU의 자원을 잘못 사용하여 자원을 소모하는지를 알 수 있다면, 

해당 SQL만 튜닝하여 사용하면 문제는 쉽게 해결이 될 것이다. 

그러나 이 원인이 개별적인 SQL문장이 아니고 DBMS차원에서의 문제라면 이야기는 또 달라진다. 하여 DBMS차원에서 많이 발생하는 CPU자원의 소모에 대해 다음과 같이 알아보자.

      1) Reparsing

      2) 잘못 설계된 Object

      3) Wait Event

      4) Latch Contention

      5) 읽기 일관성

 ------------------------------------------------------------------------------------

   1) 잘못 짜여진 SQL

       : CPU를 소모하는 특정 SQL을 파악하는 것이 가능하다면, 해당 SQL문이 반드시 우선적으로

         튜닝되어야 한다. 잘못 짜여진 SQL문은 CPU자원 뿐만 아니라, 기타 다른 여러 자원들의

         소모 또한 매우 많이 발생시키기 때문이다.

  2) Reparsing

      : Oracle이 SQL문을 수행할 때는 syntax

      : Oracle이 SQL문을 수행할 때는 syntax와 semantics가 적절하게 작성되었는지를 파악하는

        Parse과정을 거치게 된다.이 과정에서 많은 CPU가 소모될 수 있다. 한 번 parse되면,     

        parsing된 정보가 메모리에 사라지기 전까지는 동일한 SQL문에 대해서는 더 이상 parse

       하지 않는다. 따라서 부적절하게 메모리가 사용되면, 쓸데없는 reparsing이 많이 발생하게

       된다. 다음 절차를 통해서 reparsing이 일어나고 있는지 확인해 본다.

       - v$sysstat를 통해서 parse관련 정보를 조회

          SELECT * FROM V$SYSSTAT

          WHERE NAME IN ( 'parse time cpu','parse time elapsed','parse count(hard)',

                                     'CPU used by this session' );

         parsing에 걸리는 시간이 얼마인지를 알아볼 수 있다. Application이 parsing을 많이 할

         수록 Contention이 많이 발생하고, System이 많은 시간을 대기하게 된다. 만약에

         parse time cpu가 cpu의 많은 부분을 차지하고 있다면, 대부분의 시간을 parsing하는데

         소모하고 있다는 의미이다. 이러한 경우에는 Application이 Literal SQL을 많이 사용해서

         공유되어서 사용되지 못하고 있거나, shared pool이 잘못 구성되어 있다는 의미이다.

         자바의 경우 PrepareStatement문장을 이용하지 않아서 발생할 수 있다.

       - v$sqlarea를 조회해서 자주 Parsing되는 SQL문 찾기

          select sql_text,parse_calls, executions

          from    v$sqlarea

          order by parse_calls;

          parse call이 많이 발생하는 SQL문장을 튜닝한다. 만약 parse time CPU가 전체 CPU

          사용량보다 적은 부분을 차지 한다면, CPU가 어느 부분에서 많이 소모되는지 파악해야

          한다. 다음과 같은 절차를 통해서 이러한 부분을 파악할 수 있다.

          (1) Buffer Gets가 많이 발생하는 SQL문 찾기

               다음의 SQL을 통해서 Database Buffer를 많이 access하는 SQL문을 찾아 볼 수 있다.

               select address,

                         hash_value,

                         buffer_gets,

                         executions,

                         buffer_gets/executions "gets/exec",

                         sql_text

               from    v$sqlarea

               where  buffer_gets > 50000

               and    executions > 0

               order by buffer_gets;

               위의 문장을 통하여서 execution 횟수가 많이 발생하면서, execution당 buffer_gets가

               많이 발생하는 SQL문이 어떤 것인지 알아 볼 수 있다.

           (2) 위 쿼리를 통해서 조사되어야 할 SQL문이 어떤 SQL문인지를 확인한 후에, 위에서 구

                한 address와 Hash Value를 통해서 전체 SQL문장 조회

                select   sql_text from v$sqltext

                where   address='&address_wanted'

                and       hash_value=&hash_value

                order by piece;

                이렇게 구한 SQL문은 Explain plan등을 이용해서 실제로 이 SQL이 CPU를 많이 소모

                하는 SQL인지를 확인해 본다. 만약 Bind Variable을 사용하는 데도 부하가 크다면,

                이 SQL문은 특정 Bind Variable에 대해서만 많은 CPU를 사용하는 것은 아닌지

                확인해 본다.

          (3) 대부분의 CPU를 소모하는 session이 어떤 session인지 파악해본다.

              select  v.sid,

                        substr(s.name,1,30) "statistic",

                        v.value

              from    v$statname s, v$sesstat v

              where s.name = 'CPU used by this session'

              and    v.statistic# = s.statistic#

              and    v.value > 0

              order  by 3;

              Database에서 나타나는 CPU time은 DB가 startup한 이후에 축적되는 값이다.그래서

              특정 기간 동안 수집된 data를 통해 사용량을 구하는 스크립트나 툴을 사용해야 정확

              한 비교를 할 수 있다. CPU를 사용하는 session이 확인되면, 해당 session의

              v$session view를 이용해서 추가적인 정보를 참고한다.

          (4) 특정 session이 결정되면, SQL_TRACE를 이용해서 해당 session에서 사용한 SQL문

               들 중에서 CPU를 많이 소모하는 SQL문을 파악한 후, 다음 사항들을 파악하여 튜닝한다

               > SQL문 튜닝으로 CPU usage를 줄일 수 있는지 파악하여 조치한다.

               > Reparse가 이루어지지 않도록 application을 수정한다. 예를 들면, 자주 사용되는

                  sql문들에 대해서는 cursor를 사용해서 open과 fetch를 반복적으로 사용하여

                  parse를 줄인다.( 권장하는 방법이 아님 )

               > SESSION_CACHED_CURSORS 초기화 파라미터를 이용해서 parsing이 반복적으로

                  이루지는 것을 줄인다.( 권장하는 방법이 아님 )

               > Parse count가 적고,execute count도 적으나  SQL문이 WHERE절을 제외한 부분이

                  비슷하다면, Literal SQL문을 많이 사용하고 있는지는 않은지 파악하여 가능하면

                  Bind Variable을 사용해서 parsing을 줄일 수 있도록 한다.

      3) 잘못 설계된 object

           Scalability에 대한 설계가 잘못되었을 경우에도 CPU를 많이 소모하게 될 수 있다. 예를

           들면 너무 많은 인덱스를 사용한다던가, right-hand index문제( 특정 블록에 입력이 몰리는

           index 구조 문제 )를 일으킨다던가, 특정 block에 너무 많은 rows가 들어있다던가, 아니면

           partitioning이 적절하게 이루어지지 않은 경우가 있다. 이러한 문제 때문에 발생된 경합은

           CPU를 많이 소모하게 되고, scability가 선형적으로 증가하지 못하게 한다. 이러한 문제는

           주로 Buffer busy waits으로 발생된다.