[Oracle] DBA의 역할 : oracle 모니터링 항목

Oracle DBA를 할 때 운영중인 Oracle DB의 모니터링 항목에 대해 알아보자.

 

이 항목은 필자가 운영업무를 하면서 필요한 것들을 모은 것이니, 표준이 있는 것은 아니다.

 

1. buffer cache hit ratio (90%이상)

select 
	trunc(( 1- ( phy.value - lob.value - dir.value)/ses.value) * 100) as cache_hit_ratio
from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
where ses.name = 'session logical reads'
and dir.name = 'physical reads direct'
and lob.name = 'physical reads direct (lob)'
and phy.name = 'physical reads';

2. dictionary cache hit ratio (95%이상)

select 
	trunc(sum(gets - getmisses) / sum(gets) * 100) as dictionary_cache_hit_ratio
from v$rowcache;

3. latch hit ratio (99% 이상)

select 
	trunc(( 1 - (sum(misses) / sum(gets))) * 100) as latch_hit_ratio
from v$latch;

4. library cache hit ratio (99% 이상)

select 
	sum(pins) as executions,
	sum(pinhits) as executions_hits,
	sum(reloads) as misses,
	round(( sum(pins) / (sum(pins) + sum(reloads))) * 100, 2) as hit_ratio
from v$librarycache;

5. memory sort ratio (90% 이상)

select 
	m.value,
	d.value,
	trunc(m.value * 100 / (d.value + m.value)) as memory_sort_ratio
from (select value from v$sysstat where name = 'sorts (disk)') d,
	(select value from v$sysstat where name = 'sorts (memory)') m;

6. invalid object check (invalid object있는지 check)

select 
	owner,
	object_name,
	object_type,
	status,
	temporary,
	to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as last_ddl_time
from dba_objects
where owner in (select username from dba_users where account_status = 'OPEN')
and status = 'INVALID'
and to_char(last_ddl_time, 'yyyymmdd') = to_char(sysdate - 1, 'yyyymmdd');

7. tablespace usage pct check

SELECT 
	a.tablespace_name AS TABLESPACE_NAME,
	trunc(sum(a.maxbytes)/1024/1024/1024, 1) AS total_max_gb,
	trunc(sum(a.bytes)/1024/1024/1024, 1) AS total_gb,
	trunc(sum(a.bytes - nvl(b.free_bytes, 0))/1024/1024/1024, 1) AS used_gb,
	trunc(sum(nvl(b.free_bytes, 0))/1024/1024/1024, 1) AS free_gb,
	trunc(sum(a.bytes - nvl(b.free_bytes, 0))/sum(a.maxbytes)*100,1) AS used_pct
FROM (
	SELECT 
		tablespace_name,
		file_id,
		file_name,
		bytes,
		autoextensible,
		decode(maxbytes, 0, bytes, maxbytes) AS MAXBYTES
	FROM dba_data_files) a LEFT OUTER JOIN
	(
	SELECT 
		tablespace_name,
		file_id,
		sum(nvl(bytes, 0)) AS FREE_BYTES
	FROM dba_free_space
	GROUP BY tablespace_name, file_id) b
	ON (a.tablespace_name = b.tablespace_name
	AND a.file_id = b.file_id)
GROUP BY a.TABLESPACE_NAME 
HAVING trunc(sum(a.bytes - nvl(b.free_bytes, 0))/sum(a.maxbytes)*100, 1) >= 0  -- 특정 used_pct(%) 기준 조회 시

8. log switch횟수

select 
	to_char(first_time, 'yyyy-mm-dd hh24') as sample_hh,
	count(*) as cnt
from v$log_history
where thread# = 1
and to_char(first_time, 'yyyy-mm-dd') = to_char(trunc(sysdate-1), 'yyyy-mm-dd')
group by to_char(first_time, 'yyyy-mm-dd hh24')
having count(*) >= 0	-- 특정 횟수 이상 조회 시
order by sample_hh;

9. datafile 개수와 db_files파라미터 비교

select
	(select count(*) from dba_data_files) as current_datafile_cnt,
	value as max_datafile
from v$parameter
where name = 'db_files';

 
 
10. alert_log 체크

실제 alert_log에서 "ORA-" 로 시작하는 error check하기

11. session 체크

* processes라는 파라미터 값이 실제 접근할 수 있는 session 최대 값을 의미한다.

SELECT *
FROM (
	SELECT 
		to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS dt,
		count(*) AS cnt
	FROM v$session
	WHERE status = 'ACTIVE');

위 쿼리를 통해 실시간 "ACTIVE" SESSION을 체크할 수 있으며, LOG를 남겨 processes parameter에 근접한지 모니터링 해야한다.

12. temp tablespace 체크

SELECT 
	a.tablespace_name,
	to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS dt,
	d.gb_total,
	round(a.used_blocks * d.block_size/1024/1024/1024, 4) AS used_gb,
	round((round(A.used_blocks * d.block_size/1024/1024/1024, 4))/d.gb_total*100, 2) AS used_pct
FROM v$sort_segment a,
	(
		SELECT
			b.name,
			c.block_size,
			round(sum (c.bytes)/1024/1024/1024,4) AS gb_total
		FROM v$tablespace b, v$tempfile c
		WHERE b.ts# = c.ts#
		GROUP BY b.name, c.block_size
	) d
WHERE a.tablespace_name = d.name;

위 쿼리를 통해 temp tablespace의 total size와 free size를 log로 남겨 모니터링 해야한다.

 

13. undo tablesapce 체크

SELECT
	to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS dt,
	tablespace_name,
	trunc(sum(total_bytes)/1024/1024/1024, 1) AS total_mb,
	trunc(sum(used_bytes)/1024/1024, 1) AS used_mb,
	trunc(sum(total_bytes - used_bytes)/1024/1024, 1) AS free_mb,
	trunc(sum(used_bytes)/sum(total_bytes)*100, 1) AS used_pct
FROM (
	SELECT 
		a.tablespace_name,
		CASE WHEN a.autoextensible = 'YES' THEN 
			a.maxbytes
		ELSE
			a.bytes
		END AS total_bytes,
		a.bytes - b.free_bytes AS used_bytes
	FROM 	
		(
			SELECT
				tablespace_name,
				file_id,
				file_name,
				bytes,
				autoextensible,
				maxbytes
			FROM dba_data_files
			WHERE tablespace_name LIKE 'UNDOTBS%'
		) a LEFT OUTER JOIN
		(
			SELECT
				tablespace_name,
				file_id,
				sum(nvl(bytes, 0)) AS free_bytes
			FROM dba_free_space
			GROUP BY tablespace_name, FILE_id
		) b
		ON (a.tablespace_name = b.tablespace_name
		AND a.file_id = b.file_id)
	ORDER BY a.tablespace_name, a.file_name
	)
WHERE tablespace_name LIKE 'UNDOTBS%'
GROUP BY tablespace_name
ORDER BY tablespace_name;

위 쿼리를 통해 undo tablespace의 total size와 free size를 log로 남겨 모니터링 해야한다.