■ 초기 파라미터 파일(initialization parameter file)
SQL> show parameter spfile
NAME TYPE VALUE
------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora
1. 서버 파라미터 파일 (SPFILE)
- 일반적으로 사용되는 초기 파라미터 파일 유형
- 오라클 서버가 읽고 쓸 수 있는 이진파일(binary file)이라 편집기를(vi) 수동으로 편집해서는 안된다.
- 이 파일은 오라클이 실행되는 서버에 있으며 종료 및 시작과 관계없이 계속 유지 된다.
- 기본이름은 spfile<SID>.ora
2. 텍스트 초기 파라미터 파일(PFILE)
- 텍스트 편집기(vi)를 사용하여 수동으로 편집한다.
- 오라클 db를 open한 후에 수동으로 편집하더라도 변경한 값을 인식할 수 없다.
- db를 처음 open 시점에 한번만 access한다.
- 기본이름은 init<SID>.ora
startup nomount 단계예서 $ORACLE_HOME/dbs 디렉터리에서 SPFILE<SID>.ora를 먼저 찾고
없으면 init<SID>.ora를 찾는다. 둘다 없으면 오류발생한다.
# 파라미터 값 변경
1. static parameter
- 초기 파라미터 파일에서만 변경
- instance를 재시작해야 적용된다. (운영중 변경불가)
- 시스템 레벨 (ALTER SYSTEM SET), DBA 작업
SELECT * FROM v$parameter WHERE issys_modifiable = 'FALSE';
ALTER SYSTEM SET 파라미터 = 값 SCORE = SPFILE;
2. dynamic parameter
- 데이터베이스 온라인 상태인 동안 벼경 가능
- 다음 레벨에서 수정 가능
- 세션 레벨 (ALTER SESSION SET), 일반 유저 가능
- 시스템 레벨 (ALTER SYSTEM SET), DBA 작업
- 세션 기간 또는 scope설정에 따른 기간 동안 유효할 수 있도록 설정
SELECT * FROM v$parameter WHERE isses_modifiable = 'TRUE'
OR issys_modifiable = 'IMMEDIATE'
OR issys_modifiable = 'DEFERRED';
ALTER SYSTEM SET 파라미터 = 값 SCORE = SPFILE | MEMORY | BOTH;
ALTER SYSTEM SET 파라미터 = 값 DEFERRED SCORE = SPFILE | MEMORY | BOTH;
# SESSION LEVEL에서 수정할 수 있는 파라미터
# 현재 내 session에만 영향을 준다. (재접속 시 초기화 됨)
SELECT * FROM v$parameter WHERE isses_modifiable = 'TRUE';
select * from v$parameter where name = 'nls_date_format';
select sysdate from dual;
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
select sysdate from dual;
# SYSTEM LAVEL
# 오라클에 접속하는 모든 유저에게 영향을 주는 작업
ALTER SYSTEM SET 파라미터 = 값 SCOPE =
select * from v$parameter where issys_modifiable = 'FALSE';
ㄴ static parameter
SCOPE = SPFILE
- 변경 사항이 서버 파라미터 파일(spfile<SID>.ora)에만 적용되면 현재 instance에는 변경되지 않는다.
- 변경한 값으로 인식되려명 오라클을 재시작해야한다.
- dynamic parameter와 static parameter 모두 변경사항을 다음 시작시에 영구적으로 적용한다.
- static parameter는 scope=spfile만 설정해야 한다.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
...
processes integer 150
SELECT * FROM v$parameter WHERE name = 'processes';
ALTER SYSTEM SET processes = 200 SCOPE = SPFILE;
SELECT * FROM v$parameter WHERE name = 'processes';
DB shutdown > startup 후 적용
SCOPE = MEMORY
- 변경 사항이 메모리에만 적용한다.
- 현재 instance 가 변경되고 변경 사항이 즉시 적용된다.
- 현재만 바꾸고 db 재시작하면 변경값은 기본값으로 설정 된다.
- 서버 파라미터 파일에는 적용되지 않는다.
- dynamic parameter 경우 사용할 수 있다.
SELECT * FROM v$parameter
WHERE isses_modifiable = 'TRUE'
OR issys_modifiable = 'IMMEDIATE';
- static patameter는 사용할 수 없다.
SCOPE = BOTH
- 파라미터의 변경값을 현재 메모리에도 적용되고 서버 파라미터 파일에도 적용한다.
- dynamic parameter 경우 사용할 수 있다.
SELECT * FROM v$parameter
WHERE isses_modifiable = 'TRUE'
OR issys_modifiable = 'IMMEDIATE';
- static patameter는 사용할 수 없다.
SELECT * FROM v$parameter WHERE name = 'sort_area_size';
# SESSION LEVEL
ALTER SESSION SET sort_area_size = 1048576;
# SYSTEM LEVEL
ALTER SYSTEM SET sort_area_size = 1048576 DEFERRED;
# DEFERRED : 시스템 변경이 현재 SESSION이 아닌 다음 SESSION부터 허용한다.
SELECT * FROM v$parameter WHERE issys_modifiable = 'DEFERRED';
ㄴ dydamic parameter
ALTER SYSTEM SET 파라미터 = 값 DEFERRED SCOPE = BOTH(기본값);
SQL> shutdown immediate
SQL> startup
SELECT * FROM v$parameter WHERE name = 'processes';
SELECT * FROM v$parameter WHERE name = 'sort_area_size';
ALTER SYSTEM SET sort_area_size = 65536 DEFERRED SCOPE = MEMORY;
SQL> exit
[oracle@oracle dbs]$ sqlplus / as sysdba
SELECT name, value FROM v$parameter WHERE name = 'sort_area_size';
NAME VALUE
--------------------------------------------------------------------------------
sort_area_size 65536
SQL> shutdown immediate
SQL> startup
SQL> SELECT name, value FROM v$parameter WHERE name = 'sort_area_size';
NAME VALUE
--------------------------------------------------------------------------------
sort_area_size 1048576
# ISSES_MODIFIABLE
현재 내 session에만 영향을 주는 작업 가능 여부
| TRUE | dynamic parameter | 재접속 시 초기화 |
| ALTER SESSION SET 파라미터 = 값; | ||
| FALSE | session 작업 불가 | |
# ISSYS_MODIFIABLE
오라클에 접속하는 모든 유저에게 영향을 주는 작업 가능 여부
| FALSE | static parameter | 변경 후 DB 재시작 필요. |
| ALTER SYSTEM SET 파라미터 = 값 SCORE = SPFILE; | ||
| DEFERRED | dynamic parameter | 변경 후 다음 session접속부터 적용. |
| ALTER SYSTEM SET 파라미터 = 값 DEFERRED SCORE = SPFILE | MEMORY | BOTH(기본값); | ||
| IMMEDIATE | dynamic parameter | 즉시 적용 가능. |
| ALTER SYSTEM SET 파라미터 = 값 SCORE = SPFILE | MEMORY | BOTH; | ||
# SCOPE (적용범위)
| SPFILE | 변경 사항이 서버 파라미터 파일(spfile<SID>.ora)에만 적용, DB재시작 후 영구 적용 |
| MEMORY | 현재 instance에 즉시 적용, db재시작 시 초기화 |
| BOTH | 현재 메모리 및 서버 파리미터 파일(spfile<SID>.ora)에도 적용 |
# pfile 생성
SQL> create pfile from spfile;
File created.
[oracle@oracle dbs]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G
SQL> shutdown immediate
SQL > !
[oracle@oracle dbs]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
[oracle@oracle dbs]$ mv spfileora11g.ora spfileora11g.bak
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.bak
init.ora lkORA11G
[oracle@oracle dbs]$ exit
SQL> startup
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
>>> pfile 로 시작한 db (value : null)
# pfile 이용해서 spfile 생성
SQL> create spfile from pfile;
File created.
# dbca를 이용하지않고 db생성 시에는 pfile을 만들고 db생성해야 한다.
SQL> shutdown immediate
SQL> !
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.bak
init.ora lkORA11G spfileora11g.ora
[oracle@oracle dbs]$ exit
SQL> startup
SQL> show parameter spfile
NAME TYPE VALUE
---------------------------- ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora
# INSTANCE = SGA + Background Process
# user process : 사용자가 작성한 SQL문을 server process로 전달해주고 결과를 가지고오는 프로세스
# server process : SQL문을 수행하는 프로세스
# connection : user process - server process 통신 경로
# session : 데이터베이스 instance에 대한 현재 유저가 로그인 상태를 나타낸다.
■ SGA (System Global Area)
- 모든 오라클 프로세스가 액세스하는 공유 메모리
- SGA 영역에 있는 구성 요소들을 db 운영중에 동적으로 설정할 수 있다.
- SGA 메모리는 SGA_MAX_SIZE로 설정한다. (총량)
ALTER SYSTEM SET SGA_MAX_SIZE = 1G SCORE = SPFILE;
- SGA_MAX_SIZE 파라미터는 SGA 총 메모리 사이즈를 관리하는 파라미터(static parameter)
SELECT * FROM v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1367004 No
Redo Buffers 5419008 No
Buffer Cache Size 301989888 Yes
Shared Pool Size 104857600 Yes
Large Pool Size 8388608 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 711430144 No
Startup overhead in Shared Pool 68784648 No
Free SGA Memory Available 285212672
- RESizeable : no -> 변경불가 항목
- SGA 구성 요소들은 granule(증감단위)에 따라 할당하자.
- 연속적인 가상 메모리 할당.
- 예상된 총 SGA_MAX_SIZE기준으로 granule size가 결정된다.
- SGA_MAX_SIZE가 1GB 작거나 같으면 4mbyte 기준, 1GB보다 크면 16mbyte 단위로 결정된다.
SQL> show sga
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes : 오라클이 instance 생성 시 사용되는 알고리즘들이 이 메모리에서 수행
Variable Size 402654244 bytes
Database Buffers 301989888 bytes
Redo Buffers 5419008 bytes
Variable Size = Shared Pool Size + Large Pool Size + Java Pool Size + Streams Pool Size
■ Database Buffer Cache, Data Buffer Cache
- 데이터 파일에서 읽은 데이터 블록의 복사본을 저장하는 영역
- 오라클은 물리적인 I/O를 최소화하기 위해 최근에 사용된 블록(BLOCK)을 저장하는 메모리영역
- 모든 유저는 data buffer cache에 대한 액세스를 공유
- 기본적으로 48mbyte 또는 4mbyte * CPU 수 중 큰 값
- data buffer cache 내부 구성 요소 파라미터
db_cache_size (필수 구성) : db_block_size 기본 블록 크기에 배수 단위로 설정한다.
db_keep_size (옵션 구성)
db_recycle_cache_size (옵션 구성)
db_nk_cache_size (n : 2, 4, 8, 16, 32) (옵션 구성)
- 동적으로 크기를 조정 할 수 있다. (dynamic parameter)
ALTER SYSTEM SET db_cache_size = 96m SCORE = BOTH;
- LRU(Least Recently Used) 알고리즘을 통해 관리한다.
■ shared pool
SELECT * FROM v$sgastat WHERE pool = 'shared pool';
- shared_pool_size 파라미터를 이용해서 크기를 지정한다.
- 동적으로 크기를 조정할 수 있다.
ALTER SYSTEM SET shared_pool_size = 128m;
- library cache : SQL문, 실행계획을 가지고 있는 메모리
- data dictionary cache : data dictionary 테이블의 정보를 가지고 있는 메모리
- oracle shared server 환경일 때 UGA(User Global Area)가 생성된다.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> SELECT * FROM v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
...
Shared Pool Size 104857600 Yes
SELECT * FROM v$parameter WHERE name = 'shared_pool_size';
isses_modifiable = TRUE
issys_modifiable = IMMEDIATE
ㄴ dynamic parameter
■ Large pool
- large_pool_size 파라미터로 크기 조정
- 동적인 파라미터이다.
ALTER SYSTEM SET large_pool_size = 10m SCORE = BOTH;
- 다음 용도의 데이터를 메모리에 저장하는데 사용
- oracle shared server 환경일 때 UGA(User Global Area)가 생성된다.
shared server 환경일 때 shared pool 메모리 공간의 부담을 줄이기 위해서 large pool 사용하자.
- RMAN을 이용해서 백업 및 복원 작업 수행할 때
- parallel query(병렬쿼리) 작업 수행할 때
SQL> show parameter large_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL> SELECT * FROM v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
...
Large Pool Size 8388608 Yes
SELECT * FROM v$parameter WHERE name = 'large_pool_size';
isses_modifiable = FALSE
issys_modifiable = IMMEDIATE
■ Java pool
- java_pool_size 파라미터로 크기 조정
- 동적 파라미터
ALTER SYSTEM SET java_pool_size = 24m;
- JVM 내의 모든 세션별 JAVA 코드 및 데이터를 메모리에 저장하는데 사용한다.
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL> SELECT * FROM v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
...
Java Pool Size 4194304 Yes
SELECT * FROM v$parameter WHERE name = 'java_pool_size';
isses_modifiable = FALSE
issys_modifiable = IMMEDIATE
■ steam pool
- 하나의 데이터베이스에서 다른 데이터베이스로 데이터를 이동, 복제하는 stream process에 필요한 buffer queue message 사용
- streams_pool_size 파라미터로 크기 조정
- 동적 파라미터
ALTER SYSTEM SET streams_pool_size = 10m;
SQL> show parameter streams_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL> SELECT * FROM v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
...
Streams Pool Size 0 Yes
SELECT * FROM v$parameter WHERE name = 'streams_pool_size';
isses_modifiable = FALSE
issys_modifiable = IMMEDIATE
■ REDO LOG BUFFER
- 데이터베이스 데이터 블록의 모든 변경 사항을 기록한다.
- 기본 목적은 복구(recovery)
- 내부의 기록된 변경사항을 리두항목(redo entry) 라고 합니다.
- 리두항목(redo entry)에는 변경 사항을 재구성하거나 재실행할 정보가 포함되어 있다.
- 리두항목이 생성되는 SQL문 : DML(insert, update, delete, merge), create, alter, drop, select * fror update
- log_buffer
- static parameter
ALTER SYSTEM SET log_buffer = 5m SCORE = SPFILE;
SELECT * FROM v$parameter WHERE name = 'log_buffer';
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 5234688
1. parrse (필수)
1.syntax, semantic, 권한
2. 동일한 SQL문이 S.P(L.C) 조회 (Soft Parsing)-> Latch
3. hard parsing - 메모리 할당
- 실행계획
# 실행계획
- data 처리방법 - rowid scan - user by rowid
- index by rowid
- full table scan
- join 순서 결정
- join 방법 결정
2. bind (선택)
변수에 실제값이 입력되는 단계
3. execute
1. undo segment 할당
2. undo segment undo block 할당
3. 수정하려는 row가 존재하는block이 data buffer cache에 있는지 조회
4. redo log buffer, redo entry 작성
5. 수정 대상 row가 있는 block header transaction sloct 획득해야 한다.
6. row level lock 건다.
7. 이전값은 undo block 작성하고, 새로운 값으로 변경한 후 block header CR(읽기일관성) block 설정
'Oracle 11g > Oracle DB' 카테고리의 다른 글
| 48 ORACLE ASSM, Tablespace, Undo (0) | 2023.12.14 |
|---|---|
| 47 ORACLE Redo Log File, Tablespace, FLM (0) | 2023.12.13 |
| 46 ORACLE ASMM, PGA, AMM, Background Process, Control file (0) | 2023.12.12 |
| 44 ORACLE Listener, Alert Log File, Startup, Shutdown (2) | 2023.12.08 |
| 43 ORACLE OS, DB Install (1) | 2023.12.07 |