C#,Delphi,Oracle,MSSQL 개발자블로그

[Oracle] 커서란 무엇이고 SQL에서 어떻게 사용되는가 본문

Programming/Oracle

[Oracle] 커서란 무엇이고 SQL에서 어떻게 사용되는가

19760323 2017. 6. 5. 16:23

먼저 오라클에서 쿼리를 날리게 되면, 오라클은 옵티마이저라는 놈을 이용해서
실행계획 즉 plan이라는걸 짜게(?) 됩니다.
이 때 실행계획이 완성되기 전까지는 꽤 많은 리소스들이 사용된다고 합니다.
실행계획이란건 이런거죠.
예를 들어 select empno from student where empno = 100
이렇다면, 오라클은 empno컬럼에 걸린 인덱스를 가지고 empno가 100인것을 찾겠다..
뭐 이정도 되겠죠. 물론 이건 너무 간단한 예이니깐..머..
그런데 조금 있다가 누군가가 select empno from student where empno = 100이라고 똑같은 쿼리를 날리게 되면, 오라클은 흠..새로운 쿼리가 들어왔으니 또 계획을 세워야 겠구만..헉헉헉..
이렇게 한다면 곤란하겠죠? 앞서서 말씀드렸지만 계획을 세우고 그 결과에 따라 결과를 가져오기까지는 내부적으로 일이 많다구요.
내부적인 일은 이런겁니다. 일단 권한을 확인하고, 어떻게 가져오면 제일 빠르고..뭐 이런거죠.
일단 쿼리를 날리면 오라클은 메모리, 그러니깐 shared pool이라고 불리는 오라클의 메모리 영역에서 똑같은 쿼리가 있는지 확인합니다. 그래서 똑같은 쿼리가 있다면 그걸 이용하죠. 왜냐면 거기엔 이미 최적의 실행계획이 포함되어있기 때문에 또 다시 계획 따위를 세우는 번거로운 일을 안해도 되기 때문이죠.

바로 이렇게 이미 수행된 SQL문을 저장하는 영역(?), 단위(?)가 바로 커서입니다.

이에 따른 오라클의 파라미터값은 initSID.ora에 open_cursors = 50 이런거죠.
default값이 50입니다. 한 세션에서 열수있는 최대 커서개수죠.
필요하다면 이 수치를 높여줘야겠죠.
그런데 커서를 수를 늘리면 당연히 오라클이 메모리를 많이 먹습니다.
커서 하나가 늘때마다 10Kbytes(정확하지 않습니다..^^;;)가 늘어납니다.
그래서 커서가 늘면 늘수록 오라클은 "연속된 메모리 공간"을 찾게됩니다.
(이건 오라클의 스토리지 관리 메카니즘인 extent관리에서도 마찬가집니다, 언제나 연속된 공간을 필요로 합니다, 즉 tablespace가 100MB가 남이있어도, 10M짜리 익스텐트를 할당못할 경우에 에러가 발생합니다. 이 경우가 바로 연속된 10M짜리 공간이 없는 경우죠)
다시 말하자면 무조건 늘린다고 좋은건 아니라는거죠.

커서의 효과적인 활용을 위해서는 바인딩 변수라는걸 써야 합니다.
즉, select empno from students where empno=100
이렇게 쿼리를 만드실게 아니고..
select empno from students where empno=:a
이런식으로 바인딩 변수를 써줘야 합니다. 
그럼 커서를 적절히 이용하는 결과가 되죠.

또한 오라클은 대소문자 구분을 합니다.
예를 들어, select empno from students where empno=:a와
SELECT EMPNO FROM STUDENTS WHERE...는 다른 쿼리입니다. 오라클 입장에서는요.
그러나 물론 결과는 같죠.
다시 말하자면, 소문자 쿼리가 커서에 저장되어있어도, 대문자 쿼리가 올 경우 오라클은 실행계획부터 다시 합니다.

물론 쿼리 한 두개..암껏두 아닙니다.
그렇지만 큰 대형 DB에서 이런 일이 매일 매초마다 일어난다면 곤란할 수 있습니다.

오라클 816버전부터는 위의 바인딩 변수를 쓸 수없는 경우를 대비해서
initSID.ora에 cursor_sharing=force로 해주심 가능합니다.
initSID.ora를 수정하면 DB를 restart해줘야하는데요..
명령어로는
ALTER SESSION SET cursor_sharing = FORCE;
ALTER SYSTEM SET cursor_sharing = FORCE;
이렇게 해주심 되겠습니다.

마지막으로 현재 커서에 대한 정보는
select * from v$cursor; 뭐 이렇게 해보심됩니다.

또한 현재 메모리에 올라있는 SQL에 대한 정보는
v$sqlarea, v$sql..뭐 이런 뷰들을 보심 될검다.

