Chapter01.Introduction to SQL Tuning

1 SQL 튜닝 소개

1.1 SQL 튜닝
SQL Tuning은 구체적이고 측정 가능하며 달성 가능한 목표를 충족시키기 위해 SQL문 성능을 개선하는 반복 프로세스입니다. 
SQL Tuning은 배포된 응용 프로그램의 문제점을 수정하는 것을 의미합니다. 반대로 응용 프로그램 디자인은 응용 프로그램을 
배포하기 전에 보안 및 성능 목표를 설정합니다.

1.2 SQL 튜닝의 목적
SQL문은 미리 결정되고 측정 가능한 표준에 따라 수행하지 못할 때 문제가 됩니다. 
문제를 확인한 후에는 일반적인 튜닝 세션에 다음 목표 중 하나가 있습니다.
• 사용자 반응 시간 단축: 사용자가 내용을 발표하고 응답을 받을 때까지 걸리는 시간을 줄입니다.
• 처리량 향상: 명세서에 액세스 한 모든 행을 처리하는 데 필요한 자원을 최소한으로 사용함을 의미합니다.
응답 시간 문제의 경우, 고객이 장바구니를 갱신한 후 3분 동안 중단되는 온라인 서적 판매자 응용 프로그램을 고려하십시오. 
모든 데이터베이스 호스트 CPU를 사용하는 데이터 웨어 하우스의 3차 병렬 쿼리와 대조하여 다른 쿼리 실행을 방지합니다. 
각각의 경우 사용자 응답 시간은 3분이지만 문제의 원인은 다르며 튜닝 목표도 다릅니다.
 
1.3 SQL 튜닝을 위한 전제 조건
SQL Tuning은 데이터베이스 지식을 필요로 합니다. SQL을 튜닝하는 경우 이 설명서는 
사용자가 다음과 같은 지식과 기술을 가지고 있다고 가정합니다.
• 데이터베이스 아키텍처에 대한 지식
데이터베이스 아키텍처는 관리자만의 도메인이 아닙니다. 개발자는 오라클 데이터베이스에 대해 최소한의 시간에 애플리케이션을 
개발하고자 하므로 데이터베이스 아키텍처 및 기능을 활용해야 합니다. 예를 들어, Oracle Database 동시성 제어 및 다중 읽기 일관성을 
이해하지 못하면 응용 프로그램이 데이터 무결성을 손상시키고 느리게 실행되며 확장성을 저하시킬 수 있습니다.
Oracle Database 개념은 Oracle Database의 기본적인 관계형 데이터 구조, 트랜잭션 관리, 저장 구조 및 인스턴스 아키텍처에 대해 설명합니다.
• SQL 및 PL/SQL에 대한 지식
GUI 기반 도구가 있기 때문에 SQL을 모르더라도 응용 프로그램을 작성하고 데이터베이스를 관리 할 수 있습니다. 
그러나 SQL을 모른 채로 응용 프로그램이나 데이터베이스를 튜닝하는 것은 불가능합니다. Oracle Database 개념에는 
Oracle SQL 및 PL/SQL에 대한 소개가 포함되어 있습니다. 또한 Oracle Database SQL 언어, Oracle Database PL/SQL 패키지 및 유형 참조 및 
Oracle Database PL/SQL 패키지 및 유형 참조에 대한 실무 지식이 있어야합니다.
• 데이터베이스 제공 SQL 튜닝 도구에 익숙함
데이터베이스는 성능 통계를 생성하고 이러한 통계를 해석하는 SQL 튜닝 도구를 제공합니다. 
Oracle Database 2 Day + Performance Tuning Guide는 주요 SQL 튜닝 도구를 소개합니다.
 
1.4 SQL 튜닝을 위한 작업 및 도구
예를 들어 사용자 응답 시간을 3분에서 1초 미만으로 줄이는 것과 같이 튜닝 세션의 목표를 확인한 후에는 
이 목표를 달성하는 방법이 문제가 됩니다.
 
