Oracle Parameter

 

Parameter 란?

• 사용자가 원하는 어떤 값을 오라클에게 전해주기 위해 사용하는 변수 같은 역할을 하는 것.
• 묵시적 파라미터 : 관리자가 지정하지 않을 경우 자동으로 기본값을 가집니다.
• 명시적 파라미터 : 관리자가 지정해 주어야만 값을 가집니다.

 

pfie과 spfile의 비교

항목 / 파일	pfile	spfile
파일 경로	$ORACLE_HOME/dbs (UNIX) , $ORACLE_HOME\database (Windows)
파일 이름	initSID.ora	SpfileSID.ora
내용 변경	관리자 (유저)	서버 프로세스
파일 형태	TEXT (OS 편집기로 편집가능)	Binary (OS 편집기로 편집 불가)

pfile을 8i까지 기본으로 사용했으나 9i부터 spfile을 기본으로 사용합니다.

spfile과 pfile이 동시에 존재 한다면 spfile을 먼저 읽어 들이게되어 spfile의 정보를 가지고 startup 하게 됩니다.

 

사용하고 있는 DB가 spfile을 사용하고 있는지 pfile을 사용하고 있는지 확인하기

pfile을 사용하는 경우

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

spfile을 사용하는 경우

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.3/dbs/spfileORA11.ora

value 값에 spfile 경로가 있고 없고의 차이가 있습니다.

 

파라메터 파일 내용 변경하기

pfile은 initSID.ora를 vi로 열어 직접 변경 후 DB를 재구동 해주면 설정값이 적용됩니다. spfile은 서버 프로세스에 변경을 요청해야 하기 때문에 alter system set 명령을 이용하여 변경하여야 합니다.

– db_cache_size 변경 예제

SYS> alter system set db_cache_size=50m scope=memory;

문장 마지막에 scope 옵션이 있는데, scope 옵션에는 3가지가 올 수 있습니다.

• MEMORY : spfile 내용은 변경하지 않고 현재 작동중인 instance에만 적용. DB를 재구동하면 이 설정값은 사라진다.
• SPFILE : spfile에 내용을 변경하나 현재 작동중인 instance에는 적용하지 않고, DB를 재구동 해야지만 적용된다.
• BOTH : 현재 작동중인 Instance에 적용하고, spfile의 내용도 변경하여 DB를 재구동 하여도 변경된 설정값을 가진다.

 

spfile에서 pfile 만드는 법

SYS> create pfile from spfile;

 

pfile에서 spfile 만드는 법

SYS> create spfile from pfile;

※ 주의 dbs 경로에 pfile과 spfile이 모두 존재하는데, 현재 instance가 spfile로 구동되어 있다면, pfile에서 spfile을 만들었을 경우, 기존 pfile에 문제가 있는 파라미터라면 spfile이 덮어 씌워져 장애가 발생 할 수 있습니다.

 

주요 파라미터들의 의미

BACKGROUND_DUMP_DEST: 백그라운드 프로세스 발생 로그와 ALERT LOG 기록경로

CLIENT_RESULT_CACHE_LAG (11g 부터): CLIENT에 캐시 되어있는 RESULT 유효 사용기간 (milliseconds 단위)

CLIENT_RESULT_CAHCE_SIZE (11g 부터): RESULT CACHE 크기

CLUSTER_DATABASE: 기본값 FALSE, REAL APPLICATION CLUSTER (RAC) 기능 쓰는지 여부

COMPATIBLE: 호환가능한 이전 버전을 지정

CONTORL_FILES : CONTROL_FILES 경로 지정, 최대 8개

CURSOR_SHARING: HARD PARSING 줄이고 커서 공유 사용하는데 목적, 동일문장 기준 설정

• EXACT: 모든 문장이나 변수 값까지 동일해야함.
• SIMILAR : 문장은 동일하나 바인드 변수 값이 달라도됨
• FORCE : 문장은 동일하나 상수 값이 다른 SQL도 인정

DB_BLOCK_SIZE: DB 생성후 지정, 이후 변경 불가. DB에 사용될 STANDARD BLOCK SIZE 지정

DB_CACHE_ADVICE: V$DB_CACHE_ADVICE 뷰에서 서로 다른 캐시 사이즈에 대한 통계정보를 모을지 안모을지 지정

