cursors(커서) 관리

0. 커서란
 *. 커서는 Private SQL의 작업영역이다.(sql이 하나 실행될때 마다 커서가 하나 열린다)
 * 커서가 열리면 반드시 닫아줘야 한다.
 * 간혹: 'ORA-01000 : 최대 열기 커서 수를 초과' 에러가 발생하는데 , 이는 커서를 열고 커서를 닫지 않아서 이다.
 > jdbc프로그램 하면서 Preparestatement , Result 이런것들을 close() 하지 않아서 발생
 ( Connection을 닫지 않았을 때는 'ORA-00020: maximum number of processes (100)' 와 같은 프로세스 수 에러가 발행한다.)
 *. 오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다.
 *. 커서의 종류
   - 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언된다.
   - 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서이다.
 *. 커서는 결과 릴레이션의 모든 튜플들을 튜플단위로 가리킨다.
   각 튜플은 차례대로 호스트 변수로 인출(FETCH)되어 호스트 언어 프로그램에 의해 처리될수 있다.
 *. 내장형 SQL문의 실행결과로 반환된 복수개의 튜플들을 접근할수 있도록 도와주는 개념.
 *. 질의 실행 결과로 반환된 테이블의 튜플들을 순서대로 가리키는 튜플에 대한 포인터로 생각하면 됨.
 *. 커서를 이용해서 질의 결과로 반환된 튜플들을 한번에 하나씩 차례로 처리 가능하다.
  - DECLARE : 커서를 정의하는 등 커서에 관련된 선언을 하는 명령어.
  - DECLARE CURSOR는 말그대로 커서를 사용하기 위해 선언하는 것이다.
  - OPEN : 커서가 질의 결과의 첫번째 튜플을 가리키도록 설정하는 명령어.
  - FETCH
   - 질의 결과에 대한 다중 튜플을 가지고 있는 커서에서 현재의 다음 튜플로 이동시키는 명령어.
   - 결과 집합(릴레이션)에서 하나의 행을 가져오고, 커서를 이동시킴.     
   - CLOSE : 사용한 커서를 닫는다는 것이다.
                 커서를 열기만 하고 닫지 않으면 나중에 DB가 뻗어버린다. 커서가 열릴 때마다 자원을 사용하기 때문이다.
                 그것을 제한하기 위해 parameter로도 제한을 둘 수 있다.
                 질의 실행 결과에 대한 처리 종료시 커서를 닫기위해 사용하는 명령어. (커서 종료 명령)
   - Pro*C : 오라클 데이터베이스 내의 테이블에 존재하는 레코드들을 조회, 수정 등의 데이터 처리를 하고,
          C 프로그램으로 전환하기 위해 사용하는 언어이다.          
   - PROCEDURE : 오라클 내에서 사용되는 오브젝트

 > 또, 루프안에서 위 객체를 생성하였으면, 루프내에서 close해줘야 한다. 루프밖에서 한번 닫으면 안된다.
 ,예를 들어 루프안에서 10번 객체를 생성해 놓고, 루프밖에서 1번 닫으면 9개의 객체는 커서를 닫지 않은 상태로 있다.
---------------------------------------------------

1. 데이터베이스의  open_cursors 값 확인
*. SQL> show parameter open_cursors ;
*. 의미: '한 세션당' 최대 오픈할 수 있는 커서수
*. 커서수 조정 : v$parameter 테이블의 open_cursors 수를 조정한다.
*. $ORACLE_HOME/dbs/init.ora value로 확인
*. OPEN_CURSOR, processses 파라메터 지정
*. 프로세스는 커서에 1.5배정도 적용
ex) 'alter system set open_cursors=커서수 scope=both' (memory/spfile/both: 기본값은 memory)
---------------------------------------------------

2. 열린 커서가 많은 세션 수 확인
select o.sid, osuser, machine, count(*) num_curs 
from v$open_cursor o, v$session s 
where o.sid=s.sid
-- and user_name = 'SCOTT'
group by o.sid, osuser, machine
order by num_curs desc;
---------------------------------------------------

3. 열린 커서에 대해 실행(안닫힌) 중인 sql 확인
select q.sql_text from v$open_cursor o, v$sql q  where q.hash_value= o.hash_value and o.sid= 125;

※. SID값으로 SERIAL# 찾기

select * from v$session where sid in (2272)

 

※. 세션 KILL하 하기(조회된 SID, serial#)

alter system kill session 'sid, serial#' ;
----------------------------------------------------------

4. 오래된 접속 세션 순서
SELECT o.sid, osuser, machine, min(s.last_call_et ) as lastcall
, sysdate, min(sysdate-s.last_call_et/24/60/60) as lastcalldate,  COUNT(*) num_curs
 FROM v$open_cursor o, v$session s
 WHERE  o.sid=s.sid 
 GROUP BY o.sid, osuser, machine
ORDER BY  lastcall DESC;
----------------------------------------------------------

5. 현재 열려있는 모든 세션 모니터링 정보
SELECT /*+ rule */
s.status "Status", s.serial# "Serial#", s.TYPE "Type",
s.username "DB User", s.osuser "Client User", s.server "Server",
s.machine "Machine", s.module "Module", s.terminal "Terminal",
s.program "Program", p.program "O.S. Program",
s.logon_time "Connect Time", lockwait "Lock Wait",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, s.serial#, si.sid, s.sql_address "Address",
s.sql_hash_value "Sql Hash", s.action
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND s.username IS NOT NULL
AND NVL (s.osuser, 'x') <> 'SYSTEM'
AND s.TYPE <> 'BACKGROUND'
ORDER BY 3
----------------------------------------------------------

6. 오래된 접속 sql순서
select a.*, q.sql_text
from
(
SELECT o.sid, osuser, machine, min(s.last_call_et ) as lastcall, sysdate, min(sysdate-s.last_call_et/24/60/60) as lastcalldate,  COUNT(*) num_curs,min(o.hash_value) hash
 FROM v$open_cursor o, v$session s
 WHERE  o.sid=s.sid
 GROUP BY o.sid, osuser, machine
ORDER BY  lastcall DESC
) a
, v$sql q
where
q.hash_value=a.hash
--and machine like '%webcash-4ej936i%'
ORDER BY  lastcall DESC
---------------------------------------------------

7. SQL문당 사용하는 커서수 - 해당sql이 사용하는 커서수를 말한다.
SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC