쿠카의 개발일지

SQL 옵티마이저 7.1 통계정보와 비용 계산 원리 7.1.1 선택도와 카디널리티 선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하낟. 가장 단순한 ‘=’ 조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 개수(Number of Distinct Values, 이하 ‘NDV’)를 이용해 아래와 같이 구한다. 선택도 = 1 / NDV 카디널리티(Cardinality)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수이며, 아래 공식으로 구한다. 카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV 7.1.2 통계정보 통계정보에는 오브젝트 통계와 시스템 통계가 있다. 오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 ..

5.1 소트 연산에 대한 이해 소트 수행 과정 소트는 PGA에 할당한 Sort Area에서 수행하며 PGA가 부족하면 Temp 테이블 스페이스를 사용 메모리 소트 : PGA 내에서 소트 완료할 경우 디스크 소트 : Temp 테이블 스페이스까지 사용하여 소트를 완료할 경우 소트는 최대한 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함 아래는 소트 오퍼레이션 종류에 대해 설명한다. Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타난다. -- Sort Area에 변수를 하나 만들어 데이터를 읽으면서 최대값 비교하며 최대값을 찾아 리턴 SELECT MAX(SAL) FROM 직원;​​ MAX, MIN, SUM, AVG 함수를 사..

4.1 NL 조인 NL 조인 (Nested 조인) : 기본 조인이며 다중 for문처럼 조인하는 방식 수행 빈도가 많은 것은 인덱스를 타야 하므로 Inner 쪽(이중 for문으로 설명하자면 안쪽 for문을 의미) 테이블은 인덱스를 반드시 사용해야 한다. NL 조인 실행 계획 : 아래는 NL 조인 실행 계획 예시이다. 사원이 Outer에 해당하고 고객이 Inner에 해당한다. NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE) INDEX(RANGE SCAN) OF '사원_X1' (INDEX) TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) INDEX(RANGE SCAN) OF '고객_X1' (INDEX)​ 힌트를 사용한 ..

3.1.1 테이블 랜덤 액세스 절차 쿼리문에 사용하는 컬럼을 인덱스가 모두 포함하는 경우가 아니라면, 인덱스를 스캔한 후에 ROWID 획득한다. ROWID(오브젝트 번호, 데이터파일 번호, 블록번호)가 가리키는 테이블 블록을 버퍼 캐시에서 먼저 찾아본다. 못 찾을 때만 ROWID를 가지고 디스크에서 찾아 블록을 읽는다. 1장에서 설명했지만, 디스크에서 읽는 방법은 엄청 느리지만, 버퍼 캐시를 탐색하는 것도 생각보다 고비용 구조이므로 테이블 랜덤 액세스 수 자체를 줄여야 한다. 3.1.2 인덱스 클러스터링 팩터 - 클러스터링 팩터(Clustering Factor, 이하 'CF')는 '군진성 계수', 특정 컬럼을 기준으로 같은 값을 갖는데이터가 서로 모여있는 정도를 의미한다. - 인덱스 ROWID로 테이블을..

2.1 인덱스 구조 및 탐색 2.1.1 미리 보는 인덱스 튜닝 데이터를 찾는 두 가지 방법은 다음과 같다. 1) 테이블 전체를 스캔한다. 2) 인덱스를 이용한다. 인덱스 튜닝의 두 가지 핵심요소 첫 번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이다. 즉. ‘인덱스 스캔 효율화 튜닝’이다. 두 번째 핵심요소는 테이블 액세스 횟수를 줄이는 것이다. 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 ‘랜덤 액세스 최소화 튜닝’이라고 한다. SQL 튜닝은 랜덤 I/O와의 전쟁 데이터베이스 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느리다. 인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O ..

1.3.1 SQL이 느린 이유 SQL이 느린이유? 디스크 I/O 때문! I/O = 잠 OS가 I/O를 처리하는 동안 프로세스는 잠을 잔다! 디스크에서 데이터를 읽어야 할 때는 CPU를 OS에 반환하고 잠시 수면 상태에서 I/O가 완료되기를 대기 (I/O Call 하고 CPU 반환 -> SLEEP) 1.3.2 DB 저장구조 데이터를 저장하려면 가장 먼저 테이블 스페이스 가 필요하다 - 테이블 스페이스 : 세그먼트를 담는 컨테이너 (여러 개의 데이터 파일로 구성) - 세그먼트 : 테이블, 인덱스 처럼 데이터 저장 공간이 필요한 오브젝트 (여러 익스텐트로 구성) - 익스텐트 : 공간을 확장하는 단위 (연속된 블록의 집합) - 블록(페이지) : 레코드를 실제로 저장하는 공간 한 블록은 하나의 테이블이 독점 (한..

옵티마이저 힌트 운전자 자신만 아는 정보나 경험을 활용해 더 빨리 목적지에 도착할 수 있는 것처럼 통계정보에 담을 수 없는 데이터 또는 업무 특성을 활용해 개발자가 직접 더 효율적인 엑세스 경로를 찾아 낼 수 있다. 이럴 때 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다. 힌트 사용법은 아래와 같다. 주석 기호에 '+'를 붙이면 된다. SELECT /*+ INDEX(A 고객_PK) */ 고객명, 연락처, 주소, 가입일시 FROM 고객 A WHERE 고객ID = '00000000' 소프트 파싱 vs 하드파싱 SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 라이브러리 캐시(Library Cache)라고 한다. SQL..

구조적, 집합적, 선언적 질의 언어 SQL은 원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수밖에 없다. 즉, 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 바로 SQL 옵티마이저다. 옵티마이저가 프로그래밍을 대신해 주는 셈이다. DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전과정을 'SQL 최적화' 라고 한다. SQL 최적화 SQL 파싱 - 사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행한다. SQL 파싱을 요약하면 아래와 같다. 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성 Syntax 체크 : 문법적 오류가 없는지 확인. 예를 들어, ..

어느날 잘되던 API를 체크해보았는데 빌드는 되었지만 런타임 500 오류가 발생하였다. 오류 메시지를 보니 Field '…' doesn't have a default value 라는 메시지를 찾을 수 있었다. 소스코드를 점검해보니 이상이 없었고, 검색해보니 열(컬럼)에 기본값이 따로 지정되지 않았는데 그 열의 값을 뚜렷하게 지정하지 않는 채로 INSERT나 UPDATE 등으로 자료를 넣으면 오류가 발생할 수 있다고 한다. 위 경우는 해당 테이블의 PK에 auto increment 속성이 해제되어 있었고 이를 추가하여 해결할 수 있었다. -- auto increment alter table aaa modify bbb bigint auto_increment; PK에 자동증가 설정 추가 후, API 가 정상동..

ORDER BY 1,2 란? 보통 ORDER BY 뒤에는 컬럼명이 오는데, 종종 ORDER BY 1, 2 이런식으로 숫자로 표현된 경우가 있다. 1은 첫번째 컬럼을 의미하고 2는 두번째 컬럼을 의미한다. 즉, 숫자는 조회되었을 때의 컬럼 순서이다. --컬럼 사용 SELECT SALARY, EMPLOYEE_ID FROM EMPLOYEE e ORDER BY SALARY, EMPLOYEE_ID; --숫자표현 사용 SELECT SALARY, EMPLOYEE_ID FROM EMPLOYEES e ORDER BY 1, 2; SALARY가 오름차순, EMPLOYEE_ID가 오름차순으로 결과가 잘 나온 것을 확인할 수 있다. 참고 : https://jhnyang.tistory.com/470 [SQL] 정렬하기 order..