• OFF : 기능 사용 안함. 메모리 할당 안함.
• READY : 기능 사용안함, 할당된 메모리 유지
• ON : 기능사용, 추가적인 CPU와 메모리 사용량 증가

PGA_AGGREGATE_TARGET: 하나의 인스턴스에 접속한 서버프로세스가 사용 가능한 총 PGA 크기 설정

• 값이 0 이면 WORK_SIZE_POLICY 의 값이 MANUAL 로 설정
• 값이 0 이상이면 WORK_SIZE_POLICY 의 값이 AUTO 로 설정

PROCESS: 기본값은 100, OS 상 오라클 관련 프로세스 최대값 (USER PROCESS, SERVER ETC). SESSIONS 과 TRANSACTIONS 파라미터 기본값은 이 파라미터가 기준.

RECYCLEBIN: 기본값 ON, 휴지통과 같은 개념. OFF로 하면 TABLE DROP 후 바로 삭제.

REMOTE_LISTENER: 원격지 서버 리스너 이름

REMOTE_LOGIN_PASSWORDFILE: 외부 접속시 암호파일 사용여부

• SHARED : SYS 유저와 NON-SYS 유저 포함. 하나이상의 DB가 암호파일 공유해서 사용
• EXCLUSIVE : SYS 유저와 NON-SYS 유저 포함. 하나의 DB당 하나의 암호 사용
• NONE : 암호파일 무시, OS 인증 방식

RESULT_CACHE_MAX_RESULT (11g 부터): RESULT CACHE 내의 RESULT 최대 크기 지정. 기본값은 RESULT_CACHE_MAX_SIZE 의 5%

RESULT_CACHE_MAX_SIZE (11g 부터): RESULT CACHE 크기지정, SHARED_POOL_SIZE 의 1%, SGA_TARGET 의 0.5 %, MEMORY_TARGET 의 0.25% 권장

RESULT_CACHE_MODE (11g 부터): RESULT CACHE 운영방식

