Oracle SQL High-Performance Tuning
Chapter2 SQL Tuning Quick Start
TOP TIPS AND HINTS FOR SQL TUNING
HINT : 성능을 염두에 두고 디자인하고 개발한다.
보통 SQL 튜닝은 실제로 성능 요건에 주어진 어떠한 고려 없이 디자인되고 개발된 어플리케이션상에서 이루어진다. 흔히 SQL 튜닝은 그러한 어플리케이션을 위한 최고의 선택이나, 성능을 염두에 두고 어플리케이션을 디자인 하는 것이 보다 효과적이고 효율적이다.
디자인에 의해 좋은 성능을 만들어 내는 것은 개발 라이프 사이클 내에서 다음의 행위들을 필요로 한다.
· 개발 초기 단계에 시스템의 성능 특성에 대한 명시적인 내역
· 논리적이고 물리적인 모델링을 하는 동안 크리티컬한 트랜잭션과 쿼리에 포커스를 맞춘다. 프로토타입 디자인에서 이러한 SQL 문장의 시뮬레이션은 보통 주요 성능상의 약점을 발견해 낼 수 있다.
· 목표 환경에 대한 효과적인 시뮬레이션. 이것은 동시 유저의 로드를 시뮬레이션 하는 것과 대표적인 데이터 볼륨을 획득하는 것을 포함할 수 있다.
· 크리티컬 트랜잭션 성능의 계속적인 측정. 가능하다면, 이것은 다른 퀄리티의 측정과 함께 통합되어야 한다.
HINT : 튜닝 환경을 수립한다.
개발환경에서 잘 동작하는 것 같았던 SQL 문장이 프로덕션 시스템으로 풀려난 후 빈약한 성능을 보이는 것은 흔한 일이 아니다. 이러한 불쾌한 일들의 주요 원인은 부적절한 개발 혹은 볼륨 테스팅 환경이다. 특별히 현실적인 혹은 대표적인 데이터 볼륨이 없는 환경은 비현실적인 SQL 성능으로 이끌어 가곤 한다.
이상적인 튜닝 혹은 개발 환경은 다음의 것들이다.
· 데이터 볼륨이 현실적이거나 혹은 최소한 비례적이다. 오늘날 지속적으로 증가하는 대형 프로덕션 데이터베이스와 함께 보통 프로덕션 데이터 볼륨과 정확하게 복제하는 것은 불가능하다.
그러나, 항상 납득할만한 데이터의 서브셋을 구축하는 것이 가능해야 한다. 예를 들어, 1GB 데이터베이스의 20% 샘플은 성능 튜닝에 대해 적절할 것이다. 어떤 비용을 감수하고라도 SQL 개발자들은 비어있거나 혹은 거의 비어있는 테이블들에 대해 코드를 구축하거나 혹은 테스트하는 환경을 피해야 한다.
· 튜닝 툴들이 사용가능하다. 여러분의 볼륨과 개발환경에 사용가능한 많은 튜닝 툴들을 제공한다. 이것은 서드파티 튜닝 툴들을 포함할 수 도 있으나 최소한 사용 가능한 기본적인 오라클 툴들을 포함해야 할 것이다. 개발자들이 EXPLAIN PLAN, SQL TRACE, 그리고 TKPROF의 사용 법을 알게 한다. 관련된 데이터베이스 옵션들을 효율적으로 사용하기 위해 셋팅하도록 한다.
· 문서화가 가능하다. 이러한 문서화는 데이터베이스 디자인, 상세 인덱스, 성능 요구사항들, 그리고 볼륨 측정기준을 포함해야 한다. SQL 프로그래머는 효율적인 SQL을 진행시켜 나가기 위해 이러한 모든 정보를 필요로 한다.
HINT : SQL 튜닝 툴을 사용한다.
실행 플랜은 오라클이 SQL 문장을 실행하기 위해 수행할 단계들의 대표적인 예이다. 예를 들어, 실행 플랜은 인덱스가 사용되어야 할지나, 테이블이 조인되는 순서 등등을 밝혀준다.
실행 플랜을 결정하지 않고 SQL 문장 실행에 관련된 통계 정보를 모으지 않는다면 마치 어둠속에서 작업하는 분위기 일 것이다.
Explain Plan - EXPLAIN PLAN 명령어는 SQL 문장에 대한 실행 계획을 생성하기 위해 사용가능 하다.
EXPLAIN PLAN을 사용하기 위해 다음을 한다.
· $ORACLE_HOME/rdbms/admin/utlxplan.sql(on UNIX) 혹
은 %ORACLE_HOME%\rdbms\admin\ utlxplan.sql(on Windows)에 있는 스크립트를 사용해서 PLAN_TABLE을 생성한다.
· sql 문장에 대해 EXPLAIN PLAN 명령어를 실행한다.
· 다음과 같은 쿼리를 사용해서 문장에 대한 실행 플랜을 보여 준다.:
SELECT RTRIM (LPAD (‘ ‘, 2 * LEVEL) ||
RTRIM (operation) || ‘ ‘ ||
RTRIM (options) || ‘ ‘ || object_name) query_plan
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0;
· PLAN_TABLE의 내용을 지운다.
SQL trace/tkprof - EXPLAIN PLAN 명령어의 보다 파워풀한 사촌은 SQL trace 툴이다. SQL trace를 사용하기 위해 다음을 한다.:
· 오라클 세션내에서 ALTER SESSION SET SQL_TRACE TRUE 명령어를 실행한다.
· init.ora 파라미터 user_dump_dest에 의해 지정된 목적지에서 생성된 트레이스 파일을 위치시킨다.
· 트레이스 파일을 포맷하기 위해 tkprof 프로그램을 사용한다. 예를 들어,
tkprof input_trace_file output_report_file
sort=’(prsela, exeela, fchela)’
explain=username/password
output file은 실행 플랜과(일반적으로 실행 플랜으로부터 사용가능하지 않은 몇가지 부가적인 정보와 함께) 다음의 SQL trace의 초기화를 실행했던 모든 SQL 문장에 대한 실행 통계를 포함할 것이다.
AUTOTRACE – 또한 “SET AUTOTRACE ON” 명령어를 사용함으로써 SQL*PLUS내에서 제한된 통계정보와 EXPLAIN PLAN을 얻을 수 있다. 이것은 TKPROF에 의해 생성된 것들 만큼 복잡하지 않음에도 불구하고 실행 플랜과 몇가지 실행 통계들을 생성한다.
HINT: INDEX WISELY
인덱스는 일차적으로 SQL 문장의 성능을 향상시키기 위해 존재한다. 많은 경우에 있어서 최고의 인덱스를 생성하는 것이 높은 성능으로의 가장 쉬운 경로이다.
Use concatenated indexes – 한 개의 인덱스가 작동을 할 때 두개의 인덱스를 사용하지 마라. SURNAME과 FIRSTNAME을 찾는다면, 각각의 컬럼에 대해 불필요하게 분리된 인덱스를 생성하지 않는다. 대신에 SURNAME과 FIRSTNAME 둘다에 연결된(concatenated) 인덱스를 생성하라. 인덱스 그 자체 상에서 연결된 인덱스에 대해 “leading” 부분을 사용할 수 있다. 따라서 FIRSTNAME을 주는 일 없이 SURNAME 컬럼상에서 종종 쿼리한다면 SURNAME이 인덱스상에 먼저 와야 한다.
Overindex to avoid a table lookup – 때때로 오버인덱싱 함으로써 SQL 실행을 향상시킬 수 있다. 오버인덱싱은 SELECT절에 나타나는 컬럼들을 연결하는 것을 포함하나 인덱스로의 WHERE절에서는 그렇지 않다. 예를 들어, EMPLOYEE_ID를 찾기 위해 SURNAME과 FIRSTNAME을 찾는 경우를 상상해보자. SURNAME과 FIRSTNAME상에서 연결된 인덱스는 빠르게 적절한 EMPLOYEE_ID를 포함하는 행을 위치시킬 것이다. 그러나, 우리는 인덱스와 테이블 둘다에 억세스할 필요가 있을 것이다. SURNAME, FIRSTNAME, 그리고 EMPLOYEE_ID상에 인덱스가 있다면 쿼리는 인덱스만을 사용해서 만족될 수 있다. 이러한 테크닉은 조인에서 중간 테이블들이 다음 테이블에 대한 조인 키를 얻기 위해 드물게 쿼리되기 때문에 조인을 최적화 할 때 특별히 유용하다.
Consider advanced indexing options – 오라클의 기본적인 B-tree 인덱스는 탄력적이고 효과적이며 대부분의 상황에 대해 적당하다. 그러나, 오라클은 특정 상황에서의 성능을 향상시킬 수 있는 많은 선택적인 인덱싱 스키마를 제공한다.
· Index clusters는 하나 혹은 그 이상의 테이블로부터 행들이 클러스터 키 순서에 따라 위치되도록 한다. 테이블을 클러스터링 하는 것은 조인 성능에 상당한 향상을 가져올 수 있다. 그러나, 클러스터에서 독립적인 테이블에 대한 테이블 스캔은 심각하게 성능을 저하시킬 수 있다. 인덱스 클러스터는 보통 항상 함께 억세스되는 테이블들에 대해서만 권장된다. 그리고 나서도 비정규화(denormalization)와 같은 선택사항들이 고려되어야 한다.
· hash clusters에서 키값은 수학적으로 해시값으로 번역된다. 행들은 이러한 해시값에 근거한 해시 클러스터에 저장된다. 해시키가 알려져 있을 때 행을 위치시키는 것은 인덱스 룩업에 의해 필요한 2번 혹은 3번의 I/O들 보다 단지 한번의 I/O만을 필요로 할 것이다. 그러나, 해시키의 레인지 스캔은 수행될 수 없다. 더 나아가, 클러스터가 잘 설정되 있지 않다거나 혹은 클러스터의 사이즈가 변경된다면, 해시키상에서 오버플로우가 일어나거나 혹은 클러스터가 드문드문 증가할 수 있다. 첫번째 경우에 해시키의 검색이 성능저하를 가져올 수 있고 두번째 경우에 테이블 스캔의 효율성이 떨어질 것이다.
· Bit-mapped indexes는 각각 상대적으로 뚜렷이 구별되는 값들을 거의 갖고 있지 않는 다양한 컬럼들에 대해 행해지는 쿼리에 적합하다. 이 인덱스는 연결된 인덱스보다 사이즈가 작고 연결 인덱스와는 달리 컬럼들이 어떠한 조합으로도 표현되는 쿼리들을 지원할 수 있다. 그러나, bit-mapped indexes는 수정이 많이 일어나는 테이블에 대해서는 적당하지 않다. 왜냐하면 bit-mapped indexes의 locking이 행 레벨보다는 블록에서 일어나기 때문이다.
· index-orgaized table에서 모든 데이터는 B-tree 인덱스 구조내에 저장된다. 이것은 프라이머리 키를 통한 데이터로의 억세스를 향상시킬 수 있고 인덱스와 테이블 둘다에 키값을 저장하는 여분의 작업을 감소시킨다. index-orgaized tables는 오버플로우 세그먼트에 위치해 있는 빈번하게 억세스되지 않는 컬럼들과 함께 설정될 수 있다. 이것은 상대적으로 작고 효과적인 인덱스 구조를 유지한다.
쿼리가 최고의 인덱스를 사용하도록 한다. – 초보 SQL 프로그래머들은 보통 그들의 SQL 문장에 대한 실행 플랜이 어떤 인덱스라도 사용한다면 만족한다. 그러나, 때때로 인덱스 수정의 기회가 있을 것이고 오라클 옵티마이저는 – 특별히 보다 오래된 룰 기반 옵티마이저 – 항상 최고의 인덱스를 선택하지는 않을 것이다. 오라클에 의해 선택된 인덱스가 가장 적절하고 필요하다면 인덱스를 변경하기 위해 힌트를 사용하도록 하라.
HINT: REDUCE PARSING
SQL 문장을 파싱하는 것은 SQL을 검증하고 최적의 실행 플랜을 결정하는 과정이다. 낮은 I/O 요구를 가지고 있지만 빈번하게 실행되는 SQL에 대해(예를 들어, OLTP 타입의 어플리케이션에 의해 생성된 SQL) SQL 파싱의 오버헤드를 감소시키는 것은 매우 중요하다.
오라클 세션이 SQL 문장을 파스하기를 원할 때 먼저 오라클 SHARED POOL에서 동일한 SQL문장을 찾는다. 매치되는 문장이 발견되지 않는다면, 오라클은 그 문장에 대한 최적의 실행 플랜을 결정하고 shared pool에 파스된 표현을 저장할 것이다.
SQL을 파싱하는 과정은 CPU 집중적이다. I/O가 잘 튜닝되었다면, SQL 파싱의 오버헤드는 SQL을 실행하는 전체 오버헤드에 대한 중요한 부분이 될 수 있다. SQL 문장 파싱의 오버헤드를 감소시키는 많은 방법들이 있다.
· Use bind variables - Bind variables는 쿼리의 변수 부분이 프로그램 변수로의 포인터에 의해 표현되도록 해준다. 바인드 변수를 사용한다면 SQL 문장의 텍스트는 실행에서 실행까지 변하지 않을 것이고 오라클은 보통 파스 오버헤드를 감소시키면서 SHARED POOL에서 매치되는 부분을 발견할 것이다.
· Reuse cursors – Cursors(혹은 context areas)는 SQL 문장의 파스된 표현을 저장하는 메모리의 영역이다. 한번 이상 동일한 SQL 문장을 재실행 한다면 존재하는 커서를 재오픈할 수 있고 파스 콜의 실행을 피할 수 있다. 커서 재사용의 메커니즘은 개발툴과 프로그래밍 언어에 따라서 다양하다.
· Use a cursor cache – 개발툴이 커서를 재사용 하는 것이 어렵거나 혹은 불가능 하다면, SESSION_CACHED_CURSORS init.ora 파라미터를 사용해서 오라클로 하여금 각각의 세션에 대해 커서 캐시를 생성하도록 지시할 수 있다. SESSION_CACHED_CURSORS가 0보다 크다면, 오라클은 캐시에서 가장 최근에 재실행된 커서들의 개수를 저장할 것이다. SQL 문장이 재실행 된다면 그것은 캐시에서 발견될 것이고 파스콜은 피할 수 있을 것이다.
HINT: 비용 기반 옵티마이저를 이용한다.
SQL 문장에 대해 실행 플랜을 결정하는 오라클 소프트웨어의 요소는 옵티마이저로 알려져 있다. 오라클은 쿼리 최적화에 대해 두가지 접근방법을 지원한다.
· rule-based optimizer는 다양한 억세스 경로에 순위를 매기는 룰들의 셋에 기반해 실행 플랜을 결정한다. 예를 들어, 인덱스 기반의 검색이 전체 테이블 스캔보다 더 낮은 순위를 가지고 있고 따라서 규칙 기반 옵티마이저는 가능하다면 인덱스를 사용할 것이다.
· cost-based optimizer는 다양한 억세스 방법을 만족시키기 위해 필요한 컴퓨터 리소스(비용)의 추정에 기반해 실행 플랜을 결정한다.
Star와 hash 조인과 같은 많은 발전된 SQL 억세스 방법들은 오직 비용 기반 옵티마이저를 사용할 때에만 사용 가능하다.
비용 기반 옵티마이저는 거의 모든 새로운 프로젝트에 대해 최고의 선택이고 룰에서 비용 기반 최적화로의 전환은 많은 존재하는 프로젝트에 대해 가치있는 작업이 될 것이다. 다음의 가이드 라인을 살펴보자.
· Optimizer_mode – 비용 기반 옵티마이저의 기본 모드는(optimizer_mode=CHOOSE) SQL 문장의 모든 행들을 검색하기 위해 걸리는 시간을 최적화 할려는 시도를 할 것이고 종종 인덱스 룩업을 넘어서 전체 테이블 스캔을 선호할 것이다. 비용 기반 최적화로 전환할 때 많은 유저들은 이전에 잘 튜닝된 인덱스 룩업들이 긴 시간이 걸리는 테이블 스캔으로 변하는 것을 발견하고 실망한다. 이것을 피하기 위해, init.ora에서 OPTIMIZER_MODE=FIRST_ROWS를 셋팅하거나 혹은 여러분의 코드에서 ALTER SESSIOM SET OPTIMIZER_GOAL=FIRST_ROWS를 셋팅하라. 이것은 비용기반 옵티마이저가 여러분의 결과 셋에서 첫번째 행을 검색하는데 걸리는 시간을 최소화 시키고 인덱스의 사용을 촉진하도록 지시한다.
· Hints – 비용 기반 옵티마이저가 아무리 정교하다 할지라도, 여전히 문장의 실행 경로를 수정할 필요가 있는 경우들이 있을 것이다. SQL hints는 보통 이것을 하기 위한 최적의 방법이다. 힌트를 사용해서 옵티마이저가 여러분이 선호하는 억세스 경로를 따르도록 할 수 있고(선호하는 인덱스와 같은) 병렬 쿼리 옵션을 사용한다거나 조인 순서를 선택할 수 있다.
힌트는 SQL 문장에서 다음의 첫번째 단어의 코멘트로서 입력된다. 코멘트에서 플러스 기호 “+”는 오라클이 코멘트가 힌트를 포함하고 있음을 알도록 해준다. 다음의 예에서 힌트는 옵티마이저에게 CUST_I2 인덱스를 사용하도록 지시하고 있다.:
SELECT /*+ INDEX(CUSTOMERS CUST_I2) */ *
FROM CUSTOMERS
WHERE NAME =: cust_name
· Analyze your tables – 비용기반 옵티마이저의 실행 플랜은 analyze 명령어에 의해 수집된 테이블 통계정보를 사용해서 계산된다. 피크 볼륨일 때 테이블을 정규적으로 분석한다.(예를 들어, 배치 작업에 의해 막 로드되기 전에 테이블을 분석하지 마라.) 작은 것에서부터 중간 사이즈의 테이블에 대해 ANALYZE TABLE table_name COMPUTE STATISTICS를 사용하라.; 더 큰 테이블에 대해는 ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 20 PERCENT처럼 샘플을 이용한다.
· Use histograms – 오라클 7.3 이전에 비용기반 옵티마이저는 컬럼에서 뚜렷이 구별되는 값들의 사용가능한 개수를 알고 있었으나 컬럼내의 데이터 분포에 대해서는 아니었다. 이것은 질의에서 특정 값이 드물고 인덱스 룩업으로부터 이익을 얻는다 할지라도 컬럼상에서 단지 몇 개의 값으로 인덱스의 사용을 감퇴시킨다는 것을 의미했다. 오라클 7.3에서 소개되었던 histogram은 컬럼의 분산 데이터가 수집되도록 하고 비용기반 옵티마이저가 더 나은 결정을 내리도록 허용할 것이다. Analyze 명령어의 FOR COLUMNS절로 히즈토그램을 생성할 수 있다.(예를 들어, ANALYZE TABLE table_name COMPUTE STATISTICS FOR ALL INDEXED COLUMNS) 바인드 변수를 사용하고 있다면 히즈토그램을 이용할 수 없음을 명심하라.
HINT: AVOID ACCIDENTAL TABLE SCANS
가장 기본적인 SQL 튜닝 문제의 하나는 우연한 테이블 스캔이다. 우연한 테이블 스캔은 보통 SQL 프로그래머가 인덱스가 걸린 컬럼상에서 인덱스에 의해 지원될 수 없는 검색을 수행할려고 할 때 일어난다. 이것은 다음의 경우일 때 발생한다.
· Using != (not equal to) – not equals 조건이 단지 적은 수의 행들을 만족한다 할지라도 오라클은 그러한 조건을 만족시키기 위해 인덱스를 사용하지 않을 것이다. 보통 인덱스 룩업에 의해 지원될 수 있는, > 혹은 IN 조건을 사용해서 이러한 쿼리들을 재코드 할 수 있다.
· Searching for NULLS – 오라클은 null값을 찾기 위해 인덱스를 사용하지 않을 것이다. 왜냐하면 널값은 보통 인덱스에 저장되지 않기 때문이다.(예외는 단지 값들중 일부가 null일 때 연결된 인덱스 엔트리이다.) 논리적으로 잃어버린 값들을 찾으려고 계획한다면, 컬럼을 DEFAULT절을 갖는 NOT NULL로 변경하는 것을 고려해보자. 예를 들어, 디폴트 값을 “UNKNOWN”으로 셋팅하고 이러한 값들을 찾기 위해 인덱스를 사용할 수 있다.
· Using functions on indexed columns – 인덱스가 걸린 컬럼상에서 어떤 펑션 혹은 작업은 오라클이 그러한 컬럼상에서 인덱스를 사용하는 것을 방해할 것이다. 예를 들어, 오라클은 그러한 컬럼을 조작하는 대신에 SUBSTR(SURNAME,1,4)=’SMIT’을 찾기 위해 인덱스를 사용할 수 없다. 이전 예에서 더 나은 검색 조건을 작성하도록 노력하라. 더 나은 검색식은 SURNAME LIKE ‘SMIT%’가 될 것이다. 오라클8i에서 펑션이 동일한 입력이 주어질 때 항상 동일한 결과를 반환한다는 것에 착안해, 펑션상에서 생성되는 인덱스인, functional indexes를 생성할 수 있다. 이것은 예를 들어, 대문자(surname)상에 인덱스를 생성하도록 한다.
HINT: OPTIMIZE NECESSARY TABLE SCANS
많은 경우에 있어서, 모든 가능한 최고의 인덱스를 사용함으로써 전체 테이블 스캔을 피하는 것이 여러분의 목표다. 그러나, 종종 전체 테이블 스캔은 피할 수 없는 문제다. 이러한 상황에서 전체 테이블 스캔의 성능을 향상시키기 위해 몇몇 다음의 테크닉들을 고려할 수 있다.
Use the Parallel Query Options – 오라클의 병렬 쿼리 옵션은 가장 리소스 집중적인 작업임에도 불구하고 전체 테이블 스캔의 성능을 향상시키는 가장 효과적인 방법이다. 병렬 쿼리는 최소한 부분적으로 전체 테이블 스캔에 근거한 SQL 문장으로 다양한 프로세스들을 할당한다. 테이블은 뚜렷이 구별되는 블록들의 집합으로 파티션되고 각각의 프로세스는 다른 데이터의 집합에서 작업한다. 더 나아가 프로세스들은 조인, 정렬, 그리고 다른 작업들을 수행하기 위해 - 혹은 오리지널 프로세스가 순환되면서 – 할당될 것이다.
테이블 스캔으로 다양한 프로세스들을 할당할려는 시도는 하드웨어와 데이터베이스 레이아웃이 적절하다면 극적으로 실행 시간을 감소시킬 수 있다.특별히 호스트 컴퓨터는 다양한 CPU들을 가지고 있어야 하고 혹은 데이터베이스는 한 개 이상의 디스크 디바이스상에 분산되 있어야 한다.
PARALLEL 힌트로 병렬 쿼리 옵션을 활성화 시키거나 혹은 PARALLEL 테이블절로 테이블에 대해 병렬 쿼리 옵션이 디폴트가 되도록 할 수 있다.
Reduce the size of the table – 전체 테이블 스캔의 성능은 일반적으로 스캔되는 테이블의 사이즈에 비례하게 될 것이다. 테이블의 사이즈를 매우 세세하게 감소시키고 따라서 전체 테이블 스캔의 성능을 향상시키는 방법들이 있다.
· Reduce PCTFREE – PCTFREE 테이블 셋팅은 행의 길이를 증가시키는 업데이트를 허용하기 위해 각각의 블록에서 어떤 공간의 퍼센티지를 예약한다. 디폴트로 PCTFREE는 10%로 셋팅되 있다. 테이블이 가끔 업데이트 된다거나 업데이트가 드물게 행의 길이를 증가시킨다면 PCTFREE를 줄일 수 있으며 따라서 전체적인 테이블의 사이즈를 줄일 수 있다.
· Increase PCTUSED – PCTUED 테이블 셋팅은 이전에 PCTFREE까지 올라갔던 어떤 시점의 블록들이 다시 인서트를 위해 적합할 것인지를 결정한다. 디폴트 값은 40%이며, 이것은 PCTFREE까지 올라간 이후에 삭제 작업이 사용된 공간의 양을 40%까지 감소시킬 때까지 그 블록이 새로운 행을 위해 단지 적합하게 될 것이라는 것을 의미한다. PCTUSED를 증가시킨다면 행들은 보다 이른 시점에 테이블로 삽입될 것이며, 블록들은 평균적으로 보다 더 가득 찰 것이다. 그리고 테이블은 더 작아질 것이다. INSERT의 성능에 있어서는 역효과가 있을 것이다. – 여러분은 스캔과 인서트의 성능 사이에 균형을 평가해봐야 할 것이다.
· Relocate long or infrequently used columns – 테이블에서 자주 억세스 되지 않고 전체 테이블 스캔을 통해서 결코 억세스 되지 않는(아마도 비트맵 이미지 혹은 임베디드되는 문서) LONG(혹은 big VARCHAR2)을 가지고 있다면, 이러한 것들을 분리된 테이블로 재위치 시키는 것을 고려해 보야 한다. 이러한 컬럼들을 재위치 시킴으로써 실질적으로 테이블의 사이즈를 감소시킬 수 있고 따라서 전체 테이블 스캔의 성능을 향상시킬 수 있다. 오라클8 LOB 타입은 거의 항상 코어 테이블 데이터의 외부에 저장될 것이다.
The CACHE hint – 일반적으로, 대부분의 전체 테이블 스캔에 의해 검색되는 행들은 거의 즉각적으로 오라클의 캐시로부터 밀려나간다. 이것은 전체 테이블 스캔이 완벽히 캐시를 가득채우고 인덱스 검색에 의해 검색된 행들을 밀어내 버렸기 때문에 일어나는 민감한 문제들이다. 그러나, 이것은 동일 테이블의 부분적인 테이블 스캔이 캐시에서 매치되는 부분을 찾을 수 없을 것 같다는 것과 따라서 높은 물리적인 I/O rate를 야기하리라는 것을 의미한다.
CACHE 힌트 혹은 CACHE 테이블 셋팅을 사용함으로써 오라클로 하여금 캐시내에 이러한 행들을 보존하도록 하게 할 수 있다. 오라클은 그러고 나서 LRU chain의 최근에 가장 덜 사용된 끝 부분에 검색된 행들을 놓을 것이고 그러한 행들은 휠씬 더 오랜 시간 동안 캐시안에 남아 있을 것이다.
Use Partitioning – 테이블로부터 검색하길 원하는 행들의 수가 인덱스 룩업이 효과적으로 검색할 수 있는 것보다 더 크지만 테이블 그 자체의 일부분만을 원한다면(전체 테이블의 10에서 40% 정도라고 해두자.) 테이블 파티셔닝을 고려해 볼 수 있다.
예를 들어, SALES 테이블이 과거 4년 동안 모든 판매 기록들을 포함하고 있고 year-to-date 토털을 계산하기 위해 현재 회계년도 동안 모든 판매 기록들을 자주 스캔할 필요가 있다고 가정하자. 스캔되는 행들의 비율이 인덱스 룩업이 안정적으로 지원하는 것보다 휠씬 크지만 여전히 전체 테이블의 일부분이다. 회계년도로 그 테이블을 파티션 한다면 적절한 회계년도와 매치되는 기록들만으로 프로세싱을 제한할 수 있다. 이것은 스캔시간을 75% 혹은 그 이상까지 감소시킬 수 있다.
Consider fast full index scan – 쿼리가 테이블에 있는 모든 혹은 대부분의 행들을 억세스하길 원하지만 그 컬럼들의 부분집합만을 원한다면, 행들을 검색하기 위해 빠른 전체 인덱스 스캔을 고려해 볼 수 있다. 이것을 하기 위해 쿼리의 select와 where절에 포함된 모든 컬럼들을 포함하는 인덱스를 만들 필요가 있다. 이러한 컬럼들이 전체 테이블의 작은 집합만으로 구성되 있다면 인덱스는 실질적으로 더 작아질 것이고 오라클은 테이블을 스캔하는 것보다 인덱스를 보다 빠르게 스캔할 수 있을 것이다. 물론 INSERT, UPDATE, 그리고 DELETE 문장의 성능에 영향을 미칠 인덱스를 유지하는데에 내재된 오버헤드가 있을 것이다.
HINT: OPTIMIZE JOINS
최적의 조인 순서와 방법을 결정하는 것은 다양한 테이블의 조인을 포함하는 SQL의 성능을 최적화 시킬 때 민감한 고려사항들이다.
Join Method – 오라클은 3가지 조인 방법을 지원한다.:
· nested loops join에서 오라클은 외부 테이블에서 발견된 각각의 행에 대해 내부 테이블의 검색을 수행한다. 이러한 타입의 억세스는 내부 테이블에 인덱스가 있을 때 가장 흔하게 볼 수 있으며 그렇지 않다면, 다양한 nested table 스캔이 일어날 것이다.
· sort-merge join을 수행할 때, 오라클은 조인 컬럼들의 값에 의해 각각의 테이블(혹은 결과셋)을 소트해야 한다. 일단 소트되고 나면, 숫자가 매겨진 페이지들의 두개의 소트된 부분들을 병합할려고 하는 만큼 두개의 데이터 셋들이 병합된다.
· hash join을 수행할 때, 오라클은 두개의 더 작은 테이블들에 대해 해시 테이블을 빌드한다. 이 해시 테이블은 인덱스가 nested loop join에서 사용되는 방법으로 무언가 유사한 패턴으로 매칭되는 행들을 찾기 위해 사용된다.
nested loop 방법은 조인을 지원하기 위해 인덱스가 있는 곳과 테이블 데이터의 서브셋을 함께 조인하는 SQL에 적합하다. 더 큰 양의 데이터가 조인되어야 하고(혹은 하거나) 인덱스가 없을 때, sort-merge 혹은 hash-hoin 방법을 사용하라. Hash join은 보통 sort merge를 능가한다. 그러나, 비용기반 옵티마이저가 효과가 있거나 혹은 힌트가 사용될 때에만 사용될 것이다.
Join Order – 최고의 조인 순서를 결정하는 것은 시행착오의 문제가 될 수 있다. 비용기반 옵티마이저는 보통 좋은 조인 순서를 집어 낼 것이다. 그러나, 그렇지 않다면, FROM절에 나타나는 테이블들이 강제적으로 정확한 순서로 조인되도록 ORDERED절을 사용할 수 있다.
일반적으로, 조인 프로세스에서 나중에 하는 것보다는 보다 초기에 행들을 제거하는 것이 더 낫다. 따라서 테이블이 제한적인 WHERE절 조건을 가지고 있다면 조인 프로세스에서 이것을 보다 일찍 선호해야 한다.
Special Joins – 오라클은 특별한 조인 타입에 대한 많은 최적화를 제공한다. 이러한 최적화의 몇몇은 비용기반 옵티마이저를 사용한다면 자동으로 수행될 것이다. 그러나 모든 것이 힌트의 사용에 의해 또한 옵티마이저에서 이루어질 수 있다.
· 스타 조인 알고리듬은 단일의 거대한 실제 테이블을 다양하고, 더 작은 디멘전 테이블들로 최적화 한다. 최적화는 STAR 힌트로 이루어 질 수 있다. 더 나아간 최적화는 실제 테이블에서 존재할지 모르는 비트맵 인덱스를 이용하기 위해 SQL을 재작성한다. 이러한 최적화는 STAR_TRANSFORMATION 힌트에 의해 이루어질 수 있다.
· 안티조인은 보통 NOT IN절을 사용한 서브쿼리로 표현된다. 이러한 타입의 쿼리들은 규칙 기반 최적화 하에서 좋지 않게 수행될 수 있으나 init.ora 파라미터인 ALWAYS_ANTI_JOIN이 HASH로 셋팅되거나 혹은 HASH_AJ 힌트가 서브쿼리에 추가된다면 효율적으로 수행될 수 있다.
· 세미조인은 보통 EXISTS절을 사용한 서브쿼리로 표현된다. 이러한 쿼리는 서브쿼리를 지원하는 인덱스가 없다면 빈약하게 수행될지 모르나 init.ora 파라미터인 ALWAYS_SEMI_JOIN이 HASH로 셋팅되거나 혹은 HASH_SJ 힌트가 서브쿼리에 추가된다면 효율적으로 수행될 수 있다.
· CONNECT BY 오퍼레이터를 사용한 계층(Hierarchical) 쿼리는 CONNECT BY 조인 조건을 지원하기 위한 인덱스가 없다면 테이블 볼륨이 증가할 때 급속하게 성능이 저하될 것이다.
HINT: USE ARRAY PROCESSING
배열 프로세싱은 단일 작업으로 한 개 이상의 행을 질의하거나 혹은 인서트하기 위해 오라클의 능력을 이용한다. 다양한 행의 데이터를 다루는 SQL에 대해 배열 프로세싱은 보통 실행 시간에 있어서 50% 혹은 그 이상의 감소를 보인다.(네트웍을 통해 작업한다면 보다 많이) 몇몇 어플리케이션 환경에서 배열 프로세싱은 자동으로 수행되고 이러한 기능을 사용하기 위해 어떤 작업도 할 필요가 없을 것이다. 다른 환경에서, 배열 프로세싱은 프로그래머의 책임이 될른지도 모른다.
“무언가가 좋다면 보다 많은 것이 더 좋아져야 한다”는 관점에서 많은 프로그래머들이 거대배열을 사용한다. 이것은 과잉 사용이 될 수 있고 심지어는 프로그램에 대한 메모리 요구량을 증가시킴으로써 성능을 감소시킬 수도 있다. 배열 프로세싱의 대부분의 이점들이 1부터 대략 20까지 배열 사이즈를 증가시킴으로써 얻어진다. 더 나아간 증가는 이점을 감소시키는 결과를 가져오고 일반적으로 배열 사이즈의 증가가 100을 넘어갈 때 보다 많은 향상을 기대하기는 어려울 것이다.
HINT: AVOID LOCK CONTENTION
락에 대해 경합하는 어플리케이션은 예측할 수 없고 실망스러운 성능을 보여줄 수 있다. 오라클의 row-level locking 전략과 read locks의 부재는 락의 경합을 최소한으로 감소시켜 준다. Locking overhead를 최소화 하기 위해 다음의 가이드라인들을 따라야 한다.:
Adopt an appropriate locking strategy – locking에 대한 두가지의 가장 공통적인 접근은 pessimistic locking strategy와 optimistic locking strategy로 알려져 있다. pessimistic locking strategy에서 어떤 행은 그것이 처음 검색될 때(SELECT문장의 FOR UPDATE를 사용해서) 락이 걸리게 된다. 이러한 락은 트랜잭션이 완료될 때까지 걸려 있는다. optimistic locking strategy에서 행은 처음 질의될 때 락이 걸리지 않는다. 어떤 행이 순차적으로 업데이트될 필요가 있다면 업데이트되지 않도록 하기 위해 먼저 체크된다. 그 행이 업데이트 된다면 에러가 발생하고 트랜잭션은 재시도 되어야 한다.
optimistic locking strategy는 테이블 락의 지속기간을 감소시키고 인터렉티브한 어플리케이션에서 유저가 “점심을 먹으러 나가 있는” 동안 락이 걸려 있지 않도록 방지한다.
한편으로, optimistic strategy는 유저가 트랜잭션을 재시도 해야 한다거나 혹은 빈번하게 트랜잭션을 재시도할 필요가 있는 배치성 작업의 작업 처리량에서 지연이 발생한다. 그러나, optimistic strategy는 특별히 인터렉티브한 어플리케이션에 대해 종종 최고의 전략이다.
Use sequences – 오라클 시퀀스 생성자는 어플리케이션에게 효과적이고 방해하지 않는 방법으로 유니크한 시퀀스 넘버들을 제공하기 위해 존재한다. 컨트롤 테이블에서 다음 키값을 보존하는 선택적인 접근은 그러한 테이블에 대해 고수준의 락 경합을 야기한다. 시퀀스 생성자는 몇가지 작은 약점들을 가지고 있다. – 특별히 트랜잭션이 롤백할 때 시퀀스 넘버를 잃어버릴 수도 있다. 그러나, 진정으로 해야할 만한 어쩔 수 없는 이유를 가지고 있다면 키값을 생성하기 위해 - 그리고 컨트롤 테이블이 아닌 - 시퀀스 생성자를 사용하라.
Use FOR UPDATE wisely – SELECT 문장의 FOR UPDATE절은 행들이 질의될 때 락이 걸리게 된다. 이것은 파워풀하고 유용한 절이지만 적절하게 사용되지 않는다면 성능상의 문제를 야기할 수 있다. 특별히 FOR UPDATE를 사용하는 SELECT 문장은 첫번째 행이 리턴되기 전에 모든 행들이 락이 걸리기 때문에 반응 시간의 문제를 일으킬 것이다. 또한 단지 몇 개의 행이 fetch된다 할지라도 where절과 매치되는 모든 행이 락이 걸리게 되고 이것은 너무 많은 행들이 락이 걸리게 되므로 결정적으로 락의 경합을 야기할 것이다.
Beware of inindexed foreign keys – 오라클의 row-level locking 기능은 page- 혹은 block-level locking과 함께 RDBMS에서 발생하는 많은 락 충돌들을 제거해 준다. 그러나, row-level locking이 발생할 수 있는 몇가지 환경들이 있다. 대부분의 공통적인 환경은 인덱스가 걸리지 않은 외래키 제약조건의 경우이다.
외래키 인덱스가 존재하지 않는다면 오라클은 자식 테이블을 업데이트 하는 동안 전체 부모 테이블에 락을 걸 것이다. 따라서, 자식 테이블이 빈번하게 업데이트된다는 것을 알고 있고 부모 테이블상에서의 테이블 락이 문제를 야기할 것 같다면 외래키 제약조건에 인덱스를 걸어야 한다.
HINT: USE PARTITIONING FOR LARGE TABLES
파티셔닝은 단일 테이블 혹은 인덱스가 다양한 데이터베이스 세그먼트로 처리되도록 한다. 이러한 접근은 많은 이점을 가지고 있다.
· 병렬 UPDATE와 DELETE는 파티션된 객체상에서만 수행될 수 있다.
· 많은 유지보수 작업들이 지연시간을 줄이거나 제거하면서 독립적인 파티션들 상에서 수행될 수 있다.
· 파티셔닝은 지명된 파티션을 제거함으로써 간단하게 오래된 행들을 제거해 준다. 동일한 수의 행들을 지우는 작업은 비실용적일 것이다.
· 어떤 쿼리를 파티션의 서브셋으로 제한하는 것이 가능하다. 따라서 전체 테이블 스캔에서 일어날 수 있는 오버헤드를 감소시킬 수 있다. 이것을 partition elimination이라고 부른다.
· 두개의 유사하게 파티션된 테이블을 조인하는 것은 병렬로 효과적으로 수행될 수 있다.
오라클은 세가지 파티셔닝 스키마를 제공한다.
· Range partitioning은 행들을 컬럼값에 기반한 파티션으로 할당한다. 이것은 전형적으로 예를 들어, 각 지역의 판매가 분리된 파티션에 위치되도록 하기 위해 판매 날짜상에 파티션을 잡기 위해 사용된다. 그러나, 그러한 파티션은 실질적으로 사이즈가 계속 변할 것이고 병렬 작업의 이점들을 감소시킨다.
· Hash partitioning은 행들을 컬럼값의 해시 계산 결과에 기반해서 파티션으로 할당한다. 이것은 보통 각각의 파티션에서 행들에 대해 균등하게 분포되도록 해주나 partition elimination과 오래된 데이터의 빠른 제거와 같은 몇몇 range partitioning의 이점을 제공해 주지 않는다.
· Composite partitioning은 데이터를 먼저 영역별로 파티션(range partitioning)하고 그리고 나서 해시에 의해 서브파티션으로 나눈다.(hash partitioning) 주의깊게 했다면, 이것은 range와 hash partitioning 스키마 둘다의 이점을 전달해 줄 수 있다.
HINT: CONSIDER PL/SQL FOR TRICKY SQL
SQL은 대부분의 데이터 검색과 조작 작업에 대해 가장 적합한 비절차적인 언어이다. 그러나, 절차적인 접근이 더 나은 결과를 가져다 주는 많은 환경들이 있다. 이러한 환경들에서 PL/SQL 언어는(혹은 Java) 표준 SQL 대신에 사용될 수 있다.
PL/SQL이 표준 SQL 대신에 사용될 수 있는 모든 상황을 철저하게 분류하는 것이 가능하지 않음에도 불구하고, PL/SQL은 다음의 상황에서 유효한 대안이 될 수 있다.
· 대량의 데이터를 반환할 필요성이 거의 없다.(예를 들어, UPDATE 트랜잭션 혹은 오직 단일값 혹은 행을 검색할 때)
· 표준 SQL은 논리적으로 필요한 것보다 보다 많은 리소스를 필요로 하고 어떤 힌트의 조합도 작용할 것 같지 않다. 이것은 특별히 몇몇 데이터의 암시적인 특성들이 있다면 옵티마이저가 이해할 수 없다거나 혹은 SQL이 특별히 복잡해질 것 같다는 의미이다.
· 여러분은 데이터가 어떻게 검색이 되어야 하고 처리되어야 하는지 명백한 생각을 가지고 있다. 그러나, 표준 SQL을 사용해서 여러분의 알고리듬을 실행에 옮길 수는 없다.
PL/SQL이 성능을 향상시키기 위해 발견된 특정 환경들중 몇몇은 다음과 같다.
· 두번째로 가장 높은 값 결정하기
· LOW_VALUE와 HIGH_COLUMN를 갖는 테이블에 대해 range lookup 수행하기
· SET절 내에서 서브쿼리를 갖는 UPDATE 문장의 WHERE절과 SET절내에서 동일 테이블이 참조되는 상호관련된 업데이트 수행하기
PL/SQL 트리거는 또한 비정규화(denormalization)를 수행할 때 큰 가치를 가질 수 있다.
SQL TUNING QUICK TIPS
CHAPTER 1: INTRODUCTION TO SQL TUNING
· SQL 튜닝은 거의 항상 비용 효과적인 작업이다. 이것은 비용적으로 하드웨어 업그레이드를 피하는 것과 어플리케이션의 확장성과 성능 향상을 도와준다.
· 대부분의 튜닝과 quality assurance process들처럼 SQL 튜닝은 비용이 덜 들고 어플리케이션 개발 라이프 사이클 내에서 처리되는 것이 보다 빠를수록 보다 큰 향상을 가져온다. 그러나, SQL 튜닝은 여전히 어떤 단계에서든 효과적인 수단이며 종종 프로덕션 시스템에 대해 사용가능한 오직 유용한 튜닝 방법이 될 수 있다.
· 경험과 좋은 테크닉이 SQL 튜닝 프로세스를 신속하게 할 수 있음에도 불구하고 SQL 튜닝은 최적의 SQL이 발견되기 전에 몇가지 향상된 점들이 테스트될 필요가 있는 반복적인 작업으로 남아 있다.
· 여러분의 SQL을 튜닝하기 위해 프로덕트한 환경에서 실행해 볼려고 노력하라. 여러분의 키테이블에 대해 실제적이고 객관적인 데이터 볼륨을 확보하라. 어플리케이션과 데이터베이스 디자인 정보가 가까이에 있고 튜닝 툴들을 사용가능하게 하라.
CHAPTER4: SQL PROCESSING INTERNALS
· Parsing은 비용이 들며 과도한 파싱은 확장성을 떨어뜨린다. 커서를 재사용하고 어플리케이션에서 바인드 변수를 사용함으로써 파스 오버헤드를 최소화 시켜라.
CHAPTER5: THE OPTIMIZER
· 비용기반 옵티마이저는 SQL의 성능을 향상시키기 위해 디자인된 많은 정교한 알고리듬을 포함하고 규직기반 옵티마이저에 비해 보다 더 뛰어나다. 모든 새로운 어플리케이션은 비용 기반 옵티마이저에 기반해 개발되어야 한다. 여전히 규칙기반 옵티마이저를 사용하고 있는 존재하는 어플리케이션들은 비용 기반 옵티마이저로의 마이그레이션을 고려해봐야 한다.
· 비용기반 옵티마이저를 사용할 때 정규적으로 옵티마이저 통계정보를 모으거나 혹은 테이블 볼륨이 언제 변결되었는지를 알아두라.
· DBMS_STATS는 ANALYZE 명령어보다 테이블 통계정보를 모으기 위해 보다 파워풀한 방법을 제공한다. 특히, 통계정보가 병렬로 모이도록 하라.
· 문장에 대한 이상적인 실행 플랜이 변경되지 않도록 저장된 아웃라인을 사용할 수 있다. 그러나, EXPLAIN PLAN은 이러한 저장된 실행 플랜을 반영하지는 않을 것이다.
· SQL 문장에 대해 실행 플랜을 변경하기 위해 힌트 혹은 옵티마이저 트릭을 사용할 수 있다. 그러나, 힌트는 보다 파워풀하고 자기 문서적(self-documenting)이다. 그리고 첫번째 대안으로써 사용되어야 한다.
CHAPTER 8 : TUNING TABLE ACCESS
Table scan versus indexed access
· 인덱스 스캔 억세스대 테이블 스캔 억세스에 대해 어떠한 “한 사이즈가 모든 것에 들어맞는다”는 손익분기점 같은 것은 없다. 단지 몇 개의 행들이 억세스 되고 있다면, 인덱스가 더 선호될 것이다. 거의 모든 행들이 억세스 되고 있다면, 전체 테이블 스캔이 선호될 것이다. 이러한 두가지 극단 사이에서 여러분의 “마일리지”는 변화할 것이다.
· 테이블을 분석하고, 올바른 옵티마이저를 지정하고, 적절한 곳에서 히즈토그램을 사용하고 힌트를 사용함으로써 인덱스와 테이블 스캔 사이에 비용기반 옵티마이저가 선택하는 것을 도와야 한다.
· 단지 몇 개의 구별되는 값들이 있으나 몇몇 값들은 매우 드문 인덱스가 걸린 컬럼들상에서 컬럼 히즈토그램을 생성하라. 히즈토그램은 바인드 변수를 통해서 쿼리로 제공되는 값을 검색하기 위해 사용될 수 없다는 것을 기억하라.
· 히즈토그램은 영역 스캔 혹은 뷸균등하게 분포된 데이터 검색의 최적화를 향상시킬 수 있다. 그러나, 바인트 변수는 보통 트랜잭션 프로세싱 환경에서 파싱을 감소시키기 위해 사용되어야 하고 히즈토그램은 바인드 변수와 함께 사용될 수 없다.
Effective use of index
· 오라클은 쿼리 조건이 “not equals”(!=)라면 인덱스를 사용할 수 없을 것이다. 쿼리가 인덱스의 접근으로 이익을 볼 수 있다고 생각한다면 쿼리를 IN, OR, 혹은 “>”를 사용해서 쿼리를 재작성 하라. 오라클이 적절한 인덱스를 사용하도록 하기 위해 힌트 혹은 컬럼 히즈토그램의 사용을 필요로 할지도 모른다.
· 인덱스가 걸린 컬럼에서 NULL값을 검색하는 것을 피하라. 대신에, 컬럼을 디폴트 값으로 NOT NULL로 정의한다.
· NOT NULL인 값들을 찾기 위해 인덱스를 사용할 수 있다. 대부분의 값들이 NULL이라면 NULL값들은 인덱스가 걸리지 않기 때문에 인덱스는 매우 작고 효율적일 것이다.
· 다른 무차별적인 인덱스에서 드문 값을 검색할 때 전체 테이블 스캔을 피하기 위해 힌트 혹은 컬럼 히즈토그램을 사용하라.
· WHERE절에서 인덱스가 걸린 컬럼들에 어떤 펑션 혹은 작업들을 적용하는 것을 피하라. 대신에, 인덱스가 걸린 컬럼들과 비교가 되고 있는 값들에 펑션을 적용하라.
· WHERE절에서 인덱스가 걸린 컬럼들에 펑션을 적용하는 것을 피할 수 없을 때 동일한 펑션에 기반한 functional index를 사용하는 것을 고려해 본다.
· 컬럼이 수천개의 뚜렷이 구별되는 많은 값들을 가지고 있을 때 비트맵 인덱스는 여전히 잘 작동한다. 그러나, 비트맵 인덱스에 대해 필요한 스토리지는 급속하게 올라갈 것이다.
· 인덱스로 구성된 테이블(Index Organized Table)은 모든 혹은 대부분의 쿼리가 프라이머리 키 상에서의 인덱스 스캔에 의해 수행되거나 혹은 데이터를 컬럼의 억세스 빈도로 쪼개고 싶은 테이블에 대해 이점을 제공해 줄 수 있다.
· 가능하다면, 연결된 인덱스(concatenated index)내에서 WHERE절에 포함되는 모든 컬럼들을 포함시킴으로써 쿼리를 최적화 시켜라.
· 조심스럽게 연결된 인덱스에서 최고의 컬럼 순서를 결정하라. 쿼리에 들어가는 컬럼들은 인덱스에서 첫번째 컬럼에 대해 좋은 후보들을 만들어 놓는다. 인덱스가 COMPRESS flag로 만들어진다면 중복성이 많은(low cardinality) 컬럼들을 먼저 위치시키는 것은 압축과 스캔 성능을 향상 시킬 것이다.
· 모든 쿼리에 적합하게 연결된 인덱스를 구성할 수 없다면, 인덱스 병합을 사용해 볼 수도 있을 것이다. 그러나, 거의 어떤 값들을 갖고 있지 않는 인덱스들은 그것들이 B-tree 인덱스라면 효율적으로 병합될 수 없다는 것에 주의하라.
· 선택적인 인덱스상에서 영역 스캔(보다 크던 작던)에 대해 바인드 변수의 사용은 인덱스를 촉진 시킬 것이다. 이것이 적절치 않다면, 힌트를 사용해서 그것을 변경해야 한다. 리터럴을 제공한다면, 옵티마이저는 보다 잘 알려진 결정을 수행할 것이다.(그러나 파스 오버헤드가 증가할 것이다.) 컬럼 히즈토그램은 또한 데이터가 최대값과 최소값 사이에 균등하게 분포되 있지 않다면 도움을 줄 것이다.
· Range lookups – “high” 그리고 “low” 값들을 포함하는 테이블에서 영역을 매칭시키는 – 은 표준 SQL로 성공적으로 최적화 시키는 데에 실패할 것이다. 이러한 경우에, PL/SQL 혹은 다른 절차적인 접근이 필요할 것이다.
· 오라클은 검색 스트링에서 leading wildcard(%,_)가 없는 경우에만 LIKE 연산자를 포함하는 쿼리를 해결하기 위해 인덱스를 사용할 수 있다.
· OR 조건을 포함하는 쿼리들은 비용기반 옵티마이저가 효과적으로 해결하기에는 어려울 수 있다. 때때로(항상은 아니지만) 히즈토그램이 도움을 줄 수 있다. 그렇지 않으면, FULL 혹은 USE_CONCAT 그리고 INDEX 힌트가 최고의 실행 플랜을 선택하기 위해 사용될 수 있다.
· 효율적인 인덱스들을 밝혀내기 위해 tkprof의 row count column을 사용하라. row count column에서 기대되는 값보다 더 큰 값은 연결된 인덱스에서 모든 컬럼들이 다 사용중이지는 않는다는 것을 가리킬 수도 있다.
· 인덱스에 있는 모든 행을 읽어들임으로써 해결될 수 있는 쿼리들에 대해 index fast full scan을 사용하라. 테이블에 있는 행들의 수를 카운트하는 것이 완벽한 예이다.
Optimizing bit map indexes
· 비트맵 인덱스의 성능은 때때로 BITMAP_MERGE_AREA_SIZE를 증가시키거나 혹은 근원적인 테이블에 MINIMIZE RECORDS_PER_BLOCK절을 적용함으로써 향상될 수 있다.
Optimizing hash clusters
· 정적인 테이블에 해시 클러스터만을 사용하거나 혹은 정기적으로 해시 클러스터를 재빌드할 준비를 하도록 하라.
Optimizing index organized tables
· index organized table을 쪼개는 방법에 대해 주의깊게 생각하라. 오버플로우 세그먼트에 있는 데이터가 억세스 하는데 보다 오랜 시간이 걸리는 반면에 인덱스 세그먼트에 있는 데이터는 억세스할 시 보다 빠를 것이다.
Optimizing table scans
· 과거보다 실질적으로 휠씬 적은 수의 행들을 포함하는 테이블들은 하이 워터 마크를 리셋하기 위해 재빌드를 필요로 할 것이다. 이것은 전체 테이블 스캔을 위해 필요한 블록의 수를 감소시킬 것이다.
· 테이블이 빈번한 테이블 스캔을 일으킨다면, 특별히 테이블이 업데이트 되지 않는다면, PCTFREE를 필요한 것보다 높이 잡지 않도록 하라. 또한 특별히 높은 동시적인 삽입 작업이 일어나지 않는다면 PCTUSED를 증가시키는 것을 고려하라.
· 전체 테이블 스캔이 크리티컬한 테이블에 대해, 분리된 테이블에 길고 가끔 억세스되는 컬럼들을 위치시키는 것을 고려해보라. LOBs과 CLOBs에 대해 DISABLE STORAGE IN ROW절을 고려해보자.
· 테이블 스캔은 보통 오라클 버퍼 캐시에서 좋은 히트율을 보이지 않는다. 테이블 스캔에 대해 더 좋은 히트율을 원한다면 CACHE절, CACHE 힌트를 사용하거나 혹은 multiple buffer cache를 이용함으로써 히트율을 향상시킬 수 있을 것이다.
· SAMPLE절은 일반적으로 전체 테이블 스캔을 필요로 하는 집합적인 쿼리에 대해 근사적인 해답을 얻기 위해 사용될 수 있다.
· 단일 콜로 데이터베이스로부터 일괄적인 행들을 검색하기 위해 array fetch를 사용하라. 이것은 데이터베이스와 네트웍 오버헤드 둘다를 감소시켜 줄 것이다. 일반적으로, array fetch는 대략 중요도의 순서(10배), bulk 쿼리에서의 향상을 제공해 준다.
· 작업량보다는 반응시간을 최적화 시키고 싶다면, FOR UPDATE와 ORDER BY절에 유의하라. – 이러한 것들은 첫번째 행에 대한 최적화를 어렵게 하거나 불가능하게 한다.
CHAPTER 9: TUNING JOINS AND SUBQUERIES
Choosing the best join method
· nested loop join 방법은 지원하는 인덱스가 있을 때 테이블 데이터의 서브셋을 포함하는 조인에 적합하다.
· hash-join 알고리듬은 거의 항상 sort-merge 알고리듬보다 휠씬 성능이 낫다. 그러나, 비용기반 옵티마이저는 종종 sort-merge 조인을 더 선호한다. 따라서 더 빠른 hash join을 이용하기 위해 USE_HASH를 이용할 필요가 있을 것이다.
· 비용기반 옵티마이저는 옵티마이저의 목적이 CHOOSE 혹은 ALL_ROWS로 셋팅되 있을 때 sort-merge 조인을 더 선호할 것이다. 일차적으로 반응 시간과 연관이 있다면, 옵티마이저 목표를 FIRST_ROWS로 셋팅하라. 그리고 나서 비용기반 옵티마이저는 nested loop join을 선호할 것이다.
Choosing the best join order
· 조인 순서를 결정하는데 있어 가장 중요한 고려사항은 가능하다면, 초기에 원하지 않는 행들을 제거하는 것이다. 이것을 넘어, 해시 조인과 nested loop 둘다 더 큰 테이블로부터 더 작은 테이블로 조인할 때 최고로 작용한다. sort-merge 조인은 더 크거나 혹은 더 작은 테이블들이 조인되는 순서에 의해 영향을 받지 않는다.
Optimizing hash joins
· HASH_AREA_SIZE 값의 증가는 해시 조인의 성능을 해시 테이블이 메모리에 들어맞는 포인트까지 향상시킬 것이다. HASH_IO_MULTIBLOCK_IO_COUNT를 증가시키는 것은 매우 큰 해시 조인이 위 파라미터의 중간 값으로 더 나은 수행을 함에도 불구하고 I/O 측면에서 해시 조인의 성능을 운영체제의 의존적인 한계까지 향상시켜 줄 것이다.
Clustering tables to improve join performance
· 테이블 클러스터링은 테이블이 조인되지만 테이블상에서 많은 다른 작업들의 성능을 저하시킬 때 성능을 향상시켜 줄 수 있다. 오직 테이블들이 거의 항상 함께 억세스될 때에만 테이블 클러스터링을 고려하자.
Special joins: outer, star, and hierarchical
· outer join 작업은 옵티마이저가 고려할 수 있는 조인 순서들을 제한한다. 불필요하게 outer join을 수행하지 않는다.
· 매우 큰 fact 테이블을 더 작은, 관련되지 않은 디멘젼 테이블로 조인할 때 오라클의 스타 쿼리 최적화를 고려해보자. fact 테이블상에 연결된 인덱스가 필요하고 STAR 힌트를 지정할 필요가 있을 것이다.
· CONNECT BY 연산자를 사용해서 계층(hierarchical) 쿼리를 수행할 때 STAR WITH와 CONNECT BY절 둘다 인덱스를 사용해서 쓰일 수 잇도록 한다.
· 계층의 일부분만을 선택할 때 WHERE절 보다는 STAR WITH절을 사용하는 행들을 제거한다. WHERE절은 전체적인 계층이 빌드된 이후에만 처리될 것이다.
Subqueries
· 상호 관련이 있는 서브쿼리에 대해, 서브쿼리가 완벽히 최적화 되도록 한다. 가능하다면, 서브쿼리가 테이블 억세스 없이 직접적인 인덱스 룩업에 의해 사용될 수 있도록 한다.
· 상호 관련이 있는 서브쿼리는 보통 절차적인 접근을 사용해서 보다 효율적으로 실행될 수 있다. – 아마도 PL/SQL을 사용해서.
· EXISTS를 사용할 때, 서브쿼리가 보다 효율적으로 실행되거나 혹은 세미조인 최적화를 사용하도록 한다. 이상적으로, 서브쿼리는 인덱스 룩업만을 사용해서 만족될 수 있도록 해야 한다.
Semijoins and Antijoins
· 지원하는 인덱스 없이 EXISTS 서브쿼리를 가지고 있다면, 세미조인으로 쿼리를 사용하기 위해 HASH_SJ 혹은 MERGE_SJ 힌트를 사용한다. 또한 ALWAYS_SEMI_JOIN 환경 파라미터를 사용할 수 있으나, 이것은 몇몇 인덱스가 걸린 EXISTS 서브쿼리에 대해 약간의 네거티브 효과를 가질 수도 있다.
· 룰기반의 최적화를 사용할 때 안티조인을 수행하기 위해 NOT IN의 사용을 피한다. 대신 NOT EXISTS를 사용한다.
· MINUS 연산자는 안티조인을 효과적으로 수행하기 위해 사용될 수 있다. 그러나, 쿼리에서 반환될 수 있는 컬럼들 상에 제약들이 있다.
· 다른 테이블에서 발견되지 않는 것들을 제외하고(안티조인) 테이블에서 모든 행들을 검색하길 원할 때 오라클의 안티조인 힌트를 이용하길 바란다. 그렇지 않다면(룰기반의 옵티마이저 혹은 오라클 7.3 이전 버전을 사용한다면) NOT IN 서브쿼리의 참조로 NOT EXISTS 서브쿼리를 사용한다.
CHAPTER 10: SORTS, AGGREGATES, AND SET OPERATIONS
Sorting
· SORT_AREA_SIZE의 증가는 디스크 정렬이 발생할 가능성을 줄여주고 – 어떤 영역의 값들과 함께 – 메모리 정렬의 성능을 향상시켜 줄 수 있다.
· 더 낮은 SORT_AREA_RETAINED_SIZE 파라미터의 셋팅은 정렬 메모리가 운영체제로 풀려나지 않도록 한다. SORT_AREA_SIZE에 대한 셋팅이 시스템의 메모리 용량을 초과하지 않도록 한다.
· 디스크 정렬이 발생한다면, 템포러리 테이블스페이스와 데이터베이스의 환경은 디스크 정렬에 대한 경합이 발생하지 않도록 하기 위해 크리티컬해 질 수 있다.
· 비용기반 옵티마이저는 optimizer_mode=FIRST_ROWS라면 정렬을 피하기 위해 노력할 것이다.
· DISTINCT 연산자가 필요하지 않다면 사용하지 않는다. DISTINCT는 보통 정렬을 수행할 것이다. 실제로 중복값들을 제거할 필요가 없다면 UNION에 대한 참조로 UNION ALL을 사용한다.
· 정렬을 피하기 위해 인덱스를 사용하는 것은 일반적으로 순서대로 행을 검색할 때 더 나은 반응시간(첫번째 행을 검색하는 시간)을 유도할 것이다. 그러나 그러한 계획은 보통 휠씬 더 안좋은 작업처리량(모든 행들을 검색하는 시간)을 유발할 것이다.
· 전체 테이블 스캔에 기반한 정렬을 수행할 때 스캔과 정렬 성능을 향상 시키기 위해 병렬 쿼리 옵션의 사용을 고려해보자. 그러나 증가되는 시스템 오버헤드에는 주의한다.
Aggregate functions
· 컬럼에 대한 최대 혹은 최소 값을 구하기 위한 가장 빠른 방법은 그러한 컬럼상에 B-tree 인덱스를 거는 것이다.
· 오라클 8.1.6 analytic function은 대다수의 공통적인 비즈니스와 analytic 쿼리로의 절차적 혹은 비용이 드는 셀프조인 솔루션을 피하는 것을 도와줄 수 있다.
· GROUP BY를 최적화 하기 위해 인덱스를 사용한다면, 인덱스 전체 스캔(an index full scan) 솔루션이 더 나은 반응 시간을 보여 주는 반면에, 빠른 전체 인덱스 스캔(a fast full-index scan) 솔루션은 아마도 더 나은 작업처리량을 보여 줄 것이다.
· 가능하다면, 행들이 그룹핑 되기 전에 행들을 제거하기 위해 HAVING절의 위치에 WHERE절을 사용한다. 그룹 펑션을 사용할 때에만 HAVING절을 사용한다.
Set operations
· UNION 연산자에서 중복행들을 제거할 필요가 없다면, UNION 대신에 UNION ALL을 사용한다. 이것은 잠재적으로 비용이 드는 정렬을 피할 것이다.
· intersect를 수행할 때, 또한 nested loop 혹은 hash-join method를 사용하는 조인으로 문장을 재코딩 하는 것을 고려한다.
· MINUS 작업을 수행할 때, HASH_AJ 힌트를 사용해서 ANTI-JOIN으로 문장을 재코딩 하는 것을 고려한다.
CHAPTER 11: PARALLEL SQL
· 병렬 처리는 머신 환경이 적당할 때에만 도움을 줄 수 있다. 하드웨어의 성능을 넘어서 병렬 연산을 증가 시키는 것은 실제적으로 성능에 악영향을 끼칠 수 있다.
· SQL에서 각 단계의 병렬연산을 결정하기 위해 PLAN_TABLE의 OTHER_TAG 컬럼을 사용한다. 이러한 컬럼을 잘 이용하는 툴을 사용해서 SQL을 EXPLAIN한다. 심각한 병목현상을 유발할 수 있는 PARALLEL_FROM_SERIAL tag에 대해 유의한다.
· 마지막 병렬 쿼리에서 사용된 실제적인 병렬연산 정도를 결정하고 병렬 연산 슬레이브들 상에서 좋은 로드 밸런스가 있도록 하기 위해 V$PQ_TQSTAT 테이블을 쿼리할 수 있다.
· 병렬 쿼리를 위해 데이터베이스를 환경설정 한다. 데이터가 다양한 디스크 디바이스들 상에 분산 되도록 하고 충분한 CPU와 메모리를 확보하고 병렬 환경 파라미터가 제대로 셋팅되 있도록 한다.
· 호스트 컴퓨터가 적절하게 설정되지 않았거나 혹은 다른 유저들의 성능을 심각하게 감소시킬 것 같다면 병렬 쿼리를 사용하지 않는다.
· 다른 유저들에게 허용할 수 없는 영향을 주지 않고 작업 처리량을 최대화 할 수 있는 쿼리를 위해 병렬연산의 정도를 셋팅한다.
· 병렬 쿼리에 포함된 테이블 상에서 정규적으로 ANALYZE 명령어를 사용한다.
· 복잡한 쿼리에서 모든 쿼리의 실행 단계들을 병렬화 하기 위해 노력한다. 연속으로 처리되는 실행 단계에 초점을 맞추기 위해 플랜 테이블의 OTHER_TAG 컬럼을 사용한다. NOPARALLEL로 셋팅되 있으나 디폴트로 병렬인 테이블로 조인되는 테이블들을 주의한다.
· INTERSECT와 MINUS 연산은 병렬작업을 하지 않는다. – 이러한 작업을 병렬화 하고자 한다면동등(equivalent) 조인과 안티조인 같은 선택작업을 사용한다.
CHAPTER 12: OPTIMIZING DML
· DML 문장이 WHERE절 혹은 서브쿼리를 포함하고 있다면, 서브쿼리 혹은 WHERE절이 표준 쿼리 최적화 기법을 사용해서 최적화 되도록 한다.
· 인덱스는 항상 INSERT와 DELETE 문장의 오버헤드를 동반하고 UPDATE 문장의 오버헤드를 수반할른지도 모른다. 특별히, 빈번하게 업데이트되는 컬럼들 상에서의 과도한 인덱싱을 피한다.
· 비트맵 인덱스는 특히, 컬럼에 대해 많은 뚜렷이 구별되는 값들이 있다면 B-tree 인덱스보다 많은 유지 보수상의 오버헤드를 보통 가지고 있다. 비트맵 인덱스는 또한 동시성의 DML 작업이 있을 때 휠씬 더 큰 락 오버헤드를 수반한다.
· 테이블로부터 모든 행들을 제거할 때, DELETE 대신에 TRUNCATE를 사용한다.
· 큰 테이블로부터 그다지 쓰이지 않는 행들의 삭제가 심각한 오버헤드를 일으킨다면, 행들이 제거되도록 지정하기 위해 사용되는 데이트 컬럼상에서 영역적으로 파티션되는 테이블의 생성을 고려한다. 그리고 나서 해당되는 파티션을 제거함으로써 이러한 행들을 제거할 수 있다.
· 데이터에서 자기 일관성을 유지하기 위해 참조 제약 조건을 사용한다. 그러나, DML 문장에 대한 성능 영향에 대해 주의한다. 비용상 전체 테이블 락을 피하기 위해 외래키 컬럼 상에서 인덱스를 갖도록 한다.
· 대량의 인서트 성능을 향상시키기 위해 가능하다면 배열 인서트 기능을 사용한다.
· 버퍼 캐시에서 블록에 대한 경합이 있거나 혹은 파티션 되지 않은 테이블로 병렬로 인서트를 하고 싶을 때 – APPEND 힌트를 사용해서 – direct mode insert를 고려하자.
· 인서트 작업에 대한 redo log 오버헤드를 감소시키기 위해 UNLOGGED를 사용할 수 있다. 그러나 데이터베이스가 복구를 필요로 하는 이벤트에서 포함된 객체들이 복구될 수 있도록 특별한 노력을 기울일 필요가 있을 것이다.
· 많은 동시성의 인서트 작업이 예상되는 테이블에 대해 다양한 freelist들을 생성하기 위해 CREATE TABLE문장에 FREELISTS절을 사용한다.
· 분리된 트랜잭션들은 매우 특별한 환경에서 어떤 작은 트랜잭션의 성능을 향상시킬 수 있다. 그러나, 그러한 것들은 긴 시간이 걸리는 쿼리의 성공적인 실행을 가로막을 수 있다.
· 트랜잭션의 커밋은 I/O 오버헤드를 포함하기 때문에 대규모 업데이트 동안에는 자주 커밋을 하지 않는다.
· 트랜잭션을 정확히 커밋하고, 락이 컬려 있는 동안 유저의 상호작용을 피하면서 주의깊게 락에 대한 전략을 고려함으로써 락에 대한 경합을 최소화 하도록 한다.
CHAPTER 13: VLDB AND WAREHOUSING
Partitioning
· 뷰의 파티셔닝은 인덱스를 사용하려 하는데 검색할려고 하는 행의 퍼센티지가 너무 높다면 유용할 수 있다.
· 일반적으로, 로컬(파티션된) 인덱스들은 파티셔닝의 최대 이점을 놓칠 수 있다. 파티션된 테이블상에서는 글로벌 인덱스를 피한다.
· 영역 쿼리를 위한 partition elimination를 이용한다거나 혹은 빠르게 파티션을 제거함으로써 historical 데이터를 제거하고 싶을 때 영역 파티셔닝을 고려하자.
· 파티션들 사이에 행들의 균형이 파티션을 제거함으로써 데이터를 제거하는 partition elimination보다 중요할 때 해시 파티셔닝을 고려하자.
· 영역 파티셔닝이 필요하지만 각 파티션 사이즈에서의 좋은 균형을 원한다면 해시(복합 파티셔닝)에 의한 서브파티셔닝을 고려하자. 아마도 좋은 균형을 이루기 위해 서브파티션들의 수를 조정할 필요가 있을 것이다.
Snapshots and materialized views
· 결과가 전체적으로 동시적일 필요가 없는 큰 테이블상에서 복합 쿼리를 이용하기 위해 스냅샷의 사용을 고려하자.
· 소스 테이블에서 소수의 행들이 변경될 때 스냅샷 로그와 빠른 리프레시 메커니즘을 사용하자. 다수의 행들이 변경된다면, 스냅샷 로그의 오버헤드를 피하고 완벽한 리프레시를 사용한다. 그리고 그것은 어느 면에서 “빠른” 리프레시보다 더 빠를것이다.
· Query rewrite은 QUERY_REWRITE_ENABLED 파라미터가 TRUE이고 유저가 QUERY REWRITE 권한을 가지고 있을 때에만 발생할 것이다.
· ON COMMIT절이 사용되지 않는다면 materialized view의 오버헤드와 잘못된 결과의 위험 때문에 query rewrite과 함께 하는 materialized view는 실제로 데이터 웨어하우스와 같은 상대적으로 정적인 환경에 적합하다.
CHAPTER 14: USING AND TUNING PL/SQL
Uses of PL/SQL
· 테이블을 비정규화 하기 위해 PL/SQL의 사용을 고려해보자. PL/SQL 트리거는 자동화 시킬 수 있고 비정규화의 효율을 향상시킬 수 있다.
· PL/SQL은 특별히 조인 쿼리에 기반한 상호 관계를 갖는 UPDATE 혹은 UPDATE의 경우에, UPDATE와 DELETE 트랜잭션에 실질적인 향상을 제공할 수 있다.
PL/SQL code tuning
· 가능하다면, PL/SQL 루프의 반복을 줄인다. 각 루프는 CPU를 소모한다. 따라서 계속할 필요가 없다면 루프를 빠져나간다. 또한 가능하다면 루프의 외부로 “loop invariant” 문장을 이동시킴으로써 루프내에 프로세싱을 감소시킨다.
· IF 문장이 반복적으로 실행된다면 IF 구조에서 보다 초기에 가장 공통적으로 만족되는 조건을 위치 시키는 것은 성능을 최적화 시킬 것이다.
· recursive programming을 피한다. Iterative solution은 거의 항상 recursive solution보다 성능이 낫다.
Efficient database access with PL/SQL
· 많은 수의 행들을 처리하거나 쿼리할 때 FORALL과 BULK CONNECT절을 사용해서 오라클8i의 배열 프로세싱을 이용한다.
· 때때로 병렬 SQL 문장으로 PL/SQL 펑션들을 임베딩 함으로써 병렬 실행을 달성하는 것이 가능하다.
· 커서내에서 검색했던 행을 수정하고자 할 때 WHERE CURRENT OF절을 사용하거나 혹은 ROWID를 저장해서 사용한다.
· DML문에 의해 처리되는 행들상에서 리포트할 때 RETURNING INTO절을 사용한다.
· 펑션 혹은 프로시져로 아규먼트로써 큰 PL/SQL 테이블들을 넘겨줄 때 항상 NOCOPY절을 고려한다.
· 정렬되는 서브프로그램의 동적인 재컴파일을 줄이기 위해 PL/SQL 패키지를 사용하자. 공유 풀에 사이즈가 크거나 혹은 성능상의 크리티컬한 패키지들을 고정시키는 것을 고려하자.
Triggers
· 트리거가 필요할 때만 실행되도록 하기 위해 CREATE TRIGGER 문장에 대해 OF
COLUMNS와 WHEN절을 사용한다.
· 불필요하게 FOR EACH ROW 트리거를 사용하지 않는다. FOR EACH ROW 트리거를 사용한다면, BEFORE 트리거 대신에 AFTER 트리거를 사용한다.
General improvements
· 특별히 쿼리가 전체 테이블 스캔을 내포한다면, PL/SQL에 임베드되는 SELECT 문장에 의해 생성되는 암시적인 커서 대신에 명시적인 커서를 사용한다.
· 자주 억세스되는 값들을 캐시하기 위해 PL/SQL 테이블을 사용하는 것은 현저하게 성능을 향상시킬 수 있다.
· PL/SQL 프로그램이 트랜잭션의 세션을 넘어서 남아있을 필요가 없는 데이터를 저장할 필요가 있을 때 temporary table을 사용하자.
Dynamic SQL with PL/SQL
· DBMS_SQL을 사용한다면, 바인드 변수와 어레이 프로세싱을 사용하도록 한다.
· 가능하다면, EXECUTE IMMEDIATE에서 USING절과 함께 바인드 변수를 사용하도록 한다.
· Dynamic SQL이 사용하기에 보다 복잡함에도 불구하고 DBMS_SQL을 사용한 Dynamic SQL은 종종 EXECUTE IMMEDIATE을 사용해서 실행되는 Dynamic SQL보다 성능이 더 낫다.
· PL/SQL 코드에서 “hot spots”을 지정하기 위해 DBMS_PROFILER 패키지를 사용한다.
CHAPTER 15: USING AND TUNING ORACLE JAVA
PL/SQL versus JAVA
· 특별히 부동 소수점 연산을 포함하는, 비용이 드는 연산 작업에 대해서는 PL/SQL 스토어드 프로시져 대신에 자바 스토어드 프로시져를 사용한다.
· PL/SQL 스토어드 프로시져는 전형적으로 데이터베이스 집중적인 루틴들에 대해 JDBC 스토어드 프로시져에 비해 성능이 휠씬 나을 것이다.
· 성능이 유일한 관점이라면, 연산 집중적인 루틴들에 대해서는 자바 스토어드 프로시져를, 데이터베이스 집중적인 루틴들에 대해서는 PL/SQL을 사용한다.
Optimizing JDBC
· 반복적으로 실행되는 문장에 대해 바인드 변수와 함께 PreparedStatements를 항상 사용한다.
· 여러 번 PreparedStatements를 재실행할 것을 알고 있다면 그것을 공용 객체(public object)로 선언하고 마지막 실행 이후까지 close() 메소드를 사용하지 않는다.
· 스토어드 프로시져 외부에서 실행되는 JDBC 프로그램에 대해 setAutoCommit 메소드로 오토커밋 기능을 무효화 시킨다.
· 다양한 행들을 가지고 오거나 혹은 수정할 때 오라클의 어레이 익스텐션인
setDefaultRowPrefetch와 setExecuteBatch를 항상 사용한다.
· 오라클의 JDBC 익스텐션의 사용은 – 오라클 데이터 타입 클래스와 defineColumnType 메소드를 포함해서 – 결과는 작지만 중요한 성능상의 향상을 가져온다.
Optimizing SQLJ
· SQLJ 문장 캐시의 사이즈를 어플리케이션에 적절하게 잡도록 한다.
· 대량의 인서트 혹은 다른 DML의 성능을 향상시키기 위해 SQLJ 배칭을 사용하도록 한다. 이러한 기능은 8.1.6에서만 사용가능 하다.
CHAPTER 16: ORACLE OBJECT TYPES
VARRAYs, Nested Tables and Object types
· In-line VARRAYs는 결정적으로 행의 길이를 증가시키고 전체 스캔의 성능을 감소시킨다. 전체 스캔 성능이 크리티컬 하다면, 라인 외부에 VARRAY를 저장한다. 다른 객체 솔루션(nested tables, object tables) 또한 미세하게 행의 길이를 증가시키고 스캔 성능을 감소시킨다.
· 큰 사이즈를 갖는 nested table을 사용할 때 nested 테이블 세그먼트의 (최소한) NESTED_TABLE_ID 컬럼 상에 인덱스의 생성을 강력히 고려해본다.
· nested table은 부모 행들을 통한 “nested” 데이터로의 억세스를 매우 비효율적으로 만든다. VARRAYs는 다양한 행들을 통한 독립적인 요소들에 억세스를 시도할 때 유사한 단점을 가지고 있다.
· REF 컬럼으로부터 참조되는 객체행으로 효과적인 네비개이션을 필요로 한다면 REF 컬럼에 컬럼의 ROWID를 저장하기 위해 WITH ROWID절의 사용을 고려하자.
· 오라클8i에서 인덱스로 구성된 테이블 세그먼트에서 nested 테이블을 생성하는 것은 거의 항상 최고의 선택이다. 이것은 성능과 스토리지 둘다를 향상시킨다.
· EXPLAIN PLAN은 SQL 문장을 튜닝하기 위한 가장 기존적인 툴이다. 그러나, 이것은 객체와 nested 테이블 억세스에 관한 불충분한 정보를 제공할 수 있다.
LOBS and LONGS
· LOBs은 분리된 세그먼트에 저장될 수 있기 때문에 그들은 전체 테이블 스캔의 성능에 거의 아무런 영향을 미치지 않을 것이다.
· CACHE 셋팅의 사용은 캐시에 저장된 다른 데이터의 비용에도 불구하고 LOB 검색 시간을 향상시킨다.
· LOB 타입들(CLOB, BLOB, BFILE)은 데이터의 어떤 부분에도 효과적인 랜덤 억세스를 허용한다. 반면에 LONG 데이터 타입은 전체적인 구조를 항상 읽어들어야 한다.
CHAPTER 17: MISCELLANEOUS TOPICS
Views
· 어떠한 컨트롤도 갖지 않는 SQL을 생성하는 서드 파티 쿼리 혹은 개발툴에 의해 만들어지는 SQL에 영향을 주기 위해 뷰에서 임베드되는 힌트들
Distributed SQL
· 원격 노드에서 테이블 조인의 뷰를 생성하는 것은 드라이빙 사이트에서 보다는 원격 노드에서 조인이 실행되도록 할 수 있다. 이것은 조건만 적절하다면 성능을 향상시켜 줄 것이다.
· 주의깊게 분산된 SQL을 위해 드라이빙 사이트를 선택한다. 이상적인 드라이빙 사이트는 가장 강력한 처리 능력, 대부분의 로컬 데이터, 그리고 가장 최근의 오라클 버전을 갖는 사이트다.
Sequences
· 시퀀스를 생성할 때 시퀀스가 억세스될 빈도를 반영하는 캐시를 지정한다. 병렬서버 환경에 있지 않다면 ORDER 옵션을 지정하지 않는다.
· 어떠한 유니크 키값도 스킵되지 않는 확실한 필수요소가 없다면 시퀀스 테이블 대신에 시퀀스 생성자를 사용한다. 시퀀스 생성자를 사용할 때 바쁜 트랜잭션 처리 환경에서 CACHE 값이 적절하도록 하고 배치성으로 시퀀스 넘버를 가져오는 것을 고려하자.
DECODE
· GROUP BY절을 위해 너무 복잡한 표현들에 대해 모인 통계들을 컴파일 하기 위해 DECODE의 사용을 고려한다. SIGN 펑션을 사용함으로써 영역들을 모을 수 있다.
Optimizing DML
· temporary 테이블 혹은 인덱스를 생성할 때 NOLOGGING 옵션의 사용을 고려한다. NOLOGGING 옵션으로 생성된 객체들이 백업에 포함될 때까지 이러한 객체들이 복귀될 수 없다는 것에 주의하자.
· parallel과 unlogged 옵션을 결합해서 사용하는 것은 서브쿼리와 인덱스로부터 테이블을 생성하기 위한 가장 빠른 방법이다.
Optimizing V$ queries
· V$ 테이블상의 인덱스는 정확한 룩업을 위해서만 사용될 수 있고 펑션이 또한 동등 조건의 측면으로 적용된다면 무효화 될 것이다.
· V$ 테이블상에는 어떠한 통계정보도 없기 때문에 옵티마이저는 더 나은 조인 순서 혹은 방법을 결정할 수 없다.
CHAPTER 18: APPLICATION DESIGN ISSUES
· 성능 튜닝을 데이터 모델링과 어플리케이션 디자인 프로세스로 빌드한다. 시스템 요구 스펙에서 성능 요구사항들을 정의하고 프로토타입 트랜잭션을 사용함으로써 좀 더 일찍 빌드하는 동안 성능을 측정한다.
Logical to physical modeling
· 논리적인 모델의 원투원 표현인 물리적인 모델을 생성하지 않는다. 어플리케이션이 그것의 잠재적인 전체 성능에 도달하도록 허용하는 물리적인 모델을 빌드하기 위한 시간을 갖는다. 물리적인 모델링을 하는 동안 쓰인 시간은 프로덕션 튜닝을 하는 동안 여러 번 재지불 될 것임을 기억한다.
· 서브타입으로부터 유도된 테이블들을 이용할 때 수퍼타입과 서브타입의 테이블 둘다의 사용을 피한다. 대신에, 모든 서브타입에 대해서는 싱글테이블 혹은 수퍼타입 테이블 없는 다양한 서브테이블을 사용한다.
· 가능하다면, 연결된 혹은 숫자가 아닌 컬럼들로 이루어져 있는 자연키 대신에 시퀀스에 의해 생성된, 인위적인 숫자키를 사용한다.
· row chaining의 위험이 과도하지 않다면 행의 길이를 줄이고 테이블 스캔을 최적화 하기 위해 CHARs 대신에 VARCHAR2s를 사용한다.
· 전체적으로 이득과 제한 사항들을 고려하지 않고 선택적인 스토리지 옵션을 고려하지 않는다면, 디자인에서 LONG과 LOB 데이터타입을 사용하지 않는다. LOBs은 일반적으로 LONG 데이터 대신에 사용되어야 한다.
· 널값을 찾는 쿼리를 구성한다면 컬럼을 널값으로 정의하지 않는다. 대신에, 디폴트로 컬럼을 NOT NULL로 정의한다.
Denormalization
· 크리티컬 쿼리에서 과도한 조인을 피하기 위해 컬럼 복제를 고려하자. 이것은 비정규화된 데이터가 정적인 룩업 테이블에 저장될 때 매유 효과적일 수 있다.
· 결합된 작업을 수행하는 쿼리들은 리소스를 매우 많이 소모할 수 있다. 가능하다면 materialized view를 사용함으로써 비정규화된 결합된 정보의 보유를 고려하자.
· 유도된 값들상에서 인덱스된 검색을 수행하고자 한다면 여분의 컬럼들을 유도된 데이터로 유지한다. 오라클8i에서 펑셔널 인덱스는 보다 효율적인 접근이 아니라는 것을 확인한다.
· 큰 테이블이 빈번한 테이블 스캔을 일으킨다면, 행의 길이를 감소시키고 테이블 스캔 성능을 향상시키기 위해 길고 가끔 억세스 되는 컬럼들을 분리된 서브테이블로 옮기는 것을 고려하자.
· 어플리케이션 코드 대신에 비정규화된 데이터를 유지하기 위해 데이터베이스 트리거를 사용한다. 데이터베이스 트리거는 비일관적이고 비정규화된 데이터의 위험을 감소시키고, 어플리케이션 코드를 단순화 시키며 종종 보다 효율적으로 수행될 것이다.
Application design
· SQL_TRACE를 어플리케이션에서 가능하도록 하기 위한 기능을 빌드한다. 더미 SQL 문장을 사용함으로써 트레이스 파일에 표시를 한다.
· 어플리케이션으로 크리티컬한 성능상의 지표를 리포트 하기 위한 기능을 빌드한다. 다양한 서브루틴에서 사용된 시간을 결정하기 위해 프로파일링 하는 툴의 사용을 고려하자.
· 어플리케이션에 대해 올바른 락킹 전략을 선택한다. 가능하다면, 낙관적인 락킹 전략을 이용한다. 그리고 그것은 락의 지속성을 감소시키는 경향이 있다.
· 작은 혹은 중간 사이즈의 정적인 테이블로부터 자주 억세스되는 데이터를 캐싱하는 것은 프로그램의 성능을 향상시킨다는 점에서 매우 효과적일 수 있다. 그러나, 메모리 사용과 프로그램의 복잡성의 문제들에 대해서는 주의한다.
· 클라이언트 기반의 처리와 서버 기반의 PL/SQL 스토어드 프로그램 사이에 어플리케이션 처리의 분할을 조심스럽게 고려하자. 각각의 트랜잭션과 클라이언트와 서버 하드웨어의 상대적인 파워에 의해 필요한 유저 상호작용과 데이터베이스 프로세싱의 레벨을 기억한다.
· 긴 시간이 걸리는 배치잡들을 병렬로 수행함으로써 사용가능한 프로세싱 파워를 이용할 수 있도록 한다.
CHAPTER 19: ORACLE SERVER DESIGN
Disk Requirements
· 데이터베이스에서 사용가능한 디스크 디바이스의 수는 달성될 수 있는 최대 I/O 레이트를 결정한다. 적당한 I/O 레이트를 계산하고 어플리케이션에 의해 필요한 디스크 디바이스의 수를 추정하기 위해 이러한 레이트를 사용하도록 한다. Redo Logs는 중요한 업데이트 행위가 있다면 전용으로 사용하는 디바이스상에 있어야 한다.
· RAID 5 베이스의 솔루션을 고려하고 있다면 그다지 변화가 없는 캐시로 설정되 있는 RAID 어레이로 우위를 준다. 그러한 캐시는 RAID 5와 관련되 있는 WRITE I/O 오버헤드를 감소시켜 줄 수 있다.
Multiple CPUs
· 멀티 CPU 시스템의 확장성의 결함 때문에 파워가 약한 많은 수의 CPU를 갖는 것보다 적은 수의 보다 파워풀한 CPU를 갖는 것이 휠씬 낫다. CPU의 수를 두배로 하는 것이 처리 용량을 두배로 늘려줄 거라고 생각지 말자.
Physical database layout
· 데이터베이스 블록 사이즈가 최소한 운영체제의 블록 사이즈만큼 되도록 한다.
· 적절하게 버퍼캐시를 사이즈함으로써 디스크 read 오버헤드를 줄인다.
· 트랜잭션 처리 성능을 최대화 하기 위해 빠른 전용 디스크 디바이스에 redo log를 위치 시킨다.
· 아카이브로그 모드와 높은 업데이트 환경에서 구동되고 있다면, redo log를 위해 추가적인 전용 디바이스와 아카이브 디렉토리를 위해 다른 전용 디바이스를 할당한다.
· 데이터베이스 파일을 위해 몇가지 스트라이핑 방식을 사용한다. 그러나 write 작업이 매우 낮거나 혹은 디스크 어레이가 별로 변화가 없는 캐시를 가지고 있지 않다면 RAID 5를 피한다. 오라클 스트라이핑은 운영하고 있는 스트라이핑이 사용할 수 없을 때 사용할 수 있다.
· 유닉스 혹은 윈도우 운영체제 하에서 매우 높은 I/O 요구를 갖는 데이터베이스에 대해 RAW 디바이스의 사용을 고려하자.
· 다양한 디바이스에 걸쳐 데이터베이스 파일을 스트라이핑 하고 병렬 데이터베이스의 몇가지 폼들이 용량을 쓰도록 함으로써 DBWR의 성능을 최적화 시킨다. 비동기식의 혹은 리스트 I/O가 보다 더 선호된다. 이러한 기능을 사용할 수 없다면, DBWR_IO_SLAVES 파라미터로 다양한 DBWR를 생성한다.
· TEMPORARY 세그먼트를 갖고 있는 테이블스페이스가 CREATE TABLESPACE의 TEMPORARY절로 생성되도록 한다.
CHAPTER 20: ORACLE SERVER TUNING
Operating system bottlenecks
· 부족한 자유 메모리 공간, 스와핑, 혹은 과도한 페이징은 메모리의 병목현상을 일으킨다. 보다 많은 메모리를 확보하거나 혹은 메모리 요구량을 감소시키기 위한 액션을 취한다.
· 어떠한 디스크 디비이스도 시스템에 대한 병목현상을 일으키지 않도록 한다. 다양한 디바이스상에 데이터 파일을 분산시키고 redo log는 빠른 전용 디바이스에 위치시키도록 한다.
· 어플리케이션이 CPU 집중적이라면 SQL을 튜닝하거나 불필요한 파싱을 제거함으로써 CPU 로드의 감소를 고려하자. CPU를 추가한다면 부가적인 CPU의 이점이 추가된 CPU의 수가 증가하는 것 만큼 줄어든다는 점을 기억하자.
Critical performance ratios
· 버퍼캐시 히트율을 향상시키기 위해, DB_BLOCK_BUFFERS 환경 파라미터로 버퍼캐시 블록의 수를 증가시킨다.
· 라이브러리 캐시에서 get hit rate를 증가시키기 위해, 리터럴 보다는 바인드 변수가 SQL 문장에 사용되도록 한다. 컬럼 히즈토그램의 기능 억제에 대해 주의하자.
· 라이브러리 캐시 pin 히트율(<99%) 혹은 딕셔너리 캐시 히트율(<95%)에 대한 낮은 히트율은 아마도 공유 풀이 너무 작다는 것을 가리킨다. SHARED_POOL 환경 파라미터로 공유풀의 사이즈를 증가시킨다.
· chained fetch ratio에 대한 높은 값(>0.1%)은 더 높은 PCTFREE의 값으로 테이블을 재빌드할 필요성을 제시한다.
· parse/execute ratio와 CPU parse 오버헤드 둘다가 높다면, 어플리케이션의 파스 오버헤드를 줄이기 위해 강력한 유인책을 취할 필요가 있다. 바인드 변수를 사용하고, SQL 커서를 재사용하고, 혹은 세션 커서 캐시를 활성화 시키는 방안을 고려해 볼 수 있다.
· 높은 디스크 정렬율이 발생한다면 SORT_AREA_SIZE의 값을 증가시키는 것을 고려하자.
Analyzing wait events
· Log buffer space waits는 로그버퍼가 너무 작거나 혹은 redo log 파일의 레이아웃이 최적화 상태가 아니라는 것을 가리킨다.
· 체크포인트, 아카이빙, 혹은 완료에 대한 Log file switch waits는 redo log와 archive log 환경이 개선을 필요로 한다는 것을 가리킨다.
· Buffer busy waits는 보통 대량으로 인서트되는 테이블이 다양한 free list를 갖도록 재생성 시켜야 하거나 혹은 충분치 않은 롤백 세그먼트가 있다는 것을 가리킨다.
· Free buffer and write complete waits는 보통 DBWR 혹은 튜닝이 되지 않은 디스크 I/O에서의 비효율성을 가리킨다.
· Enqueue waits는 어떤 프로세스가 락을 얻기 위해 기다리고 있을 때 발생한다. 이것은 데이터베이스에서 특정 행에 대한 경합, 인덱스가 걸려 있지 않는 외래키로 인한 테이블락, 혹은 오라클 인터널 락에 대한 경합을 의미한다.
Latches
· cache buffer lru chain과 cache buffer chain latches에 대한 경합은 데이터베이스가 매우 높은 물리적 혹은 논리적인 I/O 레이트를 갖는 로드를 받고 있다면 발생한다. SQL을 튜닝하거나 혹은 버퍼 캐시의 사이즈를 증가시킴으로써 I/O 레이트를 감소시킨다. DB_BLOCK_LRU_LATCHES의 값을 증가시키는 것이 도움을 줄 것이다.
· library cache와 library cache pin latches에 대한 경합은 heavy parsing 혹은 높은 SQL execution rates가 있을 때 발생할 수 있다. 라이브러리 캐시 래치상에서의 misses는 보통 공유하지 않는 SQL에 대한 과도한 재파싱의 결과다.
· redo copy latch상에서의 높은 미스율은 일반적인 현상이고 보통 심각한 래치 경합을 야기하지는 않는다.
· 여러분이 래치 경합에 직면하고 여분의 CPU 파워가 남아 있다면, SPIN_COUNT값의 증가를 고려해 본다. CPU 리소스가 여분이 없다면, SPIN_COUNT값의 감소를 고려하자.
Other
· 세션이 롤백세그먼트에 엔트리를 만들기 위해 기다리고 있다면, 롤백세그먼트의 수나 사이즈를 증가시킨다. 가끔 동적인 확장과 축소가 일어나도록 롤백세그먼트의 OPTIMAL 사이즈를 셋팅한다.
· dispatchers, parallel servers, 그리고 shared servers의 수가 잘 구성되도록 한다. 너무 적은 수의 서버는 MTS 혹은 병렬 서버를 통한 세션 연결의 성능을 감소시킬 수 있으며 CPU, 디스크, 혹은 메모리 리소스에 과부하를 줄 수 있다.
출처 : Tong - 라제폰님의 데이타베이스통
'DataBase > Oracle' 카테고리의 다른 글
| 테이블rename (0) | 2009.01.19 |
|---|---|
| [펌]SQL튜닝 이해 (0) | 2009.01.09 |
| [펌]Row를 한컬럼에 담아 옆으로 펼치기(SYS_CONNECT_BY_PATH) (0) | 2009.01.09 |
| [펌]DB 링크 및 스냅샷 (0) | 2009.01.09 |
| [펌]오라클 SELECT를 이용한 내용 확인 (0) | 2009.01.09 |