Chat GPT 3.5 에게 단순한 질문을 시작해서 점점 깊이 있게 대화해 보기

구글에서 챗gpt 로그인 후 질문해 보았습니다.

Let's go ~~~~



User
52살 DBA 일을 하고 있는데 이직 하려고 한다 . 어떤 일을 해야할까 ?


ChatGPT
DBA로서의 경력과 기술을 업그레이드하거나 새로운 기술을 배워 전문성을 향상시키는 것이 도움이 될 수 있습니다. 새로운 데이터베이스 기술, 클라우드 플랫폼, 혹은 데이터 분석 및 머신 러닝 관련 기술에 대해 학습하거나 자격증 취득을 고려할 수 있습니다. 또한 현재 업계 동향을 파악하고 네트워킹을 통해 새로운 기회를 찾는 것도 좋은 방법입니다. 이직을 위해 경력과 기술을 적극적으로 발전시키는 것이 중요합니다.




User
그럼 현재 업계 동향은 어떤것 같아 ?

ChatGPT
2022년까지의 동향을 기반으로 이야기해야 하기 때문에 최신 동향을 반영하지는 못할 수 있습니다. 그러나 데이터 관련 분야에서는 클라우드 기술의 중요성이 계속해서 증가하고 있습니다. 데이터 마이그레이션, 보안 및 클라우드 기반의 데이터베이스 서비스 등에 대한 수요가 높아지고 있으며, 빅데이터, 인공지능, 머신러닝 분야에서 데이터베이스 관리자들의 역할이 점차 확장되고 있습니다. 또한 데이터 보안 및 개인정보 보호에 대한 규제가 강화되고 있어, 이에 대응하기 위한 기술과 전문성도 중요한 요소가 될 것입니다. 최신 동향을 계속 관찰하고, 이러한 분야에 대한 지식과 기술을 갖추는 것이 유리할 것입니다.




User
그럼 데이터 보안 을 위해 해야되는 것들은 어떤게 있어 ? 그리고 개인정보 보호에 대한 규제 강화를 위한 방법은 뭐가 있을까?

ChatGPT
데이터 보안을 강화하기 위해서는 다음과 같은 조치들을 고려할 수 있습니다:

암호화: 민감한 데이터를 저장 및 전송 시 암호화하여 보호합니다.
접근 제어: 데이터에 접근하는 권한을 관리하고 제어하여 불법적인 접근을 방지합니다.
모니터링 및 감시: 데이터 접근과 활동을 모니터링하여 이상 징후를 감지하고 조치를 취합니다.
정기적인 보안 갱신: 보안 솔루션과 시스템을 최신 상태로 유지하고 정기적인 보안 갱신을 진행합니다.
훈련 및 교육: 직원들에게 보안 정책과 절차를 교육하고 보안에 대한 인식을 높입니다.
개인정보 보호를 위한 규제 강화를 위해 다음과 같은 접근 방법들이 있을 수 있습니다:

법규 준수: 현지의 개인정보 보호법 및 규정을 준수하고 이를 충족시키는 정책 및 절차를 수립합니다.
데이터 익명화: 민감한 개인정보를 익명화하여 식별 불가능하게 처리합니다.
사용자 동의 및 투명성 강화: 개인정보 수집 및 사용에 대한 명시적인 동의를 얻고 정보 수집 목적을 명확히 투명하게 공개합니다.
보안 강화: 개인정보를 보호하기 위한 보안 조치를 강화하고 개인정보 침해 사고에 대비하는 방안을 마련합니다.
개인정보 관리자 지정: 개인정보 보호를 담당할 책임자를 지정하고 관리체계를 구축합니다.
이러한 조치들을 통해 데이터 보안 및 개인정보 보호에 대한 요구사항을 충족시키고 보안 강화를 할 수 있습니다.




User
데이터 접근과 활동을 모니터링하여 이상 징후를 감지 등에 사용되는 솔루션(프로그램)으로는 어떤것이 있어 ?

ChatGPT
데이터 접근과 활동을 모니터링하여 이상 징후를 감지하는 데 사용되는 몇 가지 솔루션과 프로그램이 있습니다. 몇 가지 주목할 만한 것들은 다음과 같습니다:

SIEM (보안 정보 및 이벤트 관리 시스템): SIEM은 보안 이벤트, 로그 및 데이터를 수집하여 분석하고 이상 징후를 식별하는 데 사용됩니다. Splunk, IBM QRadar, LogRhythm 등이 대표적인 SIEM 솔루션입니다.

UEBA (사용자 및 엔터티 행동 분석): 사용자와 엔터티의 행동 패턴을 분석하여 이상 징후를 탐지합니다. Exabeam, Securonix, Rapid7 등이 UEBA를 활용한 솔루션을 제공합니다.

네트워크 모니터링 도구: 네트워크 활동을 모니터링하여 이상한 네트워크 트래픽을 감지하고 보안 위협을 탐지하는데 사용됩니다. Wireshark, SolarWinds 등이 네트워크 모니터링 도구로 활용될 수 있습니다.

EDR (Endpoint Detection and Response): Endpoint 장치에서 발생하는 이벤트를 실시간으로 모니터링하고 위협을 탐지하여 대응하는 솔루션입니다. CrowdStrike, Carbon Black, SentinelOne 등이 EDR 솔루션을 제공합니다.

머신 러닝 및 AI 기반 보안 솔루션: 머신 러닝 및 인공 지능 기술을 활용하여 대량의 데이터를 분석하고 이상 징후를 자동으로 식별하는 솔루션들이 있습니다.

이러한 솔루션들은 각기 다른 기능과 장점을 가지고 있으며, 조직의 요구사항과 환경에 맞게 선택되어야 합니다.


###### 느낌점 #######
- IT 분야라 그런지 똘똘하게 대답해 준다
- 대화 흐름은 질문을 어떻게 하는가가 중요해보인다.
- 챗gpt 3.5 버전은 2022년까지 학습된 내용인듯
- 무엇보다 심심할때 잼있게 놀며 지식을 넓혀갈수 있을듯하다
- 100% 신뢰 보다는 이렇게 생각하는 구나 정도로 생각을 넓히는 자세도 필요해 보인다.

DevOps? SRE? 도대체 뭐가 다른거지??
최근에 저 스스로도 의문이지만 다른 분들로 부터 많이 받는 질문 중의 하나가,
'DevOps와 SRE의 차이가 무엇인가요?' 입니다.
DevOps vs SRE 혹은 그냥 두개의 단어만 가지고 Googling을 하면 수 많은 비교글들을 찾을 수 있긴 하지만,
각각의 정의를 나열할 뿐 정작 그래서 뭐가 다른지에 대한 실질적으로 표현한 글은 찾을수 없었던 것 같고..
자동화 (CI/CD)를 통한 개발, 배포적인 면에서는 차이가 없어 보이며 어떤 부분에서는 개발에서 진행하면 DevOps, 기존 운영에서 진행하면 SRE로 표현 하는 등 실질적인 차이점에 대하여 정리된 내용은 없는것 같아
저 스스로도 정리도 할겸 DevOps vs SRE의 차이점을 제 나름대로 재 정의해 보고자 합니다.
우선 SRE를 처음으로 정의한 Site Reliability Engineering에 따르면
"class SRE implements interface DevOps"
로 정의 하고 있습니다.
뭔가 engineer스러운 표현 이기도 하고 이써빌리티 한것 같기도 하지만 개인적으로 오히려 더 헷갈리는 표현이라고 생각 드는 부분도 있긴 합니다.
해서  Wikipedia에서 각 항목을 어떻게 정의 하는지 한번 살펴 보겠습니다.
DevOps 정의 살펴보기
DevOps is a set of practices that combines software development and IT operations.
로 정의 하고 있으며 간략히 번역하면
DevOps는 기존의 개발팀(Dev)와 운영팀 (Ops)를 결합한 'practice'이다.
라고 말씀 드릴 수 있을것 같습니다.
DevOps를 정의 할때 위 내용에 추가하여 항상 따라오는 설명이 DevOps는 단순히 개발팀과 운영팀을 조직 및 기능적으로 결함 한 것이 아닌 문화 철학, 방식 및 도구의 조합으로 표현 하고 있습니다.
무언가 글로써 설명하려니 어려운데 제 나름대로 DevOps를 실제 동작하는 방식과 결합하여 다시 표현해 보면,
CI/CD (Continuous Integration/Continuous Delivery or Deployment) 및 Automation을 통하여 기존의 개발과 운영을 (프로세스, 조직, 문화적으로) 분리하여 개발 완료 후 변경 관리, 검증, 배포 프로세스등을 통해 통제하에 상용계/운영계 환경에 적용 하고, 개발팀은 기능 개발을 통해 패키지를 배포하는 역할을(개발), 운영팀은 해당 패키지를 상용계에 적용하며 그 이후에 대한 관리를(운영) 하던 분리된 개념에서 하나의 팀, 프로세스, 문화적으로 개발 - 테스트 - 배포를 자동화 하여 좀더 agile 하게 적용하는 방법론으로 얘기 할 수 있을것 같습니다.

