[SQL튜닝] 오라클 CPU 많이 차지하는 쿼리 찾기

CPU부하 급증 SQL 찾기
먼저 시스템 자원현황을 살펴보기 위해서 unix에서 top을 실행한다.
[KAMCO:/oracle/app/oracle/product/806/work]# top
load averages:  1.54,  1.47,  2.07                                                         12:24:08
1461 processes:1457 sleeping, 2 stopped, 2 on cpu
CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap
Memory: 9216M real, 211M free, 9434M swap in use, 7976M swap free
  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
17334 oracle     1  51    0 2510M 2488M sleep  36:46  2.24% oracle
29538 root       5  55    0 4808K 3632K sleep   3:50  1.48% save
29536 root       5  53    0 8048K 6864K sleep   3:34  1.47% save
29537 root       5  60    0 4768K 3648K sleep   0:22  1.35% save
24582 root       1   0    0  414M 1288K sleep 150.0H  0.86% rtf_daemon
9781 oracle    11  58    0 2510M 2481M sleep 933:20  0.74% oracle
6993 oracle     1  20    0 2509M 2485M cpu9   83.3H  0.57% oracle
2208 oracle     1  50    0 2515M 2492M sleep   0:01  0.52% oracle
2211 oracle     1   0    0 2592K 1712K cpu8    0:00  0.36% top
  476 tuxkigum  11  50    0 2524M 2491M sleep  45:13  0.32% oracle
  470 tuxkigum  12   2    0 2522M 2491M sleep  45:24  0.12% oracle
  474 tuxkigum  12  58    0 2524M 2490M sleep  41:19  0.10% oracle
25911 kamzone   11  14    2 2510M 2486M sleep   2:00  0.10% oracle
8824 xwnts     39  23   12  322M   51M sleep  82:17  0.10% java
17692 oracle     1  25    0 2515M 2491M sleep 111:29  0.09% oracle

이중에서 cpu의 사용량이 많은 프로세스(17334)에 대해서 어떤 SQL이 사용되고 있는지
살펴보자. 아래의 SQL을 cpu_overhead.sql로 저장하고 실행한다.
---------------------------------------------------------------------------------------
-- programed by Lee Chang Kie --
ttitle 'Cpu Overhead SQL Check'
clear screen
set verify  off
set pagesize 200
set linesize 110
set embedded off
set feedback off
col col0 format a25 heading "Sid-Serial"
col col1 format a10 heading "UserName"
col col2 format a10 heading "Schema"
col col3 format a10 heading "OsUser"
col col4 format a10 heading "Process"
col col5 format a10 heading "Machine"
col col6 format a10 heading "Terminal"
col col7 format a20 heading "Program"
col col8 format 9 heading "Piece"
col col9 format a8 heading "Status"
col col10 format a64 heading "SQL"
!rm -f ./cpu_overhead.lst
spool cpu_overhead.lst
Select A.sid||','||A.serial# col0,
       A.username col1,
       A.schemaname col2,
       A.osuser col3,
       A.process col4,
       A.machine col5,
       A.Terminal col6,
       upper(A.program) col7,
       C.piece col8,
       A.status col9,
       C.sql_text col10
  From v$session A, v$process B, v$sqltext C
Where B.spid = '&1'
   and A.paddr = B.addr
   and C.address = A.sql_address
order by C.piece;
spool off
----------------------------------------------------------------------------------------
[KAMCO:/oracle/app/oracle/product/806/work]# sqlplus internal
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Mar 4 13:01:40 2005
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @cpu_overhead
그러면 다음과 같이 프로세스 번호를 입력하라고 뜰 것이다.
Enter value for 1:
top명령을 실행했을 때 가장 상위에 나타는 프로세스ID(17334)를 입력한다.
그러면 아래와 같이 부하를 가중시키는 SQL이 검출될 것이다.
필요시 힌트, 인덱스정책, 실행계획등이나 트레이스를 떠서 필요한 튜닝을
수행해야 할 것이다.
                                            Cpu Overhead SQL Check
Sid-Serial                UserName   Schema     OsUser     Process    Machine    Terminal
------------------------- ---------- ---------- ---------- ---------- ---------- ----------
Program              Piece Status   SQL
-------------------- ----- -------- ----------------------------------------------------------------
664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      0 ACTIVE   SELECT A.LOAN_NO LOAN_NO,A.LOAN_TYPE LOAN_TYPE,NVL(A.SANGYE_DATE
V1-V3)

664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      1 ACTIVE   ,' ') SANGYE_DATE,NVL(A.RUPT_DATE,' ') RUPT_DATE,NVL(A.SANSIL_DAV1-V3) 

1. top
select  lpad(to_char(s.sid),6,' ') sid,p.spid,to_char(s.LOGON_TIME,'mm/dd hh24:mi:SS') Time, s.username,
        s.program, s.status, lpad(to_char(s.command),3,' ') cmd
  from v$session s,
       v$process p
 where s.paddr = p.addr and
       p.spid=&1
;

2. 위에서 나온 sid를 확인하여 아래 쿼리 수행 후 cpu 부하 유발 sql을 골라내어 튜닝대상으로 지정한다.

select s.sid, q.sql_text        
  from v$sql q, v$session s       
  where q.address=s.sql_address   
    and q.hash_value=s.sql_hash_value
    and s.sid in (&sid)
    ;