02장. SQL 튜닝 개요

2장. SQL 튜닝 개요

 

학습 목표

 

이번 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.

①   SQL 문장의 어떤 속성이 성능을 저하시키는지 설명 할 수 있다.

②   SQL을 튜닝 하는데 사용 할 수 있는 오라클 도구들을 나열 할 수 있다.

③   튜닝 작업을 나열 할 수 있다.

 

비효율적인 SQL 성능의 원인

 

SQL 문장은 다음과 같은 다양한 원인으로 인해 성능이 저하된다.

①   오래된 옵티마이저 통계 : SQL 실행 계획은 CBO(Cost-based optimizer)에 의해 생성된다.

CBO는 가장 효율적인 실행 계획을 선택하기 위해, 해당 쿼리에서 참조되는 테이블 및 인덱스들의

데이터 양과 분포에 대한 정확한 정보를 필요로 한다. 정확한 옵티마이저 통계가 없으면, CBO는

준 최적화 된 실행 계획을 유발하고 생성하기 쉽다.

②   액세스 수단 누락 : 인덱스, 구체화된 뷰, 파티션과 같은 액세스 구조의 누락은 SQL 성능을

저하시키는 가장 일반적인 원인이다. 올바른 액세스 구조 집합은 SQL 성능을 수 배 향상시킬 수 있다.

③   준 최적화 된 실행 계획의 선택 : CBO는 가끔 SQL 문장에 대하여 준 최적화 된 실행 계획을 선택하기도

한다. 이러한 현상은 비용, 카디널리티, 조건절의 선택도와 같이 SQL 문장의 일부 속성들을 잘못 예측하여 발생한다.

④   제대로 작성되지 못한 SQL : 만약 SQL 문장이 적절치 못하게 작성되었다면, 옵티마이저가 성능 향상을

위해 할 수 있는 것은 그리 많지 않다. 카르테시안 프로덕트를 유발하는 누락된 조인 조건 또는 UNION ALL을

써야 할 곳에 UNION을 사용하는 것처럼 고비용의 SQL 문장 사용은 비효율적인 SQL 작성의 예이다.

 

위와 같은 4가지 주요 원인은 성능에 급격한 영향을 미칠 수 있다.

 

참고 : 성능 저하의 추가 원인은 메모리, I/O, CPU 등과 같은 하드웨어 관련 문제와 연결 될 수도 있다.

 

비효율적인 SQL : 예제

 

그림 2.1

 

위 그린은 비효율적인 SQL 문장의 실행이 발생 될 수 있는 올바르지 못한 SQL 문장의 5가지 예 이다.

1. 이 문장은 일반적인 비즈니스 쿼리 유형이다. 이 쿼리는 각 상품의 정가가 해당 상품의 평균가격*1.15

보다 낮은 상품의 개수를 리턴한다. 이 문장은 상관관계 서브쿼리를 포함하고 있기 때문에 메인 쿼리의

모든 행에 대하여 서브쿼리가 매번 실행된다. 해당 쿼리를 다음과 같이 작성 할 수 있다.

 

SELECT COUNT(*)

FROM products p, (SELECT prod_id, AVG(unit_cost) ac FROM costs GROUP BY prod_id) c

WHERE p.prod_id = c.prod_id

AND p.prod_list_price < 1.15 * c.ac;

 

2. 이 쿼리는 조인 컬럼에 함수를 적용하여 인덱스가 사용될 수 있는 조건을 제한하였다. 가능하다면,

= 만 사용한다. 그렇지 않은 경우, 함수 기반 인덱스가 필요 할 수도 있다.

3. 이 쿼리는 암묵적 데이터 타입 변환이 수행되는 조건을 가지고 있다. ORDER_ID_CHAR 컬럼은 문자

타입이고 상수는 숫자 타입니다. 상수를 컬럼의 데이터 타입에 일치시키도록 한다.

4. 네 번째 쿼리는 조건절의 비교에서 데이터 타입을 일치시키기 위해 데이터 형 변환 함수를 사용하였다.

여기에서 문제는 TO_CHAR 함수가 상수가 아닌 컬럼에 적용된 것이다.

즉, 함수는 테이블의 모든 행에 대해서 매번 호출된다. 상수를 변환하는 것이 컬럼을 변환하는 것보다

