[Oracle] DB 모니터링 쿼리

• 쿼리가 optimize 가 제대로 되지 않아서 SQL cache 안에서 쉽게 사라지지 않는 쿼리를 확인 하는 쿼리 (1)

SELECT Disk_Reads DiskReads, Executions, SQL_Text SQLText
FROM
(
SELECT Disk_Reads, Executions, LTRIM(SQL_Text) SQL_Text,
Operation, Options,
Row_Number() OVER
(Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
KeepHighSQL
FROM
(
SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
Max(Executions) OVER (Partition By sql_text) Executions,
sql_text, p.operation,p.options
FROM v$sql t, v$sql_plan p
WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
AND t.Executions > 1
)
ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=30;

• 쿼리가 optimize 가 제대로 되지 않아서 SQL cache 안에서 쉽게 사라지지 않는 쿼리를 확인 하는 쿼리 (2)

SELECT * FROM
(SELECT
sql_text,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;

• 특정 DISK_READS 횟수 이상만큼의 쿼리를 보는 쿼리 - 문제가 되었던 getsesseioninfo.jsp 는 141484750 회 읽혔음 OTL

SELECT parsing_user_id, executions, sorts, command_type, disk_reads,
sql_text
FROM v$sqlarea
WHERE disk_reads > &&access_level
ORDER BY disk_reads desc;

• V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA, V$SQL 뷰를 이용하여 현재 시스템에서 할당된 실행 영역 중 top 10을 찾습니다.

SELECT c.sql_text, w.operation_type, top_ten.wasize
FROM ( SELECT *
FROM ( SELECT workarea_address, actual_mem_used wasize
FROM v$sql_workarea_active
ORDER BY actual_mem_used desc)
WHERE ROWNUM <= 10 ) top_ten,
v$sql_workarea w, v$sql c
WHERE w.workarea_address = top_ten.workarea_address
AND c.address = w.address
AND c.child_number = w.child_number
AND c.hash_value = w.hash_value;

• V$SQL_WORKAREA 뷰를 통해서 다음과 같이 SQL 실행 메모리를 가장 많이 필요로 하는
  top 10 실행 영역을 구할 수 있다.

SELECT *
FROM ( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM v$sql_workarea
ORDER BY estimated_optimal_size DESC )
WHERE ROWNUM <= 10;

• 커서가 열린 뒤 닫히지 않아서 메모리가 반환되지 못하는 경우
• current cursor 가 몇백 몇천개인지 확인할 것
  

SELECT ss.username username, se.sid SID_NUM , p.spid SPID_NUM,
sum(decode(name,'recursive calls',value)) "Recursive Calls",
sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(name,'opened cursors current',value)) "Current Cursors"
FROM v$session ss, v$sesstat se, v$statname sn, v$process p
WHERE se.statistic# = sn.statistic#
AND ( name like '%opened cursors current%'
OR name like '%recursive calls%'
OR name like '%opened cursors cumulative%')
AND se.sid = ss.sid
and ss.paddr=p.addr
AND ss.username is not null
GROUP BY ss.username , se.sid , p.spid
order by "Current Cursors" asc

• 같은 쿼리지만 쉐어할 수 없어서 다른 쿼리로 인식되는 경우 카운트
  

select sql_text from v$sqlarea where version_count > 1;

• PGA/UGA 사용량을 산출하는 쿼리

ttitle '**********< Program Global Area >**********'
ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,
max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,
sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';

ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,
max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,
sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';

• i/o 사용량이 많은 순서대로 프로세스를 확인하는 쿼리

select A.disk_reads, A.sql_text,
B.sid, B.serial#
from v$sqlarea A,
v$session B
where A.disk_reads > 10000
and A.ADDRESS = B.SQL_ADDRESS(+)
AND A.HASH_VALUE = B. SQL_HASH_VALUE(+)
order by A.disk_reads desc;

• 메모리 사용량이 많은 순서대로 프로세스 확인(자세한 확인이 필요함...)

Select
D.sid "SID",
D.username "Oracle User",
D.program "Program",
D.module "Module",
D.status "Status",
D.type "Type",
D.server "Connection",
to_char(D.logon_time,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME,
D.osuser OS_USER ,
decode(D.lockwait,null,'','Blocking') LLOCK,
decode(D.taddr,null,'','TX') TRAN,
A.User_Name,
D.machine MACHINE,
B.Disk_Reads,
B.Buffer_Gets,
B.Rows_Processed,
C.SQL_Text,
A.Address
From V$Open_Cursor A,
V$SQLArea B,
V$SQLText C,
sys.v_$session D
Where 1=1
AND B.ADDRESS = D.SQL_ADDRESS
AND B.HASH_VALUE = D.SQL_HASH_VALUE
And A.Address = C.Address
;

• UGA/PGA 확인 쿼리
• 사용량이 peak 일때 돌려보자
  

SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC