V$INSTANCE

Oracle 데이터베이스 인스턴스의 상태와 관련된 정보를 제공하는 동적 성능 뷰. 이 뷰를 통해 데이터베이스 인스턴스의 이름, 상태, 시작 시간, 모드 등을 확인할 수 있다.

---

주요 열

• INSTANCE_NUMBER: 인스턴스 번호 (멀티인스턴스 환경에서 유용)
• INSTANCE_NAME: 인스턴스 이름
• HOST_NAME: 데이터베이스가 실행 중인 호스트 이름
• VERSION: Oracle 데이터베이스의 버전
• STATUS: 인스턴스 상태 (STARTED, MOUNTED, OPEN 등)
• STARTUP_TIME: 인스턴스가 시작된 시간
• LOGINS: 현재 로그인 허용 상태 (ALLOWED, RESTRICTED)
• DATABASE_STATUS: 데이터베이스 상태 (ACTIVE, SUSPENDED 등)

---

사용 예제

1. 기본 정보 조회

SELECT INSTANCE_NAME, HOST_NAME, STATUS, STARTUP_TIME 
FROM V$INSTANCE;

결과 예시:

INSTANCE_NAME	HOST_NAME	STATUS	STARTUP_TIME
orcl	myserver.local	OPEN	16-JAN-2025 10:00:00

---

1. Oracle 버전 및 상태 확인

SELECT INSTANCE_NAME, VERSION, STATUS, DATABASE_STATUS 
FROM V$INSTANCE;

결과 예시:

INSTANCE_NAME	VERSION	STATUS	DATABASE_STATUS
orcl	19.0.0.0.0	OPEN	ACTIVE

---

1. 현재 로그인 허용 상태 확인

SELECT INSTANCE_NAME, LOGINS 
FROM V$INSTANCE;

결과 예시:

INSTANCE_NAME	LOGINS
orcl	ALLOWED

---

1. 인스턴스가 실행 중인 서버의 세부 정보 확인

SELECT HOST_NAME, INSTANCE_NAME, STARTUP_TIME 
FROM V$INSTANCE;

결과 예시:

HOST_NAME	INSTANCE_NAME	STARTUP_TIME
myserver.local	orcl	16-JAN-2025 10:00:00

---

활용 방안

상태 점검: 데이터베이스 인스턴스가 정상적으로 시작되고 실행 중인지 확인.

문제 진단: 멀티인스턴스 환경에서 특정 인스턴스에 대한 문제를 빠르게 파악.

멀티인스턴스 환경(예: Oracle RAC)에서 특정 인스턴스의 메모리 관련 문제를 진단하기 위해 V$SGA를 사용하는 방법은 매우 유용. 이 환경에서는 각 인스턴스가 별도의 SGA를 가지므로, 인스턴스별 SGA 상태를 점검하고 병목현상을 빠르게 식별할 수 있다.

---

1. 특정 인스턴스의 SGA 크기 확인

멀티인스턴스 환경에서는 GV$SGA 뷰를 사용하여 모든 인스턴스의 SGA 상태를 조회할 수 있다.

SELECT INST_ID, NAME, VALUE 
FROM GV$SGA
WHERE INST_ID = <인스턴스_ID>;

예시: 2번 인스턴스의 SGA 확인

결과 예시:

 

INST_ID	NAME	VALUE
2	Fixed Size	2926248
2	Variable Size	838860800
2	Database Buffers	251658240
2	Redo Buffers	763904

---

2. 모든 인스턴스의 SGA 크기 비교

멀티인스턴스 환경에서 각 인스턴스의 SGA 크기를 비교해 메모리 분배에 불균형이 있는지 확인합니다.

SELECT INST_ID, NAME, VALUE 
FROM GV$SGA
WHERE NAME IN ('Variable Size', 'Database Buffers')
ORDER BY INST_ID, NAME;

결과 예시:

INST_ID	NAME	VALUE
1	Variable Size	838860800
1	Database Buffers	251658240
2	Variable Size	738860800
2	Database Buffers	201658240

진단:

• 2번 인스턴스의 Variable Size나 Database Buffers가 상대적으로 작다면, 메모리 부족 가능성을 의심할 수 있습니다.

---

3. 인스턴스별 메모리 세부 정보 확인

GV$SGASTAT를 사용하여 특정 인스턴스의 메모리 구성 요소를 세부적으로 분석합니다.

SELECT INST_ID, POOL, NAME, BYTES 
FROM GV$SGASTAT
WHERE INST_ID = 2
ORDER BY BYTES DESC;

결과 예시:

INST_ID	POOL	NAME	BYTES
2	shared pool	free memory	200000000
2	shared pool	sql area	120000000
2	buffer cache	db_block_buffers	80000000
2	shared pool	library cache	60000000

진단:

• free memory가 매우 적다면 메모리가 부족합니다.
• sql area나 library cache가 비정상적으로 크다면, SQL 튜닝 필요성을 의심할 수 있습니다.

---

4. 인스턴스 간 병목 현상 비교

모든 인스턴스에서 동일한 메모리 구성 요소의 사용량을 비교합니다.

SELECT INST_ID, NAME, VALUE 
FROM GV$SGASTAT
WHERE NAME IN ('free memory', 'sql area', 'library cache')
ORDER BY INST_ID, NAME;

결과 예시:

INST_ID	NAME	VALUE
1	free memory	300000000
1	sql area	150000000
1	library cache	70000000
2	free memory	200000000
2	sql area	120000000
2	library cache	60000000

진단:

• 2번 인스턴스의 free memory가 1번 인스턴스보다 적다면 메모리 부족 가능성이 높습니다.

---

5. 병목 원인 확인: 메모리 재사용율 분석

Library Cache의 메모리 재사용률을 확인하여 SQL 실행 효율성을 평가합니다.

SELECT INST_ID, NAME, VALUE 
FROM GV$SYSSTAT
WHERE INST_ID = 2 AND NAME IN ('session cursor cache hits', 'parse count (total)');

결과 예시:

INST_ID	NAME	VALUE
2	session cursor cache hits	50000
2	parse count (total)	100000

계산: 재사용률 = (session cursor cache hits / parse count (total)) * 100

• 재사용률이 낮으면 비효율적인 SQL 실행으로 인해 Shared Pool 병목 가능성.

---

6. 문제 해결 방향

A. Shared Pool 관련 문제

• SHARED_POOL_SIZE 증가.
• SQL 튜닝을 통해 바인드 변수를 사용하여 재사용률 증가.
• 비효율적인 SQL을 캐싱하지 않도록 설정.

B. Buffer Cache 관련 문제

• DB_CACHE_SIZE 증가.
• 액세스 패턴 분석 후 데이터 블록 핫스팟을 제거.

C. Redo Log Buffer 문제

• LOG_BUFFER 크기 조정.
• 트랜잭션 커밋 빈도를 줄여 Redo 로그 부담 완화.

---

결론

위 방법을 통해 특정 인스턴스의 SGA 관련 문제를 빠르게 진단할 수 있다. 멀티인스턴스 환경에서 효율적인 메모리 분배와 병목 원인 분석은 성능 최적화의 핵심.

• 로그인 제한 여부: 관리 작업 중 로그인을 제한하거나 허용 상태를 확인.