낫다. 다음과 같이 작성한다.

 

SELECT * FROM employees

WHERE salary = TO_NUMBER(:sal);

 

5. UNION ALL 연산자 대신 UNION 연산자를 사용하면 결과 집합에서 중복된 행이 존재하지 않음을

보장해준다. 그러나, 이를 위해서는 추가적인 단계인 unique sort가 발생하여 중복된 행들을 제거하게

된다. 만약, UNION 연산자에 의해 연결된 쿼리들 간에 공통된 행이 존재하지 않음을 알고 있다면,

UNION 대신에 UNION ALL을 사용한다. 그 결과 불필요한 정렬 작업을 제거 할 수 있다.

 

성능 모니터링 솔루션

 

그림 2.2

 

Statspack, SQL 트레이스 파일, 동적 성능 뷰와 같은 이전 버전에서의 고전적인 사후 튜닝 기능에

추가하여, Oracle Database 10g에서는 데이터베이스를 모니터링하는 두 가지 새로운 방법을 소개하였다.

①   예방 혹은 사전 모니터링(Proactive monitoring)

- ADDM(Automatic Database Diagnostic Monitor) : 이 구성요소는 오라클 데이터베이스 튜닝의 최신

솔루션이다. ADDM은 오라클 데이터베이스 내의 병목지점을 자동으로 식별하고, 다른 관리성

구성요소들과 협조하여, 이러한 병목지점에 대하여 사용 가능한 옵션을 추천한다.

- Oracle Database 11g는 문제가 발견된 SQL 문장을 식별하여, 이 문장에 대해 SQL Tuning Advisor를

실행시키고 사용자의 간섭 없이 해당 문장의 튜닝 결과물인 SQL 프로필 권고사항을 구현하는 등의 SQL

튜닝 작업을 더욱더 자동화하였다. Automatic SQL Tuning은 기본적으로 매일 저녁에 실행되는

Automatic SQL Tuning 작업을 통해 AUTOTASK 프레임워크를 사용한다.

②   사후 모니터링

- 서버 발생 경보 : 오라클 데이터베이스는 문제 상황을 자동으로 탐지 할 수 있다. 감지된 문제에

대응하여, 경보 메시지를 가능한 치료 방법과 함께 사용자에게 전송한다.

- 오라클 데이터베이스는 강력하고 새로운 데이터와 성능 리포팅 기능을 가지고 있다.

Enterprise Manager는 모든 관련 데이터를 사용하는 통합된 성능 관리 콘솔을 제공 한다. 드릴 다운

방식을 사용하면, 사용자는 몇 번의 마우스 클릭으로 병목지점을 직접 식별 할 수 있다.

 

데이터베이스의 건전성과 관련하여 중요 정보를 캡처하기 위한 새로운 데이터가 소개 되었다. 예를 들면,

새로운 메모리 통계 뿐만 아니라 AWR(Automatic Workload Repository) 내에 저장되는 통계 이력 등이

있다.

참고 : Enterprise Manager 또는 여기서 언급한 도구를 사용하려면 추가 라이선스가 필요할 수도 있으며

데이터베이스 관리자로부터 특정한 권한을 제공 받아야 할 수도 있다.

 

모니터링 및 튜닝 도구 : 개요

 

그림 2.3

 

Oracle Database 10g Release 2에서는 AWR 데이터로부터 SQL 리포트를 생성 할 수 있으며

($ORACLE_HOME/rdbms/admin/arwsqrpt.sql), 이는 기본적으로 Statspack의 sqrepsql.sql과 동일하다.

 

참고

①   SPA는 SQL Performance Analyzer의 약자이다.

②   ASH는 Active Session History의 약자이다.

③   AWR은 Automatic Workload Repository의 약자이다.

④   ADDM은 Automatic Database Diagnostic Monitor의 약자이다.

 

사후 튜닝을 위한 EM 성능 페이지

 

그림 2.4

실시간으로 문제점을 진단해야 하는 경우들이 있다. 격분한 사용자가 당신을 호출하였거나 모니터에서

시스템 활동이 갑작스럽게 증가한 경우이다. EM의 성능 페이지는 AWR과 동일한 데이터를 사용하고,

