튜닝 대상 SQL문 찾기
DBA가 해야 하는 일중 중요한 한가지는 SQL문을 튜닝 하는 것 인데 우선적으로 튜닝의 대상이 되는 SQL문장을 찾아야
한다.
아래 스크립트는 parse call의 횟수가 SQL executions의 두 배 이상인 것을 추출하는데 이 결과 SQL문은 튜닝의
대상이 되는 것입니다. 또한 SQL문이 embedded literals에서 재 사용성이 좋지 않은 SQL문을 추출합니다.
재사용되지 않는 SQL문은 Library Cache에 놓여지면 오버헤드를 가져올 수 있다. 사용자들이 실행하는 SQL문장은
Library Cache에서 동일한 SQL문장이 있는지 매번 확인 하는데 잘 사용되지도 않는 SQL문장이 거기에 있으면 좋지 않다.
과거 Oracle 8i 이전 버전에서는 library cache를 줄이기 위해 shared_pool paramter를 낮게 또는
alter system flush shared pool 명령을 사용하기도 했다. .(참고로 library cache는 가장 최근에 실행된 SQL
문과 그것에 관한 정보를 보관한다)
----------------------------------------------
select
sql_text,
parse_calls,
executions
from v$sqlarea
where parse_calls > 300
and executions < 2*parse_calls
and executions > 1;
----------------------------------------------
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://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=43&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0
------------------------------------------------------------
라이브러리 캐시에 대한 아래 설명을 참고 하세요~
------------------------------------------------------------
라이브러리 캐시(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의 실행은 각 세션에 대한 개별 영역에 저장
된다.
평일주간(9:30~18:20) 개강
(4/22)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(4/29)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(4/29)C#4.0,ADO.NET,Network 프로그래밍
(5/12)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(5/12)[기업100%환급]SQL기초에서 Schema Object까지
(5/12)안드로이드개발자과정
평일야간(19:00~21:50) 개강
(4/22)웹퍼블리싱 마스터
(4/23)안드로이드개발자과정
(4/24)Spring3.X, MyBatis, Hibernate실무과정
(5/16)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(5/16)JAVA&WEB프레임워크실무과정
(5/16)C#,ASP.NET마스터
(5/16)SQL초보에서실전전문가까지
주말(10:00~18:00) 개강
(4/20)웹퍼블리싱 마스터
(5/03)안드로이드개발자과정
(5/17)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(5/17)SQL초보에서실전전문가까지
(5/17)개발자를위한PLSQL,SQL튜닝,힌트
(5/17)Spring3.X, MyBatis, Hibernate실무과정
(5/17)C#,ASP.NET마스터
주말저녁(18:30~22:20) 개강
(5/17)JAVA,JDBC,JSP,Servlet,Ajax,jQUERY,Spring,MyBatis,Hibernate
(5/17)SQL기초에서 Schema Object까지
댓글 없음:
댓글 쓰기