• MANUAL : /*+result_cache*/ 힌트를 사용한 쿼리는 result cache에 등록
• FORCE : 사용되는 모든 SQL RESULT CACHE 등록, /*+no_result_cache* SQL문 RESULT CACHE 등록 하지 않음
• AUTO : 많이 사용되는 쿼리 및 설정된 범위를 넘는 쿼리 등록

SESSIONS: 오라클 서버에서 생성 가능한 최대 세션 수. 최대 동시 접속자 수 지정.

• 권장값 (전체 동시접속 인원수 + 백그라운드 프로세스 수) X 10%

DB_CACHE_SIZE: DB Buffer 크기 지정

DB_CREATE_FILE_DEST: OMF(ORACLE MANAGED FILE) 환경에서 DATA FILE 생성 위치 지정

DB_CREATE_ONLINE_LOG_DEST_n: 최대 5곳 까지 다중화 가능 (n 부분에 5까지 지정). OMF 환경에서 REDO LOG FILE 과 CONTROL FILE 이 생성될 위치

DB_DOMAIN: 물리적으로 다른 네트워크로 떨어진 오라클 인스턴스들을 하나의 논리적 그룹으로 묶어주는 역활

DB_FILE_MULTI_BLOCK _SIZE: LRU 알고리즘 적용 받지않는 KEEP BUFFER CACHE 크기 지정

DB_NAME: 8문자까지 지정 가능, 대소문자 구분 안함. SINGLE 환경일 경우 INSTANCE NAME과 DB NAME이 같이 사용할 수 있고, RAC 환경일 경우 INSTANCE NAME 과 DB NAME은 다르다.

DB_nK_CACHE_SIZE: NON-STANDARD BLCOK SIZE 크기

DB_RECOVERY_FILE_DEST: FLASH RECOVERY AREA 경로지정. RMAN 백업파일, FLASHBACK LOG FILE, ARCHIVED LOG FILE 저장 (11g 부터는 FLASH RECOVERY AREA)

DB_UNIQUE_NAME: 기본값은 DB_NAME (INSTANCE일 경우), +ASM (ASM 일경우).

DB_WRITER_PROCESS: DBWR 의 갯수지정. 1 또는 CPU 갯수/8

INSTANCE_NUMBER: 해당 인스턴스 고유번호 지정. RAC 환경에서 (1부터 최대값)

LDAP_ARCHIVE_DEST_n: 지정가능 경로 총 10개, REDO LOG FILE 저장경로 지정.

LDAP_DIRECTORY_SYSAUTH: 기본값은 NO, SYSDBA 나 SYSOPER 권한의 디렉토리 인증기능 사용

LOG_ARCHIVE_DEST_STATE_n: 지정경로 사용여부

• ENABLE : 기본값, 해당경로 사용
• DEFER : 정의된 경로값 유지, 다음 활성화 될때까지 사용안되고 분류
• ALTERNATE : 지정된 경로 값들이 모두 실패할 경우, 이 경로가 활성화 됨

NLS_LANGUAGE: DB내에 기본적으로 사용될 언어지정. NLS_DATE_LANGUAGE 와 NLS_SORT 에도 영향을 줌.

NLS_TERRITORY: 해당 언어와, 날짜(요일,주) 사용하는 지역 지정

OPEN_CURSORS: 1개의 세션당 PL/SQL 등에서 사용하는 CURSOR의 최대 OPEN 갯수 지정

COMPLEX_VIEW_MERGING_FALSE: 실행계획 세울때 뷰 쿼리를 메인 쿼리와 합쳐서 수행하는 MERGE 기능 (VIEW를 포함하는 쿼리일 경우).  단순 VIEW는 GROUP BY 나 DISTINCT 등이 없는것, 복합 VIEW는 있는것인데 후자일때 MERGE 방법이 복잡해진다. 상황에 맞게 사용

CURSOR_FEATURES_ENABLED = 10: BUG 6795880 ( ‘kksfbc child completion 대기상태 Hang) 해결방법

_FAST_START_INSTANCE_RECOVERY_TARGET = 360: RAC 환경, 한쪽 노드가 장애로 CRASH 되었을 경우 다른 노드에서 해당 CRASH 를 RECOVERY 할 시간 지정

_GBY_HASH_AGGREGATION_ENABLED = FALSE: (10g R2부터) group by 를 order by 처럼 정렬되게 출력함. 기존 방식을 group by 수행하면 hash 알고리즘으로 그룹핑해서 정렬되지 않고, order by 를 같이 써줘야 했음

_GC_AFFINITY_TIME=0: RAC 환경에서 자동으로 요청 번호를 조사해서 마스터 노드 지정 해주는 시간 간격 (동적 리스타터링). 요청빈도가 많은 쪽이 마스터.

_GC_UNDO_AFFINITY = FALSE: RAC에서 UNDO SEGMENT를 활성화 한 노드가 자동으로 마스터노드가 되는 기능을 사용안함으로 지정

_IN_MEMORY_UNDO = FALSE: 작은 트랜잭션이 많을 경우 사용. 대량 DATA 변경되는 경우 사용안함. 활성화는 UNDO DATA 를 UNDO SEGMENT에 기록하지 않고 SHARED POOL 에 만들어져 있는 IMU (IN MEMORY UDNO) POOL에 기록. IMU POOL이 가득차면 기록된 DATA를 한꺼번에 UNDO SEGMENT 에 쓰고, 그 후 UNDO 데이터는UNDO SEGMENT 에 기록된다.

SESSION_CACHED_CURSORS: 하나의 SESSION이 CACHE할 수 있는 CURSORS 수

SESSION_MAX_OPEN_FILES: 1개의 세션에서 열 수 있는 최대 BFILES 의 개수

SGA_TARGET (10g 부터) : ASSM (AUTOMATIC SHARED MEMORY MANAGEMENT) 사용시 SGA 전체 사이즈 지정.

• 대상 : DB_CACHE_SIZE, LARGE_POOL_SIZE, STREAMS_POOL_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE
• 비대상 : LOG BUFFER, BUFFER CAHCE (KEEP, RECYCLE OTHER BLOCK SIZE), FIXED SIZE, INTERNAL ALLOCATIONS

UNDO_TABLESPACE: UNDO TABLESPACE 이름 지정

UNDO_MANAGEMENT: UNDO DATA의 관리 방법 지정

• AUTO : AUM (AUTOMATIC UNDO MANAGEMENT) 기능 오라클 자동관리
• MANUAL : MUM (MANUAL UNDO MANAGEMENT) 기능 수동관리

USER_DUMP_DEST: USER PROCESS가 생성하는 TRACE FILE 저장 경로

 

10g 설치 후 변경해야 하는 파라미터들

_DG_BROKER_SERVICE_NAMES =”: DATA GUARD 기능 해재해서 불필요한 SERVICE가 LISTENER에 등록 방지

_B_TREE_BITMAP_PLANS = FALSE: 실행계획 세울때 옵티마이저가 WHERE 절에 조건이 여러개이고 각 조건에 B-TREE 인덱스가 생성되어있을 경우, B-TREE 인덱스를 BITMAP 인덱스로 변환 후 실행계획 세우는데 이 것을 DISABLE해준다.

_BLOOM_FILTER_ENABLED = FALSE: RAC 환경일때 BUG 가 있으므로 기능해제. BLOOM FILTER 란 많은 양의 데이터 중 특정 데이터 ( 조인 파티셔닝 ) 있는지 없는지 빨리 찾아주는 기능

_CLEANUP_ROLLBACK_ENTRIES = 2000: SMON 이 종료된 (KILLED SESSION) 을 ROLLBACK하는 건수. 많을 수록 ROLLBACK속도 향상 다른작업 속도 느려짐

CLOSED_CACHED_OPEN_CURSORS = TRUE: 세션이 강제 종료된 자주 안쓰는 CURSOR를 CLOSE 해줘야함. CURSOR 는 PL/SQL에서 DATA 처리용인데, COMIMIT 과 ROLLBACK 후 CURSOR 를 CLOSE 해줘야 메모리 막고 에러줄임. 자주쓰는 CURSOR는 CLOSE하면 큰 부하 가져옴. 상황에 맞게 사용.

_KSS_USE_MUTEX_PIN = FALSE: 기본값은 사용함. SHARED CURSOR 관리를 MUTEX로 관리하라는 기능. 그러나 기존 LOCK/LATCH 보다 기능이 떨어져서 비활성화 시킴

_OPTIM_PEEK_USER_BINDS = FALSE: BIND KEEPING 이라는 기능인데 실행계획 세울때 사용. 부작용이 많아서 비활성화

_OPTIMIZER_COST_BASED_TRANSFORMATION = OFF: 옵티마이저는 SQL문에서 VIEW나 SUB-QUERY를 발견하면 RULE BASE로 QUERY TRANSFORMATION (변환작업)을 한다. 10g 이후 부터 COST BASE로 변환 작업을 하는데 정상적으로 변환이 일어나지 않아서 실행계획이 잘못 수행되므로 비활성화 시킴.

_OPTIMIZER_PUSH_PRED_COST_BASED = FALSE: 통계정보가 정확하다면 PUSH PREDICATE 로 최적의 판단을 하지만, 그렇지 않을 경우가 있으므로 RULE BASE 기반에서 쿼리 변화가 일어나게 설정하도록 PUSH PREDICATE을 비활성화 해준다.

_PX_USE_LARGE_POOL = TRUE: PARALLEL QUERY 를 수행한 경우 LARGE POOL 사용우무 결정

_ROW_CACHE_CURSORS = 1000: 데이터 딕셔너리 캐시에 캐싱되는 양을 지정. 기본값 20 인데 충분하지 않음

 

11g 설치 후 변경 해줘야 하는 파라미터들

OPEN_LINKS: 초기값 4개, 권장값 40개, 하나의 세션에 동시에 사용 할 수 있는 DB 링크의 갯수 지정

OPEN_LINKS_PER_INSTANCE: 초기값 4개, 권장값 40개 , 하나의 인스턴스에 동시에 사용할수 있는 DB 링크의 개수 지정

MEMORY_TARGET: 초기값 10G, 권장값 :0 이나 주석처리. 메모리 (SGA+PGA) 크기 자동 튜닝 기능때 총 메모리량 지정

DB_WRITER_PROCESS: 초기값 1, 권장값 2, DBWR이 기본 갯수 지정

SESSION_CHACHED_CURSORS: 초기값 50, 권장값 500, 하나의 세션당 캐싱되는 커서의 갯수

_DIAG_DAEMON: 기본값 TRUE, 권장값 FALSE. 분석 데몬의 자동시작 유무 결정