CH03.오라클 성능관리 - 10.V$SQL, 11.End-To-End 성능관리,12.데이터베이스 성능 고도화 정석 해법

10.V$SQL

튜닝을 할때 가장 효율적인 방법 중 하나는 주기적으로 사용되는 상위 10%이내의 프로그램만 집중적으로 튜닝하는 것입니다. 전체 SQL을 다 튜닝할 수 없기 때문에 시스템부하가 높은 쿼리나 자주 수행되는 쿼리 등 전략적으로 접근하여야 합니다.
v$sql은 집중 튜닝이 필요한 대상 SQL을 선정하는 데 활용할 수 있는 매우 유용한 도구입니다. 그 뿐만아니라 튜닝 전후 성능 향상도를 비교할 목적으로 통계를 내는 데도 활용 할 수 있습니다.
v$sql은 라이브러리 캐시에 캐싱돼 있는 각 child커서에 대한 수행통계를 보여줍니다. 그리고 v$sqlarea는 parent 커서에 대한 수행통계를 나타내며, 많은 컬럼이 v$sql을 group by 해서 구한 값입니다. v$sql은 쿼리가 수행을 마칠 때마다 갱신되며, 오랫동안 수행되는 쿼리는 5초마다 갱신이 이루어집니다.

select
1. sql_id,child_number,sql_Text,sql_fulltext, parsing_schema_name,
2.sharable_mem,persistent_mem,runtime_mem,
3.loads,invaludations,parse_calls,executions,fetches,row_processed,
4.cpu_time,elapsed_time,
5.buffer_gets,disk_reads,sorts,
6.application_wait_time,concurrency_wait_time,cluster_wait_time,user_io_wait_time,
7.first_load_time,last_active_time
from v$sql
1. 라이브러리 캐시에 적재된 SQL 커서 자체에 대한 정보
2. SQL 커서에 의해 사용되는 메모리 사용량
3. 하드파싱 및 무효화 발생횟수, parse,execute,fetch call 발생횟수, execute 또는 fetch call 시점에 처리한 로우 건수 등
4.SQl을 수행하면서 사용된 CPU TIME과 소요시간
5.SQL을 수행하면서 발생한 논리적 블록 읽기와 디스크 읽기, 그리고 소트 발생 횟수
6.SQL 수행도중 대기 이벤트 때문에 지연이 발생한 시간
7.커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점
v$sql에 보이는 통계정보들도 다른 v$뷰처럼 누적값입니다. 따라서 그냥 보이는 대로 판단하는 것보다 sql 수행횟수로 나눈 값 즉, SQL 한번 수행당 얼만큼의 일량과 시간을 소비하는지 계산해야 의미 있는 분석이 가능합니다.

v$sql과 조인해서 추가 정보를 얻을 수 있는 유용한 뷰들이 제공되는데 그 중 v$sql_plan을 통해 실행계획을 확인하고 v$sql_plan_Statistics를 통해 각 row source별 수행 통계를 화인할 수 있습니다.
v$sql_bind_capture뷰를 조회하면 전체는 아니더라도 정해진 기간에 한번씩 샘플링한 바인드 변수 값을 확인할 수 있습니다. 더 자주 샘플링하려면 _cursor_bind_capture_interval 파라미터 값을 줄이면 됩니다.(기본 설정 값은 900초입니다.)

AWR은 v$sql을 포함해 SQl커서와 관련된 각종 수행 통계를 주기적으로 저장합니다. 스냅샷 시점에 캐시에 남아있던 커서의 수행 통계만 저장됩니다. 또한 캐시에 남아있더라도 그 방대한 양의 SQL수행통계를 스냅샷 시점별로 모두 저장할 수 없으므로 아래와 같은 기준에 따라 TOP SQL만 수집합니다.
- Parse call
- Executions
- Buffer Gets
- Disk Reads
- Elapsed Time
- CPU Time
- Wait time
- Version Count
- Sahrable Memory
이와 관련해 11g부터 colored SQL이라는 기능이 추가되었습니다. 위 기준에 의해 Top SQL에 포함되지 않더라도 사용자가 명시적으로 지정한 커서의 수행통계가 AWR에 주기적으로 수집되도록 마크하는 기능입니다.
begin
dbms_workload_repository.add_colored_sql(sql_id => ‘xxxxxxxxxxxx’)
end;
/

