튜닝 대상 SQL문 & cursor_sharing

튜닝 대상 SQL문 & cursor_sharing

DBA가 해야 하는 일중 중요한 한가지는 SQL문을 튜닝 하는 것인데 그러기 위해서는 우선적으로 튜닝의 대상이 되는 SQL문장을 찾아야겠죠…?

아래 스크립트는 parse call의 횟수가 SQL executions의 두 배 이상인 것을 추출하는데 이 SQL의 결과로 나타나는 SQL문장이 튜닝의 대상이 되는 것이죠^^ 또한 SQL문이 embedded literals(library Cache)에서 재 사용 성이 좋지 않은 SQL문을 추출합니다.


---------------------------------------------------------------------------------
select
   sql_text,
   parse_calls,
   executions
from  v$sqlarea
where parse_calls > 300
and   executions < 2*parse_calls
and   executions > 1;  
---------------------------------------------------------------------------------


재사용되지 않는 SQL문은 Library Cache에 놓여지면 오버헤드를 가져올 수 있으며  (왜냐: 사용자들이 실행하는 SQL문장은 Library Cache에서 동일한 SQL문장이 있는지 매번 확인 하는데 잘 사용되지도 않는 SQL문장이 거기에 있으면 민페를 끼치는 거죠^^) Oracle 8i 이전 버전에서는 library cache를 줄이기 위해 shared_pool paramter를 낮게 또는 alter system flush shared pool 명령을 사용하기도 했습니다. .(참고로 library cache는 가장 최근에 실행된 SQL 문과 그것에 관한 정보를 보관 합니다)


Oracle 8i 이후의 cursor_sharing 파라미터를 사용하면 많은 이점이 있는데…
cursor_sharing parameter가 가질 수 있는 값은 EXACT, SIMILAR, FORCE 이며 그 중 FORCE인 경우 WHERE 조건 절에 정의된 상수가 다르더라도 Soft Parsing을 합니다.

즉 무슨 말인고 하니 where ename = ‘FORD’와 where ename = ‘SMITH’라는 서로 다른 SQL문장을 실행했다고 했을 때 기본적으로는각 SQL문장마다 파싱을 수행하며 library cache에서 두 군데 영역에 각 SQL문이 자리를 잡고 있겠지만(서로 다른 SQL문장으로 인식한다는 이야기) cursor_sharing = FORCE 인 경우엔 같은 SQL문으로 인식하여 파싱을 한번만 즉 Library Cache에 하나만 위치하게 된다는 것입니다.

Cursor_sharing parameter에 대해서는 다음 URL에서 꼭 확인 바랍니다. (중요!!)

http://oraclejava.co.kr/zboard/view.php?id=LecOrccleTun&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&keyword=cursor_sharing&select_arrange=headnum&desc=asc&no=16



------------------------------------------------------------
라이브러리 캐시에 대한 아래 설명을 참고 하세요~
------------------------------------------------------------

라이브러리 캐시(library cache)

라이브러리 캐시는 사용자가 요청한 질의 문장을 Server Process가 여러 단계를 거쳐 작업할 때 사용하는 작업 공간으로서 공유 SQL 영역과 개별 SQL 영역, PL/SQL 영역으로 나누어 볼 수 있습니다.  공유 SQL 영역에는 SQL문에 대한 텍스트, 파스 트리, 실행 계획등을 저장 하고 있으며 Shared PL/SQL영역은 최근에 실행한 PL/SQL 문장과 파싱되고 컴파일된 프로그램 Unit들, Procedure등을 저장 합니다. 개별 SQL 영역은 MTS(Multiple Transaction Server)에서 사용 합니다.

Library Cache의 가장 중요한 목적은 Library Cache안에 저장된 Object를 가장 빠르게 찾고 저장하는 기법을 제공하는 것으로 오라클의 모든 동적 메모리를 관리하는 Heap Manager와 library cache Manager에 의해 관리 되어집니다.