ADDM을 사용하여 데이터베이스와 호스트 시스템의 운영 관련 정보를 표시하여 손쉽게 해당 문제점의

근본 원인을 신속하게 드릴 다운하여 검토 할 수 있도록 해준다.

 

튜닝 도구 : 개요

 

ADDM : 데이터베이스 인스턴스로부터 수집된 성능 데이터를 계속 분석한다.

SQL Tuning Advisor : 문제가 있는 것으로 식별된 SQL 문장을 분석한다. 이 작업은 기본적으로 자동

수행 되는 작업이지만, 성능 향상을 위한 방법을 찾기 위해 아무 때나 특정 SQL 작업 부하에 대하여

SQL Tuning Advisor를 실행 할 수도 있다.

 

SQL Access Advisor : SQL 문장을 분석하고 구체화 된 뷰, 인덱스, 구체화 된 뷰 로그, 파티션을 분석한다.

SQL Performance Analyzer : 데이터베이스 업그레이드 또는 새로운 인덱스 추가와 같은 변경사항으로

인해서 발생한 전체적인 영향을 자동으로 평가한다.

SQL Monitoring : 오라클 데이터베이스의 실시간 SQL 모니터링 기능은 사용자가 SQL 문장의 성능을

모니터링 할 수 있도록 해준다.

참고 : SQL Plan Management는 실행 계획의 변화를 제어하는데 사용 할 수도 있다. 이 주제는 이

과정에서 다루지 않는다.

 

SQL Tuning 작업 : 개요

 

많은 SQL 튜닝 작업이 정기적으로 수행되어야만 한다. WHERE절을 잘 작성하는 방법을 볼 수도 있지만,

그것은 새로운 인덱스의 구성에 따라 달라진다. 다음 목록은 반드시 수행해야 할 몇몇 중요 작업에 대한

배경 지식을 제공하며, SQL을 튜닝 할 때, 도움을 받을 수 있는 아이디어를 제공 한다.

①   부하가 많이 걸리는 SQL 문장을 식별하는 것은 사용자가 수행해야 할 가장 중요할 작업 중 하나이다.

ADDM은 이러한 특별한 작업에 적절한 이상적인 도구이다.

②   기본적으로 오라클 데이터베이스는 옵티마이저 통계를 자동으로 수집한다. 이를 위해 유지 관리 윈도우

내에서 특정 작업이 예정되어 있다.

③   운영 체제 통계는 주요 하드웨어 구성요소의 이용률 및 성능 뿐만 아니라 운영 체제 자체의 성능에

관한 정보를 제공한다.

④   인덱스를 잘 구성하면 성능에 도움이 되기도 한다. 예를 들어, 선택되지 않는 인덱스를 제거하여 DML 문장의

속도를 향상 시키거나, 인덱스에 컬럼을 추가하여 선택도를 향상시키는 것이다.

⑤   저장된 통계, 아웃라인, SQL 실행계획 기준선을 이용하면 장기간 SQL 문장의 기존 실행 계획을 유지 할 수 있다.

 

CPU 및 대기 시간 튜닝

 

그림 2.5

 

시스템을 튜닝 할 때, CPU 시간을 시스템의 대기 시간과 비교하는 것은 매우 중요하다. CPU 시간을

대기 시간과 비교하여, 얼마나 많은 응답 시간이 유용한 작업에 소비되었는지, 다른 프로세스들에 의해

발생된 대기 시간이 얼마나 되는지를 결정 할 수 있다.

 

일반적으로 CPU 시간이 대부분인 시스템은 대기 시간이 대부분인 시스템에 비해 튜닝에 대한 부담이

감소되지만, 한편으로는 잘못 작성된 SQL 문장에 의해 CPU 사용률이 높아진 경우도 존재 할 수 있다.

 

비록, CPU 시간대 대기 시간의 비율은 시스템의 부하가 증가하면 감소하는 경향이 있다. 대기 시간의

가파른 증가는 경합의 징후이므로, 확장성을 향상시켜야 한다. 하나의 노드에 CPU를 추가하거나,

클러스터에 노드를 추가하는 것은 경합이 진행되는 상황에서는 매우 제한적인 이득 밖에 제공하지