의 형태로 사용할 수 있습니다. Colored SQL목록은 dba_hist_colored_sql 또는 wrm$_colored_sql뷰를 통해 조회가능합니다.
단, 이기능을 사용하더라도 스냅샷 시점에 캐시에서 밀려나고 없는 SQL 정보까지 저장할 수는 없습니다.

11.End-To-End 성능관리

시스템이 점차 3-tier 이상 n-tier환경으로 새롭게 구축돼 가는 환경이어서 DB구간에 대한 분석만으로는 문제를 신속하게 해결하기 어려워졌습니다.
이런 요구사항에 발맞춰 end-to-end 방식의 애플리케이션 성능관리 툴이 많이 도입되고 있는 추세입니다. 이 툴을 사용하면 web,ap,db로 나누어 어느 구간에서 병목이 발생하는지 실시간으로 모니터링할 수 있습니다.

12. 데이터베이스 성능 고도화 정석 해법

데이터베이스도 아래와 같은 절차에 따라 문제를 해결해 나갑니다.
모니터링 자료 수집 -> 분석 진단 -> 튜닝 -> 평가

데이터베이스 성능 튜닝의 3대 핵심요소는 아래의 세가지입니다.
1.라이브러리 캐시 최적화
2.데이터베이스 call 최소화
3.I/O 효율화 및 버퍼캐시 최적화

물론 고성능 데이터베이스를 구축하는 데 있어 더 근원적인 요소는 데이터 모델 및 DB설계에 있지만, 튜닝과는 거리가 있기 때문에 제외하겠습니다.
I/O 효율화 및 버퍼캐시 최적화에는 인덱스와 조인 원리, 옵티마이저 원리를 기반으로 한 SQL이 포함됩니다.

데이터 모델 및 DB 설계 : 보는 시각에 따라 다를 수 있는데 여기서는 업무적 특성을 반영한 정규화된 데이터 모델을 바탕으로 DBMS 물리적 특성과 성능을 고려한 설계 변경, 그리고 DBMS 요소에 대한 설계를 실시하는 것을 말합니다
- 논리 데이터 모델 : DBMS 물리적 특성은 배제하고 데이터의 업무적 특성만을 반영한 논리설계. 개념모델과 논리모델로 구분하기도 함
- 물리 데이터 모델 : DBMS 물리적 틍성과 성능을 고려한 설계 변경,테이블 정의, 컬럼 정의, PK/FK 정의 등
- DB 설계 : 파티션 설계, 클러스터 및 iot구성, 기초 인덱스 설계, 뷰 설계, 시퀀스 설계 등

애플리케이션 튜닝 : lock경합, 파싱 부하, 데이터베이스 call 발생량을 최소화하고 인덱스 설계 조정 및 SQL 변경을 통해 I/O효율을 높이는 등의 튜닝 요소를 말합니다.
데이터베이스 튜닝 : 초기화 파라미터 설정, SGA/PGA 메모리 구성, Redo 파일 구성, 테이블스페이스 및 데이터파일 구성들에 대한 전략을 조정하는 것
시스템 튜닝 : OS레벨에서 CPU와 메모리 구성, I/O서브시스템 구성, 네트워크 설정같은 하드웨어적인 튜닝

데이터베이스 성능을 좌우하는 열쇠는 고품질 데이터 모델과 효과적으로 구현된 애플리케이션에 있습니다. 잘못된 데이터 모델은 비효율적인 액세스 유형을 야기합니다. 이 때문에 생기는 SQL성능 문제를 해결하려고 과도한 반정규화를 실시하게 되고, 이는 궁극적으로 데이터의 일관성과 품질 저하로 연결됩니다. 이는 다시 기존 데이터에 대한 신뢰성을 떨어뜨려 새로운 데이터 요건으로 표출되고 중복 데이터를 계속 양산하는 결과를 낳습니다.
그리고 애플리케이션에서 근본적으로 일하는 일량을 최소화해야만 합니다.

3가지 주요 튜닝 포인트입니다.

이상으로 3장의 다양한 통계정보 수집방법과 분석방법에 대해서 알아보았습니다.