이러한 목적을 위해 Library Cache Manager도 Hashing 기법을 이용하여 Object에 대한 이름을 갖는 핸들을 찾고 이 핸들은 결국 Object를 가리키고 있는 겁니다. Hash Table은 Hash Bucket으로 구성 되어 있으며 이 Burcket들은 여러 개의 library cache Handle List를 가지고 있습니다.

Library Cache Handle은 결국 Library Cache Object를 가리키고 있으며 library cache Object의 이름, namespace, timespace, reference list, object를 locking하고 있는 lock list, pinning하고 있는 pin list를 포함하고 있습니다.

library cache에는 아래와 같은 Library Objects들이 있습니다.

SQL AREA(SQL CURSOR)
TABLE/PROCEDURE(VIEW, SYNONYM, SEQUENCE 등 포함)
BODY
TRIGGER
INDEX
CLUSTER
OBJECT(PL/SQL Anonymous BLOCK)
PIPE


이번에는 library cache Manager가 Object를 찾는 절차에 대해 보도록 하겠습니다.

1.        주어진 Object의 Namespace, Object Name, Owner, DataBase Link값을 hash function을 적용하여 Object가 존재하는 hash bucket을 찾습니다.
2.        hash bucket을 찾은 다음 linked list를 따라 Object가 존재하는 지를 check 합니다.
3.        만약 Object가 존재 한다면 9로 가고 아니면 library cache Manager는 주어진 이름으로 Empty Object를 생성 합니다.
4.        3에서 생성된 Empty Object를 Hash Table에 포함 시킵니다.
5.        Client에게 Object를 로드 하도록 요청 합니다.
6.        Client가 디스크에서 읽어 Object를 찾습니다.
7.        Heap Manager에게 Memory를 할당 하도록 요청 합니다.
8.        Object를 로드 합니다.
9.        찾은 Object를 사용 합니다.


오라클의 모든 SQL은 공유 SQL 영역과 개별 SQL 영역에서 수행 됩니다. 만약 두명의 사용자가 같은 SQL문을 사용 할 경우 공유 SQL 영역을 재사용 하여 자원을 절약 합니다.그러나 사용자는 개별 SQL 영역에 복사본을 보유하게 됩니다.

라이브러리 캐시에는 SQL 문장을 하나씩 실행 할 때 마다 사용된 SQL 문장이 저장되며 User가 이전과 동일한 SQL 문장을 실행하면 Parse Time이 줄어들어 성능이 향상 됩니다. 동일한 문장이란… 대.소문자.공백을 포함하여 문자열이 동일 해야 하며 참조된 Object가 동일 해야 하며 또한 변수 유형과 이름, 사용자가 동일 해야 합니다. 결국 100% 같은 문장만이 동일한 문장으로 인식한다는것 꼭 기억 하시기 바랍니다.


1.공유 SQL 영역 :  주어진 SQL 문장에 대해 Parse Tree와 실행 계획을 가지고 있습니다. 오라클은 여러번 걸쳐 사용되는 SQL문장을 하나의 공유 SQL 영역에 저장하여 관리 합니다. 만약 전체 공유 풀이 모두 사용되어 꽉 차게 되면 LRU 알고리즘에 따라 메모리 사용 빈도가 낮은 SQL을 제거 하게 됩니다.

2.PL/SQL 프러시저와 패키지 : SQL을 처리하는 것과 같이 PL/SQL 프로그램 Unit(Function, Procedure, Package, Anonymous Block)을 처리 합니다. SQL과 마찬가지로 구문 분석 후 컴파일 된 형태로 공유 SQL 영역에 저장 됩니다. 오라클은 프로그램 단위로 실행되는 세션에 할당된 값을 저장하기 위해 개별 SQL 영역에 메모리를 할당 합니다. 여기에는 지역변수, 전역변수, 패키지변수, 실행된 SQL에 대한 버퍼가 포함되며 이렇게하여 개별 사용자는 자신의 개별 SQL 영역에 분리된 복사본을 가지게 됩니다. PL/SQL 프로그램의 구문분석 정보와 실행 계획은 공유 SQL 영역에서 사용 되며 SQL의 실행은 각 세션에 대한 개별 영역에 저장 됩니다.