친절한 SQL 튜닝
-
친절한 SQL 튜닝_7장.SQL 옵티마이저_2친절한 SQL 튜닝 2020. 10. 4. 13:26
SQL 옵티마이저 7.2 옵티마이저에 대한 이해 7.2.1 옵티마이저 종류 비용기반(Cost-Based) 옵티마이저(이하 ‘CBO’)는 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출하고, 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저이다. CBO가 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다. 과거에는 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저를 사용했어다. 일명, ‘규칙기반(Rule-Based) 옵티마이저’, 줄여서 RBO!, RBO는 데이터 특성을 나타내는 통계정보를 전혀 활용..
-
친절한 SQL 튜닝_7장.SQL 옵티마이저_1친절한 SQL 튜닝 2020. 10. 4. 12:04
SQL 옵티마이저 7.1 통계정보와 비용 계산 원리 7.1.1 선택도와 카디널리티 선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하낟. 가장 단순한 ‘=’ 조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 개수(Number of Distinct Values, 이하 ‘NDV’)를 이용해 아래와 같이 구한다. 선택도 = 1 / NDV 카디널리티(Cardinality)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수이며, 아래 공식으로 구한다. 카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV 7.1.2 통계정보 통계정보에는 오브젝트 통계와 시스템 통계가 있다. 오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 ..
-
친절한 SQL 튜닝_6장.DML 튜닝_4친절한 SQL 튜닝 2020. 10. 3. 10:51
DML 튜닝 6.4 Lock과 트랜잭션 동시성 제어 6.4.1 오라클 Lock 오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용한다. 이 외에도 내부에 더 많은 종류의 Lock이 존재한다. 래치는 SGA에 공유된 각종 자료구조를 보호하기 위해 사용하며, 버퍼 Lock은 버퍼 블록에 대한 액세스를 직렬화하기 위해 사용한다. 라이브러리 캐시 Lock과 Pin은 라이브러리 캐시에 공유된 SQL 커서와 PL/SQL 프로그램을 보호하기 위해 사용한다. 애플리케이션 개발 측면에서 가장 중요하게 다루어야 할 Lock은 무엇보다 DML Lock이다. DML Lock은 다중 트랜잭션이 동시에..
-
친절한 SQL 튜닝_6장.DML 튜닝_3친절한 SQL 튜닝 2020. 10. 3. 09:44
DML 튜닝 6.3 파티션을 활용한 DML 튜닝 6.3.1 테이블 파티션 파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나뉘서 저장하는 것을 말한다. 파티션이 필요한 이유를 관리적 측면과 성능적 측면으로 나뉘 짧게 요약하면 아래와 같다. 관리적 측면 : 파티션 단위 백업, 추가, 삭제 변경 -> 가용성 향상 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산 파티션에는 Range, 해시, 리스트 세 종류가 있다. Range 파티션 오라클 8 버전부터 제공된 가장 기초적인 방식으로 주로 날자 컬럼을 기준으로 파티셔닝한다. 아래는 주문 테이블을 주문일자 기준으로 분기별 Range 파티셔닝하는 방법을 예시하고 있다. 1 2 3..
-
친절한 SQL 튜닝_6장.DML 튜닝_2친절한 SQL 튜닝 2020. 10. 3. 09:03
DML 튜닝 6.2 Direct Path I/O 활용 온라인 트랜잭션은 기준성 데이터, 특정 고객, 특정 상품, 최근 거래 등을 반복적으로 읽기 때문에 버퍼캐시가 성능 향상에 도움을 준다. 반면, 정보계 시스템(DW/OLAP 등)이나 배치 프로그램에서 사용하는 SQL은 주로 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O메커니즘이 오히려 성능을 떨어뜨릴 수 있다. 그래서 오라클은 버퍼캐시를 경유하지 않고 곧 바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공하는데, 지금부터 살펴보자. 6.2.1 Direct Path I/O 일반적인 블록 I/O는 DB 버퍼캐시를 경유한다. 즉, 읽고자 하는 블록을 먼저 버퍼캐시에서 찾아보고, 찾지 못할 때만 디스크에서 읽는다. 데이터를..
-
친절한 SQL 튜닝_6장.DML 튜닝_1친절한 SQL 튜닝 2020. 10. 3. 08:52
DML 튜닝 6.1. 기본 DML 튜닝 6.1.1 DML 성능에 영향을 미치는 요소 인덱스와 DML 성능 테이블에 레코드를 입력하면, 인덱스에도 입력해야 한다. 테이블은 Freelist를 통해 입력할 블록을 할당받지만, 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야 한다. 인덱스에 입력하는 과정이 더 복잡하므로 DML 성능에 미치는 영향도 크다. DELETE 할 때도 마찬가지다. 테이블에서 레코드 하나를 삭제하면, 인덱스 레코드를 모두 찾아서 삭제해 줘야 한다. UPDATE 할 때는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경해 주면 된다. 그 대신, 테이블에서 한 건 변경할 때마다 인덱스에는 두 개 오퍼레이션이 발생한다. 인덱스는 정렬된 자료구조이기 때문이다. 예를 들어, ‘A..
-
친절한 SQL 튜닝_5장.소트 튜닝_4친절한 SQL 튜닝 2020. 9. 20. 11:43
소트 튜닝 5.4 Sort Area를 적게 사용하도록 SQL 작성 5.4.1 소트 데이터 줄이기 특정 기간에 발생한 주문상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 Sort Area를 더 적게 사용할까? [1번] 1 2 3 4 SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, ‘yyyymmdd hh24:mi:ss’) FROM 주문상품 WHERE 주문일시 BETWEEN :START AND :END ORDER BY 상품번호 Colored by Color Scripter cs [2번] 1 2 3 4 5 6 7 SELECT LPAD(상품번호, 30) || LPAD(상품명, 30)..
-
친절한 SQL 튜닝_5장.소트 튜닝_3친절한 SQL 튜닝 2020. 9. 18. 12:17
소트 튜닝 5.3 인덱스를 이용한 소트 연산 생략 인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다. 이를 활용하면 SQL에 Order By 또는 Group By 절이 있어도 소트 연산을 생략할 수 있다. 여기에 Top N 쿼리 특성을 결합하면, 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 매우 빠른 응답 속도를 낼 수 있다. 특정 조건을 만족하는 최소값 또는 최대값도 빨리 찾을 수 있어 이력 데이터를 조회할 때 매우 유용하다. 5.3.1 Sort Order By 생략 인덱스 선두 컬럼을 『종목코드+ 거래일시』 순으로 구성하지 않으면, 아래 쿼리에서 소트 연산을 생략할 수 없다. 1 2 3 4 SELECT 거래일시, 체결건수, 체결수량, 거래대금 FROM 종목거래 WHERE 종목코드 = ..
-
친절한 SQL 튜닝_5장.소트 튜닝_2친절한 SQL 튜닝 2020. 9. 18. 12:09
소트 튜닝 5.2. 소트가 발생하지 않도록 SQL 작성 5.2.1 Union vs. Union All SQL에 Union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행한다. 반면, Union All은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다. 따라서 될 수 있으면 Union All을 사용해야 한다. 그런데 Union을 Union All로 변경하려다 자칫 결과 집합이 달라질 수 있으므로 주의해야 한다. Union 대신 Union All을 사용해도 되는지를 정확히 판단하려면 데이터 모델에 대한 이해와 집합적 사고가 필요하다. 그런 능력이 부족하면 알 수 없는 데이터 중복, 혹시 모를 데이터 중복을 우려해 중복 제거용 연산자를 불필요하..
-
친절한 SQL 튜닝_5장.소트 튜닝_1친절한 SQL 튜닝 2020. 9. 18. 12:05
소트 튜닝 5.1. 소트 연사에 대한 이해 5.1.1 소트 수행 과정 소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다. 메모리 공간인 Sort Area가 다 차면, 디스크 Temp 테이블스페이스를 활용한다. Sort Area에서 작업을 완료할 수 있는지에 따라 소트를 두 가지 유형으로 나눈다. 메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, ‘Internal Sort’라고도 한다. 디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, ‘External Sort’라고도 한다. 소트 연산은 메모리 집약적(Memory-intesive)일 뿐만 아니라..