1.4.1 SQL 튜닝 작업
튜닝 세션의 세부 사항은 당신이 사전 대응적으로 또는 반응적으로 튜닝을 하는지 여부를 포함하는 많은 요인에 
따라 다릅니다. 사전 조치 SQL 튜닝에서는 SQL Tuning Advisor를 사용하여 SQL문 수행의 향상 여부를 결정합니다. 
리 액티브 SQL 튜닝에서는 사용자가 겪은 SQL 관련 문제를 수정합니다.
사전 처리적으로든 반응적으로든 튜닝할 때 일반적인 SQL 튜닝 세션에는 다음 작업의 전부 또는 대부분이 포함됩니다.
1. 고부하 SQL 문 식별
과거 실행 기록을 검토하여 응용 프로그램 작업 부하 및 시스템 리소스의 많은 부분을 담당하는 문을 찾습니다.

2. 성과 관련 데이터 수집
옵티마이저 통계는 SQL 튜닝에 중요합니다. 이러한 통계가 없거나 더 이상 정확하지 않으면 옵티마이저는 
최상의 계획을 생성 할 수 없습니다. SQL 성능과 관련된 기타 데이터에는 명령문이 액세스한 테이블 및 뷰의 구조 및 명령문에서 
사용 가능한 모든 인덱스의 정의가 포함됩니다.
 
3. 문제의 원인 파악
일반적으로 SQL 성능 문제의 원인은 다음과 같습니다.
• 비효율적으로 설계된 SQL문
불필요한 작업을 수행하도록 SQL 문을 작성한 경우, 옵티마이저는 성능 향상을 위해 많은 작업을 수행 할 수 없습니다.
비효율적인 설계의 예
- 데카르트 조인으로 연결되는 조인 조건을 추가하는 것을 무시합니다.
- 힌트를 사용하여 조인에서 큰 테이블을 운전 테이블로 지정
UNION ALL 대신 UNION 지정
- 외부 쿼리의 모든 행에 대해 하위 쿼리 실행
• 차선책 실행 계획
 
쿼리 최적화 프로그램(최적화 프로그램)은 가장 효율적인 실행 계획을 결정하는 내부 소프트웨어입니다. 
때로 옵티마이저는 데이터베이스에서 데이터를 검색하는 수단인 차선 액세스 경로로 플랜을 선택하기도 합니다. 
예를 들어 선택성이 낮은 쿼리 조건자에 대한 계획은 인덱스가 아닌 큰 테이블에서 전체 테이블 검색을 사용할 수 있습니다. 
최적으로 수행중인 SQL문의 실행 계획을 순차적으로 수행 할 때 명령문 계획과 비교할 수 있습니다. 
이 비교는 데이터 볼륨의 변경과 같은 정보와 함께 성능 저하 원인을 식별하는데 도움이 될 수 있습니다.
• 누락된 SQL 액세스 구조
인덱스와 구체화 된 뷰와 같은 SQL 액세스 구조가 없으면 SQL 성능이 차선책이 되는 전형적인 이유입니다. 
최적의 액세스 구조 세트는 SQL 성능을 몇 배 향상시킬 수 있습니다.
• 오래된 최적화 프로그램 통계
DBMS_STATS로 수집된 통계는 자동 또는 수동 통계 유지 관리 작업이 DML로 인해 발생한 테이블 데이터의 변경 내용을 
따라갈 수 없는 경우 오래 될 수 있습니다.
테이블의 오래된 통계가 테이블 데이터를 정확하게 반영하지 않기 때문에 옵티마이저는 잘못된 정보를 기반으로 
의사 결정을 내리고 차선의 실행 계획을 생성 할 수 있습니다.
• 하드웨어 문제
차선 성능은 메모리, I/O 및 CPU 문제로 연결될 수 있습니다.