못한다. 반대로, 부하가 증가 할 때, CPU 시간의 비율이 급격이 감소하지 않는 시스템은 더욱 더 확장

될 수 있으며, 필요할 경우 CPU를 추가하거나 RAC에 인스턴스를 추가하여 큰 이득을 얻을 수 있다.

 

참고 : 만약, CPU 시간 부분이 상위 5개의 이벤트에 포함되면, AWR은 Top 5 Timed Events 섹션에서

CPU 시간을 대기 시간과 함께 표시한다.

 

애플리케이션 디자인, 구현, 구성과 확장성

적절하지 못한 애플리케이션 디자인, 구현, 구성은 확장성에 심각한 영향을 미치며, 그 결과는 다음과 같다.

①   제대로 작성 되지 못한 SQL 및 인덱스 디자인은 동일한 개수의 행을 리턴하는데 있어서 더 많은 횟수의 논리적 입출력(I/O)를 발생시킨다.

②   데이터베이스 객체가 더 이상 유지 관리 되지 않기 때문에 가용성이 감소된다.

 

그러나, 디자인이 유일한 문제는 아니다. 애플리케이션의 물리적 구현도 취약점이 될 수 있다.

①   시스템이 높은 I/O 횟수를 유발하도록 작성된 SQL 문장과 함께 운영 환경으로 이동 할 수 있다.

②   COMMIT 또는 ROLLBACK의 드문 사용은 자원에 오랜 기간 잠금을 유발시킨다.

③   운영 환경은 시험 환경에서 작성된 실행 계획과 다른 실행 계획을 사용 할 수 있다.

④   메모리를 과도하게 사용하는 애플리케이션은 잦은 메모리 할당 및 해제로 인해 메모리 단편화를

과도하게 발생 시킬 수 있다.

⑤   비효율적인 메모리 이용률은 운영 체제의 가상 메모리 서브시스템에 과도한 부담을 줄 수 있다.

그 결과, 성능 및 가용성에 영향을 미친다.

 

고객 시스템에서의 일상적인 실수

1. 잘못된 접속 관리 : 애플리케이션은 데이터베이스와의 상호 작용을 위해 접속 및 해제를 반복한다.

이 문제는 애플리케이션 서버에 구축된 무상태(stateless) 미들웨어에서 일상적으로 발생한다. 이러한

경우, 성능에 미치는 영향은 2배 이상이 되며, 확장성이 떨어 진다.

2. 커서 및 Shared Pool의 잘못된 사용 : 커서를 사용하지 않으면 반복적인 잔 파싱이 발생 한다. 만약,

바인드 변수가 사용되지 않았다면 모든 유사한 SQL 문장들은 하드 파싱을 수행하게 된다. 이러한 경우,

성능에 미치는 영향은 비례하며, 확장성이 떨어진다. 바인드 변수와 커서를 사용하여 커서를 오픈 및

반복 실행하며, 혹시 애플리케이션이 동적 SQL을 생성하는지 의심하여야 한다.

3. 잘못된 SQL : 잘못된 SQL은 애플리케이션에 대하여 적절한 수준 이상의 많은 자원을 사용하는

SQL이다. 이러한 경우는 24시간 이상 수행되는 DSS 쿼리가 될 수도 있고 수 분 이 걸리는 온라인

애플리케이션이 될 수도 있다. 많은 시스템 자원을 소비하는 SQL은 잠재적인 성능 향상을 위해

조사하여야 한다. ADDM은 부하가 많이 걸리는 SQL을 식별하고 SQL Tuning Advisor는 성능 향상을

위한 권고사항을 제공하는데 사용된다.

4. 비표준 초기화 파라메터 사용 : 이는 적절치 못한 권고 사항 또는 옳지 않은 가정을 기반으로

구현된다. 대부분의 시스템은 오직 기본적인 파라메터 집합만으로 허용 가능한 우수한 성능을 제공한다.

특별히, 문서화 되지 않은 옵티마이저 기능은 많은 조사를 필요로 하는 큰 문제를 발생시킬 수도 있다.

이와 유사하게, 초기화 파라메터 파일 내의 옵티마이저 파라메터 집합은 이미 증명된 최적의 실행 계획을

