rlatotquf45 님의 블로그

SQL 수행 도중 옵티마이저는 가공된 데이터 집합이 필요할 때 PGA(private global area)나 Temp 테이블 스페이스를 활용한다. 이전에 작성한 소트머지조인, 해시조인이 있었고, 이번엔 데이터 소팅 및 그룹핑에 대해 알아보자. 소트 수행 과정소팅은 메모리에 할당한 PGA에서 진행하거나, 디스크에 할당된 Temp 테이블 스페이스에서 진행한다. 따라서 소트를 다음처럼 구분한다.메모리 소트(In-memory sort) : 전체 데이터 정렬을 메모리 내에서 완료. Internal sort 라고도 함디스크 소트(To-Dist sort) : PGA 공간 + 디스크 공간까지 활용하는 경우. External sort 라고도 함디스크 소팅에 경우, 양이 많을 때 PGA에서 정렬된 중간 집합을 Temp 테..

서브쿼리 변환이 필요한 이유최근 옵티마이저는 비용을 평가하고 실행계획을 세우기 전, SQL을 최적화에 유리한 형태로 변환하는 작업을 진행한다.쿼리 변환(Query Transformation)은 옵티마이저가 SQL을 분석해 의미적으로 동일(같은 결과집합 생성)하면서 더 나은 성능을 기대하는 형태로 작성하는 것이다. SQL 성능과 관련해 새로 개발되는 핵심 기능이 대부분 쿼리변환 영역에 속한다. 서브쿼리는 하나의 sql 문 안에 괄호로 묶은 별도의 쿼리 블록(query block)이다. 오라클에서는 다음과 같이 분류한다인라인 뷰(Inline view) : FROM 절에 사용한 서브쿼리중첩된 서브쿼리(nested subquery) : WHERE 절에 사용한 서브쿼리, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태..

일반적인 조인 메소드 선택 기준은 다음과 같다소량 데이터 조인 -> NL 조인대량 데이터 조인(최적화 해도 랜덤 액세스가 많은 경우) -> 해시 조인대량 데이터 조인인데 해시 조인으로 처리를 못할 때, 즉 조인 조건식이 등치(=)조건이 아닐 때(조인 조건식이 없는 카테시안 곱 포함) -> 소트 머지 조인수행빈도가 매우 높은 쿼리에 대해선 어떻게 해야 할까(최적화된)NL 조인과 해시 조인 성능이 같으면, NL 조인해시 조인이 약간 더 빨라도 NL 조인NL조인보다 해시 조인이 매우 빠른 경우 해시 조인이전 포스팅에서 부터 NL조인보다 소트 머지, 해시 조인이 더 빠르다고 했는데 왜 NL 조인이 더 우선시 될까NL 조인에 사용되는 인덱스는 DROP하지 않는 한 영구적으로 유지 되면서 다양한 쿼리를 위해 공유 ..

조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때 소트 머지 조인이나 해시 조인을 사용한다. SGA vs PGASGA는 공유 메모리 영역으로, 캐시된 데이터를 여러 프로세스가 공유할 수 있다 하지만 동시에 액세스 할수는 없기에 프로세스간 액세스를 직렬화 하기 위해 Lock 메커니즘으로서 래치(Latch)가 존재한다. 그리고 DB 버퍼캐시에서 블록을 읽으려면 버퍼 Lock도 얻어야 한다. 오라클 서버 프로세스는 자신만의 고유 메모리 영역을 가진다. 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA(process/program Private Global Area)라고 부르며, 프로세스에 종속적인 "고유 데이터"를 저장하는 용도다.할당받은 PGA공간이 작은 경우 Temp..