4. 문제의 범위 정의
솔루션의 범위는 문제의 범위와 일치해야 합니다. 데이터베이스 레벨의 문제점과 명령문 레벨의 문제점을 고려하십시오. 
예를 들어, 공유 풀이 너무 작아서 커서가 빨리 없어져 많은 하드 분석이 발생합니다.
초기화 매개 변수를 사용하여 공유 풀 크기를 늘리면 데이터베이스 레벨에서 문제점을 수정하고 모든 세션의 성능을 향상시킵니다.
그러나 단일 SQL 문이 유용한 색인을 사용하지 않는 경우, 전체 데이터베이스에 대한 최적화된 초기화 매개 변수를 
변경하면 전체 성능이 저하 될 수 있습니다. 단일 SQL 문에 문제가 있는 경우 적절하게 범위가 지정된 솔루션은 이 문제만 해결합니다.
 
5. 차선책으로 SQL문을 수행하기 위한 수정 조치 구현
이러한 조치는 상황에 따라 다릅니다. 예를 들어, 바인드 변수를 사용하도록 명령문을 다시 작성하여 불필요한 
하드 구문 분석을 피하면서 더 효율적으로 SQL 문을 다시 작성할 수 있습니다.
또한 equijoins를 사용하고, WHERE 절에서 함수를 제거하고, 복잡한 SQL문을 여러 개의 간단한 명령문으로 분리 할 수 있습니다.
경우에 따라 명령문을 다시 작성하지 않고 스키마 오브젝트를 재구성하여 SQL 성능을 향상시킬 수 있습니다. 
예를 들어, 새 액세스 경로를 색인화 하거나, 병합된 색인의 열 순서를 재 지정할 수 있습니다. 
테이블을 분할하거나, 파생된 값을 도입하거나, 데이터베이스 디자인을 변경할 수도 있습니다.
 
6. SQL 성능 회귀 방지
최적의 SQL 성능을 보장하려면 실행 계획이 지속적으로 최적의 성능을 제공하는지 확인하고 사용 가능한 경우 
더 나은 계획을 선택하십시오. 옵티마이저 통계, SQL 프로파일 및 SQL 계획 기준선을 사용하여 이러한 목표를 달성 할 수 있습니다.
 
1.4.2 SQL 튜닝 툴
SQL Tuning 도구는 자동화되거나 수동입니다. 이러한 맥락에서 데이터베이스 자체가 진단, 조언 또는 시정 조치를 제공 할 수 있는 경우 
도구가 자동화됩니다. 수동 도구를 사용하려면 이러한 모든 작업을 수행해야합니다.
모든 튜닝 도구는 동적 성능 뷰의 기본 도구에 의존하며, 통계 및 메트릭을 수집합니다. 모든 튜닝 도구는 데이터베이스 인스턴스가 
수집하는 동적 성능 보기, 통계, 메트릭의 기본 도구로 수집된다. 데이터베이스 자체에는 SQL문을 튜닝하는데 필요한 데이터와 메타 데이터가 들어 있습니다.
 
1.4.2.1 자동화된 SQL 튜닝 도구
Oracle Database는 SQL 튜닝과 관련된 여러 어드바이저를 제공합니다.
또한 SQL 계획 관리는 성능 회귀를 방지하고 SQL 성능을 향상시키는 데 도움이 되는 메커니즘입니다.
모든 자동 SQL 튜닝 도구는 SQL 튜닝 세트를 입력으로 사용할 수 있습니다.
SQL Tuning SET(STS)은 실행 통계 및 실행 컨텍스트와 함께 하나 이상의 SQL문을 포함하는 데이터베이스 개체입니다.
 
1.4.2.1.1 자동 데이터베이스 진단 모니터(ADDM)
ADDM은 Oracle Database에 내장된 자체 진단 소프트웨어입니다. ADDM은 성능 문제의 근본 원인을 자동으로 찾아 내고 
수정 권고 사항을 제공하고 예상되는 이점을 계량화 할 수 있습니다. ADDM은 또한 조치가 필요하지 않은 영역을 식별합니다. 
ADDM 및 기타 권고자는 데이터베이스 구성 요소에 서비스를 제공하여 통계를 수집, 유지 및 사용하는 인프라인 
AWR (Automatic Workload Repository)을 사용합니다.
ADDM은 AWR의 통계를 조사하고 분석하여 로드가 많은 SQL을 비롯한 가능한 성능 문제를 확인합니다. 
예를 들어 야간에 실행되도록 ADDM을 구성 할 수 있습니다. 아침에 최신 ADDM 보고서를 검토하여 문제의 원인과 권장되는 
수정 사항이 있는지 확인할 수 있습니다. 이 보고서는 특정 SELECT 문이 엄청난 양의 CPU를 소비하고 SQL Tuning Advisor를 실행할 것을 권장합니다.
 