무시 할 수 있다. 이러할 이유로 인하여, 스키마, 스키마 통계, 옵티마이저 설정은 성능의 일관성을

보장하기 위해 함께 관리되어야만 한다.

5. 데이터베이스 I/O 불량 : 많은 사이트들이 자신의 데이터베이스를 올바르지 않게 가용 디스크들에

저장시킨다. 사이트들은 디스크의 개수를 옳게 지정하지 못하는데, 그 이유는 I/O 밴드폭이 아닌 디스크

여유공간을 기반으로 디스크를 설정하기 때문이다.

6. 리두 로그 설정 문제 : 많은 사이트들이 너무나 작은 리두 로그 파일들로 데이터베이스를 운용한다.

소용량의 리두 로그는 시스템에 체크포인트를 연속적으로 발생시켜 버퍼 캐시 및 I/O 시스템에 많은

부하를 유발시킨다. 만약, 리두 로그의 용랑이 매우 적다면, 아카이브가 리두 로그를 따라 잡기 힘들며,

그로 인해 데이터베이스는 아카이브 프로세스가 작업을 완료 할 때까지 대기하여야만 한다.

7. 과도한 직렬화 : 언두 세그먼트의 부족으로 인한 버퍼 캐시 내 데이터 블록의 직렬화는 많은 수의

사용자를 갖는 애플리케이션과 작은 크기의 언두 세그먼트에서 일상적으로 발생한다. ASSM(Automatic

Segment Space Management)과 자동 언두 관리를 이용하여 이러한 문제를 해결 할 수 있다.

8. 대용량 전체 테이블 스캔 : 대용량 또는 인터렉티브한 온라인 작업에서 대용량 전체 테이블 스캔은

잘못된 트랜잭션 설계, 인덱스 누락, 적절치 못한 SQL 최적화가 수행되었음을 나타낸다. 일반적으로

대용량 테이블에 대한 스캔은 과도한 I/O를 발생시키고 확장성을 떨어뜨린다.

9. 대량의 재귀(SYS) SQL : SYS에 의해 실행되는 대량의 재귀 SQL은 익스텐트 할당과 같은 공간 관리

활동이 발생 했음을 나타낸다. 그 결과, 확장성을 떨어뜨리며 사용자의 응답 시간에 영향을 미친다.

익스텐트 할당으로 인한 재귀 SQL을 감소시키려면 지역 관리 테이블스페이스를 사용하도록 한다.

특정 사용자 ID에서 실행되는 재귀 SQL은 아마도 SQL 또는 PL/SQL이므로 문제가 되지 않는다.

10. 배포 및 이관 문제 : 많은 경우에서 애플리케이션 사용자들은 과도한 자원을 사용한다. 그 이유는

테이블을 소유한 스키마가 개발 환경 또는 과거 구현 환경에서 성공적으로 이관되지 못해서 그런 것이다.

대표적인 예는 인덱스 누락 또는 올바르지 못한 통계이다. 이러한 오류는 준 최적 실행 계획과 적절치

못한 성능을 유발시킨다. 알려진 성능의 애플리케이션을 이관하는 경우, 실행 계획의 안정성을 위해

DBMS_STATS 패키지를 이용하여 스키마 통계를 익스포트한다. 비록 이러한 오류들이 ADDM에 의해

직접 탐지되지 않을지라도 ADDM은 과도한 부하를 발생시키는 SQL을 강조하여 표시한다.

 

사전 또는 예방 튜닝 방법

일반적으로 튜닝은 성능 문제를 수정하는 것이다. 그러나, 튜닝은 분석, 디자인, 코딩, 운영, 유지 관리의

모든 단계에서 반드시 고려되어야만 한다. 튜닝 작업은 때때로 시스템이 운영 되는 시점까지 남겨지게

된다. 이 때, 튜닝은 사후 작업이 되며, 대부분의 중요 병목지점이 식별되고 수정 된다.

 

애플리케이션 디자인의 단순화

애플리케이션은 다른 디자인 및 공학 제품과 차이점이 없다. 만약, 디자인이 올바르다면 애플리케이션도

올바른 것이다. 이 원리를 애플리케이션을 구축 할 때 반드시 기억해야 한다.

다음과 같은 디자인 문제에 대하여 생각해보자.

