SQL) 데이터베이스 최소화 원리 Ⅲ

jinsung·2025년 12월 16일

SQLP oracle sql 데이터 자격증

SQL

목록 보기

25/46

7. PL/SQL 함수의 특징과 성능 부하

PL/SQL이 대체 뭔데?

PL/SQL이란 PL은 Procedural Language로 절차적 언어를 말하고, 거기에 SQL을 더한 것이다.

SQL만으로는 할 수없는 로직들(반복, 조건, 예외처리등)을 절차적 프로그래밍으로 해결한다.

DECLARE
    -- 변수 선언부
BEGIN
    -- 실행부
EXCEPTION
    -- 예외 처리부
END;

대게 이런 구조를 가지고 있다.

PL/SQL의 특징은 뭐가 있죠?

오라클은 PL/SQL로 작성된 함수나 프로시저의 이식성을 고려해 오라클 서버가 아닌 Oracle Forms, Oracle Reports 같은 제품에서도 수행될 수 있도록 설계하였다.
그래서 PL/SQL로 작성한 함수와 프로시저를 컴파일하면 Java 언어처럼 바이트코드가 생성돼 이를 해석하고 실행할 수 있는 PL/SQL 엔진만 있다면 어디서든 실행가능하다.

PL/SQL도 Java처럼 인터프리터 언어이기 때문에

Native 코드로 완전 컴파일된 내장함수에 비해 많이 느리다.
함수 실행 시 매번 SQL 실행엔진과 PL/SQL 가상머신사이에 컨텍스트 스위칭이 일어남.
PL/SQL 함수와 프로시저는 모듈화, 공용화하면 안된다.

Recursive Call를 포함하지 않는 함수의 성능 부하

오라클 내장 함수 to_char을 사용 -> 2.45초
사용자 정의 함수 to_char을 사용 (Recursive Call 포함x) -> 15.57초

=> Recursive Call 없이 5~10배정도 느려졌다.

Recursive Call을 포함하는 함수의 성능 부하

대용량 조회 쿼리에서 함수를 남용하면 읽는 레코드 수만큼 건건이 함수 호출이 발생해 성능이 극도로 나빠진다. 따라서 사용자 정의 함수는 소량의 데이터 조회 시에만 사용해야 하고, 대용량 조회 시에는 부분범위처리가 가능한 상황에서 제한적으로 사용해야 한다.
그리고 성능을 위해서라면 가급적 조인이나 스칼라 서브쿼리 형태로 변환해야 한다.
어쩔 수 없을 때는 함수를 쓰되 호출 횟수를 최소화할 수 있는 방법을 강구해야 한다.

함수를 필터 조건으로 사용할 때 주의 사항

함수를 WHERE 조건 절에서 필터 조건으로 사용할 때는 주의가 필요하다.
=> 조건절과 인덱스 상황에 따라 함수 호출 횟수가 달라지기 때문

케이스 1) FULL SCAN

=> 스캔하면서 읽은 전체 건수만큼 함수 호출

케이스 2) 인덱스 선두컬럼의 조건절과 함수 비교

=> 함수 호출이 단 한 번 일어남
함수를 먼저 실행하고, 리턴된 값으로 인덱스를 액세스하는 상수 조건으로 사용
조건절에서 함수 비교하는 부분이 인덱스를 액세스하는 경우 함수 호출은 단 한 번 일어남

케이스 3) 선두 컬럼이 "="로 액세스 + 필터 조건에 함수 비교

=> 테이블을 액세스하는만큼 함수 호출이 일어남

케이스 4) 선행 컬럼이 누락된 경우

=> 인덱스를 스캔할 첫번째 레코드 액세스 단계 1번 +
필터 단계 n번 + 마지막 one-plus 1번

케이스 5) '=' 조건이 아닌 경우 (>=, <=)

=> 인덱스를 스캔할 첫번째 레코드 액세스 단계 1번 +
필터 단계 n번

함수의 읽기 일관성

쿼리를 수행하고 결과집합을 Fetch하는 동안 다른 세션에서 테이블의 value 값을 변경한다면 중간부터 다른 값을 리턴하게 될 것이다.
가장 기본적인 문장수준 읽기일과넝이 보장되지 않는 것으로, 함수 내에서 수행되는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽기 때문에 생기는 현상이다.

함수/프로시저를 잘못 사용하면 성능을 떨어뜨릴 뿐 아니라 데이터의 정합성까지 해칠 수 있으므로 주의해야 된다.

함수의 올바른 사용 기준?