NL조인은 인덱스를 이용한 조인이다. 기본적인 메커니즘은 프로그래밍 언어의 중첩 loop문과 로직이 비슷하다.for (i=0;i 일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다. Outer쪽은 사이즈가 크더라도 인덱스를 안 쓸수 있는데, Table Full Scan하더라도 한 번에 그치기 때문이다. 반면 Inner 루프에서 인덱스를 이용하지 않으면, Outer 루프에서 읽은 건수 수 마다 Table Full Scan을 반복한다.. NL 조인을 제어할 때는 use_nl 힌트를 사용한다.select /*+ ordered use_nl(c) */ e.사원명, c.고객명, c.전화번호from 사원 e, 고객 cwhere c.관리사원번호 = e.사원번호and e.입사일자 >= '1..

인덱스 설계가 어려운 이유인덱스가 많으면 구체적으로 다음과 같은 문제가 생긴다.DML 성능 저하(TPS 저하)데이터베이스 사이즈 증가 (디스크 공간 낭비)데이터베이스 관리 및 운영 비용 상승만약 테이블에 인덱스가 여러개 달려 있으면 신규 데이터를 넣을 때 6개의 인덱스 모두에 넣어야 하고, 인덱스는 정렬 상태까지 유지해야되므로 수직적 탐색으로 어디에 넣을지도 찾아야 한다. 그런데 만약 블록의 크기가 다 차서 넣을 공간이 없다면 인덱스 분할(index split)까지 발생한다.(특정 데이터를 5번에 넣어야 되는데 자리가 없으면 5번과 6번 사이에 새로운 블록을 끼워 놓고(인덱스는 양방향 연결 리스트니까) 5번 블록의 데이터 중 뒤쪽 절반을 6번 블록으로 옮긴다.) 데이터를 지울때도 마찬가지로, 여섯 개의 ..

PL/SQL 함수의 성능적 특성PL/SQL/사용자 정의 함수는 매우 느리다.대량 데이터를 조회해 보면 성능 차이를 확연히 느낄 수 있는데, 그 이유로는 크게 3가지가 있다.VM 상에서 실행되는 인터프리터 언어호출 시마다 컨텍스트 스위칭 발생내장 SQL에 대한 RECURSIVE CALL 발생 PL/SQL로 작성한 함수와 프로시저를 컴파일하면 바이트코드를 생성해서 이를 해석할 수 있는 VM만 있으면 어디서든 실행가능하고 PL/SQL엔진은 바이트코드를 런타임 시 해석하면서 실행한다. 하지만 PL/SQL 함수는 실행 시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.그리고 성능을 떨어뜨리는 가장 결정적인 요소는 RECRURSIVE CALL이다.만약 데이터가 100만명이고, 특정 ..

IOT, 클러스터, 테이블 파티션은 테이블 랜덤 액세스를 최소화 하는데 효과적이나, 그 구현 방법이 어렵고 이를 업무에 적용하기 위해서는 성능 검증을 많이 해야한다. 그래서 일반적인 튜닝 기법은 인덱스 컬럼 추가다. 인덱스 선행 컬럼이 조건절에 없거나, '='조건이 아니면 인덱스 스캔 과정에 비효율이 발생한다. 그럼 인덱스 스캔 효율이 좋은지 나쁜지를 확인하려면 SQL 트레이스를 통해 확인할 수 있다.예를 들어 TABLE ACCESS BY INDEX ROWID BIG_TABLE을 통해 얻은 레코드가 열 개인데, (cr=7471 pr=1466 pw=0 time=22137 us), INDEX RANGE SCAN BIG_TABLE_IDX (cr=7483 pr 1466 pw=0 time22328 us)라고 트레..

인덱스 컬럼 추가sql 성능 튜닝을 하기 위함은 테이블 랜덤 액세스 횟수를 줄이기 위함이다. 그리고 이를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는것이다. 예를 들어select /*+ index(emp emp_x01) */ * -- emp_x01 = DEPTNO + JOB순으로 구성from empwhere deptno = 30and sal >= 2000쿼리가 있을 때. sal이 인덱스 컬럼 구조에 없기 때문에 sal >= 2000 조건을 만족하는 데이터 Row를 찾기 위해 테이블을 여러번 액세스 해야한다.인덱스 구성을 deptno + sal로 구성하면 좋겠으나, 실제 운영에서 기존 인덱스를 사용하는 sql이 있을 수 있기 때문이다.그렇다고 인덱스를 모든 sql에 만들다보면 테이블..

인덱스 ROWID는 물리적 주소인가 논리적 주소인가SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니라면 인덱스를 스캔한 후 반드시 테이블을 액세스한다.이때 실행계획에는 'TABLE ACCESS BY INDEX ROWID'라고 표시된다.인덱스를 스캔하는 이유는 소량의 데이터를 인덱스에서 찾고, 테이블 레코드를 빠르게 찾아가기 위한 주소값인 ROWID를 얻기 위함이다.ROWID는 데이터파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 구성되어있다. 하지만 ROWID는 물리적 주소보단 논리적 주소에 가깝다.색인이 인덱스라면, ROWID는 인덱스에 적힌 페이지 번호라고 생각할 수 있다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다. 테이블 레코드와 물리적으로 직접 연결된 ..