1.4.2.1.2 SQL Tuning Advisor
SQL Tuning Advisor는 문제가 있는 SQL문을 식별하고 성능을 향상시키는 방법을 권장하는 내부 진단 소프트웨어입니다. 
자동 유지 보수 작업으로서 데이터베이스 유지 보수 창에서 실행될 때, SQL Tuning Advisor를 자동 SQL Tuning Advisor라고 합니다.
SQL Tuning Advisor는 하나 이상의 SQL문을 입력으로 사용하고 Automatic Tuning Optimizer를 호출하여 SQL문에 대한 튜닝을 수행합니다.
권고자는 다음 유형의 분석을 수행합니다.
• 누락되거나 오래된 통계 확인
• SQL 프로필 작성
SQL 프로파일은 SQL에 특정한 보조 정보 세트입니다. SQL 프로파일에는 자동 SQL 튜닝 중에 발견된 차선의 옵티마이저 추정에 대한 
정정 사항이 들어 있습니다. 이 정보는 카디널리티에 대한 옵티마이저 추정치를 향상시킬 수 있습니다. 카디널리티는 실행 계획의 조작에 의해 
추정되거나 실제로 반환되는 행 수 및 선택도 입니다. 이러한 향상된 예측은 옵티마이저가 더 나은 계획을 선택하도록 합니다.
• 다른 액세스 경로로 인해 성능이 크게 향상 될 수 있는지 확인합니다.
• 차선책에 부합하는 SQL문을 식별합니다.
산출물은 조언이나 권장 사항의 형태로 각 권장 사항에 대한 이론적 근거와 예상되는 이점으로 구성됩니다. 
이 권장 사항은 개체 통계 수집, 새 색인 작성, SQL 작업 및 도구의 SQL Tuning문 재구성 또는 SQL 프로필 작성과 관련됩니다.
권장 사항을 승인하여 SQL문의 튜닝을 완료하도록 선택할 수 있습니다.
 
1.4.2.1.3 SQL 액세스 관리자
SQL Access Advisor는 생성, 삭제 또는 유지할 구체화 된 뷰, 인덱스 및 구체화 된 뷰 로그를 권장하는 내부 진단 소프트웨어입니다. 
SQL Access Advisor는 실제 워크로드를 입력으로 사용하거나 Advisor가 스키마에서 가상의 작업을 파생시킬 수 있습니다. 
SQL Access Advisor는 공간 사용량과 쿼리 성능 간의 균형을 고려하여 새롭고 기존의 구체화 된 뷰와 인덱스를 가장 비용 효율적으로 
구성 할 것을 권장합니다. 또한 권고자는 파티션 작성에 대한 권장 사항을 작성합니다.
 
1.4.2.1.4 SQL 계획 관리
SQL 계획 관리는 옵티마이저가 자동으로 실행 계획을 관리하여 데이터베이스가 알려진 계획 또는 검증된 계획만 사용하도록하는 
예방 메커니즘입니다. 이 메커니즘은 각 SQL 문에 대해 하나 이상의 허용된 계획을 포함하는 SQL 계획 기준선을 작성할 수 있습니다. 
SQL 계획 관리는 기준선을 사용하여 환경 변화에 따른 계획 회귀를 방지하면서 옵티마이저가 더 나은 계획을 발견하고 사용할 수 있게 합니다.
 
