출처 : KACHISORI
제5회까지는 Oracle의 테이블의 액세스 방법이나 SQL 트레이스의 취득 방법, 실행 계획의 확인 방법등 SQL 튜닝을 실시하는데 반드시 알아 두어야 할 것을 설명했습니다. 지금까지의 정보 수집으로 실제로 SQL튜닝을 시작할 준비는 갖추어졌으므로 이번회에서는 걸러낸 SQL을 주의 깊게 살펴보삼
이번은 효율이 좋은 퍼포먼스가 뛰어난 SQL의 기술 방법에 대해서 SQL의 처리 스텝도 함께 설명해 갈 것입니다.
■ SQL의 기술을 통일하는 메리트
본연재는 지금까지 SQL 튜닝의 과정에서 읽어들여 블록수가 많은 혹은 처리 시간의 긴 SQL를 튜닝 대상으로 특정을 실시했습니다. 이러한 SQL는 데이터에의 액세스 방법이 적절하지 않은 것이 원인의 대부분입니다만.. SQL의 기술 방법에 대해서 너무 쉽게 간과해 버리는 것은 아닐까요..
튜닝 대상이 되는 어플리케이션이나, 배치처리등에서 특히 처리에 시간이 걸리는 SQL를 발견하지 못한 경우에도 데이타베이스에서 처리되는 SQL 전체에 관심을 가져서 WHERE 조건만 다른 유사 SQL가 많이 실행되지는 않는지도 조사합니다. 예를 들면, OLTP계의 처리등에서, 실행될 때마다 조건의 값(리터럴)이 다른 SQL를 실행하고 있는 어플리케이션이라면 리터럴치 부분을 변수화(바인드 변수화) 해, SQL의 기술을 통일해야 합니다.
SQL의 기술을 통일하는 것에 의한 메리트를 이해하기 위해서는, 먼저 Oracle에 있어서의 SQL의 처리 스텝을 이해할 필요가 있습니다.
■SQL의 처리 스텝
Oracle가 SQL를 실행할 때의 스텝은 크게
-
해석 처리(PARSE)
-
실행 처리(EXECUTE)
-
데이터의 꺼내 처리(FETCH) ＃ SELECT시만
의 3로 분류할 수 있습니다.
1. 해석 처리
해석 처리(이하, PARSE)는, 표 1과 같은 구문 체크, SQL의 최적화등을 실시하는 스텝입니다.Java나 C언어등에서 하는 「컴파일」에 해당하는 처리라고 생각하시면 됩니다. 한번 해석된 SQL는 실행 계획과 함께 SGA내의 일부인 공유 풀의 라이브러리 캐쉬 영역에, 캐쉬됩니다.
2. 실행 처리
PARSE 완료 후, 실제로 SQL의 실행 처리(이하, EXECUTE)를 합니다.삽입, 갱신, 삭제 처리는 이 EXECUTE로 SQL이 완료합니다.
3. 데이터의 꺼내 처리
SQL가 SELECT문의 경우에는 대상 데이터가 존재하는 경우, 데이타를 취득을 실시(이하, FETCH) 합니다.
|
처리 순서 |
처리 내용 |
|
1 |
구문의 체크 |
|
2 |
테이블, 열의 정의 체크 |
|
3 |
액세스 하는 오브젝트에의 권한 체크 |
|
4 |
실행 계획의 생성 |
|
5 |
공유 풀상에 실행 계획을 포함 해석 결과를 캐쉬 |
|
표 1 Oracle가 SQL를 실행할 때의 스텝 | |
■ 스텝의 상세
SQL가 발행되면 Oracle는 라이브러리 캐쉬를 체크해 동일한 SQL의 해석 결과가 캐쉬되어 있지 않은가 확인합니다. 캐쉬되고 있었을 경우, 나머지의 해석 처리를 스킵 해, 캐쉬되고 있던 실행 계획을 사용해 SQL를 실행할 수 있습니다.이 때의 동작을 SOFT PARSE라고 부릅니다.
동일한 해석 결과가 라이브러리 캐쉬상에 존재하지 않는 경우, 데이터 딕쇼내리에 많은 재귀 호출 콜(재귀 SQL)을 발행해, 표 1과 같은 처리를 실행합니다.이 동작을 HARD PARSE라고 부릅니다.HARD PARSE는 비교적 무거운 처리이기 때문에 대량으로 발생하면 데이타베이스 전체의 처리 퍼포먼스에 영향을 줍니다.
공유 SQL 기능을 유효하게 이용해 HARD PARSE를 회피함으로써 해석 처리에 의한 CPU 사용율의 절감, 공유 풀의 메모리 사용량 삭감등을 실현할 수 있습니다.그 결과 데이타베이스의 throughput를 향상시키는 것과 연결됩니다.
|
【정리】 |
|
|
HARD PARSE：공유 풀에 해석이 끝난 동일한 SQL문이 존재하지 않는 경우에 실행되는 해석 처리 | |
|
SOFT PARSE：공유 풀에 이미 해석 끝난 동일한 SQL문이 존재했을 경우에, 그것을 재실행하는 처리 | |
■ 재해석을 하는 경우
공유 풀에 캐쉬된 정보는 SQL이 완전하게 동일한 경우에게만 재이용됩니다. WHERE구로 지정되어 있는 조건치가 차이가 나거나 SQL의 의미가 동일해도 스페이스의 개수, 개행 위치, 대문자, 소문자 등, 조금이라도 차이가 나면 다른 SQL로서 다시 해석합니다. 표 2는 SQL이 공유되지 않는 예입니다.
표 2의 SQL은 모두 다른 SQL로서 해석됩니다. 어떠한 경우에 HARD PARSE가 발생하는지를 이해하여 HARD PARSE의 발생을 줄이는 것을 의식해야 합니다.
또한 캐쉬된 SQL가 액세스 하는 테이블, 색인의 정의 변경이나, 재작성을 실시하면, 캐쉬된 SQL는 무효가 되므로 다음 번의 실행시 하드 파싱이 발생합니다. 그때문에 이러한 처리를 실시하는 경우에는, 가능한 한 데이타베이스의 액티버티가 낮은 시간대에 실시하도록 할 필요가 있습니다.
|
실행된 SQL문 |
공유되지 않는 이유 |
|
SELECT * FROM emp WHERE empno=7788; |
인스턴스 재기동 ,공유풀의 플레쉬, 처음 실행할때 반드시 HARD PARSE 발생 |
|
SELECT * FROM emp WHERE empno=8000; |
WHERE구에 지정된 조건치가 틀림 |
|
SELECT * FROM emp WHERE empno=8000; |
스페이스 공간 갯수가 틀림 |
|
SELECT * FROM emp WHERE empno=7788; |
개행 유무가 틀림 |
|
SELECT * FROM EMP WHERE empno=7788; |
대문자 소문자가 틀림 |
2 SQL가 공유되지 않는 예
■ 동일 의미인 SQL 실행 결과
같은 결과를 돌려주는 이하의 SQL(1), (2)를 동일 세션에서 교대로 2회 실행합니다. 세션 단위의 통계 정보를 취득할 수 있는, 동적 퍼포먼스·뷰 「V$SESSTAT」를 참조해, 해석 처리가 어떻게 실행되고 있는지를 확인합니다.
|
SQL（1） |
|
예 1 실행하는 SQL |
해석 처리의 확인
 |
