|
<실습> - 날짜형 데이터를 공통 문자열 포맷으로 변환하려는 함수 - 사용자 정의 함수를 사용할 때 더 느리다. 6.29초 -> 9.43초 - Recursive Call 없이 컨텍스트 스위칭 효과만으로 5~10배정도 느려진다. => 수행시간이 길수록 부하가 심해진다. |
(3) Recursive Call를 포함하는 함수의 성능 부하
- 대개의 사용자 정의 함수에는 Recursive Call을 포함한다.
- Recursirve Call도 User Call에 비하면 작지만 매번 Execute Call / Fetch Call을 발생 시킨다.
=> 대량의 데이터를 조회하면서 레코드 단위로 함수를 호출하도록 쿼리를 작성하면 성능이 극도로 나빠진다.
- PL/SQL Created With Warning 뜨면서 ORA-00600 / ORA-00900 뜨는데... 해결하고 싶었는데 못하겠음..
- 결과는 I/O가 발생하지 않는 select 했을 뿐인데 수행시간은 훨씬 느려짐
- 수행통계
- Parse Call은 1번 뿐이지만 DB콜이 200만번 추가 발생
- I/O가 발생한다고 하면 더 큰 부하가 된다. 그리고, Recursive Call의 대부분은 I/O를 수반한다.
=> 가급적 조인 or 스칼라 서브쿼리 형태로 변환해야 한다.
(4) 함수를 필터 조건으로 사용할 때 주의 사항
- 함수를 where절에서 필터 조건으로 사용할 때도 주의해야 한다. -> 조건절 / 인덱스 상황에 따라 함수 호출 횟수가 달라지기 때문
<실습>
|
<Test 결과>
(5) 함수와 읽기 일관성
- session id 191에서 lookup 함수 호출하는 동안 session id 72에서 update 한 후 commit 했는데 값이 중간에 변경되면서 나오지는 않고, select문이 끊김 그 뒤에 값이 변경됨
- 함수를 잘못 사용해 읽기 일관성이 깨질 수 있는 다른 사례
- 주식 종목별 현재가 / 시가 총액은 수시로 변한다. -> 위와 같은 함수를 만들고 값을 집계하는 쿼리를 날리면 일관성 없는 결과는 낸다.
- '지수업종구성종목' 테이블의 PK는 '지수업종코드+종목코드'다. 즉, 한 종목이 여러 지수업종에 속할 수 있다.
=> 이 때, '코스피 대형주' 지수업종에 속한 삼성전자 주가와 '전기전자' 지수업종에 속한 삼성전자 주가를 쿼리 도중에 다르게 리턴한다면 결과집합은 일관성 없는 상태에 놓인다.
- 해결 하려면 일반 조인문 or 스칼라 서브쿼리를 사용할 때만 완벽한 문장수준 읽기 일관성이 보장된다.
- 읽기 일관성 문제는 프로시저 / 패키지 / 트리거를 사용할 때도 공통적으로 나타난다.
- 함수 / 프로시저를 잘못 사용하면 성능 뿐 아니라 데이터 정합성까지 해칠 수 있다.
(6) 함수의 올바른 사용 기준
- 오라클 함수 / 프로시저를 절대 사용하지 못하도록 개발표준이 정해지는 경우도 종종 있다.
- 오라클 Sequence 오브젝트를 사용하지 않는 한, Lock 경합을 최소화 하면서 이보다 더 빠르게 채번하는 방법은 없다.(2장에서 설명한 채번 함수)
- 채번을 위한 PL/SQL함수를 사용하지 않으면 애플리케이션 단에서 select / insert / update 날리면서 채번 해야 한다.
=> User Call을 발생시키는 것이므로 Recursive Call보다 현저히 느리며, Lock 경합 시 채기 시간도 더 길어진다.
- 배치로 처리하지 않는 이상 실시간 요건을 만족하면서 애플리케이션 단에서 트리거보다 더 빠르게 처리하는 방법은 없다.
- 함수 / 프로시저를 사용하지 않았을 때 결국 User Call을 발생시키도록 구현해야 한다면, 함수 / 프로시저를 사용하는 편이 더 낫다
- 함수 / 프로시저를 사용할 때 애플리케이션 단에서 구현할 때와 마찬가지로 데이터 일관성이 깨지지 않도록 설계하고 개발해야 한다.
- 라이브러리 캐시에서 관리해야할 오브젝트 개수와 크기가 늘어남 -> 히트율이 떨어짐 -> 경합이 증가하여 효율성 저하
- 데이터베이스 오브젝트 정의를 변경하면, 라이브러리 캐시 오브젝트간 Dependency 체인을 따라 순간적으로 동시 컴파일을 유발해 시스템 장애로 연결 될 수 있다.
결론 : 정해진 Shared Pool 크기 내에서 소화할 수 잇는 적정개수의 SQL과 PL/SQL단위 프로그램을 유지할 수 있도록 노력해야 한다.
08 PL/SQL 함수 호출 부하 해소 방안
1. 사용자 정의 함수 사용소량의 데이터 조회시
2. 대용량 데이터를 조회할 때는 부붐범위처리가 가능한 상황에서 제한적 사용
3. 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요
4. 어쩔 수 없을 때는 함수를 쓰되 호출 횟수를 최소화할 수 있는 방법을 강구
-함수 호출 부하 해소방안
1. 페이지 처리 또는 부분범위처리 활용
2. Decode 함수 또는 Case문으로 변환
3. 뷰 머지 방지를 통한 함수 호출 최소화
4. 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
5. Determinisitc 함수의 캐싱 효과 활용
6. 복잡한 합수 로직을 풀어 SQL로 구현
(1) 페이지 처리 또는 부분범위 처리 활용
- 아래 쿼리는 최종 결과 건수가 얼마건 간에 조건절에 부합하는 전체 레코드 건수만큼 함수 호출을 일으키고, 그 결과 집합을 Sort Area 또는 Temp 테이블 스페이스에 저장한다. 최종 결과 집합 10건만을 사용자에게 전송하게 된다.
- 아래 쿼리처럼 바꾸면, order by와 rownum에 의한 필터 처리 후 사용자에게 전송하는 최종 결과집합에 대해서만 함수 호출이 일어난다.
- 페이지 처리를 하지 않더라도 부분범위 처리가 가능한 상황이라면 클라이언트에게 데이터를 전송하는 맨 마지막 단계에 함수 호출이 일어나도록 함으로써 큰 성능 개선을 이룰 수 있다.
(2) Decode 함수 또는 Case문으로 변환
- 함수가 안쪽 인라인 뷰에서 order by 절에 사용된다든가, 전체 결과집합을 모두 출력하거나, insert select 문에서 사용된다면 다량의 함수 호출을 피할 수 없다.
=> 함수 로직을 풀어서 decode / case문으로 전환하거나 조인문으로 구현할 수 있는지 먼저 확인해야 한다.
=> 함수 로직이 복잡하거나 함수를 사용해야 한다면 입력되는 값의 종류가 얼마나 되는지 확인한다.
=> 값의 종류가 많지 않다면 함수를 그대로 둔 채 스칼라 서브쿼리의 캐싱효과를 이용한다.
- 체결 테이블을 쿼리해서 주식 종목에 대한 체결건수, 체결수량, 체결금액을 집계하려 한다.
- 일반적으로 시장, 증권그룹별 데이터 분성기 이루어지므로 이들 컬럼 기준으로 집계할 수 있도록 모델링
=> 업무에 따라서는 주식상품을 다르게 분류하고 집계하기도 한다.
- 집계용 쿼리를 작성하기 귀찮으니까 아래와 같은 함수를 정의한다.
- CASE 문 활용
- DECODE문 활용
- 결과는 동일하지만 시간이 다름
- Recursive Call이 포함되도록 함수를 바꿔서 테스트 해보자
- 4초 걸리던 것이 36초가 걸렸다 -> 분류순서 테이블을 쿼리하는 Recursive Call이 100만번 수행 됐음을 짐작할 수 있다.
- case or decode 함수는 여전히 빠른 수행속도를 보인다.
- 함수를 사용하는데 따른 장점 : 분류체계가 바뀌더라도 SQL들을 찾아 일일이 바꾸지 않아도 된다. => 함수 내용만 바꿔주면 됨
- 하지만 굳이 함수를 이용하지 않더라도 정보분류 및 업무 규칙, 규정들을 테이블화해서 관리한다면 매번 쿼리를 바꾸지 않고도 함수가 갖는 장점들을 그대로 가져올 수 있다.
- 이 사례에서 상품분류가 바뀔 수 있다면 아래처럼 시장코드와 증권그룹코드별 분류명을 메타정보로써 관리하는 테이블을 만들어 사용하면 된다.
- 조인 때문이 성능이 느려질 것이라고 생각했지만 수행시간은 극히 짧다.
=> 100만건을 그대로 조인한것이 아니라 GROUP BY 한뒤 20건으로 압축된 결과집합을 가지고 조인한것이므로
(3) 뷰 머지 방지를 통한 함수 호출 최소화
- 함수를 풀어 조인문으로 변경하기 곤란한 경우가 있다.
=> 함수를 그대로 두고, 함수 호출 횟수를 줄여야 한다.
- 100만건을 스캔하면서 SF_상품분류 함수를 3번씩 반복수행 = 300만번의 함수호출이 발생한다.
- 100만번 호출 할 때, 36초 정도 걸리던데 3배정도 늘어난것을 볼 수 있다.
- NO_MERGE / ROWNUM을 사용하면 시간이 3배정도 준것을 볼 수 있다.
- 스칼라 서브쿼리의 캐싱효과를 이용하면 더 빠르게 수행되도록 할 수 있다.
(4) 스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화
- 스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해 준다.
- 스칼라 서브쿼리에 있어 입력 값 : 참조하는 메인 쿼리의 컬럼 값
-> 서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴
없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장해 두는 원리
=> 함수 호출 횟수를 줄이는데 사용할 수 있다. -> 함수를 DUAL 테이블을 이용해 스칼라 서브쿼리로 한번 감싼다.
- 시장코드와 증권그룹코드로 만들어 질 수 있는 값의 조합이 20개이므로 SF_상품분류 함수에 대한 입력 값 종류도 20개
=> 20개에 대한 입력 값과 출력 값을 캐싱한다면 함후 호출 횟수를 20번으로 줄일 수 있다.
- 함수호출 20번을 예상했지만 70만번이 발생했다.
=> 해시 충돌이 발생했기 때문이다. ?? 충돌이 발생하면 기존 에느리를 밀어내고 새로 수행한 입력 값과 출력 값으로 대체할 것 같지만, 오라클은 기존 캐시 엔트리를 그대로 둔 채 스칼라서브쿼리만 한 번 더 수행하고 만다.
=> 해시 충돌이 발생한 입력 값이 반복적으로 입력되면 스칼라 서브쿼리를 사용하기 전처럼 여전히 쿼리가 반복 수행 된다.
=> 10g에서는 입력과 출력 값 크기, _query_execution_cach_max_size 파라미터에 의해 캐시 사이즈가 결정 된다.
- 히든 파라미터 값을 증가 시킨 후 테스트 하였다.
- 1초도 안걸려서 수행이 완료되었고, 함수 호출도 20번만 일어났다.
- 8i, 9i에서 테스트하면 기본적으로 256개를 캐싱한다. => 파라미터 조정 없이도 이과같은 성능 개선 효과를 얻을 수 있다.
- insert ~ select문이거나 부분범위처리 활용 없이 전체 데이터를 출력해야 하는 상황에서는 스칼라 서브쿼리를 활용해라
- 1절에서 보았떤 쿼리의 함수 호출 부분을 스칼라 서브쿼리로 덧씌운 것이다.
-> select-list에 기술함으로써 성능을 개선할 수 있음을 설명했지만, 페이지 처리 or 부분범위처리가 불가능한 상황에서는 효과가 전혀 없다.
- 스칼라 서브쿼리를 활용함으로써 큰 효과를 얻는다 => 입력 값의 종류가 소수여서 해시 충돌 가능성이 적은 함수에만 적용!!
(5) Deteministic 함수의 캐싱 효과 활용
- 10gR2에서 함수를 선언할 때 Determinisitc 키워드를 넣어 주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱효과가 나타난다.
- 함수의 입력 값과 출력 값은 CGA (Call Global Area)에 캐싱 된다.
- CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하다 => Fetch Call이 완료되면 그 값들은 모두 해제된다.
=> Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다!!
- 스칼라 서브쿼리에서의 입력 / 출력 값은 UGA에 저장되므로 Fetch Call과 상관없이 그 효과가 캐싱되는 순간부터 끝까지 유지
- 위의 ACCUM 함수는 1부터 입력 값까지 누적 합을 구하는 함수이다.
- 함수를 DETERMINISTIC으로 선언하고 컴파일 하였다.
- clinet_info 값을 삽입하여 함수 호출 횟수를 확인해본 결과 50번의 호출이 일어났다.
=> 수행시간은 1초가 채 걸리지 않았다.
- sum을 구하는 쿼리이므로 한 번의 Fetch Call 내에서 캐시 상태를 유지 하며 처리를 완료!!
- deterministic 제거하니까 함수 호출 100만번 발생 => 6.34초 시간 소요됨
- 함수 안에 쿼리 문장을 포함하고 있다면 그 함수는 일관성이 보장되지 않는다.
=> 같은 입력 값에 대해 언제라도 다른 출력 값을 낼 수 있다는 뜻
=> Deterministic 함수 일때도 마찬가지 -> Deterministic 키워드는 그 함수가 일관성 있는 결과를 리턴함을 선언하는 것일 뿐
=> 함수의 일관성 있는 결과 출력을 책임진다는 선언적 의미만을 갖는다.
- 함수가 쿼리문을 포함할 때는 캐싱효과를 위해 함부로 Deterministic으로 선언해선 안된다.
(6) 복잡한 함수 로직을 풀어 SQL로 구현
- 수정주가 : 현재와 과거 주가를 비교할 때는 수정주가를 사용 한다. ->조회 시점 기준으로 과거 주가를 수정하는 것이다.
=> 당일 주가와 전일 주가의 수정비율을 구하고, 이 비율을 주가에 반영함으로써 수정주가를 구한다.
- 수정 주가는 거래일 이후에 발생했던 주가 수정비율을 모두 곱해서 구한다.
- 업무가 복잡하다 보니 회사에서는 수정주가를 구하는 함수를 사용한다. 넓은 검색 기간과 많은 종목을 대상으로 조회하면 성능이 저하된다.
=> 성능 문제를 해결하기 위해 매일 야간 배치로 수정주가를 미리 구해 저장해 두고 있다.
=> 과거 10년치를 보관하는 많은 집계 테이블 중 주가를 속성으로 갖는 테이블들을 매일 업데이트 하는 것이다.
- 미리 구해둔 수정주가는 가장 마지막 이벤트가 발생한 이후 시점으로 조회할 때만 유요하다는 문제가 발생한다.
- 예를 들어 12월 25일 시점을 조회하는 경우에서는 12월 27일에 발생한 수정비율을 수정주가에 반영되지 않아야 하므로 미리 구해 둔 수정주가는 의미 없게 된다.