레이블이 oracle cursor인 게시물을 표시합니다. 모든 게시물 표시
레이블이 oracle cursor인 게시물을 표시합니다. 모든 게시물 표시

2012-10-07

DBMS/오라클] 각종 세션/커서 관련 유용한 쿼리


각종 세션/커서 관련 유용한 쿼리 | Oracle DB 2006/03/31 10:11


http://blog.naver.com/yc7497/130003056914


아래 모니터링 쿼리문은 여기 저기 기웃거리며 동냥해서 얻은 지식들을 적어 놓은 것입니다.
순서없이 뒤죽박죽이어서 보시기 불편할 지도 모르겠네요.

사용법은 오라클이 설치된 서버에 텔넷을 이용하여 root계정으로
접속후에 top 혹은 topas 명령으로 프로세스들을 감시하면서 cpu를 많이 차지하는 프로세스 아이디등을 발견하여 조치하는 것입니다.



==================================================

/*********************************************************************************************
* 각 테이블스페이스 사용용량 체크하기
*********************************************************************************************/
SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from    (   select     tablespace_name,
                            round((sum(bytes)/1024/1024),0) as total
               from       dba_data_files
               group by tablespace_name) a,
         (     select     tablespace_name,
                             round((sum(bytes)/1024/1024),0) as free
               from        dba_free_space
               group by  tablespace_name) b
where      a.tablespace_name = b.tablespace_name(+)
order by   a.tablespace_name;

/*********************************************************************************************

/*오라클서버의 메모리에 관한 테이블*/
select * from v$sgastat
 
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool

/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '675958'
order by c.PIECE



/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE

/*프로세스 아이디를 이용하여 쿼리문 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE

/*세션 죽이기(SID,SERAIL#)*/
ALTER SYSTEM KILL SESSION '8,4093'

/*hash_value 가지고 SQL 문장 찾아내는 쿼리 */
select sql_text from v$sqltext where hash_value= 317853294


/*오라클 세션과 관련된 테이블*/
select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

/*인엑티브 상태의 세션들-로그온타입이 과도하게 지나도록 없어지지 않는것은 세션을 죽이도록 한다
단 machine이 머신이름 에 해당하는 것만 죽이도록 한다
prev_hash_value 로 해쉬밸류를 알아내어 쿼리문을 확인할 수 있다
*/
select sid,serial#,username,status,logon_time,prev_hash_value,machine
from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'KILLED'

SELECT COUNT(*) FROM V$SESSION WHERE MACHINE ='머신이름' AND SCHEMANAME ='스키마이름'  AND STATUS = 'ACTIVE'
   
--현재 스키마이름 스키마로 실행중인(active) 프로세스를 알아내는 쿼리문
select SID, SERIAL#, SQL_HASH_VALUE, PREV_HASH_VALUE,ROW_WAIT_OBJ#, LOGON_TIME, MACHINE
from v$session
where schemaname ='스키마이름' and status = 'ACTIVE'
 
--위에서 알아내 hash_value 값을 이용하여 해당 프로세스가 실행중인 쿼리문 알아내기
select * from v$sqltext where hash_value = 2626426688-
order by piece

select * from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'ACTIVE' and ROW_WAIT_OBJ# > 0



/******************************************************************************************/
 현재 커서 수 확인하는 Query
/******************************************************************************************/


SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = '유저이름'
GROUP BY sid
ORDER BY cursor DESC


SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC





 
 

 
   116, 15217
 
    select sql_text from v$sqltext
 where hash_value= 3252988466 --1389452958
 order by piece
 
select * from v$session_wait
 
  select sid, serial#, username, taddr, used_ublk, used_urec
 from v$transaction t, v$session s
 where t.addr = s.taddr;


 
select *  from sys.v_$open_cursor
 
 
  select user_name, sql_text, count(*) cnt from sys.v_$open_cursor
 where user_name = '유저이름' and sid = 114
  group by user_name, sql_text
  order by cnt desc

 alter system kill session '27,127'

--ALTER SYSTEM KILL SESSION '45, 3977'
--이런식으로 통계정보를 생성하신 후에

 analyze table emp compute statistics;

 alter table customers pctfree 5 ;

select * from dba_tables
where table_name = 'RP_PART_MST'




/*******************************************************************************
* LOCK 관련
*******************************************************************************/

--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL  

--락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

--락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
       a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
  and b.id1 = c.object_id
  and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'


/**********************************************************************************************/
select *
  from v$session
  where sid in (80,100)

SELECT l.SESSION_ID,
    LPAD('  ', DECODE(l.xidusn,0,3,0))
    ||l.oracle_username "User Name",
    o.owner, o.object_name, o.object_type
    FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc


--아래 table 은 존재하지 않네...

SELECT sw.username "WAITING_USER", bu.username "LOCKING_USER",
    dw.lock_type, dw.mode_held, dw.mode_requested,
    dw.waiting_session, dw.holding_session
FROM dba_waiters dw, v$session sw, v$session bu
WHERE dw.waiting_session = sw.sid
ANd dw.holding_session = bu.sid  
   

SELECT s.username, s.sid, s.serial#
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid


SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id


Select *
FROM
  all_col_comments
WHERE
  table_name = '테이블이름'
 
 
/************************************************
 alter session으로 죽지않는 프로세스 죽이기
 1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
 2.su -오라클계정
 3.sqlplus '/as sysdba''
 4.connect system/sys
 5.ALTER SYSTEM KILL SESSION '137,1723'
**************************************************/

 
 



DBMS/오라클]오라클_커서_사용법


원본 출처 :: http://netme.kr/bbs/board.php?bo_table=sub_2011&wr_id=23


1.  커서의  정의

커서란  SQL  Plus에서  사용자가  실행한  SQL문의  단위를  의미합니다.
오라클렝서  수행한  모든  쿼리문은  커서  단위로  처리합니다.

PL/SQL의  SQL문처럼  하나의  결과를  리턴하는  경우  커서  없이도  SQL문의  실행결과가 
암시적으로  커서에  저장되므로  이를  암시적  커서라고  합니다.

SQL문을  수행한  후에  결과로  얻어지는  행이  여러  개일  경우에는  암시적인  커서에  정보를 
저장할  수  없기에  에러가  발생합니다.  이럴  경우에는  반드시  명시적인  커서를  사용해야  합니다.

명시적인  커서는  PL/SQL의  레코드(RECORD)와  PL/SQL의  테이블(TABLE)을  결합한  것으로서
프로그램  언어의  구조체  배열과  유사합니다.



[커서의  사용]

1)  커서를  선언한다.
CURSOR  cur_name

2)  커서를  오픈한다.
OPEN  cur_name

3)  커서에  조회한  결과를  인출해  지정한다.
FECTCH  cur_name  ...

4)  커서를  닫는다
CLOSE  cur_name






2.  20번  부서에  근무하는  사원의  정보를  출력하는  예제


ed  cur01


SET  SERVEROUTPUT  ON
declare
   vempno   NUMBER(4);
   vename   VARCHAR2(20);
   vsal   NUMBER(7,  2);

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   OPEN  C1;

   dbms_output.put_line('empno   ename   sal');

   LOOP
      FETCH  C1  INTO  vempno,  vename,  vsal;
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vempno)||'   '||vename||'   '||to_char(vsal));
   END  LOOP;


end;
/



저장하고  실행합니다.

@cur01
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL  procedure  successfully  completed.





3.  OPEN-FETCH-CLOSE가  없이  커서  처리

ed  cur02


SET  SERVEROUTPUT  ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   dbms_output.put_line('empno   ename   sal');

   FOR   vemp   IN   C1   LOOP
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal));
   END  LOOP;

end;
/


정장하고  실행합니다.

@cur02
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL  procedure  successfully  completed.



4.  커서의  상태

%NOTFOUND   커서  영역의  자료가  모두  FETCH  됬는가를  알려줌
%FOUND      커서  영역에  FETCH가  되지  않은  자료가  있는가를  알려줌
%ISOPEN      커서가  OPEN된  상태인가를  알려줌
%ROWCOUNT   FETCH된  RECORD가  몇  개  있는지  알려줌

cur02  예제를  수정합니다.

ed  cur02

SET  SERVEROUTPUT  ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   dbms_output.put_line('empno   ename   sal   record  count');

   FOR   vemp   IN   C1   LOOP
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal)||'   '||C1%ROWCOUNT);
   END  LOOP;

end;
/


저장하고  실행합니다.

@cur02

결과는  record  count  추가되었습니다.




5.  커서를  활용한  실용  예제
1)  급여  총합을  구하는  예제
ed  cur03

SET  SERVEROUTPUT  ON
declare
   tot   NUMBER  :=  0;

   CURSOR   emp_cursor
   IS
   select  ename,  sal
   from  emp;

begin
   dbms_output.put_line('name   sal');
   dbms_output.put_line('------------------------------------------');

   FOR  cur_var  IN  emp_cursor  LOOP
      tot  :=  tot  +  cur_var.sal;
      dbms_output.put_line(cur_var.ename);
      dbms_output.put_line('-   '||cur_var.sal);
   END  LOOP;
  
   dbms_output.put_line('------------------------------------------');
   dbms_output.put_line('-   '||tot);
end;
/


저장하고  실행합니다

@cur03




2)  사원별  급여  현황을  그래포로  표현
ed  cur04

SET  SERVEROUTPUT  ON
declare
   CURSOR   emp_cursor
   IS
   select  ename,  sal
   from  emp
   order  by  sal  desc;

   star   varchar2(100);
   cnt   number  :=  0;

begin
   dbms_output.put_line('   sal  of  emp');
   dbms_output.put_line('------------------------------------------');

   FOR  cur_var  IN  emp_cursor  LOOP
      star  :=  NULL;
      cnt  :=  round(cur_var.sal/100,  0);

      for  i  in  1..  cnt  loop
         star  :=  star||'*';
      end  loop;

   dbms_output.put_line(cur_var.ename);
   dbms_output.put_line('-   '||star||'('||cur_var.sal||')');
   END  LOOP;
end;
/


저장하고  실행합니다

@cur04