SGA 튜닝사례 그리고 단상

SGA 튜닝사례 그리고 DB/SQL 튜닝역할에 대한 단상

 SGA 크기만을 늘려서 튜닝되는 모습을 보는 일은 특별한 즐거움이다.
어떤 사이트의 경우엔 SGA 크기가 극단적으로 작게 설정되어 있는 경우도 있다. 오라클 설치업체가 Default 설정으로 설치하고 그냥 가버린 경우도 있었다. 이런 경우 시스템은 거의 사용하기 어려운 지경인데, DBA 를 쓰기가 비용적으로 어려워서 개발자들만으로 개발 프로젝트를 수행한 경우에 이런 경우들을 자주 보게 된다.
심지어 제법 규모가 있는 SI 회사가 수행한 프로젝트인데도 이런 경우가 꽤 있다. DBA 를 쓰는 비용을 아끼려 하기 때문이다.

여러 프로젝트들을 지원하다 보면 DB 튜닝의 역할에 대한 모호함을 종종 만난다.
SQL 튜닝을 개발자가 해야 하느냐 DBA 가 해야 하느냐를 두고 싸우는 일들이 그런 경우이다. 그럴 때 내게 묻는다면 나는 튜너(Tuner)가 할 일이라고 대답한다. 그리고 비용을 들여 튜너를 쓰시라고 말한다.

개발자가 SQL 튜닝을 하기는 어렵다. SQL 튜닝을 위해서는 DB 안에서 꽤 많은 권한을 가져야 하는데 개발자에게는 그런 많은 DB 권한을 주지 않는다. 개발자가 SQL 튜닝 책들을 공부한 후 실제 프로젝트에서 튜닝을 한번 해 보고 싶어도 권한이 없기 때문에 어렵다.
SQL 튜닝은 DBA 적인 지식도 어느정도 알고 있어야 할 수 있다. 사실 인덱스 하나 만들기 위해서도 많은 DBA 적 지식의 뒷받침이 있어야 한다. 그래야 적절한 인덱스를 안전하게 만들 수 있다.
결론적으로, 개발자가 SQL 튜닝, 더 나아가서 DB 를 튜닝하는 것은 어렵다고 할 수 있다. 개발자가 튜너가 되려면 DB 전문교육을 받아야 하고 경험을 쌓아야 한다. 현실적으로 쉬운 일은 아닐 것이다.

그렇다면 SQL 튜닝은 DBA 가 할 수는 없는 것인가? 나의 대답은 이렇다. “할 수 있다.” 단 역시 SQL 튜닝 공부를 해야 한다.

DBA 는 개발자에 비해서 SQL 튜닝을 하기에 훨씬 좋은 조건과 기회를 가지고 있다. 우선 DB 에 대해서 최고의 권한을 가지고 있기 때문이다. 이것은 개발자가 가질 수 없는 조건이기도 하거니와 튜닝을 위해서는 거의 필수적인 조건이다.
그리고 DBA 는 SQL 튜닝에 필수적이거나 보완적인 DBA 적 지식도 가지고 있다. 각종 세그먼트(힙테이블/인덱스/클러스터/파티션/언두 등)의 구조, 테이블스페이스, SGA, AWR, 리두로그파일 등에 대한 지식들이다. 사실 DBA 가 SQL 튜닝을 하지 못하는 것은 오직 공부를 하지 않았기 때문이라고 할 수 있다.

어떤 프로젝트에서 DBA 가 (시간적 여건이 가능함에도 불구하고) SQL 튜닝을 하지 못할 때, 개발자에게 SQL 튜닝 역할을 전가하려고 하기도 한다. 그럴 때 개발자와 DBA 사이에 갈등이 생기는 것이다.
이것은 운영 프로젝트에서도 마찬가지이다. SR 에 의한 SQL 배포시 튜닝책임을 두고 DBA 와 개발자, 조직으로 보면 인프라와 업무운영 조직 간에 책임 미루기를 나는 어렵지 않게 보아왔다.

이런 모습은 매우 큰 규모의 운영 프로젝트(ITO) 에서도 볼 수 있는 현상이었다.
그 원인은 간단하다. 성능관리 전문인력, 즉 전문지식을 가진 DB/SQL 튜너를 Role 로서 두고있지 않기 때문이다.
튜너가 갖추어야 할 지식은 DBA 지식에 SQL 튜닝 지식을 더한 것이다. 두가지 지식이 모두 있어야 한다. 여기에 AWR 분석과 실시간 성능 모니터링 능력이 뒷받침된다면 더 바랄 것이 없을 것이다.
이런 고급인력은 단가가 비싸다. 그래서 잘 쓰지 않는 것이다.

이야기가 많이 샌 감이 있지만 DBA 를 쓰지 않는 세태를 말하다 보니 여기까지 왔다.
맨 앞에서 얘기했던, 극단적으로 SGA 를 작게 잡아서 성능문제가 되었던 사이트에 중급 정도의 DBA 만 있었어도 그런 일은 없었을 것이다. DBA 가 SQL 튜닝은 못했더라도 SGA 크기는 어느정도 잡아 놓았을 것이기 때문이다.
그래도 요즘엔, 프로젝트에서 비싼 튜너는 쓰지 못하더라도 중급 DBA 정도는 필수인력으로 인식하는 PM 이 많이 늘은 것 같다. 다행이라고 생각한다.

최근 SGA 를 튜닝한 이 사이트는 CPU 8 코어에 물리 메모리 64 GB 이었는데 SGA 는 sga_target = 8000 mb, pga_aggregate_target = 4000 mb 이었다. physical read 는 초당 700 회, hard parsing 은 초당 20 회 정도로 많았다.
물리 메모리는 37 gb 가 free 상태였다. 나는 현재의 Dynamic SGA 방식을 Static 방식으로 바꾸고, shared_pool_size = 2000 mb, db_cache_size = 16000 mb 로 수정했다. SGA 가 대략 2배 조금 더 커진 것이다.

결과는 좋았다. AWR 을 분석해 그린 아래의 차트는 성능이 좋아졌음을 말해준다.

hard parsing 횟수도 많이 준 것을 볼 수 있다. shared pool (library cache) 크기가 늘었기 때문일 것이다.

아래의 제니퍼 X-View 는 특정 업무의 SGA 튜닝 전후의 모습이다. physical read 가 많이 줄었기 때문에 응답시간이 많이 개선된 것으로 볼 수 있다.

이렇게 단순히 SGA 크기를 늘려서 튜닝효과를 보는 경우는 튜너로서는 즐거운 일이 아닐 수 없다. 적은 수고로 큰 효과를 본 것이기 때문이다.
하지만 이런 행운은 많이 만나지 못한다. 대부분의 튜닝결과는 치열한 SQL 튜닝의 땀에 대한 보상으로 얻어지기 때문이다.