[오라클]DB실시간 모니터링 문장

1. 프로세스 모니터링

1) 서버 프로세스
SELECT sid, serial#, username, status "Status of Session", server "ServerType", osuser, machine, program, process FROM v$session;
2) 사용자 프로세스
SELECT sid, serial#, osuser, process, machine, program, to_char(logon_time, 'yyyy/mm/dd hh:mi:ss') as Logon_Time FROM v$session WHERE (upper(server) <> 'PSEUDO' AND UPPER(program) NOT LIKE 'oracle.exe%') and serial# <> 1;

2. SGA 모니터링

1) Data Buffer Cache 구조
SELECT id, name, block_size, buffers FROM v$buffer_pool;

2) 히트율
SELECT phy.value "Physical Read",cur.value+con.value "Buffer Cache Read", (1-((phy.value) / (cur.value+con.value)))*100 "히트율" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';
3) Wait Status 확인
SELECT (SELECT total_waits FROM v$system_event WHERE event = 'free buffer waits') "Free Buffer Waits", (SELECT total_waits FROM v$system_event WHERE event = 'buffer busy waits') "Buffer Busy Waits" FROM dual;
4) 메모리 사용상태(세션별)
SELECT A.process, A.username, B.block_gets, B.consistent_gets, B.physical_reads FROM v$sess_io B, v$session A WHERE B.sid = A.sid;

3. 로그 버퍼 히트율 모니터링

SELECT S1.value "Redo Alloc Retries", S2.value "Redo Entries", S1.value / S2.value * 100 "히트율" FROM v$sysstat S1, v$sysstat S2 WHERE S1.name = 'redo buffer allocation retries' AND S2.name = 'redo entries';

4. Shared Pool 모니터링

1) 히트율
SELECT Gets, Gethits, Gethitratio*100 "히트율"FROM v$Librarycache WHERE namespace = 'SQL AREA';

2) Reload 상태
SELECT SUM(pins) "Executions(PINS)", SUM(reloads) "Cache Misses(RELOADS) " , SUM(reloads) / SUM(pins)*100 "Reload" FROM v$Librarycache;
3) Reserved Pool(응답상태)
SELECT requests, request_misses, request_failures, free_space, avg_free_size, max_free_size, used_space,avg_used_size FROM v$shared_pool_reserved;
4) Parsing SQL문 상태
SELECT cpu_time, elapsed_time,executions, loads, invalidations, version_count, parse_calls, sorts, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN (2,3,6,7) AND rownum <= 200 ORDER BY sql_text DESC;
5) 파싱 SQL문(메모리순)
SELECT buffer_gets, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7) ORDER BY buffer_gets DESC;
6) 파싱 SQL문(I/O순)
SELECT disk_reads, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7) ORDER BY disk_reads DESC;
7) Shard Pool 히트율
SELECT SUM(gets) " Gets", SUM(getmisses) "Cache Get Misses", SUM(getmisses)/SUM(gets)*100 "히트율" FROM v$rowcache;

5. Large Pool 메모리 상태

SELECT pool, name, bytes FROM v$sgastat WHERE pool = 'large pool';

6. 프로세스 상태

1) DBWR 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%DB%';
2) DBWR 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'free buffer waits';

3) CKPT 정보

SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%CKPT%';
4) CKPT 대기상태
SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event LIKE 'log file switch (checkpoint%';
5) LGWR 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%LGWR%';
6) LGWR 대기상태
SELECT sid, event, seconds_in_wait, state FROM v$session_wait WHERE event = 'log buffer space%';
7) PMON 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%PMON%';
8) PMON 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'pmon timer';
9) SMON 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%SMON%';
10) SMON 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'smon timer';

7. 데이터 파일 상태

1) Data-File 구조
SELECT B.file_id "File #", B.file_name, B.tablespace_name, B.bytes "Bytes", ((B.bytes - sum(nvl(A.bytes,0)))) "Used Space", (SUM(NVL(A.bytes,0))) "Free Space", (SUM(NVL(A.bytes,0)) / (B.bytes)) * 100 "FreeSpace Ratio" FROM sys.dba_free_space A, sys.dba_data_files B WHERE A.file_id(+) = B.file_id GROUP BY B.tablespace_name, B.file_id, B.file_name, B.bytes ORDER BY B.file_id;
2) Disk I/O 상태
select name, phyrds, phywrts FROM v$datafile DF, v$filestat FS WHERE DF.file# = FS.file#;
3) Object 종류
SELECT A.owner, A.object_id, A.object_name, B.partition_name, B.tablespace_name, B.bytes, B.blocks, B.extents, B.initial_extent, B.next_extent, B.min_extents, B.max_extents, B.pct_increase, B.freelists, B.relative_fno, B.buffer_pool, A.created, A.status FROM dba_objects A, dba_segments B WHERE A.owner = 'SCOTT' AND A.object_type = 'TABLE' ORDER BY A.object_name;
4) 롤백세그먼트 경합상태
SELECT SUM(waits) "Waits", SUM(gets) "Gets", 100 * SUM(waits)/SUM(gets) "히트율" FROM v$rollstat;
5) 롤백세그먼트 대기상태
SELECT (SELECT count FROM v$waitstat WHERE class = 'undo header') "Undo Header", (SELECT count FROM v$waitstat WHERE class = 'undo block') "Undo Block", (SELECT count FROM v$waitstat WHERE class = 'system undo header') "System Undo Header", (SELECT count FROM v$waitstat WHERE class = 'system undo block') "System Undo block" FROM dual;

6) Temp 세그먼트 경함상태
SELECT username, user, contents, segtype, extents, blocks FROM v$sort_usage;
7) Lock 상태(Holder & Waiter)
SELECT LPAD(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type FROM v$lock WHERE id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0) ORDER BY id1, request;
8) Lock 상태(Waiters)
SELECT LPAD(' ',DECODE(C.request,0,0,1))||C.sid sess, B.username, B.program, C.type, A.sql_text SQL FROM v$sqlarea A,v$session B, v$lock C, v$access D WHERE C.id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0 OR lmode = 6) AND A.address(+) = B.sql_address AND A.hash_value(+) = B.sql_hash_value AND B.sid = D.sid AND B.sid = C.sid AND D.sid = C.sid AND D.owner != 'SYS' ORDER BY C.id1, C.request;
9) DB 사용자
SELECT username, default_tablespace, temporary_tablespace, account_status, expiry_date, lock_date, profile FROM dba_users;

8. Control 파일 구조

SELECT * FROM v$controlfile;

9. 리두로그 파일확인

1) 파일 구조
SELECT A.group# col1, bytes / 1024 col2, members col3, member col4 , A.status col5, sequence# FROM v$log A, v$logfile B WHERE A.group# = B.group#;
2) 대기 상태
SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event LIKE 'log file switch completion%';

10. 아카이브로그 확인

1) 로그 설정 상태
SELECT value FROM v$parameter WHERE name = 'log_archive_start';

2) 아카이브 파일
SELECT name, sequence#, first_change#, first_time, next_change#, next_time, archived, status FROM v$archived_log;

11. 파라메터 확인

SELECT name, type, value, isdefault FROM v$parameter;