출처 : WIKIPEDIA https://en.wikipedia.org/wiki/DevOps_toolchain#
다르게 표현 하자면,
안정적인 서비스의 운영이라는 목표를 이루기 위해 기존에는 ‘상용계 적용 전에 다양한 절차와 검증 과정을 통하여 최대한의 안정성을 확보하는 방향’으로 진행 되었다면, 최근의 트랜드는 ‘개발 + 운영 조직, 문화, 프로세스의 결합을 통하여 문제를 빠른 수정, 배포를 통해 지속적으로 문제를 개선하여 안정성을 확보 하는 방향’으로 변화한 결과물이라고 정의 할 수 있을 것 같습니다.
SRE(Site Reliability Engineering) 정의 살펴보기
Site reliability engineering(SRE) is a set of principles and practices that incorporates aspects of software engineering and applies them to infrastructure and operations problems.
사이트 신뢰성 엔지니어링(SRE)은 IT 운영에 대한 소프트웨어 엔지니어링 접근 방식입니다. SRE 팀은 소프트웨어를 툴로 활용하여 시스템을 관리하고, 문제를 해결하고, 운영 테스크를 자동화합니다.
좀더 설명을 덧붙이면,
서비스의 신뢰성 (Service Reliability)를 측정 가능한 지표로 정의 하고 그 지표를 달성하기 위한 프로세스 및 툴을 자동화 하되, 해당 지표를 기준으로 변경 혹은 배포 주기를 조정 하는 소프트웨어 공학론 혹은 방법론으로 정의 할수 있을것 같습니다.

출처 : Squadcast https://www.squadcast.com/blog
말이 좀 복잡한데 ㅜㅜ
예전부터 사용되어 오던 Service Level Agreement (SLA) 지표를 기준으로 추가적인 현재의 운영 안정화 상태를 몇가지 추가적인 지표로 정의 하고 (Service Level Object, Service Level Index, Error budget 등) 해당 지표를 기준으로 신규 기능 개발 (Development 및 DevOps automation)에 좀더 투자 할지 아니면 안정화에 더 투자 할지를 조정 하는 방법론이라고 할 수 있을것 같습니다.
다르게 표현하면 DevOps와 유사하게 ‘개발 - 테스트 - 배포까지 자동화 (automation) 하여 agile한 개발 및 빠른 배포를 통해 빠른 문제 해결을 통해 서비스를 안정화 시키되 그 주기나 속도 혹은 안정화 정도를 측정하는 지표를 수립하여 전체적인 서비스의 안정성을 확보한다’ 정의 하고 싶습니다.
물론 SRE workbook을 전체적으로 살펴 보면 저러한 지표를 수립하기 위한 방법론, 툴, 실제 장애나 문제 발생시 대응 방안등 SRE를 단순히 몇가지 지표로만 정의 하기 보단 Reliability를 확보하기 위한 다양한 내용을 포함 하는 것 전체를 SRE로 표현하는 것이 맞을것 같습니다.
DevOps와 SRE
위 내용들을 최근에 IT업계 및 실 생활에서 뜨거운 자동화관련 아이템인 ‘자동차 자동운전 시스템’에 비유하여  DevOps와 SRE를 비교하여 설명해 보면,

자동운전시스템을 개발 하여 실제 도로에 적용 하는 부분을 DevOps 혹은 automation으로 비유 한다면,
실질적인 운전 자체는 큰 사고나 이상 없이 잘 동작 한다 하더라도 어린이 보호 구역이나 고속도로에서 동일한 속도로 차가 이동해서는 안 될 것이며 같은 유형의 도로라 하더라도 날씨, 노면의 상태, 커브의 각도, 차선의 개수 등에 따른 조정이 필요하다고 생각 되고 그 최대 제한속도를 조정하는 지표를 정의 하고 (SLO, SLI, Error budget) 그 지표에 따라 안정성 여부를 판단 및 확보하는 것을 SRE로 비유 가능 하지 않을까 합니다.

결과적으로 DevOps와 SRE를 다른 개념으로 이해하거나 상호 배척되는 프로세스로 생각하시는 분들도 있는것 같습니다.
개인적으로 생각하기엔 SRE를 적용하기 위한 기본 layer로서 automation(혹은 DevOps)가 있다고 생각되며 일반적으로 SRE만 얘기 하시는 분들은 표현만 다르게 할 뿐 DevOps 혹은 CI/CD를 기본으로한 automation을 바탕으로 Reliability의 개념을 추가하여 확장 하는 것으로 받아 들이는 것으로  정의 하는 것이 맞지 않을까 합니다.
즉, 자동화도 좋은데 자동화를 통해 빠르게 빠르게 속도만 높이면 되는 것이 아니라
어느정도의 속도가 적절한지를 판단하는
지표나 방법을 추가한 내용이 SRE라 표현 하고 싶습니다.
참고로 현재의 SpaceONE는 다양한 경험과 지식을 가지고 있는 개발팀을 바탕으로 시장에 충분히 매력이 있는 상품으로 완성해 왔고 상품 자체적으로도 여러가지 장점도 많지만 그 바탕에는 aglie한 개발 방법론을 기본으로 한 최적화된 DevOps 프로세스를 이미 갖춘 결과도 있다고 생각 됩니다.
여기에 위 말씀 드린 SRE 방법론을 도입하여 고객에게 조금 더 안정적으로 제공되는 서비스가 되도록 하는것이 저희 임무라고 생각하고 있습니다.

옵티마이저가 실행계획 만드는 과정

운영자가 시스템을 관리할 때 가장 힘든 문제점은 바로 실행계획 (Plan)의 변경이다. 일반적으로 운영을 위주로 하는 데이터베이 스 시스템에 과부하가 걸리고 업무 지연으로 인해 장애가 나타난 다면 70% 이상이 SQL 실행계획 변경으로 인한 문제점이다.

그런데 왜 SQL 실행계획의 변경이 장애를 가지고 오는지에 대해 인지하는 관리자는 많지 않다. 흔히 모니터링을 하는 관리 자들은 여러 가지 툴을 이용해 이러한 증상들을 Wait Event로 많이 접하지만, 이러한 현상이 왜 일어나는지에 대해 알지 못하 는 사람이 대다수다. 그렇다면 이러한 문제점을 인지하고 미리 막을 수는 없는 것일까

컨설팅 과정에서 필자는 SQL 실행계획이 변경되는 것에 대해 애플리케이션 프로그램에 힌트를 추가해 실행계획이 변경되지 못하도록 권고하고 있다. 그럼 이러한 문제점을 가지고 있는 오 라클 환경을 계속 지켜만 봐야 하는 것일까 그렇지 않다. 자신 이 운영하는 시스템에는 이러한 문제의 발생이 최소화되도록 예 방해야 하는 것이다. 안정적인 운영을 위해 SQL 실행계획이 변 경하는 것을 최소화하고, 빠른 대응으로써 장애를 최소화하는 방 법을 익히도록 하자.

적을 배우자, 옵티마이저

원인이 있으면 결과가 있는 법. 문제는 오라클 내부에 있는 인 공지능인 옵티마이저다. 그럼 도대체 옵티마이저가 무엇이기에 잘 운영되고 있는 시스템을 임의로 바꾸는 것인지 알아보자.

옵티마이저는 실행계획을 생성하기 위한 방법들로 Table Access 방법, Table Join 방법 등과 참고자료로 통계정보, 시스 템의 환경, 환경설정 파일 등을 활용해 최적의 실행계획을 생성 한다.

하지만 최적의 길이라고 선택한 것이 여러분들이 접하고 있는 실행계획 변경으로 인한 문제점인 것이다. 그렇다면 옵티마이저 가 어떻게 실행계획을 설정하는지 간단히 살펴보도록 하자.

A 테이블과 B 테이블이 각각 100건과 10,000건 존재하고, B 의 [ID] 컬럼에‘0001’은 10건이 매칭된다. 실행계획은 <리스트 1>의 내용과 같이 B 테이블을 선처리 테이블로 선정한 후 B 테 이블의 인덱스인 B_IDX를 액세스한 후 A 테이블을 후처리 테이 블로 선정해 A_IDX 인덱스를 활용해 Nested Loops 방식으로조인이 이뤄지도록 되어 있다.

