쁘도리의 블로그

전체쿼리 결과 집합을 쉼 없이 연속적으로 전송하지 않고 사용자로 부터 Fetch Call이 있을때 마다 일정량씩 나누어 전송하는 것을 '부분범위 처리'라고 한다.

정렬조건이 있을때 부분범위 처리

인덱스는 항상 정렬된 상태를 유지하므로 전체 데이터를 정렬하지 않고도 정렬된 상태의 결과집합을 바로 전송할 수 있다.

Array Size 조정을 통한 Fetch Call 최소화

대량 데이터를 파일로 내려받는다면 데이터를 모두 전송해야하므로 가급적 Array Size를 크게 설정해야한다. Array Size를 조정한다고 해서 전송해야 할 총량이 변하진 않지만, Fetch Call 횟수를 그만큼 줄일수 있으며, 반대로 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라고 할 경우 Array Size를 작게 설정하는 것이 유리하다.(불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일수 있음.)

쿼리 툴에서 부분범위 처리

모든 DBMS는 데이터를 조금씩 나눠서 전송한다. 즉, 부분범위 처리 방식으로 결과집합을 전송한다. 이 특징을 이용해 중간에 멈췄다가 사용자의 추가 요청이 있을 때마다 데이터를 가져오도록 구현하고 안하고의 차이는 책의 저자가 말하길 DBMS 클라이언트 프로그램을 개발하는 개발자의 몫이라고 말한다.

부분범위 처리 구현

개발 프레임워크에 미리 부분범위 처리 된 구현 기능을 활용하면 된다고 한다. 

OLTP(Online Transaction Processing) 

온라인 트랜잭션을 처리하는 프로그램을 말하며, 일반적으로 소량 데이터를 읽고 갱신한다. (하지만 항상 소량 데이터만 조회하는것은 아님)

수천수만 건을 조회하는 경우에는 많은 테이블 랜덤 액세스가 발생하기  때문에 제 성능을 내기 어려울수 있다. 버퍼캐시히트율이 좋다면 빠른 성능을 보일수 있지만, 아닌 경우도 존재하기 때문이다. 

 그러나 OLTP성 업무에서 쿼리 집합이 아주 많을 때 사용자가 모든 데이터를 일일이 다 확인하지 않는다. 특정한 정렬 순서로 상위 일부 데이터만 확인한다. (ex. 은행계좌 입출금 조회, 뉴스 또는 게시판 등등..) 주로 목록을 조회하는 경우. 이런 경우 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 바르게 보여줄수 있다. (인덱스와 부분범위 처리를 잘 활용한다면 OLTP환경에서 극적인 성능 개선 효과를 얻을수 있다고 함.)

멈출 수 있어야 의미있는 부분범위 처리(문제는 앞쪽 일부만 출력하고 멈출 수 있는가..? _부분범위 처리 핵심*)

해당 내용은 추후 5장 3절에서  다룬다고 함..

배치I/O  :  읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능.

  인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽는데(오라클11g 까지), 배치I/O기능이 작동하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다. (오라클12c 이후 해당)

(* 12c에 도입된 일반 배치 I/O와 NL조인에 작동하는 기존 배치I/O는 파라미터, 힌트, 실행계획 표현방식이 모두 다르다.)

 배치I/O를 통해 얻을수 있는 성능 이점이 많은에도 불구하고 시스템레벨에서 이를 비활성화 하는 경우가 종종있다. 이 기능을 비활성화하는 이유는 '필요한' order by를 생략한 SQL패턴 때문라고 한다. SQL에 order by가 없으면 결과 집합의 정렬 순서를 보장할 필요가 없으므로

옵티마이저가 배치I/O를 선택할 수 있고, 출력된 결과집합의 정렬 순서가 매번 다를수 있다.

데이터 정렬 이슈 :

배치I/O 기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를수 있다는 사실에 주목해야 함..

오라클에서 랜덤 엑세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하는 방법을 IOT(Index-Organized Table)이라 함.

(MS-SQL Server 에서는 '클러스터형 Clustered 인덱스'라고 함. 

 테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그자리에 테이블 데이터를 갖는다.(즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다. IOT에서는 '인덱스 리프 블록이 곧 데이터 블록' 이다. )

일반 테이블은 '힙 구조 테이블'이라고 부른다.  일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식을 사용한다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.

IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나이며, Between이나 부등호 조건으로 넓은 범위를 읽을때 유리하다. 또한, 데이터 입력과 조회 패턴이 서로 다른 테이블에도 유용하다.

클러스터 테이블 : 클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다.

인덱스 클러스터 테이블 :

 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조. 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다. 추가적으로 여러 테이블 레코드를 같은 블록에 저장할 수도 있는데, 이를 '다중 테이블 클러스터' 라고 함. (일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음)

 클러스터에 테이블을 담기 전에 아래와 같이 클러스터 인덱스를 반드시 정의해야 한다. 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문..

 클러스터 인덱스도 일반 B*Tree 인덱스 구조를 사용하지만,  테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가르킨다는 점이 다르다. 따라서 클러스터 인덱스의 키값은 항상 유니크하다(=중복값이 없다)

해시 클러스터 테이블 :

 해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다. 

온라임 프로그래밍 튜닝 vs 배치 프로그래밍 튜닝

_온라인 프로그래밍 튜닝은 보통 소량 데이터를 읽고 갱신 하므로, 인덱스를 효과적으로 활용하는 것이 중요. 조인도 대부분 NL 방식을 사용 NL조인은 인덱스를 이용하는 조인 방식 (온라인 환경에서 대량 데이터를 조회 할 때도 아주 빠른 응답 속도를 낼수 있다 )

_배치 프로그래밍 튜닝 항상 전체범위 처리 기준으로 튜닝해야 한다.  처리대상 집합 중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표. 이럴때는 Full scan과 해시 조인이 유리함

** 초대용량 테이블을 full scan 하면 상당히 오래 기다려야 하므로, 배치 프로그램에서는 파티셔닝 활용 전량이 매우 중요한 튜닝요소, 이 책의 저자는 모든 성능 문제를 인덱스로 해결 하려 하면 안되며, 인덱스는 다양한 튜닝 도구 중 하나일 뿐 이라고 한다.

*NL조인, 해시조인 참고 url https://mozi.tistory.com/222

3.1.4 인덱스 컬럼 추가

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다. 

> 인덱스에 컬럼을 추가 할 경우 인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여줄수 있다.

3.1.5 인덱스만 읽고 처리

비효율이 없더라도 인덱스 스캔 과정에서 얻은 데이터가 많다면 그만큼 테이블 랜덤 액세스가 많이 발생하므로 성능이 느릴 수 밖에 없다.

이런 경우일때 반드시 성능을 개선해야 한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법을 고려해 볼 수 있다. 이 방법이 효과는 매우 좋을수 있지만, 추가해야 할 컬럼이 많아 실제 적용하기 곤란한 경우가 많다고 한다.

[+] 인덱스만 읽어서 처리하는 쿼리를 'Covered 쿼리'라 부르며, 쿼리에 사용한 인덱스를 'Covered 인덱스'라고 부른다.

Include 인덱스 ( SQL Server 2005 버전에 추가된 유용한 기능 )

인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능. 인덱스를 생성할 때 include 옵션을 지정하면 된다. 컬럼은 최대 1,023개 까지 지정할 수 있다. include 인덱스는 순전히 테이블 랜덤 액세스를 줄이는 용도로 개발됐다.

저장 프로시저 (Stored Procedure, SP)

"저장 프로시저 또는 스토어드 프로시저(stored procedure)는
일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다."

_위키백과

"데이터베이스에 대한 일련의 작업을 정리한 절차를
관계형 데이터베이스 관리 시스템에 저장한(지속성) 것으로,
영구저장모듈(Persistent Storage Module)이라고도 불린다."

장점 -

네트워크 부하 감소

처리 시간 감소

데이터 무결성 유지

편리한 유지보수 및 개발 업무와의 구분

절차적 기능 구현 가능

단점 -

불편한 유지보수 ( 개발적 역량으로 인해 )

DB 확장 어려움

1. (인수없는) 프로시저 생성방법

CREATE OR REPLACE PROCEDURE 프로시저이름 

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

변수이름 데이터타입;

변수이름 데이터타입;

..

]

BEGIN

 기능 구현;

END;

2. (인수있는) 프로시저 생성방법

