티스토리 뷰
Oracle 동적 성능 뷰(Dynamic Performance Views)는 데이터베이스의 실시간 상태 및 성능 정보를 제공하는 특수한 뷰.
주로 `V$` 접두사로 시작하며, 메모리 구조(SGA, PGA 등)에 저장된 정보. 데이터베이스가 가동 중일 때만 접근 가능하며, DBA(데이터베이스 관리자)가 모니터링, 튜닝, 문제 진단에 활용.
---
주요 특징
* 메모리 기반: 디스크가 아닌 SGA(System Global Area)의 정보를 참조. 인스턴스 종료 시 데이터가 초기화.
* 실시간 데이터: 현재 데이터베이스의 상태(세션, 잠금, SQL 실행 통계, 리소스 사용량 등)를 반영.
예: `V$SESSION`, `V$SQL`, `V$LOCK` 등.
* 동적 데이터: 데이터베이스의 상태가 변함에 따라 실시간으로 정보 갱신
* 성능 정보 중심: CPU 사용량, 메모리 사용량, I/O 통계, 세션 정보, SQL 실행 정보 등 성능과 관련된 다양한 지표 제공.
* 읽기 전용: V$ 뷰는 데이터를 조회하는 용도로만 사용되며, 데이터를 직접 수정할 수 없다.
* 권한 필요: 대부분의 V$ 뷰는 `SELECT ANY DICTIONARY` 시스템 권한 또는 `SELECT_CATALOG_ROLE` 역할이 필요. 일부 뷰는 `PUBLIC` 권한으로 접근 가능.
* 접두사 `V$`: 모든 동적 성능 뷰 이름은 `V$` 또는 `GV$` (RAC 환경) 로 시작.
V$ 뷰의 중요성
* 문제 진단: 성능 저하, 오류 발생 시 원인 분석 및 해결에 결정적인 정보 제공.
* 성능 모니터링: 데이터베이스의 실시간 성능 지표를 확인하여 병목 지점 및 이상 징후 감지.
* 튜닝: 성능 분석 데이터를 기반으로 SQL, 인스턴스, 스토리지 등 다양한 영역의 튜닝 방향 설정, 효과 측정.
* 용량 계획: 시스템 자원 사용량 분석하여 향후 용량 계획 수립에 활용.
---
주요 동적 성능 뷰
| 뷰 이름 | 설명 |
| v$session | 현재 접속한 세션 정보(사용자, 상태, sql 등) |
| v$sql | 실행된 SQL 문의 통계 및 실행 계획 |
| v$lock | 현재 발생한 잠금(Lock) 정보 |
| v$datafile | 데이터 파일의 상태 및 정보 |
| v$log | 리두 로그 파일 정보 |
| v$process | 데이터베이스 프로세스 정보 |
| v$sysstat | 시스템 전체 통계(캐시 적중률, I/O 등) |
---
주의 사항:
* 성능 영향: V$ 뷰 조회 자체는 일반적으로 성능에 큰 영향을 미치지 않지만, 너무 자주 또는 과도하게 많은 V$ 뷰를 조회하는 경우 약간의 부하를 줄 수 있다. 특히 부하가 높은 시스템에서는 주의해야 한다.
* 권한 관리: V$ 뷰 접근 권한 관리하여 보안 유지. 불필요한 V$ 뷰 접근 권한을 부여하지 않도록 주의해야 한다.
* Oracle 버전 차이: V$ 뷰의 종류, 컬럼, 데이터 내용은 Oracle 버전에 따라 다를 수 있다. Oracle 버전에 맞는 V$ 뷰 참고.
결론:
Oracle 동적 성능 뷰 (V$ 뷰)는 Oracle 데이터베이스의 성능 모니터링, 문제 진단, 튜닝에 필수적인 도구. 다양한 V$ 뷰를 효과적으로 활용하면 데이터베이스의 현재 상태를 실시간으로 파악하고 성능 문제를 신속하게 해결하여 안정적이고 효율적인 데이터베이스 운영 가능. 데이터베이스 관리자 및 개발자는 V$ 뷰를 숙지하고 적극적으로 활용하는 것이 중요.
활용 사례
1. V$INSTANCE: 데이터베이스 인스턴스 정보
* 설명: 데이터베이스 인스턴스의 기본적인 정보 (시작 시간, 버전, 상태, 호스트 이름 등) 제공.
* 시나리오:
* 데이터베이스 시작 시간 확인: 데이터베이스가 언제 시작되었는지 확인하여 시스템 가동 시간 파악.
* Oracle 버전 확인: 현재 데이터베이스의 Oracle 버전을 확인하여 패치 적용 및 호환성 검토에 활용.
* 데이터베이스 상태 확인: 데이터베이스가 `OPEN`, `MOUNTED`, `STARTED` 등 어떤 상태인지 작동 여부 판단.
* RAC 환경에서 인스턴스별 정보 확인: `GV$INSTANCE`를 사용하여 RAC 환경의 각 인스턴스를 개별적으로 확인.
-- V$INSTANCE 예시 쿼리
SELECT
instance_name,
host_name,
version,
startup_time, status
FROM v$instance;
-- 사용 시나리오 예시: 데이터베이스 시작 시간 및 버전 확인
-- 쿼리 결과:
INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS
-------------------------- ------------------- --------------- ---------------------------- --------------
ORCL server01 19.0.0.0.0 2023-10-27 10:00:00 OPEN
-- 시나리오 설명: 데이터베이스 'ORCL' 인스턴스는 'server01' 호스트에서 19.0.0.0.0 버전으로 2023년 10월 27일 10시에 시작되었으며 현재 'OPEN' 상태임을 알 수 있다.
2. V$SESSION: 현재 세션 정보
* 설명: 데이터베이스에 접속된 각 세션의 정보 (세션 ID, 사용자, SQL, 상태, 대기 이벤트 등) 제공.
* 사용 시나리오:
* 활성 세션 목록 확인: 현재 데이터베이스에 접속된 활성 세션 목록을 확인하여 사용자 접속 현황 파악.
* 특정 사용자 세션 확인: 특정 사용자가 접속한 세션 정보를 확인하여 사용자별 작업 내용 추적.
* 장기 실행 세션 확인: 오랫동안 실행 중인 세션을 확인하여 성능 저하의 원인이 되는 세션 식별.
* 세션별 SQL 확인: 각 세션에서 실행 중인 SQL 문장을 확인하여 특정 세션의 작업 내용 분석.
* 세션 대기 이벤트 확인: 각 세션이 어떤 대기 이벤트 때문에 멈춰있는지 확인하여 병목 지점 파악.
sql
-- V$SESSION 예시 쿼리
SELECT
sid,
serial#,
username,
status,
sql_id,
wait_class,
event
FROM v$session
WHERE status = 'ACTIVE';
-- 사용 시나리오 예시: 활성 세션 중 대기 이벤트가 'User I/O' 인 세션 확인
-- 쿼리 결과:
SID SERIAL# USERNAME STATUS SQL_ID WAIT_CLASS EVENT
------- -------------- ----------------- ----------- -------------------- --------------------- --------------------
123 456 USER1 ACTIVE abcdefg123456 User I/O db file sequential read
-- 시나리오 설명: SID 123, SERIAL# 456 세션은 USER1 사용자가 실행 중이며, 'User I/O' 대기 클래스에 속하는 'db file sequential read' 이벤트 때문에 대기 중임을 알 수 있다. 이는 I/O 성능 문제 가능성을 시사한다.
3. V$SQLAREA / V$SQL: SQL 문장 정보 및 성능 통계
* 설명: 데이터베이스에서 실행된 SQL 문장의 정보 (SQL 텍스트, 실행 횟수, 실행 시간, 버퍼 캐시 적중률, 디스크 읽기 횟수 등) 및 성능 통계 제공.
`V$SQLAREA`는 집계된 통계 제공,
`V$SQL`은 개별 실행 통계 제공.
* 사용 시나리오:
* 자원 소모가 많은 SQL 식별: 실행 횟수, CPU 시간, 디스크 I/O 등 자원 소모가 많은 SQL문장을 튜닝 대상 SQL 선정.
* SQL 실행 계획 확인: 특정 SQL 문장의 실행 계획을 확인, 분석하고 인덱스 추가, SQL 재작성 등의 튜닝 수행.
* SQL 성능 변화 추적: 특정 SQL 문장의 성능 지표를 주기적으로 모니터링하여 성능 변화 확인, 문제 발생 시점 파악.
* SQL 튜닝 효과 측정: SQL 튜닝 후 `V$SQLAREA` 또는 `V$SQL`의 성능 지표 변화를 비교하여 효과를 정량적 측정.
-- V$SQLAREA 예시 쿼리 (CPU 시간 기준 상위 10개 SQL)
SELECT
sql_id,
sql_text,
executions,
cpu_time,
disk_reads
FROM v$sqlarea
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;
-- 사용 시나리오 예시: CPU 시간을 많이 사용하는 상위 SQL 확인 및 튜닝 대상 선정
쿼리 결과: (일부 컬럼만 예시)
SQL_ID SQL_TEXT EXECUTIONS CPU_TIME DISK_READS
------------- --------------------------------------- ---------- -------- ----------
abcdefg123456 SELECT * FROM LARGE_TABLE WHERE ... 1000 12345 100000
...
-- 시나리오 설명: CPU 시간을 가장 많이 사용하는 SQL 문장 중 하나는 'abcdefg123456' SQL_ID를 가진 SQL이며, 이는 'LARGE_TABLE' 테이블에서 특정 조건으로 데이터를 조회하는 쿼리임을 알 수 있다. 이 SQL은 튜닝 우선순위가 높은 대상이 될 수 있다.
4. V$WAITSTAT / V$SYSTEM_WAIT_CLASS: 대기 이벤트 통계
* 설명: 데이터베이스가 어떤 대기 이벤트 때문에 성능 병목이 발생하는지 파악하기 위한 통계 제공.
`V$WAITSTAT`은 오래된 뷰이며,
`V$SYSTEM_WAIT_CLASS` 또는 `V$EVENT_NAME` 뷰를 사용하는 것이 더 세분화된 정보를 얻을 수 있다.
* 사용 시나리오:
* 주요 대기 이벤트 확인: 데이터베이스에서 가장 많이 발생하는 대기 이벤트를 확인하여 성능 병목의 종류 (I/O, 락, CPU 등) 파악.
* 대기 이벤트별 시간 분석: 각 대기 이벤트에 소요되는 시간을 분석하여 영향을 미치는지 확인.
* 대기 이벤트별 원인 분석: 특정 대기 이벤트가 많이 발생하는 경우, 해당 대기 이벤트의 원인을 분석하고 해결 방안 모색. (예: 'db file sequential read' 이벤트가 많으면 디스크 I/O 성능 문제 또는 인덱스 부재 가능성)
-- V$SYSTEM_WAIT_CLASS 예시 쿼리 (대기 클래스별 대기 시간)
SELECT
wait_class,
time_waited,
waits
FROM v$system_wait_class
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;
-- 사용 시나리오 예시: 가장 많은 시간을 소비하는 대기 클래스 확인
-- 쿼리 결과:
WAIT_CLASS TIME_WAITED WAITS
------------------ ----------- ----------
User I/O 1000000 10000
Concurrency 500000 5000
...
-- 시나리오 설명: 'User I/O' 대기 클래스가 가장 많은 시간을 소비하고 있으며, 이는 I/O 관련 성능 문제가 주요 병목임을 시사. 디스크 I/O 성능 개선 또는 SQL 튜닝을 통해 I/O 부하를 줄이는 방안을 고려.
5. V$LOCK: 락 정보
* 설명: 데이터베이스에서 발생하고 있는 락 (Lock) 정보 (락 종류, 락 모드, 락을 보유한 세션, 락을 요청하는 세션 등) 제공. 락 경합으로 인한 성능 문제를 진단하는 데 사용.
* 사용 시나리오:
* 락 경합 발생 여부 확인: 현재 데이터베이스에서 락 경합이 발생하고 있는지 확인.
* 락을 보유한 세션 및 요청 세션 확인: 특정 락을 보유하고 있는 세션과 해당 락을 요청하며 대기 중인 세션 파악.
* 데드락 발생 여부 확인 및 세션 정보 확인: 데드락이 발생했는지 확인하고, 관련된 세션 정보로 발생 원인 분석.
* 락 관련 성능 문제 진단: 락 경합으로 성능 저하 발생시, `V$LOCK` 뷰를 통해 관련 정보 분석하여 문제 해결 방안 모색.
-- V$LOCK 예시 쿼리 (블로킹 세션 확인)
SELECT
(SELECT username FROM v$session WHERE sid = l.sid) blocker,
l.sid,
'is blocking',
(SELECT username FROM v$session WHERE sid = s.sid) blockee,
s.sid
FROM v$lock l, v$lock s
WHERE l.block = 1 AND s.request > 0 AND l.id1 = s.id1 AND l.id2 = s.id2;
-- 사용 시나리오 예시: 다른 세션을 블로킹하는 세션 확인
-- 쿼리 결과:
BLOCKER SID 'IS BLOCKING' BLOCKEE SID
-------- ----- ------------- -------- -----
USER_A 123 is blocking USER_B 456
-- 시나리오 설명: SID 123 세션 (USER_A) 이 SID 456 세션 (USER_B) 를 블로킹하고 있음을 알 수 있습니다. USER_A 세션이 어떤 락을 보유하고 있는지, USER_B 세션이 어떤 락을 요청하는지 추가 분석하여 락 경합 문제 해결한다.
1. 성능 튜닝
- `V$SQL_AREA`로 자주 실행되거나 리소스 소모가 큰 SQL 식별.
- `V$SESSION_WAIT`으로 세션의 대기 이벤트 분석.
2. 문제 진단
- `V$LOCK`으로 데드락 또는 장시간 잠금 확인.
- `V$SESSION_LONGOPS`로 장기 실행 중인 작업 모니터링.
3. 리소스 모니터링
- `V$SGASTAT`으로 SGA 메모리 사용량 확인.
- `V$TEMPFILE`로 임시 테이블스페이스 사용량 추적.
---
동적 뷰 vs. 정적 데이터 딕셔너리 뷰
- 동적 뷰(`V$`)
- 실시간 성능 데이터 제공.
- 예: `V$SESSION`, `V$SQL`.
- 정적 뷰(`DBA_`, `ALL_`, `USER_`)
- 데이터베이스 객체(테이블, 인덱스 등)의 메타데이터 저장.
- 예: `DBA_TABLES`, `ALL_USERS`.
---
사용 예시
-- 현재 접속 중인 세션 조회
SELECT
sid,
serial#,
username,
status,
machine
FROM V$SESSION
WHERE username IS NOT NULL;
-- 실행 중인 SQL 문 확인
SELECT
sql_id,
sql_text,
executions,
elapsed_time
FROM V$SQL
WHERE executions > 100;
---
동적 성능 뷰는 Oracle 데이터베이스 관리의 핵심 도구로, 실시간 인사이트를 통해 효율적인 운영이 가능하다.
주요 Oracle 동적 성능 뷰가 실제 상황에서 어떻게 활용되는지 구체적인 사례 시나리오
---
1. `V$SESSION`
- 문제: 특정 사용자가 장시간 트랜잭션을 종료하지 않아 다른 세션의 작업이 지연.
- 확인:
SELECT
sid,
serial#,
username,
status,
machine,
program
FROM V$SESSION
WHERE username = '문제_사용자';
- 결과로 얻은 `sid`와 `serial#`로 세션을 강제 종료:
ALTER SYSTEM KILL SESSION 'sid,serial#';
---
2. `V$SQL`
- 문제: CPU 사용률이 갑자기 증가하여 시스템이 느려진다.
- 확인:
SELECT
sql_id,
sql_text,
executions,
cpu_time,
elapsed_time
FROM V$SQL
ORDER BY cpu_time DESC;
- 결과: 상위 SQL을 확인해 비효율적인 쿼리(예: Full Scan, 과도한 조인) 튜닝.
---
3. `V$LOCK`
- 문제: 특정 테이블에 대한 잠금(Lock)이 해제되지 않아 다른 트랜잭션 대기.
- 확인:
SELECT
sid,
type,
id1,
id2,
lmode,
request
FROM V$LOCK
WHERE id1 = (SELECT object_id FROM dba_objects WHERE object_name = '테이블명');
- 결과: 잠금을 보유한 세션(`sid`)을 찾아 강제 종료하거나 트랜잭션을 커밋/롤백 유도.
---
4. `V$DATAFILE`
- 문제: 디스크 공간 부족으로 데이터 파일 확장 실패.
- 확인:
SELECT
name,
bytes/1024/1024 AS Size_MiB,
status,
autoextensible
FROM V$DATAFILE;
- 결과: `autoextensible`이 `NO`인 파일을 찾아 자동 확장 옵션을 활성화하거나 수동으로 확장.
---
5. `V$LOG`
- 문제: 리두 로그 스위칭이 너무 빈번해 I/O 부하가 발생.
- 확인:
SELECT
group#,
sequence#,
bytes/1024/1024 AS Size_MiB,
status
FROM V$LOG;
- 결과: 리두 로그 그룹의 크기(`size_mb`)를 늘려 스위칭 빈도 감소.
---
6. `V$PROCESS`
- 문제: 특정 프로세스가 과도한 메모리를 사용.
- 확인:
SELECT
pid,
spid,
program,
pga_used_mem,
pga_alloc_mem
FROM V$PROCESS
ORDER BY pga_used_mem DESC;
- 결과: OS 프로세스 ID(`spid`)를 통해 리소스 소모 원인을 분석.
---
7. `V$SYSSTAT`
- 문제: 데이터베이스 캐시 적중률이 낮아 성능 저하.
- 확인:
SELECT
name,
value
FROM V$SYSSTAT
WHERE name IN ('buffer cache hit ratio', 'physical reads');
- 결과: 캐시 크기(`DB_CACHE_SIZE`)를 조정하거나 SQL 튜닝 수행.
---
8. `V$SESSION_WAIT`
- 문제: 특정 세션이 "db file sequential read" 대기 이벤트로 지연.
- 확인:
SELECT
sid,
event,
wait_time,
seconds_in_wait
FROM V$SESSION_WAIT
WHERE event NOT LIKE 'SQL%';
- 결과: 인덱스 스캔 효율성을 확인하거나 I/O 병목을 진단.
---
9. `V$SESSION_LONGOPS`
- 문제: 대용량 테이블의 `INDEX REBUILD` 작업 진행 상태 모니터링.
- 확인:
SELECT
sid,
opname,
target,
sofar,
totalwork,
ROUND((sofar/totalwork)*100, 2) AS progress_pct
FROM V$SESSION_LONGOPS
WHERE opname LIKE '%INDEX%';
- 결과: 남은 시간 예측 및 작업 중단/재개 결정.
---
10. `V$TEMPFILE`
- 문제: 임시 테이블스페이스 사용량이 100%에 도달해 쿼리가 실패.
- 확인:
SELECT
name,
bytes/1024/1024 AS Size_MiB,
status
FROM V$TEMPFILE;
- 결과: 임시 테이블스페이스 확장 또는 불필요한 정렬(SORT) 작업 최적화.
---
요약
- 성능 튜닝: `V$SQL`, `V$SESSION_WAIT`
- 잠금/세션 관리: `V$LOCK`, `V$SESSION`
- 리소스 모니터링: `V$SYSSTAT`, `V$PROCESS`
- 스토리지 관리: `V$DATAFILE`, `V$TEMPFILE`
동적 성능 뷰를 활용하면 실시간 데이터베이스 상태를 정밀하게 진단하고 문제 확인 시간을 단축할 수 있다.
'Oracle' 카테고리의 다른 글
| Oracle Temp Tablespace 증설 작업 (2) | 2025.02.01 |
|---|---|
| Oracle 테이블스페이스 증설 (0) | 2025.01.16 |
| Oracle 아카이브 로그 삭제 (0) | 2025.01.16 |
| V$PROCESS (1) | 2025.01.16 |
| V$INSTANCE (0) | 2025.01.16 |