오라클 커서(Cursor) & Oracle Memory

1.2 오라클 커서(Cursor) & Oracle Memory

 http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=154

n  커서는 SELECT 또는 DML(Insert/Update/Delete/Merge) 조작시 클라이언트 프로세스에서 참조하는 PGA또는 SGA의 Library Cache내 전용 SQL 영역(Private SQL Area, Context Area)을 가리키는 포인터 또는 핸들이다.

n  Private SQL Area, Context Area에는, SQL문 처리를 위한 모든 정보(처리된 로우 카운드 등)들을 담고 있는 커서는 SQL 명령으로 리턴되는 하나 또는 그이상의 ROW를 포함하는데 이를 Active Set이라 하고 Active Set의 처음 로우를 기본적으로 가리키고 있다.

n  DML에서 커서 관리는 오라클 서버에서 알아서 하지만 오라클 PL/SQL에서는 SELECT 명령에 대해 커서를 조작하는 몇 가지 방법을 제공한다.

n  SQL 커서는 오라클 서버 운영모드가 Dedicate Server 환경이면 PGA, MTS(MultiThreaded  Server) 환경이면 SGA의 Library Cache에 위치한다.

n  묵시적 커서는 오라클 서버의 모든 SQL에 대해 자동으로 만들어지는 커서를 말한다.

n  명시적 커서는 PL/SQL을 작성하는 개발자가 명시적으로 Declare절에 선언하여 사용하는 커서이며 다중행을 다룰 때 사용한다.

 

 

 

n  Shared SQL Area

오라클의 Shared Pool은 SQL문을 처리하고 커서를 공유하는데 사용되는 메모리 공간이며 SGA 내부에 생성된다. 파싱된 SQL 명령어, SQL의 실행계획, 파싱되고 컴파일된 PL/SQL 프로그램들이 저장되는 각 사용자 세션에서 공유하는 공간이다.

 

 

[Cursor & SGA내부 Library Cache]

 

n  DataBase Buffer Cache와 Server Process

데이터베이스 버퍼 캐시는 모든 사용자들이 공유하며 SGA안에 위치하여 Datafile로부터 읽은 블록의 복사본을 저장하는 곳이다. 두 개의 리스트로 구성되는데 Write List와 Least Recently Used(LRU) List이다. Write List는 수정되었지만 아직 디스크의 DataFile에 반영되지 않은 dirty buffers로 구성되며 LRU List는 사용가능한 free buffers, 현재 접근되고 있는 pinned buffers, 아직 Write List로 이동되지 않은 dirty buffers로 구성되어 있다.

 

[Cursor & PGA & Redo Log]

n  SQL구문의 Shared Pool Check

사용자가 SQL 구문을 실행할 때 오라클 서버 프로세스에서 가장 먼저하는 일이 문법체크(Syntax Check)이며 그 다음 동일한 SQL 구문이 이미 실행되었는지 해시값으로 Shared SQL Area에서 검사하는 일이다. 동일한 SQL구분이 있다면 이미 만들어진 파싱트리, 실행계획 등을 공유한다.

 

[SQL문 실행시의 Shared Pool Check]

 

 

 

Dedicate Server 방식

 

서버 프로세스는 클라이언트의 요청을 받아 SQL문들을 처리하는 프로세스인데, 하나의 서버 프로세스가 하나의 클라이언트 프로세스에 대응되는 구조를 가지는 형태를 Dedicate Server 방식이라 한다.

 

Shared Server 방식

 

오라클 리너스를 사용하는 방식이며 N개의 서버 프로세스에 모든 클라이언트의 요청 처리를 할당하는 구조이다. 사용자 프로세스의 요청을 디스패처가 받고 이를 요청큐(Request Queue)에 넣어두면 오라클의 Shared Server Process가 받아서 처리하는 구조다.

 

DRCP 방식

 

오라클 서버프로세스를 풀링하는 개념으로 각각의 Dedicate Server가 풀링된다.(오라클 11g 이후 가능한 방식)

 

PGA(Program Global Area)