CREATE OR REPLACE PROCEDURE 프로시저이름(

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --IN 생략가능

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

3. 프로시져 호출방법

 - EXEC 프로시져이름 ;  -- 인수없는 경우 호출

 - EXEC 프로시져이름(값, 값,....); -- 인수있는 경우 호출;

4. 프로시저 output 매개변수 사용하기

 - 웹개발 하면서 프로시저 결과 핸들링을 위해 많이 사용 했다.

 - 프로시저를 실행하여 특정결과값을 out변수에 저장하여 보낸다.(프로시저에서 실행환경으로 값을 반환)

 - out있는 프로시저 작성방법

CREATE OR REPLACE PROCEDURE 프로시저이름 (

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --in 생략가능

변수이름 OUT 데이터타입, 변수이름 OUT 데이터타입 ... --프로시저를 호출하는곳으로 값을 보낸다.

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

-----

프로시저 문법

DECLARE

> 변수는 선언 시에 기본값을 지정할 수 있습니다. 만약 지정하지 않는다면 그 변수의 값은 NULL 값을 갖게 됩니다.

v_ :  내부 변수로서 프로시저 내에서 사용할 변수에 대해 주로 v라는 표현을 붙여 사용한다.

p_ :  위에서 cmd 창에서 입력을 받던 대체 변수들에 대해  p라는 표현을 붙였던 것을 기억할 것이다.

상수(constant)

> 상수는 변수와 다르게 한번 값이 정해지면 변경할 수 없다는 차이가 있습니다. 상수를 선언하는 방식은 변수의 선언처럼 DECLARE 구문 뒤에서 이루어지며, 상수 이름과 타입 사이에 CONSTANT라고 지정하면 됩니다.

CALL

> 저장 프로시저 호출 

Call 문의 저장 프로시저에서 반환되는 유일한 지원되는 형식은 행 집합입니다. 행 집합 직렬화는 XML for Analysis에 의해 정의됩니다. Call 문의 저장 프로시저가 다른 형식을 반환하는 경우 무시되고 호출 애플리케이션에 XML로 반환되지 않습니다 - https://docs.microsoft.com/

EXCEPTION 

> 예외처리, 프로시저가 예외처리가 없는 경우 프로시저에서 강제적으로 종료되고 오류 메시지가 출력된다고 한다.

GET DIAGNOSTICS

> GET DIAGNOSTICS 명령문은 실행된 이전 SQL문에 대한 정보를 얻습니다.

CURRENT 또는 STACKED액세스할 진단 영역을 지정합니다.

• CURRENT 첫 번째 진단 영역에 액세스하도록 지정합니다. 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. 이 값이 기본값입니다.
• STACKED 두 번째 진단 영역에 액세스하도록 지정합니다. 두 번째 진단 영역은 핸들러 내에서만 사용 가능합니다. 핸들러가 입력되기 전에 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. GET DIAGNOSTICS 명령문이 핸들러 내 첫 번째 명령문인 경우, 첫 번째 진단 영역과 두 번째 진단 영역은 동일한 진단 정보를 포함합니다.

RAISE NOTICE

> 해당 변수들을 원하는 문자열로 포맷으로 구성한 것을 PL/pgSQL의 실행환경에서 표시하는 코드입니다.

-----

프로시저 삭제

DROP PROCEDURE [프로시저 명] ;

출처:

http://www.gisdeveloper.co.kr/?p=4573 

https://www.ibm.com/docs/ko/i/7.3?topic=statements-get-diagnostics 

https://goddaehee.tistory.com/163 

https://siahn95.tistory.com/entry/DBMSSQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Stored-Procedure%EB%9E%80

3.1 테이블 액세스 최소화

- 테이블 랜덤 액세스 

인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는데 있다.

(ROWID는 물리적 주소보다 논리적 주소에 가깝다고 볼수있다고 한다. 추가적으로 정리하자면, 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담기 때문..)

_ 메인 메모리DB와 비교

디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽음. (메인 메모리 DB만큼 빠르지는 않다.)

인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 메모리상의 주소정보, 즉 포인터를 갖는다. 따라서 인덱스를 경유해 테이블을 액세스하는 비용이 오라클과 비교할수 없을정도로 낮다고 한다.

_ I/O 매커니즘복습

DBA(= 데이터파일번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보이다.

I/O 성능을 높이려면 버퍼캐시를 활용해야 한다. 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾는다.

 인덱스로 테이블 블록을 액세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA정보를 얻고, 테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. 

* 인덱스 ROWID는 포인터가 아니다! ( 디스크 상에서 테이블 레코드를 찾아가기 위한 논리적인 주소 정보!!!)

_ 인덱스 ROWID는 우편주소

- 인덱스 클러스터링 팩터

 클러스터링 팩터(Clustering Factor, 이하 'CF')는 군집성 계수로 변역할 수 있는 용어로 [ 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미함.]

_CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.

// 인덱스 클러스터링 팩터 효과

CF가 좋은 컬럼에 생성한 인덱스가 검색 효율이 좋다는것은, 테이블 액세스량에 비해 블록I/O가 적게 발생함을 의미한다. (추후 설명보충)

- 인덱스 손익분기점

인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조. 따라서, 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는(Full Scan)보다 오히려 더 느려진다. 이 느려지는 지점을 흔히 '인덱스 손익분기점' 이라고 한다.

인덱스를 이용한 테이블 액세스가 Table Full Scan보다 느려지게 만드는 가장 핵심적인 두가지 요인은 아래와 같다.

1. Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식

2. Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O방식.

위 와 같은 요인에 의해 인덱스 손익분기점은 보통 5~20% 낮은 수준에서 결정된다. CF가 나쁘면 손익 분기점은 5% 미만에서 결정되며, 심할때는 (BCHR이 매우 안 좋을때) 1% 미만으로 낮아진다. 반대로 CF가 아주 좋을때는 손익분기점이 90% 수준까지 올라가기도 한다.

* 인덱스 손익분기점과 버퍼캐시 히트율

인덱스를 스캔하면서 테이블을 액세스하다 보면 어느 순간부터 대부분 테이블 블록을 캐시에서 찾게된다. 

만 건만 넘어도 시퀀셜 액세스와 Multiblock I/O방식, 즉 Table Full Scan 방식으로 읽는게 빠를 수 있다.

주의) 저자가 말하길 인덱스가 항상 좋을수 없음을 설명하려고 손익분기점이란 개념을 사용했을뿐, 이를 높이기 위해 어떤 조치를 해야 한다는 뜻으로 오해하지 말길 바라며, 테이블 스캔이 항상 나쁜것은 아니며, 반대로 인덱스 스캔이 항상 좋은 것도 아니라는 사시을 설명하는데 목적이 있다고 한다. 

ALTER TABLE 테이블명 OWNER TO 소유자명;

ALTER TABLE 테이블명 SET SCHEMA 새로운_스키마명;

특정 유저 소유자의 전체 오브젝트들을 다른 유저 소속으로 변경할경우

REASSIGN OWNED BY 현재 소유자명 TO 새로운 소유자명;

Description: change the ownership of database objects owned by a database role
Syntax:
REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
TO { new_role | CURRENT_USER | SESSION_USER }

출처: https://orcl.tistory.com/entry/postgresql-테이블의-스키마와-owner-변경하기 

DB Index 가비지 파일 정리 명령어

vacuum verbose analyze;

베큠 관련 정리글을 남겨둔다.

https://blog.gaerae.com/2015/09/postgresql-vacuum-fsm.html

번외 :

현재 트랜잭션 실형중인 쿼리 조회 

select * from pg_stat_activity order by query_start asc; 

실행중인 쿼리 취소하기 

select pg_cancel_backend(pid int);

> pid int 값은 pg_stat_activity 쿼리조회 결과에서 실행중인 쿼리를 취소할 pid 컬럼의 int값을 pg_cancel_backend 함수 실행하면

쿼리가 취소된다. 

- Index Range Scan

> B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식, 

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 '필요한 범위만' 스캔한다.

* 인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다. 반대로 말하면, 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan은 무조건 가능하다. 

- Index Full Scan

> 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

대게 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택

- Index Unique Scan

> 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리 해 준다.

- Index Skip Scan

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. Table Full Scan 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 함.

> 조건절에 빠진 인덱스 선두 컬럼의 distinct Value 개수가 적고 후행 컬럼의 distinct Value 개수가 많을 때 유용하다. 

 Index Skip Scan 은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스 하는 스캔 방식.

- Index Fast Full Scan

Index Fast Full Scan이 Index Full Scan보다 빠른 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Mulitblock I/O 방식으로 스캔하기 때문이다. 

 Index Fast Full Scan은 Mulitblock I/O방식을 사용하므로 디스크로부터 대량의 인덱스 볼록을 읽어야 할 때 큰효과를 발휘한다. 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다. 

 Index Range Scan 또는 Index Full Scan과 달리, 인덱스 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징중 하나다.

병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨라진다. 

- Index Range Scan Descending 

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 인덱스를 뒤에서 부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 정렬된 결과집합을 얻는다는 점만 다르다. 

https://m.blog.naver.com/anytimedebug/221222479261