이러한 실행계획들이 옵티마이저의 주관적인 생각이 아님을 10053 Event를 통해 알 수 있다. 오라클은 실행계획을 생성하기 위해 많은 일을 한다. 크게 두 가지 일을 진행하게 되는데, 첫 번 째는 오라클이 CBO로 넘어오면서 가장 큰 장점이자 단점이 된 Query Transformation이다.

이 부분은 SQL을 최적화시키기 위해 이뤄지는 단계로, 개발 자가 복잡하게 만들어 놓은 SQL을 옵티마이저가 인라인뷰에 통 합할 것인지, 아니면 서브쿼리 내용을 먼저 풀 것인지, 조건들을 인라인뷰에 삽입해 데이터 액세스를 줄일 것인지 등 많은 부분을 시도한다. 또한 10053 Event를 통해 Query Transformation에 대한 성공, 실패 여부를 볼 수도 있다. <리스트 2>의 내용은 10053 Event에서 Query Optimization에 관련된 내용을 첨부한 것이다(지면상 해당 트레이스 파일을 간략화했음을 미리 알려 둔다).

SINGLE TABLE ACCESS PATH 항목은 각 테이블을 접근 할 때, 최적의 데이터 액세스를 위해 해당 테이블의 인덱스 사용 유무를 결정하는 부분이다. 테이블 액세스 방법으로는 Table Scan, index Fast Full Scan, index Scan 등이 있으며 각 수행 비용(Cost)을 계산해 제일 빠른 액세스 방식을 선정하게 된다.

<리스트 2>에서는 테이블 액세스 방식을 A 테이블과 B 테이블 모두 Index Range Scan을 사용하는 것이 최적의 비용(Cost)으 로 선정됐으며, 최적의 실행계획은 Single Table Access Path에 서 선택된 액세스 방식과 <리스트 3>에 나오는 조인방식, 조인순 서를 조합해 최적의 경로를 계산하게 된다. 다음으로는 각 테이 블의 조인 순서와 조인 방법에 따른 내용을 살펴보도록 하자.

테이블은 A와 B 2개이며, 순서는 A 테이블이 먼저 선처리되 는 것과 B 테이블이 먼저 선처리되는 두 가지가 존재한다. 그리 고 조인 방법은 NestedLoop Join, Hash Join, Sort Merge Join 이 존재한다.

경우의 수는 조인 순서(두 가지), 조인 방법(세 가지)이므로 총 여섯 가지의 방식이 존재하며, 각각의 조인 순서와 조인방식에 대해 내부 계산법을 활용해 결과를 도출하게 된다.

여기에서 Best 모델은 B 테이블을 선처리 테이블로 선정하고 A 테이블을 후처리 테이블로 선정해 Nested Loops 조인을 하는 것이 최적의 수행비용(Cost)을 쓰는 것으로 나타났다. 이렇듯 옵 티마이저는 테이블의 액세스 방법, 조인 순서, 조인 방법 등을 이 용해 많은 경우의 수 중에 최적의 비용으로 결과를 도출할 수 있 도록 비교한다.

그리고 우리가 사용하는 오라클 힌트(Leading, Use_hash, Index …) 등이 이러한 실행계획을 추출하는 데 있어서, 옵티마 이저가 혼동하지 않도록 수많은 경우의 수를 줄여주고 Parsing 단계에서의 수행속도를 향상시키는 부분일 것이다.

오라클의 옵티마이저는 실행계획을 생성할 때 많은 일들을 수 행하며 최적의 실행계획을 수립하기 위해 비교 분석한다. 그리고 이러한 실행계획을 생성할 수 있도록 참고하는 부분들이 많이 존 재한다. 참고하는 부분은 통계정보, 초기화 파라미터 등이며 해 당 내용에 관해서는 다음 기회에 설명하도록 한다.

안정적인 운영을 위한 실행계획 제어(3)

지난 시간까지의 내용을 간단히 정리해보면 데이터베이스에서 SQL을 활용해 실행계획을 수립하는 주체는 옵티마이저이고, 실 행계획 수립단계에서 옵티마이저는 많은 정보(통계정보, 초기화 파라미터 등)를 이용해 최적의 실행계획을 수립한다는 것이었다.

하지만 옵티마이저가 어떤 상황에서든 최적의 실행계획을 수 립하는 것은 아니며, 잘못된 통계정보로 인한 오판을 일으킬 수 도 있다고 설명했다. 이런 잘못된 실행계획을 바로 잡는 방법으 로 보통 애플리케이션에 오라클 힌트를 추가하는 것을 권고하나, 이 방법은 운영의 중단을 가져오기도 한다(단, 일부 WEB관련 소스에서는 중단되지 않는 경우도 있음).

운영을 하다 보면 애플리케이션에 SQL튜닝을 통한 정기적 배 포를 실시하는 경우도 있고, 예상치 못한 장애로 빠른 대응을 해 야 하는 경우도 있다. 장애가 발생하면 모든 개발자 및 엔지니어 들은 문제 해결을 위한 준비상태를 확실히 갖추고 있어야 비로소 빠른 대응이 가능해진다.

1분 1초가 급박한 순간에 실행계획 변경에 따른 장애 현상이 발견된다면 거기다 애플리케이션 배포를 위한 개발자 도착까지 많은 시간이 걸린다면 개발자가 도착할 때까지 장애 상황을 지 켜봐야만 할 것인가 물론 그럴 수만은 없다. 이번 회에는 실행 계획 변경으로 인한 장애를 예방하고, 장애 상황이 발생했을 때 우회 처리하는 방안에 대해 알아보자.

SQL의 과거 히스토리를 분석하자

오라클의 SQL은 각 구문마다 Hash함수를 적용한 Hash_ Value나 SQL_ID(10g 이상) 등으로 관리되고 있다. 실행계획 또 한 Plan_Hash_Value를 통해 동일한 실행계획인지 아닌지 판별 가능하다.

그렇다면 과거와 현재의 실행계획을 비교하기 위해 과거 시점 의 데이터를 임시 테이블에 주기적으로 복사해 비교해보면 어떨 까 현재 실행계획이 올바르게 돌아가고 있는지 혹은 그렇지 않 은지 관리가 가능해질 것이다. 이러한 기능들은 현재 유료 툴에 서 많이 사용되는 방식으로 관리자가 수동으로 관리·운영하는 것이 가능하다. 자동으로는 오라클 9i 이전 버전에서 Statpack을 활용하거나 오라클 10g 이상에서 AWR을 활용해 데이터를 수집 하고 성능데이터를 추출하는 방법이 있다.

지금부터 설명하는 내용은 오라클 10g 이상부터 활용되는 AWR과 관련된 정보를 이용하는 방법임을 미리 밝혀둔다. 오라 클 8i 및 9i 버전은 Statpack 유저의 STATS$* 뷰를 활용해 비슷 한 내용을 추출해낼 수 있다.