정해진 Shared Pool 크기 내에서 소화할 수 있는 적정 개수의 SQL과 PL/SQL 단위 프로그램을 유지하도록 노력해야 한다.
역할을 분담해 연산 위주의 작업은 애플리케이션 서버 단에서 주로 처리하고,
SQL 수행을 많이 요하는 작업은 오라클 함수/프로시저를 이용하도록 설계해야 한다.

8. PL/SQL 함수 호출 부하 해소 방안

PL/SQL (사용자 정의 함수)은

소량의 데이터 조회 시
대용량 데이터 조회 시에는 부분범위처리가 가능한 상태
조인이나 스칼라 서브쿼리 형태로 변환
어쩔 수 없을 때는 호출 횟수 최소화

어쩔 수 없을 때 호출 횟수를 최소화하는 방법을 알아보자..

1. 페이지 처리, 부분범위처리 활용

서브쿼리에 사용자 정의 함수, order by 정렬 후 메인쿼리에 rownum으로 몇 건 출력하는 쿼리가 있다면, 모든 부하를 다 받고 결과집합을 출력하게 된다.
이럴 때는 order by와 rownum에 의해 서브쿼리에서 필터처리를 한 후에 메인쿼리에서 사용자 처리 함수를 그 최종 결과 집합에 대해서만 함수 호출이 일어나게 처리하면 된다.

요즘같은 n-Tier 환경에서는 페이지 처리가 필수다 보니 가장 흥히 접하게 되는 튜닝 사례이다.

페이지 처리를 하지 않더라도 부분범위처리가 가능한 상황이라면 클라이언트에게 데이터를 전송하는 맨 마지막 단계에 함수 호출이 일어나도록 함으로써 큰 성능 개선을 이룰 수 있다.
=> 가장 바깥쪽 쿼리 select-list 에 함수를 기술한다

2. Decode 함수 또는 Case문으로 변환

함수가 안쪽 인라인 뷰에서 order by 절에 사용되거나, 전체 결과집합을 모두 출력하거나, insert-select문에서 사용된다면 다량의 함수 호출을 피할 수 없다.
그럴 때는 함수 로직을 풀어서 Decode나 Case문으로 전환하거나 조인문으로 구현할 수 있는지 먼저 확인해야 한다.
함수에 입력되는 값이 많지 않다면 스칼라 서브쿼리를 사용해 캐싱 효과를 이용해야 한다.

3. 뷰 머지 방지를 통한 함수 호출 최소화

뷰 머지(View Merge)란?

옵티마이저가 VIEW(또는 Inline View)를 해체해서 바깥 쿼리와 하나의 쿼리처럼 합쳐 실행하는 최적화 기법이다.

이 기법이 필요한 경우 좋은 기법이지만 쿼리에 함수 호출이 있을 경우 튜닝을 해도 결과값이 똑같을 수 있다.
이럴 때는 튜닝을 하고 뷰 머지를 방지해줘야 한다.

옵티마이저에게 no_merge 힌트를 주거나 뷰 내에 rownum을 사용하면 옵티마이저는 절대로 뷰 머지를 시도하지 않는다.
=> 뷰 내에 rownum이 있는 쿼리와 바깥 쿼리와 조인해버리면 결과가 달라질 수 있기 때문

4. 스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화

스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해 둔다.
스칼라 서브쿼리에 있어 입력 값은, 거기서 참조하는 메인 쿼리의 컬럼 값을 말한다.
스칼라 서브쿼리의 입력 값과 출력 값은 UGA(User Global Area)에 캐싱돼서 Fetch Call과 상관없이 그 효과가, 캐싱되는 순간부터 끝까지 유지된다.

서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고, 없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장해 두는 원리이다.
이 기능을 함수 호출 횟수를 줄이는 데 사용할 수 있는데, 함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한 번 감싸는 것이다.

insert-select문이나 부분범위처리 활용 없이 전체 데이터를 출력해야 하는 상황에서 함수 호출 때문에 성능이 크게 떨어진다면 스칼라 서브쿼리를 활용함으로써 성능을 개선할수 있다.
이 기법은 입력 값의 종류가 소수여서 해시 충동 가능성이 적은 함수에만 적용해야 한다.

5. Deterministic 함수의 캐싱 효과 활용

10gR2에서 함수를 선언할 때 Deterministic 키워드를 넣어 주면 스칼라 서브쿼리를 덧입하지 않아도 캐싱 효과가 나타난다.
함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.
Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다.
Deterministic 함수도 PL/SQL과 같이 일관성 있는 결과 출력을 하지 않는다.
따라서, 함수가 쿼리문을 포함할 때는 캐싱효과를 위해 함부로 Deterministic으로 선언해서는 안 된다.