프로세스 ID로 실행중인 SQL알아 보기

http://blog.naver.com/tkpolee/80010740220에서 퍼온 내용입니다.

먼저 시스템 자원현황을 살펴보기 위해서 unix에서 top을 실행한다.

# 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)

.