[AWR(Automatic Workload Repository)

- 오라클 10g 이상부터 지원되는 데이터 자동 수집 기능
- MMON, MMNL 백그라운드 프로세서에 의해 데이터 수집
- SYSAUX 테이블스페이스에 저장되며, 수집 주기 및 저장 기간 설정 가능
- 저장 기간을 늘리기 위해서는 SYSAUX 테이블스페이스의 증가 필요

* 정책변경 방법 : DBMS_WORKLOAD_REPOSITORY.MODIFY
_SNAPSHOT_SETTINGS
( INTERVAL=> 30 , RETENTION => 60*24*30)
· INTERVAL : 수집 주기를 설정, 30분마다 자동 수집 설정(분 단위)
· RETENTION : 저장 기간을 설정, 30일 동안 보관 설정(분 단위)

AWR의 데이터는 기본적으로 SYSAUX 테이블스페이스 WRH$*나 WRM$* 테이블에 저장되며, DBA_HIST_* 딕셔너 리를 통해 데이터 추출이 가능하다. SQL에 대한 추세 분석이 필 요할 때는 DBA_HIST_SQLSTAT 뷰를 이용하면 된다.

DBA_HIST_SQLSTAT으로 현재 실행계획과 과거 실행계획 을 비교해 보자.

<리스트 1>의 SQL을 활용해 하루 동안 실행계획이 변경된 SQL을 찾아낼 수 있다.

MAX_ELS_TIME, MIN_ELS_TIME으로 최저 응답속도 및 최고 응답속도를 비교할 수 있으며, 이 수치의 차이가 많이 나는 것은 SQL에 힌트를 추가해 성능을 고정할 수 있다.

<리스트 1>에서 추출된 SQL 중에‘g1fyytvwhhv1w’가 문제 라면 SQL_ID를 이용한다. 각 구간별 응답속도 및 실행 횟수 등의 성능 데이터를 추출해 언제 실행계획이 변경되었는지 알 아보자.

<<리스트 2>를 실행하면 SNAP_ID 역순으로 정렬된 성능 데이 터가 추출된다.

운영자는 추출된 데이터의 SNAP_ID와 PLAN_HASH_ VALUE를 통해 어떤 SQL에 실행계획 변경이 일어났는지 알 수 있고, 현재 실행계획과 과거 실행계획 내용은 DBA_HIST _SQL_PLAN 뷰를 이용해 추출해낼 수 있다.

DBA_HIST_SQL_PLAN_에서 추출된 실행계획 정보를 이용 하면 수행속도가 좋은 실행계획을 선택할 수 있다. 장애 예방을 위해서는 애플리케이션 수정을 통한 안정적인 운영을 해야 한다 는 것을 잊지 말아야 한다.

애플리케이션 수정 없이 실행계획 변경하기

운영 중 실행계획이 변경되어 애플리케이션 재배포 시간이 길 어진다면 오라클 내부의 Stored Outline과 SQL Profile을 고려 해봐야 한다.

[Stored Outline]
- 오라클 업그레이드 등으로 환경 변화 시 주요 SQL의 플랜 변경을 고정 하기 위한 기능
- 악성 쿼리의 Outline과 최적화 쿼리의 Outline을 생성해, 애플리케이션 의 구조변경 없이 DBMS에서 플랜 고정 가능

[SQL Profile]
- 10g 이상에서 Profile에 Outline 힌트를 추가해 실행계획 생성 시 조언 을 주는 기능

특히 패키지 같은 프로그램은 이미 개발된 채로 배포되므로 수 정이 불가피한 경우가 종종 있다. 프로그램 소스가 존재하지 않 아 문제가 발생할 경우에는 앞서 설명한 두 가지 방법을 활용해 일정 부분 해결이 가능하다. ‘일정 부분 해결이 가능하다’는 말 은 앞서 설명한 방법으로도 오라클이 100% 실행계획을 고정시 킬 수는 없다는 뜻이다. 오라클은 자체적으로 힌트를 분석한 뒤 자동적으로 적절한 힌트를 사용하도록 되어 있어서 간혹 사용자 의 힌트를 무시하는 경우도 발생하기 때문이다.

지금까지 옵티마이저를 통한 오라클 운영에 있어 위험요소가 되는 실행계획을 분석하고 제어하는 방법에 대해 알아봤다. 운 영 시스템의 과거 내용을 찾아보고 미래 장애요인을 예방함으 로써 안정적인 운영에 한발 더 가까워지는 계기가 되기를 기대 해본다.

[엑시엄이 보는 DB 세상]

오라클 업그레이드 SQL 튜닝 대상 추출

최근 금융사나 관공서의 차세대 프로젝트를 살펴보면 오라클 데이터베이스의 업그레이드 튜닝이 유독 많다. 오라클 9i나 10g에서 오라클 11g로의 업그레이드가 대부분이다. 데이터베이스 업그레이드가 단순한 앱 업데이트와 다름을 이제 고객도 이식하고 있다. 타사의 프로젝트에서 들려오는 무수한 장애 소식이 데이터베이스에 대한 인식을 바꾼 것이다.

오라클 데이터베이스를 업그레이드하면 기존에 잘 수행되던 SQL 실행 계획이 변함을 알고 있을 것이다. 해당 변화가 버전이 높을수록 더 많은 혜택을 주는 것이 대부분이지만, 간혹 발생하는 악성 SQL로 인한 장애는 이러한 이점을 상쇄시킨다. 그래서인지 많은 고객들이 업그레이드 튜닝을 수행에 앞서 모든 SQL에 대한 전수 조사를 원한다.

오라클 업그레이드 튜닝 절차

오라클 업그레이드 튜닝의 핵심은 미래 시스템에서 성능에 악영향을 줄 수 있는 SQL을 선별해 튜닝하는 것이다. 그렇다면 수천 또는 수만 개가 넘는 SQL에서 어떻게 이를 선별할 수 있을까 프로젝트에 제공된 시간과 재원이 한정된 만큼 SQL 선정이 프로젝트의 성패까지 좌우할 수 있다.

● SQL 검토 대상 수집
기존 시스템에서 수행시간이 길거나 문제가 된 SQL은 차기 시스템에서도 악성으로 수행될 가능성이 높다. 그러므로 AWR이나 GV$SQL 뷰를 조회해 수행시간이 길거나 빠르지만 수행 횟수가 많은 SQL을 대상으로 튜닝 검토 대상을 선정한다. 오라클 AWR과 GV$SQL 딕셔너리 뷰를 이용해 SQL 전수 조사도 수행하자. 오라클 10g 이상 버전이라면 AWR 기능이 기본으로 제공된다. DBA가 설정한 기간(기본 7일) 동안 DBMS에서 수행된 SQL 정보가 기록돼 있는데, 기본 설정일 경우 1시간마다 수집되기 때문에 그 사이에 수행됐던 SQL 정보가 메모리에서 SWAP OUT될 수 있다. 이 경우 AWR에 해당 SQL이 없을 수 있다. 이러한 빈틈을 매우기 위해 GV$SQL 뷰에서도 SQL을 수집해야 한다. GV$SQL 뷰는 현재 메모리에 파싱된 SQL 정보를 확인할 수 있기 때문이다. 이러한 방법들을 통해 거의 사용되지 않는 SQL을 제외한 대부분의 검토 대상을 추출할 수 있다(<리스트 1> 참조). AWR을 활용한 수집 방법은 ‘DBA_HIST_SQL’ 키워드로 시작되는 시스템 테이블을 조회하면 된다.

<리스트 1> GV$SQL 뷰에서 검토 대상을 추출하는 SQL 예
SELECT
PARSING_USER_ID, /* SQL을 수행한 사용자 ID */
PARSING_SCHEMA_NAME , /* SQL을 수행한 사용자명 */
SQL_ID, /* SQL ID */
SQL_FULLTEXT /* SQL 텍스트 전체 내용 */
PLAN_HASH_VALUE, /* SQL의 실행계획 해시 값 */
CASE WHEN BIND_DATA IS NULL THEN 'N'
ELSE 'Y' END BIND_DATA /* 바인드 변수 정보 유무 */
EXECUTIONS, /* 수행 횟수 (DBMS 기동 후) */
CHILD_NUMBER /* SQL의 버전 번호 */
FROM GV_SQL
WHERE PARSING_SCHEMA_NAME NOT IN ('SYS','SYSMAN','SYSTEM','MAXGAUGE') /* 시스템 스키마 제외 */
AND COMMAND_TYPE IN ('3') /* SELECT 유형 SQL만 추출 */
/* 제외할 모듈 정보 (개발자 SQL 툴 또는 비업무성 계정이 수행한 SQL 등) */
AND MODULE NOT LIKE '%Orange for ORACLE%'
AND MODULE NOT LIKE '%sqlservr.exe%'
AND MODULE NOT LIKE '%SQL Developer%'
AND MODULE NOT LIKE '%oracle@mid01 (TNS V1-V3)%'
AND MODULE NOT LIKE '%oracle@hvocidb01ud (TNS V1-V3)%'
AND MODULE NOT LIKE '%oracle@hscerpdb (TNS V1-V3)%'
/* 제외할 SQL 텍스트 정보 */'
AND SQL_FULLTEXT NOT LIKE '%DBMS_%'
AND SQL_FULLTEXT NOT LIKE 'EXEC % ';

● 실행 계획 수집
이제 튜닝 검토 대상인 SQL을 모두 수집했다. 지금부터 할 일은 해당 SQL에 대한 실행 계획을 수립하는 것이다. 오라클 버전 업그레이드 시 SQL 실행 계획이 변경되는 대상을 선별하는 것이 목적이므로 해당 TASK는 필수 과정이 아닐 수 없다. 앞서 추출한 SQL의 (SQL_ID,PLAN_HASH_VALUE) 값을 이용해 실행 계획 정보가 담긴 테이블을 조회하면 현 시스템에서 실제 수행됐던 실행 계획을 간단히 추출할 수 있다.

<리스트 2> AWR을 이용한 SQL 실행 계획 추출
SELECT SQL_ID
, PLAN_HASH_VALUE,
, ID
, PARENT_ID
, OPERATION
, OPTION
, OBJECT_NAME
FROM DBA_HIST_SQL_PLAN
WHERE (SQL_ID, PLAN_HASH_VALUE) IN (SELECT SQL_ID, PLAN_HASH_VALUE FROM 튜닝검토대상);

<리스트 3> PL/SQL을 이용한 미래 시스템에서의 실행 계획 수집
SQL> CREATE TABLE PLAN_EXEC_ERR ( SQL_ID VARCHAR2(100), ERROR_TEXT VARCHAR2(1000));
DECLARE
v_schema VHARCHAR2(32);
v_sql_text CLOB;
v_commit VARCHAR2(32);
v_sql_err VARCHAR2(100);
CURSOR c_sql IS
SELECT SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME
FROM 튜닝검토대상SQL;
BEGIN
v_commit := 'COMMIT';
FOR r_sql IN c_sql LOOP
BEGIN
v_schema := 'ALTER SESSION SET CURRENT_SCHEMA = '||r_sql.PARSING_SCHEMA_NAME;
v_sql_text := 'EXPLAIN PLAN SET STATEMENT_ID= '''|| r_sql.SQL_ID
|| ''' INTO PLAN_TABLE_TUN FOR ' || r_sql.SQL_TEXT;
v_sql_err := 'INSERT INTO KHCHOI.ERR_SQL(sql_id, sql_err) VALUES(''' || r_sql.SQL_ID|| '''';

EXECUTE IMMEDIATE v_schema;
EXECUTE IMMEDIATE v_sql_text;
EXCEPTION
WHEN OTHERS THEN EXECUTE IMMEDIATE v_sql_err||','''||SUBSTR(SQLERRM, 11, 200)||''')';
END;
EXECUTE IMMEDIATE v_commit;
END LOOP;
END;

<리스트 4> 튜닝 검토 대상 필터링
/* 서로 다른 시스템에서 수집된 실행 계획 비교 */
SELECT A.SQL_ID as SQL_ID
, NVL(A.ID, B.ID) as 현행_실행계획_ID
, A.OPERATIONS as 현행_실행계획_내용
, NVL(B.ID, A.ID) as 미래_실행계획_ID
, B.OPERATIONS as 미래_실행계획_내용
FROM (SELECT SQL_ID, ID
, LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
FROM 현행_수집_실행계획) A
FULL OUTER JOIN
(SELECT SQL_ID, ID
, LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
FROM 미래_수집_실행계획) B
ON (A.SQL_ID = B.SQL_ID AND A.ID = B.ID)
WHERE TRIM(A.OPERATIONS) <> TRIM(B.OPERATIONS) /* 실행계획이 다름을 비교하는 부분 */
OR A.SQL_ID IS NULL
OR B.SQL_ID IS NULL ;

<리스트 2>에서 만약 메모리 상주 SQL의 실행 계획을 추출하고자 하면 DBA_HIST_SQL_PLAN의 대상인 GV$SQL_PLAN을 이용하면 된다. SQL 실행 계획을 추출했다고 아직 끝난 것은 아니다. 업그레이드된 시스템의 실행 계획도 추출이 아직 남아 있다. 하지만 해당 시스템에서는 수집된 SQL이 수행된 적이 없기 때문에 이러한 계획을 그냥 얻을 수 없다. 반드시 1회 이상 수행해 업그레이드된 시스템의 오라클 옵티마이저가 선택한 실행 계획을 생성해야 한다. 이를 위한 선행 작업으로 현행 시스템의 모든 오브젝트 및 데이터를 미래 시스템으로 이관해야 한다. 옵티마이저가 올바른 판단을 할 수 있는 강력한 무기가 바로 통계정보이므로 통계 정보도 최신으로 갱신해야 한다.

선행 작업까지 마무리됐다면 앞서 수집했던 SQL_FULLTEXT 정보를 이용해 실행 계획을 미래 시스템에서 수집하자. 일일이 수행할 필요없다. PL/SQL을 활용하면 일괄 수행이 가능하다.

● 실행 계획 비교 이제 모든 재료가 준비됐다. 마지막으로 수행할 일은 현행과 미래 시스템에서 수집된 SQL의 실행 계획 중 달라지는 SQL을 추출하는 것이다. 예외적으로 LITERAL SQL을 많이 이용하는 시스템을 제외하면 보통 10~20% 정도의 SQL 실행 계획이 변경된다. 이 중에서도 상당 수는 오라클 버전 업그레이드를 통한 수행속도 향상 혜택을 얻게 되지만 그렇지 않은 경우도 있다. 이를 대비하기 위해 1차적으로 실행 계획이 달라진 모든 SQL을 검토해야 한다. 그러면 수집된 SQL 실행 계획을 서로 비교하는 <리스트 4>를 통해 튜닝 검토 대상을 최종적으로 필터링해 보자.

SQL 성능 관리를 위한 AWR Data의 활용

성능 개선… 어떻게 무엇을

현재는 다우기술에서 DB 관리 업무를 담당하고 있지만 얼마 전 까지만 해도 여러 기업을 대상으로 데이터베이스의 성능 문제를 분석하고 개선하는 일을 해왔습니다. 보통 10일, 짧으면 하루도 못 되는 시간 동안 분석과 개선을 해야 하는 경우가 많았습니다. 주어진 시간이 많지 않기 때문에 대상 시스템에 대한 성능 개선의 효과를 높이기 위해 시스템 전체 관점에서 문제가 되는 원인을 찾을 필요가 있었습니다.
따라서 제한된 시간 안에 시스템에 가장 큰 영향을 미치는 원인을 발견하는 것이 중요한 과제였습니다. 또한 그리고 그게 왜 중요한 것인지를 담당자에게 이해시키고 그 개선 효과에 대해 전달해 해당 문제를 개선하는 것이 시스템 전체에 어떤 영향을 미칠 것인지를 알려주는 것 또한 중요한 일이었습니다.
이런 종류의 문제는 저와 같은 입장을 가진 사람들에게도 중요하지만 일반 DB 관리자 입장에서도 중요한 문제가 됩니다. 지금까지의 경험상 문제가 무엇인지 명확해지면 대부분의 문제는 해결이 됐습니다. 그 방법이 SQL Tuning이나 Server Tuning을 통한 방법이 됐던 장비 교체를 통한 방법이 됐던 업무 요건을 개선하는 방법이 됐던 말입니다. 따라서 ‘어떻게 해결할 것인가’의 측면보다는 ‘무엇을 해결해야 하는가’가 더 중요하다고 생각합니다. 물론 ‘왜 해결해야 하는가’는 더 중요한 이유가 될 수 있겠지요.
이 글에서 별도의 도구 없이 어떻게 ‘무엇을 해결해야 하는가’를 찾을 수 있는 방법을 논해보고자 합니다. 방법은 논하기 전에 관련된 일반적 접근법을 논해보도록 하겠습니다.
시스템의 성능 문제를 발견하기 위해 여러 가지 접근법이 있을 수 있습니다. 일반적으로 많이 사용하는 방법은 SQL문 위주의 접근을 하는 것입니다. 자원 사용량, 대기량 등의 지표를 통해 접근하는 방법도 있습니다. 데이터베이스에서 작업을 하는 과정에서 여러 자원들이 개입되는데 어느 자원을 많이 사용하면 관련된 대기량이 증가하고 대기량이 증가하면 성능이 저하돼 성능 문제로서 인식하게 되는 것이지요. 문제가 되는 자원 사용량, 대기량 지표가 발견되면 해당 시점에 해당 자원을 많이 사용했던 SQL문장 또는 수행 구조를 찾아 개선하기도 합니다.
SQL문장과 자원 사용량 또는 대기량과의 관계는 SQL문장이 자원 사용량 또는 대기량의 절대값을 결정하고 또 자원 사용량 또는 대기량이 SQL문장의 성능에 영향을 미치는 관계로 볼 수 있습니다. 따라서 어느 방법을 사용하던 문제의 본질에 다가갈 수 있습니다

이 방법들은 개별 자원 또는 개별 SQL문장 중심의 접근이 아닌 데이터베이스 전체 시스템의 관점에서 접근하므로 Top-Down 방식의 접근이라고 합니다.

SQL 성능 개선을 위한 AWR의 활용

활동 동인으로서의 SQL 문장, 주요 자원 사용량을 알려주는 자원 사용량 또 그에 영향을 받는 대기량의 관계를 통해 SQL문장이 문제 분석의 정점에 있다고 볼 수 있습니다. 그럼 어떤 방식으로 시스템에 많은 영향을 미치는 SQL 문장을 발견할 수 있을까요 성능 관리 목적으로 출시된 여러 Tool들도 도움이 될 수 있습니다만 이 지면을 통해 소개하고자하는 내용은 ORACLE이 제공하는 AWR(Automatic Workload Repository, 이하 AWR)를 통해 문제가 되는 SQL문장을 식별하고, 자원사용량 및 대기량 지표와의 관계를 통해 해당 SQL문장의 성능 개선이 시스템에 어느 정도의 영향을 미칠 수 있는지 예측하고 관련 이해당사자들에게 해당 문제를 인지시키고 변경 작업에 대한 설득을 하기 위한 자료를 어떻게 만들지에 대한 것입니다.
AWR은 상당히 많은 데이터베이스의 성능 지표를 수집하는데요 SQL 문장은 물론 관련된 여러 자원 사용량 및 대기량 지표를 포함하고 있습니다. AWR의 정보를 추출하는 가장 일반적인 방법은 ORACLE에 제공하는 AWR Report를 활용하는 것입니다. AWR Report에는 특정 구간에서의 여러 자원 사용량과 대기량 그리고 주요 자원을 많이 사용한 SQL문장의 정보를 제공해 주기 때문에 폭넓은 분석이 가능하며 쉽게 추출해 활용할 수 있다는 장점이 있습니다.
하지만 AWR은 분석 대상 기간의 시작과 끝 시점의 변화량에 대한 평균 값을 제시해 그 과정에 대한 정보를 얻을 수 없고 SQL문에 대한 정보도 제한된 정보를 제공해 해당 SQL문제에 대한 분석을 수행하기 위해 추가적인 분석이 더 요구된다는 것이 아쉬운 점입니다.
먼저 AWR Report가 제공하는 SQL 성능 정보를 먼저 알아보기로 하겠습니다. AWR Report에서 제공하는 SQL 성능 지표는 여러 가지 자원 사용량의 관점에서 해당 자원을 많이 사용한 상위 SQL문장을 제공합니다. 대표적인 것 몇 가지를 들자면 다음과 같습니다.

더 많은 내용이 있지만 이 정도만 설명하도록 하겠습니다. SQL문장의 성능 문제를 여러 자원의 관점에서 기술하는 것은 각 관점마다 현상과 접근 방법이 다를 수 있기 때문에 여러 관점별로 접근하는 것은 중요한 의미가 있습니다. 일반적으로 최종 소요시간인 Elapsed Time을 감소시키면 문제가 해결될 수도 있지만 Elapsed Time은 앞서 설명한 그림에서와 같이 여러 가지 이유에 의해 증가할 수 있습니다. 또한 데이터베이스가 작동하는 Server의 특정 자원 예를 들면 CPU 또는 Disk가 다른 자원에 비해 조금 더 성능 문제가 있다면 Elapsed Time을 기준으로 하기 보다는 해당 자원을 기준으로 하는 것이 더 바람직하다고 볼 수 있습니다.
여러 자원 관점에서 사용량이 큰 순서대로 정렬해서 보여주는 기법을 사용하는데 이 부분은 개선 작업의 효과성과 관련됩니다. 1개의 SQL문장을 개선했을 때 하루에 1번 수행하면서 1초도 안 걸리는 것을 개선하는 것보다는 동일한 시간이 걸리더라 100만번 수행하는 것을 개선하는 것이 성능에 더 큰 영향을 미치겠죠.
사회경제 용어 중 파레토 법칙(Pareto’s law)이 있습니다. 여러 관점에서 설명되지만 20%의 구성요소가 80%의 대상 자원을 점유한다는 의미를 가지고 있습니다. 파레토 법칙을 언급하는 이유를 아시겠지요 네. 상당히 많은 데이터베이스 시스템의 성능 문제를 분석하면 성능 문제를 유발 요소의 대부분은 SQL문장에서 비롯되고(물론 Application Design 측면으로부터 문제가 발생하기도 하지만 이 글에서 이 부분은 제외하도록 하겠습니다.) 매우 적은 SQL문장이 시스템 자원 사용량을 결정하고 있습니다. 그리고 많은 경우 20%가 아닌 5%, 1% 또는 그 이하의 문장이 이런 종류의 문제를 유발합니다.
단기간에 시스템 전체의 문제를 해결하기 위해 우리가 관심을 가져야할 대상은 1% 또는 그 이하에 해당하는 SQL문장 정도입니다. 하지만 어떤 경우는 전체 시스템 자원 사용량과는 관계없이 해당 SQL 문장의 성능이 중요한 경우도 있습니다. 결산 작업과 같이 수행 빈도는 낮지만 후속작업 수행을 위해 꼭 필요하거나 이나 특정 부류의 인사가 사용하는 화면과 같은 경우가 될 수 있습니다. 이런 경우는 전체 시스템 성능 및 자원사용량의 관점에서는 큰 영향을 미치지 못합니다. 물론 업무 중요도라는 관점에서 분류를 한다면 파레토 법칙에 해당할 수 있습니다만 전체 시스템 자원 사용량 측면에서는 하위 80%에 포함되는 경우가 대부분입니다. 전체 시스템 관점에서는 중요하지 않지만 해당 업무 담당자 입장에서는 중요한 SQL문장이 존재하는 것이 일반적입니다.
이런 SQL문장을 어떻게 발견할 지 알아보겠습니다. 자원 사용량 측면의 상위 부분을 점유하는 SQL문장은 앞서 언급된 AWR Report의 각 자원 관점별 상위 SQL 목록을 통해 알 수 있습니다.
AWR에서 제공되는 SQL 상위 목록 사례를 살펴보겠습니다.

이 화면은 소요시간(Elapsed Time) 기준으로 정렬한 것으로 SQL Module과 SQL Text는 뺏습니다. 총 소요시간, 총 수행 회수, 1회 수행당 평균 소요시간, 총 DB Time 중 점유 비중, 해당 SQL 문 수행 시 CPU 사용 비중, IO Wait가 소요하는 비중이 나타나 있습니다. SQL ID를 Click하면 SQL Full Text를 확인할 수 있습니다. 자세히 살펴보면 첫번째 SQL문장의 소요시간이 꽤 큰 편이고 전체 시스템 기준에서도 비교적 많은 비중을 점유하고 있음을 알 수 있습니다. 또 다른 SQL문장들도 소요시간 기준에서 어느 정도 자원을 점유하는 것으로 나타나고 있습니다만 쉽게 눈에 들어오지는 않습니다.

AWR Report를 보조하기 위한 방법 소개

최근 들어 Big Data 분야가 많은 관심을 받고 있습니다. Big Data는 여러 관련 기술로 구성되는데 Big Data의 부상과 더불어 주목 받는 요소 기술이 데이터 시각화입니다. 분석 결과를 보다 직관적으로 이해할 수 있도록 표현하는 기법이라고 할 수 있는데요. 복잡한 시각화 기술을 사용하지 않더라도 약간의 챠트 기법을 동원하면 위와 같은 사실을 보다 인지하기 쉽도록 표현할 수 있습니다.

위에 표시된 SQL문장은 15개인데 15개의 SQL문장이 데이터베이스 소요시간의 약 61%를 점유하고 있음을 쉽게 알 수 있습니다. 시스템에서 사용되는 수 많은 SQL 문장들 중 사용자에게 불편을 초래하거나 시스템 전체 자원에 영향을 미치는 SQL문장은 많지 않다는 것을 알 수 있으며 이런 사실을 챠트로 표현할 때 텍스트로 표현할 때 보다 보다 쉽게 알 수 있습니다. 소요시간뿐 아니라 CPU 사용량, Disk 사용량 등 다른 관점에서 검토해 문제가 되는 일부 SQL문장의 성능을 개선하면 전체적으로 성능 개선 활동의 효과성을 높일 수 있습니다. 또 다른 사례를 볼까요

15개의 SQL문장이 데이터베이스 전체가 사용한 Disk Reads량의 약 80%를 점유하고 있습니다. 두 개의 SQL문장이 55%의 Disk Reads 자원을 사용하므로 SQL 2개의 Access Path를 개선하면 전체 Disk Reads량을 상당히 많이 절감할 수 있다는 것을 알 수 있습니다. 하지만 비율로 대상을 평가할 때는 한가지 주의할 점이 있습니다. 그 절대량을 함께 봐야 한다는 점입니다. 절대량이 크지 않으면 대상 SQL문장의 Access Path를 개선하더라도 대상 SQL문장뿐 아니라 전체 시스템의 자원 사용량 감소량도 미미해 큰 효과를 보기 어렵습니다. 사례에서 Disk 사용량의 25%를 점유한 SQL문장의 경우 Disk를 143824 Block(=1.1GB) 읽었습니다. Disk를 1.1GB 읽었다면 적은 량은 아니지만 하루 동안 사용한 총량으로서는 크지 않은 량입니다. 이 시점의 System 전체의 Physical Reads량의 추이를 볼까요

초당 사용량의 관점에서 해당 시스템의 전체 Disk Reads량은 매우 적은 편이라고 할 수 있습니다. 데이터베이스 관리자 입장에서 관리할 시스템이 많고 성능 개선 대상은 많습니다. 비율을 통한 가시성을 확보하더라도 해당 SQL문장이 사용한 절대량과 시스템 자원 사용량을 함께 고려해 중요하지 않은 것은 후 순위로 배치하는 것이 성능 개선 활동의 효과성을 높일 수 있다고 볼 수 있습니다.
성능 개선을 통해 얻을 수 있는 효과는 크게 두가지 입니다. 첫번째는 해당 SQL 또는 프로그램의 수행 소요시간 감소이고 두번째는 자원 사용량의 감소입니다. 그 중에서 주 목적은 수행 소요시간 감소라고 볼 수 있습니다. 수행 소요시간은 CPU 사용시간과 이런 저런 대기시간의 합으로 나타납니다. 수행 시간 개선을 위해 수행 시간 측면에서 가장 큰 SQL문장을 찾아 처리하는 것도 방법이지만 수행시간에 영향을 미치는 CPU 사용시간 및 대기시간이 큰 SQL문장을 찾아 성능을 개선하는 것도 방법이 될 수 있습니다.
AWR에서 제공하는 SQL 성능 관련 지표는 자원 사용량과 관련된 지표와 대기지표가 있습니다. 자원 사용량과 관련된 지표는 [표 1. AWR이 SQL 성능과 관련해 제공하는 자원 지표]에서 언급됐으므로 대기 지표를 추가적으로 알아보도록 하겠습니다.

성능 지표와 대기 지표는 앞서 [그림 1. 데이터베이스 활동 동인으로서의 SQL문장과 성능의 연관 관계]에서 살펴본 바와 같이 상호 관계를 맺고 있습니다. 그림 1의 내용을 여기서 소개한 AWR이 제공하는 성능 지표와 대기 지표간 관계를 추가해 표현해 보도록 하겠습니다.

이들의 관계를 한번 더 설명해볼까요 일반적으로 SQL Gets로 표현되는 Buffer Gets량이 높으면 CPU 사용시간이 증가하고 그에 따라 Elapsed Time이 증가합니다. SQL Reads로 표현되는 Disk Reads량이 높으면 IO Wait가 증가하고 그에 따라 SQL Elapsed Time이 증가하죠. Buffer Gets량이 높은데 Buffer Cache에 비효율적으로 접근하는 경우 내부 자원의 경합이 증가하면서 Elapsed Time이 증가할 수도 있고 RAC 환경에서 Disk 사용량이 높아 SQL Reads량이 높은 경우 CL Wait 즉, Clustered Wait가 증가해 Elapsed Time이 증가할 수도 있습니다. 자원 사용량과 관계없이 Data의 무결성 유지를 위한 Lock Wait가 발생할 경우도 Elapsed Time의 증가를 불러 일으킵니다.
예를 들어 Buffer Gets량이 높은 SQL문장의 Buffer Gets량을 감소시켜 성능 개선을 수행하는 경우가 많은데 Buffer Gets량 감소가 CPU 사용량 감소 및 관련된 I/O 경합 및 내부 자원 경합의 감소로 Elapsed Time이 감소하기 때문입니다.
모든 성능 개선 활동은 Elapsed Time을 감소시키기 위해 수행됩니다. 그런데 Elapsed Time 기준으로 SQL문장을 추출해 성능을 개선할 수도 있지만 경우에 따라 CPU의 성능이 느리거나 Disk의 성능이 느린 경우는 해당 자원을 많이 사용하거나 관련 대기 지표가 큰 SQL문장에 대해 개선할 수도 있습니다.
이런 정보들은 모두 AWR이 제공합니다. 하지만 AWR Report가 표현해 주는 정보는 일부에 그치기 때문에 SQL 성능 분석 시 아쉬운 점이 존재하기도 합니다.
관련된 비교 사례를 AWR Report와 제가 소개하는 방법으로 들어보겠습니다. 이 방법은 Excel Macro로 작성된 프로그램의 도움을 받습니다. 이 프로그램은 http://blog.daum.net/ironlegs 에 방문해서 Download 받을 수 있습니다.

SQL 성능 분석을 위한 방법론 및 사례 소개

일반적으로 CPU Time이 큰 SQL문장이 Elapsed Time도 큽니다. 그런데 다음 사례의 SQL_ID가 cgarqszyfv3rq인 SQL문장은 CPU Time과 Elapsed Time간 큰 관계가 없어 보입니다.

이 때의 AWR Report의 Elapsed Time과 관련된 정보는 위와 같이 표시되고 있습니다. (서두에 소개한 것과는 좀 다른 모습인데요. 서두에 소개한 내용은 11g의 AWR Report의 화면이고 위의 화면은 10g의 AWR Report의 화면입니다.) SQL_ID가 cgarqszyfv3rq인 문장의 Elapsed Time과 CPU Time이 다른 것과는 다르게 차이가 심하게 있음을 보여주고 있습니다. 이는 어떤 대기 작업이 많이 발생해 작업 수행이 지체됐음을 나타내는데요, AWR에서는 이미 이에 대한 정보를 가지고 있습니다. 아래의 내용을 보시면 총 Elapsed Time은 14,438.9초이고 CPU Time은 4,408.8초 입니다. 약 10,000초 정보도의 차이를 보여주는 데요 IO Wait가 2,815초, CLWait가 8,128초로 RAC 환경에서 Cluster Wait에 의한 대기 시간 때문에 전체 수행 시간이 증가했음을 보여주고 있습니다.

성능 개선 대상을 찾거나 성능 개선 수행 시 분석 대상 SQL문장이 성능 관점에서 어떤 특성을 가지고 있는지 미리 알고 대응하면 보다 쉽게 접근이 가능합니다.
SQL 성능 분석의 관점에서 AWR Report가 가지는 아쉬운 점이 또 존재합니다. SQL 문장만 제공한다는 점이지요. SQL 성능 개선을 위한 분석 과정에서 고려 해야 할 점은 SQL문장의 실행계획입니다. 물론 실행계획 분석을 통해 모든 성능 문제를 발견해 권고하기는 어렵지만 어떤 경우는 실행계획만 보고도 SQL문장이 가지는 문제를 확인할 수도 있습니다. 실행계획으로 분석이 되지 않을 경우 SQL문장을 실제로 수행하면서 10053/10046 Trace를 발생시킬 수도 있습니다. 이 때 필요한 것이 Bind 변수입니다. Literal SQL문이라면 Bind 변수가 필요 없지만 Literal SQL문장은 자체로서 성능 문제를 유발하는 경우가 많아 대부분 Bind 변수를 통해 수행하도록 구현하는 것이 일반적입니다. AWR은 Bind 변수도 저장해 관리하므로 과거 사용됐던 일부 Bind 변수를 추출하는 것이 가능합니다.
또 해당 SQL문장이 과거로부터 현재까지 어떤 성능 특성을 가지고 수행했는지를 알면 성능 개선 대상의 선정과정에서 도움을 받을 수 있고 해당 SQL문장의 수행 특성 또한 확인이 가능할 것입니다.
이런 정보를 또 다른 자료의 사례를 통해 보도록 하겠습니다. AWR Report의 정보 입니다. AWR Report에서는 해당 SQL문장과 대략적인 성능 정보 외에는 추가정보가 존재하지 않습니다.

그리고 제가 소개하는 방법으로부터 나온 자료들입니다.

SQL_ID가 dd3c9b3ny7b99인 SQL문장의 성능 특성은 다음과 같이 나왔습니다.

CPU Time과 ELAPSED Time간 차이가 매우 크게 나오 있고 그 차이의 대부분이 IO Wait에 기인하고 있음을 알 수 있습니다. 이 부분은 AWR Report를 통해서도 파악이 가능하지요. 실행계획을 살펴보겠습니다

위 실행계획에는 3가지 중요 정보가 나타나고 있습니다. 파란색 밑줄 처진 부분은 해당 실행계획이 생성된 일자와 Plan Hash Value가 나오고 있습니다. 조금 연한 사각형 안의 <>에 둘러싸인 부분은 해당 Table의 통계정보 상의 자료량, 조금 진한 사각형 안의 Index 이름 다음에 나오는 숫자는 해당 Key를 통한 Data 접근 시 평균 Data Block 수, () 안의 내용은 해당 Index 구성 컬럼이며 구성 컬럼 앞의 숫자는 Index 구성 컬럼 중 조건에 의해 적용된 Index 컬럼의 개수 입니다. 붉은 동그라미가 표시하고 있는 부분을 보면 두 개의 컬럼을 가지는 Index를 Index Skip Scan을 했음을 알 수 있습니다. SQL문장을 보지 않아도 EMP_NO를 Index를 통해 적용했음을 알 수 있습니다. Index Skip Scan은 Skip 대상 컬럼의 선택성이 좋으면 즉, 가짓수가 많으면 성능 관점에서 매우 불리해집니다. 그만큼 Skip 해야 하는 경우가 많아지기 때문이지요. 해당 Index의 Key 값에 대한 평균 Data Block 수가 1이라는 값에서 알 수 있듯이 해당 Index의 가짓수는 굉장히 크다고 할 수 있습니다. 일반적으로 EMP_NO는 아주 큰 회사가 아닌 한 어느 정도 값에서 예상됩니다. 그렇다면 SER_NO 값의 가짓수가 어느 정도일지 대충 짐작이 됩니다. 그 많은 가짓수를 가지는 컬럼을 Skip Scan하는 작업을 했으니 IO Wait가 많아지는 것도 이해가 될 수 있습니다. 이를 확인하기 위해 10046 Trace를 생성하면 확신을 가질 수 있습니다. SQL 수행을 위해 Bind 변수가 필요하고 AWR에 보관된 과거 실 수행된 Bind 변수를 활용하면 비교적 높은 신뢰성의 테스트가 가능해집니다.

Bind 변수는 두 가지 Set를 제공합니다. 수집 당시 상황에 따라 두 가지 Set의 Bind 변수 값이 동일할 수도 있고 틀릴 수도 있습니다.
SQL문장의 성능 분석 시 해당 SQL문장의 과거 성능 이력까지 함께 참조하면 더 도움이 됩니다. 분석 대상 시점에만 Bind 변수를 평소와 다르게 입력해 자원을 더 많이 소요했을 수도 있고 1회 수행당 자원 사용량은 동일하지만 해당 시점에만 더 많이 수행돼 문제가 됐을 수도 있습니다. 또는 신규 추가된 서비스에서 사용된 SQL문장일 수도 있겠죠

위의 정보는 20XX년 1월 12일과 13일의 해당 SQL문장의 수행기록입니다. AWR에서 한달 치 정보를 저장하는 상황에서 이틀 치만 기록됐으니 신규 서비스에서 사용하는 SQL문장으로 이해할 수 있습니다. 사용시간과 자원 사용량, 처리량 등의 성능 지표는 기록돼 있으나 대기지표는 일부만 표시돼 있습니다. 1월 12일에는 평균 170.9초가 소요됐으며 1월 13일에는 19.8초가 소요됐습니다. 소요시간은 1/10 수준으로 감소했지만 CPU 사용시간과 1회 사용시 Buffer Gets량인 BUFF/EXEC가 크게 변화가 없는 것으로 미루어 SQL문장의 근본적인 개선이 있었다고 보기는 어떤 대기 현상에 의해 시간이 지체됐음을 알 수 있습니다. 이 내용이 위에서 살펴본 IO Wait에 의한 대기 현상이 이었습니다.
이번엔 또 다른 접근법을 알아보도록 하겠습니다. 지금까지 언급한 내용들은 개별 SQL문장보다는 전체 시스템 관점에서 문제가 되는 SQL문장에 대한 것이었습니다. 그런데 수행횟수가 많지 않아 전체 시스템 관점에서 성능 문제가 되지 않더라도 매 실행 시마다 오래 수행되는 경우가 있습니다. Background로 수행만 되는 SQL문장이면 별 문제 없지만 빠른 응답이 요구되거나, 특정 시간까지 종료돼야 하는 경우 또는 해당 SQL문장이 수행돼야 다른 중요 SQL문장이 수행될 수 있는 경우 해당 SQL문장의 수행 성능은 업무적으로 중요한 것으로 볼 수 있습니다만 이런 종류의 SQL문장은 AWR Report나 위에서 설명한 방식으로는 검색되지 않아 성능 분석가가 이런 문제를 인지하지 못할 가능성이 있습니다. 이런 경우는 특정 자원에 대한 전체 사용량 (1회 평균 사용량X수행회수)가 아닌 1회 수행 시 평균 사용량으로 접근할 필요가 있습니다. 관리가 잘 된 시스템인 경우 앞서 소개한 파이 차트에서 Top SQL 문장이 점유하는 비중이 적어지고 해당 Top SQL에 특별한 문제가 없을 수 있습니다. 하지만 전체 사용량이 아닌 1회 수행 시 평균 사용량으로 접근할 경우 또 다른 내용을 볼 수 있습니다.

전체 시스템 관점에서 Top SQL이 점유하는 비중은 15% 정도로 작습니다. 차트가 이런식으로 출력되는 경우는 SQL 종류가 많으면서 특별히 성능 문제가 심각한 SQL문장이 존재하지 않거나 대부분의 SQL문장이 Literal SQL문장이 경우 이런 식으로 출력됩니다. 이런 경우 SQL 문장을 살펴봐도 큰 문제가 없으므로 다른 자원의 관점에서 검토하거나 바 차트로 표현된 것과 같이 1회 수행 시 자원사용량이 큰 SQL문장을 찾아보는 방법이 있습니다. 1회 수행 시 자원사용량이 큰 경우는 정상적인 프로그램 외에도 개발툴에 의한 Ad-hoc Query가 사용하는 것이 대부분인데요 SQL문장의 수행이력을 통해 위의 SQL문장은 정기적으로 수행되는 것임을 알 수 있습니다.

하루에 1~2회 정도 수행되며 1회 수행 시 약 1분 정도 소요됨을 알 수 있습니다. 만약 이 SQL문장이 사용자가 화면에서 수행해 결과를 기다리는 것이라면 해당 SQL문장의 성능 개선을 통해 시스템 자원 사용량에는 영향을 주지는 못하지만 누군가 에게는 큰 도움이 될 수도 있을 겁니다.
이런 활동을 업무 시간대를 기준으로 하거나 야간 배치 시간대를 기준으로 하거나 자원 사용량이나 Active Session 수가 높았던 시점을 중심으로 분석하는 등 관심 있는 시간대의 자원 사용량과 자원을 많이 소요한 SQL문장을 찾을 수 있습니다.

어떻게 개선할지 모른다면 넘어가시고 다른 것을 하셔도 됩니다. 성능 개선을 위해 꼭 기술적인 방법으로만 접근할 필요는 없습니다. SQL 문장 구조 변경, Hint 조정, Index 추가 등의 활동 외에도 업무 요건 변경을 통한 검색 범위 변경, 표현 정보의 제한 등의 방법으로도 성능 개선이 될 수도 있습니다. 아니면 장비를 변경해서라도 성능 개선을 할 수도 있겠죠. 또는 외부 전문가에게 의뢰하는 방법도 있습니다.
지금까지의 경험으로 성능 개선 활동은 분석 대상 구간을 변경하거나 분석 대상 자원의 관점을 변경하며 시스템 관점에서 영향이 큰 순서대로 성능 개선 활동을 수행하는 것이 바람직한 것 같습니다. 성능 개선이 잘 안되는 경우도 많이 있을 겁니다. 이런 경우 해당 SQL문장은 천천히 생각하고 다른 SQL문장을 분석하는 것이 좋은 것 같습니다. SQL문장의 성능 개선 효과와 성능 개선의 난이도는 상관관계가 없습니다. 성능 개선 효과가 큰 경우는 상대적으로 난이도가 쉽고 성능 개선 효과가 작은 경우는 상대적으로 난이도가 어려운 경우가 많습니다. 분석을 수행하며 SQL문장의 자원 사용과 대기량 간 상관 관계분석을 해보는 것도 흥미로운 일이 될 수 있을 겁니다.
정리해 보겠습니다. ‘소득 있는 곳에 세금 있다’는 말이 있죠 마찬가지로 데이터베이스 운영과 관련해 ‘자원 사용량 있는 곳에 대기량이 있다’는 말도 통용됩니다. 그리고 자원을 사용하는 주된 주체가 SQL문장이므로 SQL문장의 성능 개선을 통해 전체 자원 사용량의 감소 및 대기량의 감소 그리고 그를 통한 해당 업무 처리시간의 개선을 이룰 수 있습니다.
성능 개선 활동 자체가 불가능할 정도로 어려운 사례는 많지 않습니다. 가능한 수준에서 점진적으로 SQL 수행 성능을 개선하고 개선이 어려운 사례는 좀 더 많은 시간을 가지고 생각하고 다양한 방법으로 시험해 보면 좋은 효과를 볼 수 있으리라고 생각합니다.