<오라클 성능 고도화 원리와 해법1> Ch04-05 바인드 변수의 중요성
오라클 성능 고도화 원리와 해법1 - Ch04-05 바인드 변수의 중요성
바인드 변수의 중요성을 설명하기 전에 간단한 테스트를 먼저 수행해보자. 눈으로만 보고 지나치지 말고 반드시 직접 테스트 해 보기 바란다.
인덱스를 만들고, 통계 정보를 만든 다음에 정상적으로 인덱스를 잘 타는지 실행 계획까지 확인해보았다. 이제 바인드 변수를 사용한 경우와 그렇지 않은 경우를 비교할 준비가 다 되었다. 먼저 바인드 변수를 사용하는 경우부터 테스트해보자.
20,000번 SQL을 수행하는 데 불과 1.23초가 소요되었다. v$sql을 통해 커서의 수행 통계를 확인해보니 20,000번 수행하면서 20,000번 Parse Call이 발생했지만37) 하드 파싱을 통해 Shared Pool에 적재하는 작업은 단 한 번 수행한 것을 알 수 있다. Unique Scan 이므로 Fetch 횟수는 Execute 횟수와 동일하다. 아래 SQL 트레이스를 통해 생성한 리포트에서도 같은 결과를 확인할 수 있다.
이번에는 바인드 변수를 사용하지 않고 SQL 조건절에서 Literal 상수값이 동적으로 바뀌도록 변경한 후에 테스트해보자.
무려 9.03초가 소요되었다. 앞에서 1.23초였던 것에 비하면 7.3배 높은 수치다. 수행 속도도 문제지만 아래 v$sql을 조회한 결과를 보면 더 놀라운 사실을 발견하게 된다.
for loop를 통해 수행된 SQL 각각에 대해 커서가 따로 생성된 것을 볼 수 있다. Parse Call과 Execute Call은 당연히 20,000번 발생했을 것이고, 하드 파싱까지 같은 횟수만큼 반복한 사실에 주목하기 바란다. SQL을 수행할 때마다 매번 하드 파싱을 일으킨 것이고, 이것이 7.3배 더 느려지게 만든 원인임을 알 수 있다. 그리고 v$sql을 조회한 결과 건수가 2,319개인 것을 보면 무거운 하드 파싱을 거쳐 캐시에 적재된 SQL Area 중 17,681개는 이미 캐시에서 밀려나고 없는 상태다.
옵티마이저 힌트를 사용하거나 로직을 수정하지 않고 단지 프로그램 작성 패턴만 조금 바꿨을 뿐인데 성능은 물론 시스템 리소스 사용 측면에서 실로 엄청난 차이가 발생한 것이다. 시스템 사양에 따라 수행 속도는 조금씩 차이가 나겠지만 독자 스스로 테스트해봄으로써 위와 같은 결과가 결코 조작된 것이 아님을 직접 확인하기 바란다.
아래는 앞에서 이미 본 그림 4-3을 다시 그린 것인데, 가운데 놓인 옵티마이저가 하는 역할을 다시 한번 상기하기 바란다.
종목별 거래 데이터를 조회하려고 위와 같은 식으로 SQL을 작성하면, 아래처럼 프로시저가 종목마다 하나씩 만들어지게 된다. 이들 프로시저를 만들어주는 역할을 옵티마이저와 Row-Source Generator가 담당한다고 했다.
종목별 거래 데이터의 분포가 편중되지 않았다면 위 프로시저의 내부 처리 루틴은 모두 같을 것이며, 여기에 가장 큰 문제가 있다. 모든 프로시저의 처리 루틴이 같다면 여러 개 생성하기보다 아래처럼 종목을 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 마땅하다.
procedure 거래(종목 in varchar2) { ... }
이처럼 파라미터 기반 방식으로 SQL을 작성하는 방법이 제공되는데, 그것이 곧 바인드 변수를 사용하는 것이다. 그러면 하나의 프로시저를 공유하면서 반복 재사용할 수 있게 된다.
SELECT * FROM 거래 WHERE 종목 = :종목
이미 테스트 결과를 통해 증명해 보였듯이 바인드 변수 사용에 따른 효과는 아주 분명하다. 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요 시간을 줄여준다. 궁극적으로, 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는 데 기여하고, 특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적이다.
바인드 변수 사용 원칙을 잘 지키지 않으면 라이브러리 캐시 경합 때문에 시스템 정상 작동이 어려운 상황에 직면할 수 있다. 그럴 때 cursor_sharing 파라미터를 변경하는 것을 고려해볼 수 있는데, 이 기능은 응급 처방으로 사용해야지 절대 영구 적용할 목적으로 사용해서는 안된다. 잘 사용하지 않는 기능이므로 본문에서 제외시켰지만 혹시 필요한 독자를 위해 부록에 그 활용법을 소개하였다. 혹시 필요하다면 그 특징과 주의사항을 잘 숙지하고 적용하기 바란다.