①   만약, 테이블 디자인이 너무 복잡하여 어느 누구도 완전히 이해 할 수 없다면, 해당 테이블은 아마도 적절히 디자인 된 것이 아니다.

②   만약, SQL 문장이 너무 길어서 옵티마이저가 실시간으로 최적화하는 것이 불가능하다면, 잘못된 문장, 트랜잭션, 테이블 디자인이다.

③   만약, 테이블에 많은 인덱스가 정의되어 있고, 동일한 컬럼이 반복적으로 인덱스에 포함 되어 있다면, 잘못된 인덱스 디자인이다.

④   만약, 온라인 사용자에게 빠른 응답을 제공하기 위해 쿼리가 적절한 WHERE절이 없이 실행된다면 잘못된 사용자 인터페이스 또는 트랜잭션 디자인이다.

 

데이터 모델링

데이터 모델링은 성공적인 관계형 애플리케이션 디자인에 있어서 매우 중요하다. 이것은 신속하고

정확하게 비즈니스 업무를 표현하는 방향으로 수행되어야 한다. 자주 사용되는 비즈니스 트랜잭션에 의해

영향을 받는 엔터티들을 모델링하도록 노력하여야 한다. 모델링 툴을 사용하면 신속하게 스키마 정의를

생성 할 수 있고, 신속한 프로토타입이 필요할 경우에 매우 유용 할 수 있다.

데이터 정규화는 중복을 방지한다. 데이터가 정규화 되면, 사용자는 키와 관계성을 명확하게 파악 할 수

있다. 테이블, 제약조건, 인덱스를 생성하는 다음 단계는 매우 간단하다. 좋은 데이터 모델은 사용자의

쿼리를 훨씬 효율적으로 작성 할 수 있도록 해준다.

 

테이블 디자인

테이블 디자인은 핵심 트랜잭션의 유연성과 성능 사이의 타협이다. 데이터베이스의 유연성을 유지하고

예측 불가능한 작업 부하에 적응하려면 테이블 디자인은 데이터 모델과 흡사하여야 하며, 최소한

제3정규형까지는 정규화되어야 한다. 그러나, 특정 핵심 트랜잭션이 성능상의 이유로 특정 정규형을 요구

할 수도 있다.

 

성능을 고려하여 테이블의 디자인을 단순화하려면 클러스터에 미리 조인된 테이블 저장, 파생 컬럼과

집계값 추가, 구체화 된 뷰, 파티션 테이블과 같은 오라클 데이터베이스가 제공하는 기능을 사용한다.

또한, 테이블에 올바르지 않은 데이터가 저장되는 것을 방지하려면 체크 제약조건과 컬럼에 디폴트 값을

정의하도록 한다.

디자인은 비즈니스가 중요한 테이블에만 집중하여 가장 많이 사용되는 영역에서 우수한 성능을 얻을

수 있어야 한다. 중요하지 않은 테이블의 경우, 빠른 애플리케이션 개발을 위해 디자인을 적당히 수행

할 수도 있다. 그러나, 핵심이 아닌 테이블이 프로토타입 및 테스트 상에서 성능 문제를 발생시킨다면,

즉시 디자인을 수정하여야 한다.

 

인덱스 디자인

인덱스 디자인도 애플리케이션 디자이너에 의해 작성된 SQL을 기반으로 수행해야 하는 과도한 반복적인

작업이다. 그러나, 인덱스 구축의 첫 단계는 외래 키 제약조건에 인덱스를 정의하는 것이다. 그 이유는

기본 키와 외래 키 간에 조인을 수행하는 경우, 응답 시간을 감소시키기 위한 것이다. 또한, 사원의

이름과 같이 자주 액세스하는 데이터에 대하여 인덱스를 생성하는 것이다. 기본 키와 고유키는 DISABLE

VALIDATE 및 DISABLE NOVALIDATE RELY 제약조건을 제외하고 자동적으로 인덱스가 정의된다.

애플리케이션이 진화하고, 실제 데이터 규모로 테스트가 수행되면 특정 쿼리는 성능 향상을 필요로 하며,

이런 경우 인덱스는 좋은 해답이 된다.

 