1.4.2.1.5 SQL 성능 분석기
SQL 성능 분석기는 각 SQL문에 대한 성능 차이를 식별하여 SQL 작업 부하에 대한 변경 효과를 확인합니다. 
데이터베이스 업그레이드 또는 색인 추가와 같은 시스템 변경으로 인해 실행 계획이 변경되어 SQL 성능에 영향을 줄 수 있습니다. 
SQL Performance Analyzer를 사용하면 시스템 성능이 SQL 성능에 미치는 영향을 정확하게 예측할 수 있습니다. 
이 정보를 사용하여 SQL 성능이 회귀 할 때 데이터베이스를 튜닝하거나 SQL 성능이 향상 될 때 이득을 확인하고 측정 할 수 있습니다.
 
1.4.2.2 수동 SQL 튜닝 도구
경우에 따라 자동화된 도구 외에 수동 도구를 실행해야 할 수도 있습니다. 또는 자동화된 도구에 액세스하지 못할 수도 있습니다.
 
1.4.2.2.1 실행 계획
실행 계획은 SQL 수동 튜닝의 주요 진단 도구입니다. 예를 들어, 플랜을 보고 옵티마이저가 예상한 플랜을 선택하는지 여부를 판별하거나 
테이블에 인덱스를 작성하는 효과를 식별 할 수 있습니다. 여러 가지 방법으로 실행 계획을 표시 할 수 있습니다.
다음 도구가 가장 일반적으로 사용됩니다.
• 설명 계획
이 SQL문을 사용하면 옵티마이저가 실제로 명령문을 실행하지 않고 SQL문을 실행하는데 사용할 실행 계획을 볼 수 있습니다. 
Oracle Database SQL Language Reference를 참조하십시오.
• AUTOTRACE
SQL*Plus의 AUTOTRACE 명령은 실행 계획과 쿼리 성능에 대한 통계를 생성합니다. 이 명령은 디스크 읽기 및 메모리 읽기와 같은 통계를 제공합니다. 
SQL*Plus 사용자 가이드 및 참조를 참조하십시오.
• V$SQL_PLAN 및 관련 뷰
이러한 뷰에는 아직 실행중인 SQL문 및 실행 계획에 대한 정보가 공유 풀에 남아 있습니다. Oracle Database Reference를 참조하십시오. 
DBMS_XPLAN 패키지 메소드를 사용하여 EXPLAIN PLAN 명령으로 생성된 실행 계획과 V$SQL_PLAN의 쿼리를 표시 할 수 있습니다.
 
1.4.2.2.2 실시간 SQL 모니터링 및 실시간 데이터베이스 작업
Oracle Database의 Real-Time SQL Monitoring 기능을 통해 실행중인 SQL문의 성능을 모니터링 할 수 있습니다. 
기본적으로 SQL 모니터링은 명령문이 병렬로 실행될 때 또는 단일 실행에서 최소 5초의 CPU 또는 I/O 시간을 소비한 경우 자동으로 시작됩니다.
데이터베이스 작업은 최종 사용자 또는 애플리케이션 코드, 예를 들면, 일괄 작업 또는 추출, 변환 및 로드(ETL) 처리에 의해 정의된 
데이터베이스 작업의 집합이다. 데이터베이스 작업을 정의, 모니터링 및 보고 할 수 있습니다. 실시간 데이터베이스 작업은 
복합 작업을 자동으로 모니터링하는 기능을 제공합니다. 
데이터베이스는 실행이 시작되는 즉시 병렬 쿼리, DML 및 DDL문을 자동으로 모니터링합니다. 
Oracle Enterprise Manager Cloud Control(클라우드 제어)은 사용하기 쉬운 SQL 모니터링 페이지를 제공합니다. 
또는 V$SQL_MONITOR 및 V$SQL_PLAN_MONITOR 뷰를 사용하여 SQL 관련 통계를 모니터 할 수 있습니다. 
이러한 보기를 다음 보기와 함께 사용하면 모니터링중인 실행에 대한 자세한 정보를 얻을 수 있습니다.
• V$ACTIVE_SESSION_HISTORY
• V$SESSION
• V$SESSION_LONGOPS
• V$SQL
• V$SQL_PLAN
 