진짜 마지막으로, 위에서 얘기한 v$어쩌구..하는 오라클이 제공하는 뷰들에 대해서 전체적으로 알고 싶으면..
select * from dictionary; 라고 하심 됩니다.
그럼 뷰들이 쫙~ 나오고 그에 대해 간단한 설명이 나옵니다.
비슷한 뷰는 dict_columns라는 뷰도 있습니다

 

 

 

CURSOR 란?

 오라클에서 CURSOR란 시스템 글로벌 영역의 공유 풀 내에 저장공간을 사용하여 사용자가 SQL 문을 실행시키면 결과값을 저장공간에 가지고 있다가 원하는 시기에 순차적으로 fetch해 처리하여 해당 결과 셋을 프로그래밍적으로 접근할수 있게 도와주는 기능이다.

 이해를 돕기위해 아래의 명시적 커서 예문을 보자.
 하단의 선언문(DECLARE)에서 SELECT한 결과값을 실행문(BEGIN)에서 FETCH 하여 한 결과값을 순차적으로 처리할 수 있다. 뭐 이해가 안가도 그냥 보자.


열심히 그렸다. 참견 말자.


CURSOR는 묵시적커서(Implicit Cursor)와 명시적커서(Explicit Curosr)로 나뉜다.



묵시적 커서 (Implicit Cursor)

 묵시적 커서는 각 SQL문장의 실행 결과에 접근하여 그 결과값을 이용하기 위한 내부적 커서이다. 간단히 변수를 이용한다고 생각해도 좋겠다. 일반적으로 SELECT문, 혹은 다른 속성에서 값을 얻어와 변수에 저장하는데 사용된다.

예문)
SELECT name 
          into v_name
 FROM members
 WHERE student_no = 13;
 v_count = SQL%ROWCOUNT;

상단의 예문의 빨간 부분은 모두 묵시적 커서가 사용된 문장이다. 예문 내에는 커서가 선언된 부분이 없지만 v_name변수로 해당 SELECT문의 결과값이 (당연히 저 SELECT문의 결과는 1개여야 한다. 그 이상일 경우 에라.) 저장된다. 또 하단의 SQL%ROWCOUNT의 값이 v_count변수에 할당된다. 물론 SELECT문에서 나온 행수는 1개이므로 v_count에는 1이 들어간다.


묵시적 커서에서는 위에 사용된 SQL%ROWCOUNT와 같이 4가지 속성을 제공한다. 

SQL%ROWCOUNT : 최근 실행된 SQL문의 결과 행 갯수를 리턴
SQL%FOUND : 최근에 실행된 SQL문의 결과 행 존재 유무, 결과값이 있을때 TRUE를 리턴.
SQL%NOTFOUND : SQL%FOUND와 반대
SQL%ISOPEN : 최근에 실행된 SQL문의 묵시적 커서의 종료 유무 





명시적 커서 (Explicit Cursor)

 명시적 커서는 처음의 맨 상단의 예제와 같이 일반적으로 어떠한 결과값을 글로벌 영역에 저장해놓고 순차적으로 값을 Fetch해 이용하기 위해 사용된다. 명시적 커서라고 불리우는 이유는 묵시적 커서와는 다르게 명시적으로 CURSOR라고 선언하고 사용하기 때문에 누가봐도 커서니깐. 그렇게 불린다. 머. 아님말고.

 명시적 커서는 간단히 아래와 같이 4단계로 나뉜다.

 CURSOR :  커서 선언
 OPEN : 커서 열기
 FETCH ~ INTO : 커서가 가리키는 곳의 결과 값을 엑세스
 CLOSE : 커서 닫기


 다시 한번 상단의 그림으로 한단계씩 살펴보자.



1) DECLARE 
    CURSOR cursor_name IS 
    sql_statement;
커서를 선언하고 커서 선언문 내에 선언된 sql_statment를 실행하여 해당 결과값을 시스템 글로벌영역에 결과 값을 저장한다.



2) BEGIN
    OPEN cursor_name;
해당 커서 내음의 작업을 위해 커서 영역을 오픈한다.
이때 선언문에서 선언한 sql_statement를 실행해 해당 결과값을 구성한뒤 커서는 해당 결과값의 첫번째 행에 커서를 위치 시킨다.


3) FETCH cursor_name INTO variable1, variable2, ...;

cursor_name의 커서를 순차적으로 FETCH한다. 이때 cursor_name의 SELECT 문에 있는 컬럼 갯수와 variables의 갯수가 값아야 한다. 그 순서대로 variables에 값이 할당된다.



4) CLOSE cursor_name;

현재 오픈되어 있는 커서를 닫는다. 



상단의 예에서 보듯이 CURSOR는 루프 문이 아니다. JAVA에서의 VECTOR등의 같이 결과값을 하나씩 순차적으로 FETCH할때만 사용된다. 적절히 사용하기 위해서는 LOOP문과 같이 사용하면 된다.



출처: http://doraeul.tistory.com/81 [도래울]

Comments