n  PGA(Program Global Area)는 오라클 서버 프로세스가 시작될 때 생성되며 다른 세션과 공유되지 않는 영역으로 데이터베이스에 접속하는 사용자에 대응되는 오라클서버 프로세스가 사용하는 메모리 영역이다. 메모리 힙으로 세션에서 사용되는 변수, 데이터, SQL 등에서 정렬을 위한 작업공간으로 사용되며 이 공간이 부족하여 디스크에서 소트가 일어나면 SQL문은 느려지게 된다. 서버 프로세스에 할당되는 것이고 SGA에 생성되는 영역은 아니다.

 

n  PGA의 Private SQL Area는 파싱된 SQL문의 정보 또는 SQL처리를 위한 세션 정보(데이터, 변수)등을 가지는데, Server Process가 SQL, PL/SQL코드를 실행할 때 바인드 변수, 쿼리 실행 상태에 관한 정보 등을 저장하거나 쿼리 실행의 작업 영역(Work Area)으로 사용된다.

 

n  Shared Server 환경에서 UGA(User Global Area)안의 Shared SQL Area(실행계획이 저장되는 SGA내부의 요소)와는 구별된다. Shared Server 환경에서 UGA는 SGA 내부에 생성되며 Dedicate Server 환경에서는 PGA에 생성된다.

 

n  같거나 다른 여러 세션의 Private SQL Area가  SGA 내부의 하나의 실행 계획(Execution Plan)을 참조할 수 있다. 예를 들어 10개의 “SELECT * FROM EMP” 쿼리를 하나의 세션에서 5번, 서로 다른 5개의 세션에서 한 번씩 번 실행한다고 했을 때 이 모든 세션의 Private SQL Area에서 동일한 SGA내부 Library Cache안의 Shared SQL Area의 실행계획을 참조하고 각 세션의 Private SQL Area의 변수 및 데이터는 서로 공유되지 않는다.

 

n  PGA의 Private SQL Area는 RUN-TIME AREA, PERSISTENT AREA로 나눌 수 있다.

RUN_TIME AREA : 쿼리 실행의 상태정보를 보관하는 곳이며 FULL TABLE 스캔을 하는 경우 각 레코드를 검색하는 트랙과 같은 역할을 한다. 쿼리가 실행될 때 할당되고 종료되면 해제 된다.

PERSISTENT AREA : 바인드 변수를 포함하는 영역인데 바인드 변수 값은 쿼리가 실제 실행될 때 제공된다. 커서가 닫힐 때 반환되는 영역이다.

 

n  사용자 프로세스가 오라클 서버 프로세스를 호출하면 서버프로세스가 PGA를 할당하고 SQL Wrok Area를 생성한다. 이러한 공간을 통해 쿼리의 정렬 등의 작업이 이루어지고 SGA와 데이터를 주고 받으면서 작업을 한다. PGA는 UGA와 Stack Space로 구성되는데 아래와 같은 요소가 있다. (Dedicate Server에서는 User Session Data, Cursor State, Sort Area를 PGA에 저장하며 Shared Server에서는 User Session Data를 SGA에 저장한다.)

 

USER SESSION DATA : SELECT 한 값을 클라이언트로 전달하기 위한 사용자 프로세스의 정보를 저장하고 그 주소를 저장한다.

CURSOR STATE : 실행하는 SQL문의 파싱정보가 저장된 곳의 주소를 저장

SORT AREA : 정렬을 위한 공간.

STACK SPACE : SQL문장에 사용되는 바인드 변수 저장.

 

UGA(User Global Area)

n  사용자 세션과 연계된 메모리 영역으로 세션변수를 위해 할당된 메모리 영역이다.

n  세션변수 영역과 OLAP의 데이터 페이지를 저장하기 위한 OLAP POOL을 가진다.

n  Shared Server 환경에서 UGA는 SGA 내부 LARGE POOL(LARGE POOL이 없다면 SHARED POOL)에 생성되며 Dedicate Server 환경에서는 UGA는 PGA에 생성된다.

n  Shared Server 환경에서는 각 사용자 세션에서 동일한 SQL문을 실행한다면 SQL의 복사본이 SGA내의 UGA에 저장되며 Dedicate Server 환경에서는 개별 PGA에 저장된다.