서버 프로세스
** 서버 프로세스 상태
SELECT sid, serial#, username, status "Status of Session",
server "ServerType", osuser, machine, program, process
FROM v$session;
** PGA 상태(전체 세션)
SELECT MEM.value "PGA", DISK.value "Temporary 테이블스페이스",
(disk.value/mem.value) * 100 "Ratio"
FROM v$sysstat MEM, v$sysstat DISK
WHERE MEM.name = 'sorts (memory)'
AND DISK.name = 'sorts (disk)';
** PGA 상태 (세션 별)
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
** UGA 상태 (Shared_Pool)
SELECT SUM(value) "Total Session Memory"
FROM v$mystat A, v$statname B
WHERE name = 'session uga memory'
AND A.statistic# = B.statistic#;
데이터 버퍼 캐쉬
** 데이터 버퍼 캐쉬 구조
SELECT id, name, block_size, buffers
FROM v$buffer_pool;
** 히트율
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';
** 대기상태
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;
** 메모리 사용상태(세션 별) 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;
|
로그 버퍼 (히트율)
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';
공유 풀 영역 (Shared Pool Area)
** Reload 상태
SELECT Gets, Gethits, Gethitratio*100 "히트율"
FROM v$Librarycache
WHERE namespace = 'SQL AREA';
** 히트율
SELECT SUM(pins) "Executions(PINS)",
SUM(reloads) "Cache Misses(RELOADS) " ,
SUM(reloads) / SUM(pins)*100 "Reload율"
FROM v$Librarycache;
** 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;
** 파싱된 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;
** 파싱된 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;
** 파싱된 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;
Large Pool (free memory 상태)
SELECT pool, name, bytes
FROM v$sgastat
WHERE pool = 'large pool';
DB Writer 정보
** DBWR 정보
SELECT spid, program, username, serial#, terminal
FROM v$process
WHERE program LIKE '%DB%';
** 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM v$system_event
WHERE event = 'free buffer waits';
CKPT 정보
** CKPT 정보
SELECT spid, program, username, serial#, terminal
FROM v$process
WHERE program LIKE '%CKPT%';
** 대기상태
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event LIKE 'log file switch (checkpoint%';
LGWR 정보
** LGWR 정보
SELECT spid, program, username, serial#, terminal
FROM v$process
WHERE program LIKE '%LGWR%';
** 대기상태
SELECT sid, event, seconds_in_wait, state
FROM v$session_wait
WHERE event = 'log buffer space%';
PMON 정보
** PMON 정보
SELECT spid, program, username, serial#, terminal
FROM v$process
WHERE program LIKE '%PMON%';
** 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM v$system_event
WHERE event = 'pmon timer';
SMON 정보
** SMON 정보
SELECT spid, program, username, serial#, terminal
FROM v$process
WHERE program LIKE '%SMON%';
** 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM v$system_event
WHERE event = 'smon timer';
Data Files
** 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;
** Disk I/O 상태
select name, phyrds, phywrts
FROM v$datafile DF, v$filestat FS
WHERE DF.file# = FS.file#;
** 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;
** 롤백 세그먼트(경합 상태)
SELECT SUM(waits) "Waits", SUM(gets) "Gets",
100 * SUM(waits)/SUM(gets) "히트율"
FROM v$rollstat;
** 롤백 세그먼트(대기 상태)
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;
** TEMP 세그먼트(경합 상태)
SELECT username, user, contents, segtype,
extents, blocks
FROM v$sort_usage;
** 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;
** 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;
** DB 사용자
SELECT username, default_tablespace, temporary_tablespace,
account_status, expiry_date, lock_date, profile
FROM dba_users;
Control-file 구조
SELECT *
FROM v$controlfile;
Redo log files
** Redo-Log Files 구조
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#;
** I/O 대기상태
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event LIKE 'log file switch completion%';
Archived log files
SELECT value
FROM v$parameter
WHERE name = 'log_archive_start';
위의 쿼리값이 true이면 다음 문장을 실행한다.
SELECT name, sequence#, first_change#, first_time,
next_change#, next_time, archived, status
FROM v$archived_log;
Parameter file
SELECT name, type, value, isdefault
FROM v$parameter;
Password file
SELECT value
FROM v$parameter
WHERE name = 'remote_login_passwordfile'
AND (value = 'EXCLUSIVE' OR value = 'SHARED');
사용자 프로세스
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;
프로시져백업.zip