|
그림 1 같은 결과를 돌려주는 SQL의 해석 처리를 비교 |
SELECT문의 실행 결과는 같습니다만, (4), (5)그리고 확인하듯 SQL(1), (2)의 1회째의 실행 후에 「parse count(hard)」의 회수가 증가했습니다. 이것은 각각 다른 SQL로 HARD PARSE가 실행되고 있는 것을 나타내고 있습니다. 상기의 경우 SQL의 개행 위치가 다르므로 다른 SQL이라고 Oracle는 판단해 양쪽 모두의 SQL에 대해 HARD PARSE를 실시했습니다. 그에 비해 (6), (7)은 이미 해석 결과가 캐쉬되고 있기 때문에 「parse count (total)」의 회수만이 증가하고 있습니다.이것은 HARD PARSE가 실행되고 있으므로 해석 결과를 재이용한 것입니다.
어플리케이션을 개발할 때 대문자, 소문자, 개행 위치, TAB나 스페이스의 개수 등, SQL의 기술 룰을 세세하게 정해 두어야 합니다. 예로 SQL의 구문 (SELECT/DELETE/INSERT/FROM/WHERE등)은 대문자, 렬명, 표명등의 오브젝트명은 소문자, TAB는 1개 등, 구체적으로 결정해 두는 것이 중요합니다.
■ 바인드 변수화에 의한 해석 정보의 공유
앞에 기술한 예 1과 같은 SQL이 다른 값을 지정한 처리가 여러 차례 행해졌을 경우 WHERE구의 조건열의 값이 리터럴로 지정되어 있기 때문에(WHERE s_suppkey=1의 부분) 개행의 위치등을 통일한 것 만으로는 동일한 SQL로서 판단되지 않습니다. 이러한 경우에는 리터럴 부분에 바인드 변수를 사용하여 SQL의 기술을 통일, HARD PARSE를 억제할 수 있습니다.
리터럴을 사용한 SQL와 바인드 변수를 사용한 SQL를 1만회 반복했을 경우에 어떠한 차이가 나오는지를 확인해 보겠습니다. 그림 2는 리터럴로 기술한 SQL, 그림 3은 바인드 변수를 사용한 SQL로써 취득한 SQL 트레이스내의 재귀 호출 콜에 관한 결과입니다. HARD PARSE의 발생 유무에 대해 재귀 호출 콜의 발생 회수에 차이가 나타나고 있습니다.
 |
|
그림2 리터럴로 기술한 SQL |
 |
|
그림 3 바인드 변수를 사용한 SQL |
그림 2, 그림 3의 「Execute」는 모두 10001회입니다만, 「Parse」는 그림 2가 10001회, 그림 3이 2회가 되고 있습니다.또한 「Parse」의 「cpu(CPU 시간)」 「elapsed(경과시간)」를 보면 바인드 변수를 사용한 쪽이 (그림 3)는, 거의 Parse에 시간이 걸리지 않을것을 알수 있습니다. (※TKPORF의 결과는, 0.01초 미만은 출력되지 않습니다).「total」을 보더라도 바인드 변수를 사용한 SQL이 (그림 3)이 전체의 처리 시간이 압도적으로 짧은 것을 알수 있습니다.
이SQL은 단일 테이블에 관한 SQL입니다만 복수의 테이블에 엑세스하거나, 다른 스키마의 테이블을 엑세스하는 경우에는 특히나 많은 recursive call이 실행되므로 가능한한 바인드 변수화 하는 것이 유리 합니다. .
그림4의 그래프는 상기의 결과를 나타낸 것입니다.
 |
|
그림4 바인드변수와 리터럴에 의한 처리속도의 차이 |
■주의점
바인드화에 의한 메리트를 고려한다.
실행횟수가 적은 SQL이나 분석시간에 비해 실행시간이 긴 SQL등은 바인드화에 의한 메리트는 적습니다. 기존의 SQL의 바인드화를 검토할 경우 먼저 대량으로 실행되고 있는 유사한 SQL을 대상으로 합니다.
즉 데이터의 편중이 많고 히스토그램 통계정보를 취득하고 있는 경우에는 WHERE조건을 리터럴로 지정하는 것이 보다 좋은 실행계획이 선택될 가능성이 높아 집니다.
Oracle9i이후의 기능변경
Oracle9i이전 버젼에서는 바인드변수를 사용한 SQL은 변수에 셋팅된 치를 고려하여 실행계획을 결정하도록 변경되었있습니다.(바인드피크 기능). 그때문에 Hard parse발생을 계기로 이전과 다른 실행계획이 채용되는 경우가 있습니다.
운용중에 실행계획이 바뀌는 것을 피하기 위해서는 「_optim_peek_user_binds」파라메터를 「FALSE」로 설정해 바인도피크기능을 무효로 합니다. 바인트피크 기능을 무효로 함으로써 이전 버전처럼 바인도 변수 값을 고려하지 않고 실행계획이 결정됩니다.
즉 이 파라메터는 숨김 파라메터이며 인스턴스 전체에 영향을 주는 파라메터 이므로 기동중의 시스템의 경우에는 충분한 조사와 테스트를 실시할 필요가 있습니다.
커서의 공유에 대해
유사한 SQL문이 반복되고 있더라도 어플리케이션 변경이나 수정이 불가능한 경우도 있을수 있습니다. 이런 경우 오라클의 공유커서 기능을 사용함으로써 유사 SQL을 내부적으로 바인드 변수화해 해석결과를 공유시키는 것이 가능합니다.
이 기능은 바인드변수를 사용하고 있지 않는 SQL이라도 해석시간이 감소하여 대응 시간의 단축, Latch경합의 해소, 시스템리스의 사용률감소등을 기대할수 있습니다.
공유커서기능을 사용하기 위해서는 초기화 파라메터 「CURSOR_SHARING」을 설정합니다. 설정가능한 값과 기능은 아래와 같습니다.
-
FORCE：리터럴의 조금이라도 틀리더라고 동일 SQL로써 공유함
-
SIMILAR：실행계획이 동일하다면 동일 SQL로써 공유함
-
EXACT：완전일치의 텍스트를 포함하는 SQL만 공유함（디폴트）
댓글 없음:
댓글 쓰기