1.4.2.2.3 응용 프로그램 추적
SQL 추적 파일은 구문 분석 수, 실제 및 논리 읽기, 라이브러리 캐시의 누락 등 개별 SQL문의 성능 정보를 제공합니다. 
이 정보를 사용하여 SQL 성능 문제점을 진단 할 수 있습니다. DBMS_MONITOR 또는 DBMS_SESSION 패키지를 사용하여 
특정 세션에 대한 SQL 추적을 활성화 및 비활성화 할 수 있습니다. Oracle Database는 추적 메커니즘을 사용할 때 
각 서버 프로세스에 대한 추적 파일을 생성하여 추적을 구현합니다. Oracle Database는 추적 파일 분석을 위해 
다음과 같은 명령 줄 도구를 제공합니다.
• TKPROF
이 유틸리티는 SQL Trace 기능으로 생성된 추적 파일을 입력으로 받아 들인 다음 형식화된 출력 파일을 생성합니다.
• trcsess
이 유틸리티는 세션 ID, 클라이언트 ID 및 서비스 ID와 같은 기준에 따라 여러 추적 파일의 추적 출력을 통합합니다. 
trcsess가 추적 정보를 단일 출력 파일로 병합한 후에는 출력 파일을 TKPROF로 형식화 할 수 있습니다. trcsess는 
성능이나 디버깅 목적으로 특정 세션의 추적을 통합하는데 유용합니다.
엔드-투-엔드 애플리케이션 추적은 다중 계층 환경에서 성능 문제를 진단하는 프로세스를 단순화합니다. 
이러한 환경에서 중간 계층은 최종 클라이언트의 요청을 다른 데이터베이스 세션으로 라우팅하므로 데이터베이스 세션에서 
클라이언트를 추적하기가 어렵습니다. 엔드-투-엔드 애플리케이션 추적은 클라이언트 ID를 사용하여 모든 계층을 통해 
특정 최종 클라이언트를 데이터베이스로 고유하게 추적합니다.
 
1.4.2.2.4 최적화 도구 힌트
힌트는 SQL 문의 주석을 통해 옵티마이저에 전달되는 명령입니다. 힌트를 사용하면 일반적으로 최적화 프로그램에서 
자동으로 결정할 수 있습니다. 테스트 또는 개발 환경에서 힌트를 특정 액세스 경로의 성능을 테스트하는데 유용합니다. 
예를 들어, 특정 색인이 특정 조회에 대해 더 선택적임을 알 수 있습니다. 이 경우 다음 예제와 같이 힌트를 사용하여 
더 나은 실행 계획을 사용하도록 옵티마이저에게 지시 할 수 있습니다.
 
SELECT /*+ INDEX(employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;
 
1.4.3 SQL Tuning Tools에 대한 사용자 인터페이스
Cloud Control은 데이터베이스 환경의 중앙 집중식 관리를 제공하는 시스템 관리 도구입니다. Cloud Control은 
대부분의 튜닝 도구에 대한 액세스를 제공합니다. 그래픽 콘솔, Oracle Management Server, Oracle Intelligent Agent, 공통 서비스 및 
관리 도구를 결합함으로써 Cloud Control은 포괄적인 시스템 관리 플랫폼을 제공합니다. 또한 명령 행 인터페이스를 사용하여 
모든 SQL 튜닝 도구에 액세스 할 수 있습니다. 예를 들어, DBMS_ADVISOR 패키지는 SQL Tuning Advisor의 명령 줄 인터페이스입니다. 
오라클은 클라우드 컨트롤을 데이터베이스 관리 및 튜닝을 위한 최상의 인터페이스로 추천합니다. 명령 줄 인터페이스가 특정 개념이나 
작업을 잘 설명하는 경우 이 설명서에서는 명령 줄 예제를 사용합니다. 그러나 이러한 경우 튜닝 작업에는 작업과 관련된 
주요 클라우드 제어 페이지에 대한 참조가 포함됩니다.