새로운 인덱스를 구축할 때는 다음과 같은 사항을 고려하도록 한다.

인덱스 또는 인덱스 구조 테이블(IOT)에 컬럼 추가

쿼리의 속도를 향상시키는 가장 쉬운 방법 중의 하나는 실행 계획에서 테이블 스캔을 제거하여 논리적

I/O의 횟수를 감소시키는 것이다. 이것은 쿼리가 참조하는 테이블의 모든 컬럼을 인덱스에 추가함으로서

실현 할 수 있다. 이러한 컬럼들은 SELECT 목록의 컬럼, 조인에 필요한 컬럼, 정렬 컬럼들이다. 이

기법은 시간이 소비되는 I/O를 감소시켜야 하는 경우, 온라인 애플리케이션의 응답 시간을 감소시키는데

매우 유용하다. 이 방법은 애플리케이션을 적당한 규모의 데이터로 최초에 테스트하는 경우에 매우

적절하다. 이 기법의 가장 공격적인 형태는 IOT(Index Organized Table)이다.

 

뷰의 사용

 

뷰는 애플리케이션 디자인을 빠르고 단순하게 해준다. 간단한 뷰의 정의는 데이터를 인고, 표시, 수집,

저장 해야하는 프로그래머들로부터 데이터 모델의 복잡성을 감춰준다.

 

그러나, 뷰는 명확환 프로그래밍 인터페이스를 제공하지만, 너무 많은 중첩 레벨을 가지면 준 최적화

되고 자원을 과도하게 사용하는 실행 계획을 유발 할 수 있다. 뷰의 가장 좋지 않은 사용 유형은 다른

뷰를 참조하는 뷰를 조인하는 것이며, 이 뷰가 다른 뷰를 다시 참조하는 것이다. 대부분의 경우에 있어서

개발자는 뷰를 사용하지 않고 테이블을 직접 검색하는 쿼리에 만족 할 수 있다. 뷰에 내제하는 속성으로

인하여, 뷰는 옵티마이저가 최적의 실행 계획을 작성하는데 어려움을 준다.

 

SQL 실행 효율성

 

SQL 실행의 효율성을 위해 디자인 된 애플리케이션은 다음과 같은 특징을 반드시 지원하여야 한다.

좋은 데이터베이스 접속 관리 : 데이터베이스 접속은 확장성을 떨어뜨리는 과도한 작업이다. 그러므로,

데이터베이스의 동시 접속 수는 가능한 최소화 되어야 한다. 애플리케이션이 초기화 될 때, 한 명의

사용자가 접속하는 단순한 시스템이 이상적이다. 그러나, 웹 기반 또는 멀티티어 애플리케이션의 경우,

다중 데이터베이스 접속을 사용자들에게 제공하기 위해 애플리케이션 서버가 사용되면 접속 관리가

어려워 질 수 있다. 이러한 유형의 애플리케이션은 데이터베이스 접속을 풀링시키고 각 사용자의 요청에

따라 접속을 잘 유지하도록 설계하여야 한다.

좋은 커서 사용 및 관리 : 사용자 접속의 유지 관리와 마찬가지로 시스템의 파싱 활동을 최소화 하는

것도 중요하다. 파싱은 SQL 문장을 번역하고, 해당 문장에 대한 실행 계획을 생성하는 작업이다. 이

작업은 구문 확인, 권한 확인, 실행 계획 생성, 공유 구조를 Shared Pool에 로딩하는 여러 작업으로

구성된다.

 

여기에는 두 종류의 파싱 작업이 있다.

①   하드 파싱 : SQL 문장이 최초에 전송되고, Shared Pool에서 일치하는 부분이 발견되지 않으면 발생한다.

하드 파싱은 해당 작업에 포함된 모든 작업을 수행하기 때문에 많은 자원을 소비하고 확장성을 떨어뜨린다.

②  소프트 파싱 : SQL 문장이 최초에 전송되고, Shared Pool에서 일치하는 분분이 발견되면 수행된다. 이러한

일치 항목은 다른 사용자에 의해 이전에 실행된 결과 일 수 있다. SQL 문장이 공유되면 성능이 향상된다.

그러나, 소프트 파스는 여전히 구문 및 권한을 확인하는 작업을 수행하여 시스템 자원을 소비하므로 이상적이지는 않다.

 

파싱은 가능한 최소화되어야 하기 때문에 애플리케이션 개발자는 SQL 문장이 한번만 파싱되고 수회

반복해서 사용되도록 애플리케이션을 디자인해야 한다. 이러한 작업은 커서를 통해서 구현 될 수 있다.

경험 많은 SQL 프로그래머들은 커서의 오픈과 커서의 잔 실행 개념에 친숙해야 한다. 또한, 애플리케이션

개발자는 SQL 문장이 Shared Pool내에서 공유되도록 보장하여야 한다. 이를 위해서, 문장이 실행 될

때 변화되는 부분을 바인드 변수로 표현하여 변경시킨다.

만약, 그렇지 않은 경우, SQL 문장은 한번 파싱된 후, 다른 사용자들에 의해 결코 잘 사용되지 않는다.

SQL이 공유되도록 하려면 바인드 변수를 사용하고 SQL 문장에 문자 리터럴을 사용하지 않아야 한다.

 

커서를 공유하도록 SQL 작성

 

오라클이 커서를 공유하려면, 해당 코드는 동일한 방식의 문자로 작성되어야 한다. 그렇지 않으면

CURSOR_SHARING과 같은 특별한 초기화 파라메터를 사용하여야 한다. 그러므로, 임의 쿼리, SQL

스크립트, OCI 요청 내의 SQL 문장에 대하여 코딩 규칙을 정의해야 한다.

일반 공유 코드 사용

①   애플리케이션들 간에 공유 가능한 저장 프로시저를 작성한다.

②   데이터베이스 트리거를 사용한다.

③   애플리케이션 개발 툴을 사용하는 경우, 참조 트리거 및 프로시저를 작성한다.

④   다른 환경에서 라이브러리 루틴 및 프로시저를 작성한다.

포맷 표준 작성

①   PL/SQL 코드를 포함하여 모든 문장에 대한 포맷 표준을 정의한다.

②   대문자 및 소문자의 사용 규칙을 정의한다.

③   공백(스페이스, 탭, 리턴)의 사용 규칙을 정의한다.

④   주석(가능하면 SQL 문장 외부에 작성하는 것을 선호)의 사용 규칙을 정의한다.

⑤   동일한 데이터베이스 객체를 참조 할 때는 같은 이름을 사용한다. 가능하다면 각 객체에 스키마 이름을 접두사로 추가한다.

 

성능 확인 사항 

①  초기화 파라메터의 개수를 최소화한다. 이상적으로 대부분의 초기화 파라메터는 디폴트로 유지한다. 만약, 수행해야 할 튜닝 작업이 있는 경우,

이것은 시스템의 부하가 매우 낮음을 보여준다. 테이블과 인덱스는 적절한 테이블스페이스에 저장되도록 저장 옵션을 설정한다.

②   모든 SQL 문장이 최적인지 검증하고, 해당 문장의 자원 사용량을 이해한다.

③  데이터베이스에 접속하는 미들웨어 및 프로그램이 접속 관리를 효율적으로 수행하고 반복적으로 로그온 및 로그오프가 발생하지 않는지 검증한다.

④   SQL 문장이 커서를 효율적으로 사용하는지 검증한다. 각 SQL 문장은 한번 파싱되고 여러번 수행되어야 한다. 바인드 변수가 적절히 사용되지 않고

WHERE 구문의 조건이 문자 리터럴로 작성되면 이러한 현상은 절대 발생 할 수 없다.

⑤   모든 스키마 객체가 개발 환경에서 운영 환경으로 올바르게 이전 되었는지 검증한다. 여기에는 테이블, 인덱스, 시퀀스, 트리거, 패키지, 프로시저,

함수, 자바 객체, 동의어, 권한, 뷰를 포함한다. 테스트 환경에서 발생한 모든 변경사항이 운영 시스템에 모두 반영되었는지 보장하여야 한다.

⑥   시스템이 제 역할을 수행하면, 데이터베이스 및 운영 체제로부터 기준선이 되는 통계치를 수집한다. 수집된 최초의 통계 집합은 디자인 및 운영

단계에서 수립한 가정을 검증 하거나 수정 할 수 있게 해준다.