1.1 파라미터 파일의 개념과 종류[편집]

ALTER SYSTEM SET [파라미터이름=values] [SCOPE=(MEMORY/SPFILE/BOTH)]

ALTER SYSTEM SET [파라미터이름=values] [SCOPE=(MEMORY)]

ALTER SYSTEM RESET "_pga_max_size" SID='*' [SCOPE=(MEMORY/SPFILE/BOTH)];

$ cd $ORALE_HOME/dbs
$ ls -l
1.spfiletestdb.ora
2.spfile.ora
3.inittestdb.ora
4.init.ora

$ ls -al spfile*
-rw-r-----  1 oracle dba 2560  1월 26 02:17 spfiletestdb.ora

SQL> startup nomount

SQL> show parameter pfile
NAME         TYPE      VALUE
---------------------------------------------------------------
spfile         string      /home/oracle/product/10g/dbs/spfiletestdb.ora

SQL> select isspecified ,count(value) from v$spparameter group by isspecified;
ISSPECIFIED  COUNT(VALUE)
------------ ------------
FALSE               0
TRUE                22

$ ls -al initt*
-rw-r--r--  1 oracle dba 1089  1월 25 17:47 inittestdb.ora

SYS@testdb> startup nomount

SQL> show parameter pfile;
NAME         TYPE     VALUE
-----------------------------------------
spfile         string

SQL> select isdefault ,count(value) 
       from v$parameter group by isdefault
ISDEFAULT    COUNT(VALUE)
------------------ ------------
FALSE        21
TRUE       168

$ cp init.ora.010201115288 $ORACLE_HOME/dbs

SYS@testdb> create pfile from spfile;

SYS@testdb> create spfile from pfile;

ALTER SYSTEM SET 파라미터 이름=파라미터 값
[COMMENT='text']
[SCOPE=MEMORY/SPFILE/BOTH]
[SID='sid'/'*']

SYS@testdb>   
SELECT ISSYS_MODIFIABLE ,COUNT(*)
FROM v$parameter
GROUP BY ISSYS_MODIFIABLE;

ISSYS_MODIFIABLE     COUNT(*)
------------------ ----------
DEFERRED                  7
FALSE                        107
IMMEDIATE                 145

SYS@testdb>   
SELECT b.ISSYS_MODIFIABLE, COUNT(*)
FROM v$spparameter a, v$parameter b
WHERE a.name = b.name
GROUP BY b.ISSYS_MODIFIABLE;

ISSYS_MODIFIABLE     COUNT(*)
------------------ ----------
DEFERRED      7
FALSE     108
IMMEDIATE    145

select component, current_size/1024/1024 "current", min_size/1024/1024 "min", user_specified_size/1024/1024 "user",
  granule_size/1024/1024 "granule"
from v$sga_dynamic_components;

-- 1.$ORACLE_HOME/dbs 로 이동  
$ cd $ORACLE_HOME/dbs

-- 2.$ORACLE_HOME/dbs spfile,pfile 확인 (현재는 삭제되어서 존재하지 않음)
$ ls
backup  hc_testdb.dat  initdw.ora  lkTESTDB  orapwtestdb

-- 3. DB 기동(파라이터 오류 발생됨) 
$ sqlplus / as sysdba
SYS@testdb> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/product/10g/dbs/inittestdb.ora'

-- 4. 파일 복사 
-- 4.1. $ORACLE_BASE/admin/testdb/pfile에서 $ORACLE_HOME/dbs/로 파일 복사 
$ cd $ORACLE_BASE/admin/testdb/pfile
$ cp init.ora.010201115288 $ORACLE_HOME/dbs/inittestdb.ora

-- 5.데이터파일을 OPEN 하기 이전에 Controlfile의 경로가 맞는지 받드시 확인.
$ vi inittestdb.ora

# File Configuration
control_files=( '/home/oracle/oradata/testdb/control01.ctl',    
                '/home/oracle/oradata/testdb/control03.ctl')

-- 6.DB 기동 
SYS@testdb> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size      1267068 bytes
Variable Size     92277380 bytes
Database Buffers   188743680 bytes
Redo Buffers      2924544 bytes
Database mounted.
Database opened.
SYS@testdb> show parameter pfile;
NAME         TYPE      VALUE
----------------------------------------------------
spfile         string

SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size      1267068 bytes
Variable Size     92277380 bytes
Database Buffers   188743680 bytes
Redo Buffers      2924544 bytes
Database mounted.
Database opened.
SQL> show parameter pfile;
NAME         TYPE      VALUE
------------------------------------------------
spfile         string      /home/oracle/product/10g/dbs/spfiletestdb.ora

SQL> create pfile from spfile;

1.2 V$PARAMETER 컬럼 설명[편집]

(예를들어 파라미터 값이 PRIME이어야 하는데 유저가 입력한 값이 NON-PRIME NUMBER다. 그래서 오라클은 NEXT PRIME NUMBER로 값을 조정한다.
오라클에서 PRIME이 무엇인지 설명 요망)

※ 세션에 적용되는 현재 초기화 파라미터에 대한 정보를 표시한다.

※ 새 세션은 V$SYSTEM_PARAMETER로 표시되는 인스턴스 전체 값에서 파라미터 값을 상속한다.

1.3 오라클 추천 파라미터[편집]

1.4 OPTIMIZER_DYNAMIC_SAMPLING[편집]

 - 참조)OLTP 업무의 경우 Hard Parsing이 자주 발생하지 않아 크게 도움이 되지 않을 뿐더러 파싱에 의한 부하가 크기 때문에 권장되지 않음
 - DYNAMIC SAMPLING(동적 샘플링)은 통계정보가 존재하지 않은 Table에 대한 정확한 selectivity와  cardinality의 추정치를 결정하여 성능을 향상시키는 것이 목적
 - 옵션 변경 방법
   optimizer_dynamic_sampling = value
 - value 값
   0 :dynamic sampling 비활성화
   1 :9i Default value 1.수행 Query에 최소 하나 이상의 통계정보가 없는 table에 존재
                       2.통계정보가 존재하지 않은 Table이 Join을 수행하거나 Sub Query로 수행
                       3.인덱스가 존재하지 않음
                       4.통계정보가 존재하지 않는 Table의 block개수가 dynamic sampling을 수행하는

                          블록의 개수(32) 보다 많을 때

   2 : 10g Default value. 통계정보가 없는 모든 Table을 sampling 함. Sampling block의 개수는 1과 동일
   4 : where 절에서 두 개 이상의 컬럼을 참조하는 모든 table을 sampling함
   5 : default sampling block * 2
   9 : default sampling block * 128
  10 : table의 모든 block dynamic sampling

 - 일반적으로 DW 와 같이 양측 like (%:value%) 등을 사용하는 경우 예측치를 적게 잡게 되므로 dynamic_sampling 이 효율적일수 있으나 OLTP 환경에서 통계정보가 존재한다면 불필요한 부하는 주게되는 현상이 발생한다.
 - OLTP 권장값
   optimizer_dynamic_sampling = 0

1.5 _gc_policy_time[편집]

 - 참조) Resource Remastering 수행하지 않음(Remastering이 빈번할 경우 서비스 성능지연 발생)
 - RAC 환경에서 자신의 로컬 캐시에 존재하지 않는 특정 블록을 읽기 위해서 반드시 해당 블록을 관리하는
   마스터 노드와 통신을 해야 함.
   만일 특정 블록을 자주 access 하는 인스턴스가 해당 블록의 마스터 노드가 된다면 인스턴스간 통신이 줄어 들기 때문에 RAC 시스템 전체의 성능을 향상시키게 됨.
   특정 블록을 자주 액세스하는 인스턴스가 해당 블록의 마스터 노드로 변경되는 것을 동적 리소스 리마스터링 이라고 함.
 - _gc_policy_time 권장값
   _gc_policy_time = 0
  

1.6 _gc_undo_affinity[편집]

 - RAC 에서 undo segment 를 활성화 한 노드가 자동으로 마스터 노드가 되는 기능
 - _gc_undo_affinity 권장값
   _gc_undo_affinity = false
  

1.7 _optimizer_use_feedback[편집]

 - 11gR2에서 이 기능은 이제 튜닝 엔진이 아닌 런타임 엔진에까지 적용이 되었습니다.
   간단하게 정리하면 다음과 같습니다.
   .쿼리를 수행한 후 예상 로우 건수와 실제 로우 건수의 차이가 크다고 판단되면 해당 실행 계획은
    공유 불가 상태가 되고 실제 로우 건수를 저장한다. 하지만 예상 로우 건수와 실제 로우 건수의
    차이만이 유일한 팩터인지는 확실하지 않음.
   .다음에 동일한 쿼리가 다시 실행되면 최초의 실행에서 저장한 로우 건수를 OPT_ESTIMATE 힌트를 이용해 쿼리에 삽입한다.
    즉 Cardinality Feedback이 이루어진다.
   .이 과정은 단 한번만 이루어진다. 즉 최초의 쿼리만이 피드백의 대상이 된다.
   ._OPTIMIZER_USE_FEEDBACK 파라미터를 이용해 제어 가능하다. 즉 이 파라미터의 값을 FALSE로 변경하면 Cardinality Feedback은 일어나지 않는다.
 - 실행계획 변경될 수 있음
 - _optimizer_use_feedback 권장값
   _optimizer_use_feedback = false

1.8 _b_tree_bitmap_plans[편집]

 - Query Optimizer 가 SQL 을 해설할 때 Where 절에 여러 조건이 있고, 해당 조건컬럼들에 index 가 각각 생성되어 있을 경우 Btree Index 를 Bitmap 으로 conversion 하여 PLAN을 수립하여 실행함.
   이럴 경우 TYPE이나 CODE 성 컬럼의 경우 Btree Index Range Scan으로 해결되는 경우 보다 성능이 좋을수 있으나 일반적으로 성능이 저하되는 경우가 더 많음.
 - Bitmap Coversion시 성능저하가 일어나는 경우 발생
 - _b_tree_bitmap_plans 권장값
   _b_tree_bitmap_plans = false   

1.9 OPTIMIZER_INDEX_CACHING , OPTIMIZER_INDEX_COST_ADJ[편집]

 - OPTIMIZER_INDEX_CACHING설정하기 (0-100 default:0)
   . Nested Loops 조인이나 IN-List 탐침으로 수행되어 인덱스가 반복해서 랜덤 액세스를 할 때 인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 나타냄.
   . 이것은 곧 랜덤 액세스에 대한 부하가 감소되는 것을 의미하므로 옵티마이져가 Nested Loops 조인이나 IN-List 탐침으로 실행계획을 수립하는 경향이 증가한다.
     예를 들어 100으로 설정할 경우 SQL 조건에 따라 읽어들여야 하는 인덱스 블록이 100% DB Buffer Cache에서 찾아질 것을 가정하는 것이고  0으로 설정할 경우 Buffer Cache에서 찾아질 가능성이 0이라고 가정하는 것입니다.
     만약 랜덤 액세스가 많이 발생하는 환경에서 반복 액세스가 일어나는 인덱스가 적은 블록을 가지고 있거나 특정한 범위를 주로 액세스하여 재사용률이 매우 높다면 이 값을 증가시켜 주는 것이 좋다.
     그러나 이것은 비용의 조정을 의미하기 때문에 옵티마이저에게 커다란 영향을 미칠 수 있으므로 주의하여야 한다.
   . OPTIMIZER_INDEX_CACHING 파라메터는 in-list iterator 방식으로 인덱스를 탐침할 때 읽게 되는 인덱스 블록과 NL조인시 inner테이블에 속한 인덱스 블록에 대한 비용계산을 조정하고자 할 때 사용한다.
    단일 테이블을 액세스하기 위한 일반적인 index unique scan 이나 range scan 비용을 계산할 때는 영향을 미치지 않는다

 - OPTIMIZER_INDEX_COST_ADJ설정하기 (1-1000 default:100)
   . OPTIMIZER_INDEX_COST_ADJ 파라미터는 디폴트 100으로 설정되어 있으며 가능한 값의 범위는 1부터 10000 까지입니다.
     이 파라미터는 인덱스 탐색비용에 대한 평가를 조정하기 위해 사용하는 것으로서,디폴트 설정값 100은 인덱스에 대한 액세스 비용이 보통의 비용모델에 근거해서 평가될 것이고, 10으로 설정한다면 보통의 인덱스 액세스 비용의 1/10로 평가될 것임을 의미합니다.
     따라서 이 값이 작을수록 인덱스를 경유한 테이블 액세스(a Single-Block I/O) 비용이 더 적게 평가될 것이고, 클수록 인덱스를 경유하지 않는 Full Tabe Scan(a Multiblock I/O)의 액세스 비용이 더 적게 평가될 것입니다.
     바꾸어 말하면, 이 파라미터는 Single-Block I/O와 Multiblock I/O에 대한 상대적인 평가로 해석할 수 있고, 따라서 테이블 데이터 블록이 평균적으로 얼마만큼 캐싱되어 있는지에 대한 의미로 해석할 수도 있습니다.
     앞에서 설명한 OPTIMIZER_INDEX_CACHING이 인덱스가 캐싱되어 있을 확률을 가리키는 것처럼 말입니다.

 - 설정
   . 기본값인 OPTIMIZER_INDEX_CACHING = 0과 OPTIMIZER_INDEX_COST_ADJ = 100 : 이들은 일반적으로 데이터 웨어하우스/보고용 시스템에 적합.
   . OPTIMIZER_INDEX_CACHING = 90과 OPTIMIZER_INDEX_COST_ADJ = 25 : 이들은 일반적으로 트랜잭션/OLTP 시스템에 적합.

1.10 open_links[편집]

 - 초기값 4, 권장값 40
 - 하나의 session 에 동시에 사용할수 있는 DB Link 개수 지정
 - DB Link 사용을 위하여 값을 늘려놓을 필요가 있음

1.11 open_links_per_instance[편집]

 - 초기값 4, 권장값 40
 - 하나의 instance 에 동시에 사용할수 있는 DB Link 개수 지정
 - DB Link 사용을 위하여 값을 늘려놓을 필요가 있음

1.12 db_files[편집]

 - DB 에서 열수 있는 datafile 의 최대 개수
 - 권장값 : 서버 및 업무마다 조금씩 틀리지만 일반적으로 1000 이상으로 세팅

1.13 _optimizer_invalidation_period[편집]

 - 통계정보 변경 후 관련 SQL Cursor 가 invalidation 되는 시점을 지정
 - 권장값 : 통계정보 후 기존 Cursor가 즉시 무효화 되도록 설정
   _optimizer_invalidation_period = 1

== db_cache_advice ==
 - DB_CACHE_ADVICE를 통한  Buffer Cache 크기조절시 성능 예측, 즉 Buffer Cache 튜닝진단도구임(Advisory 기능)
 - Buffer Cache Advisory 기능 사용은 다음 두가지의 오버헤드를 발생
   . Advisory 기능은 buffer cache 별로 bookkeeping 을 위한 약간의 cpu 오버헤드가 발생
   . Memory : Advisory 기능은 buffer block 당 shared pool에 약 700 byte 정도의 메모리를 할당
 - DB_CACHE_ADVICE = {ON | READY | OFF}
   . OFF : Advisory 기능이 disable 되고, CPU나 MEMORY 오버헤드가 없음
   . ON  : Advisory 기능이 enable 되고, CPU나 MEMORY 오버헤드가 발생 (simulator lru latch wait event 가 발생할수 있음)
   . READY : Advisory 기능이 disable 되나, shared pool의 메모리는 할당
 - 권장값 shared pool 값이 충분하면 READY 를 설정해도 큰 문제는 없으나 그렇지 않을 경우 OFF 로 설정.

1.14 _library_cache_advice[편집]

 - libaray cache 크기 조절시 성능 예측
 - 권장 값 : _library_cache_advice = false

1.15 _db_mttr_advice[편집]

 - mttr(Mean-Time-To-Recover) 크기 조설시 성능 예측
 - 권장 값 : _db_mttr_advice = false
  

1.16 audit_trail[편집]

 - 의심가는 데이터베이스의 작업을 모니터링 하고, 기록 정보를 수집 하는 기능
 - 감사 로그를 기록해야 하므로 시스템의 속도 저하 발생 및 과도한 로그 생성으로 system tablespace의 증가
 - 11G부터 audit_trail default 위치가 DB로 설정되어 있어 tablespace full발생함
 - 권장 값 : NONE

1.17 optimizer_mode[편집]

 - batch job 등이 존재하지 않고 순수 OLTP 성 업무일 경우 변경
 - 권장 값 : FIRST_ROWS_1

1.18 _enqueue_hash_chain_latches[편집]

 - hash chain latch 는 해쉬 테이블과 해쉬 체인을 보호안다.
   기본 설정값은 cpu_count 와 동일하며 _enqueue_hash_chain_latches 파라메터를 통해 조정 가능하다.
 - 권장 값 : CPU CORE*3

1.19 _cleanup_rollback_entries[편집]

 - 한번에 rollback 하는 count (V$TRANSACTION.USED_UREC) 를 결정
   즉 Rollback의 성능향상을 가져옴.
 - 권장 값 : 500 정도, 개발 DB 나 Migration 등일때는 몇만 까지 잡고 처리해도 가능함.
   너무 큰 값을 사용할 경우 rollback 부하로 인해 다른 업무에 영향을 받을 수 있음.
 - fast_start_parallel_rollback=false 와 같이 고려
  

1.20 _highthreshold_undoretention[편집]

 - Undo tablespace가 계속 증가하는 현상 발생
   Undo 정보 확인해보면 unexpired된 extents가 많이 보임.
   즉 Undo extents 가 unexpired 상태에서 expired 되지 않는 문제로 해당 값 적용 권고
 - 권장 값 : _highthreshold_undoretention = 86400 [10800]
 - 권장 값 변경후에도 이슈가 발생시 _undo_autotune 을 false 로 변경

1.21 _optimizer_extended_cursor_sharing , _optimizer_extended_cursor_sharing_rel[편집]

 - 불필요한 child cursor 발생 방지-10g 존재 parameter
 - 권장 값 : 둘다 NONE
참조 )
alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;

1.22 _partition_large_extents=false (default=true)[편집]

   _index_partition_large_extents=false (default=false)
 - 일반적으로 테이블을 생성할 경우 64kb의 extent를 할당 받아 segment가 생성
 - Oracle 11g부터는 파티션 테이블에 대해서는 대용량 데이터라는 판단하여 extent할당하는 크기가 바뀜(table: 64KB -> 8MB, index : 64KB)
 - 이와 같은 경우 lange + hash composite partition 일 경우 테이블 생성초기부터 엄청난 공간을 차지하면서 segment 가 생성되게 됨.
 - 권장 값 : 둘다 false

1.23 _add_col_optim_enabled[편집]

 - column add 시에 dictionary 만 update 하고 , 이후 insert 시 해당 column이 생성
 - Add Column(default 값이 있고, NOT NULL 제약조건 이 있는 경우)후 Query에서Wrong Result가 Return 가능성
 - 권장 값 : false

1.24 _datafile_write_errors_crash_instance[편집]

 - Datafile한개라도 I/O error 날 경우 전체 instance shutdown
 - 권장 값 : false

1.25 _memory_imm_mode_without_autosga[편집]

 - ORA-4031 회피하기 위해 SGA_TARGET, MEMORY_TARGET이 설정되어 있지 않더라도 memory resize 

참조) 1. _gc_defer_time = 0 (default = 3ms)

 - cleanout 작업이 예상되는 current block 의 전송을 지연시키는 역활
 - block cleanout 은 dirty block 의 row level lock 정보를 해제한다는 것
 - gc current block pin time wait event 와 관련됨
   . 다른 instance 의 current 요청에 대하여, current block 에 대한 lock 을 획득하는데 소요된 시간
   . pin 에 시간이 소요되는 경우는 크게 2가지
   . 만일 전송대상인 current block 에 대하여 block cleanout 이 이루어지지 않았다면 _gc_defer_time (default : 3ms) 만큼 대기 후 lock 을 획득
   . 2번째로 block 경합이 발생하는 경우 -> 전송대상인 current block 을 local process 가 사용중이라면 lock 획들할 때 까지 대기

2. _use_adaptive_log_file_sync

 - 11g new feature로 log file sync 수행시 post wait 방식과 polling 방식을 선택할수 있음.
  . false : 기존 방식인 post wait 방식
  . polling-only : polling 방식 
  . true : post-wait 과 polling 방식의 자동 변환
 - 11.2.0.3 에서 false 권고
  post-wait 방식과 polling 방식을 자동 변환 중 성능 지연 발생 가능성이 있음. 

추가)

memory_target

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

- db_writer_process

- 초기값 50, 권장값 500
- 하나의 session 당 캐싱되는 커서의 갯수

- _diag_daemon

- 기본값 true, 권장값 false
- 분석 데몬의 자동시작유무 결정
- Diagnosability Daemon (DIAG) 는 process 와 instance failure 에 관련한 진단 정보를 포착(capture)하여
  systemstate dump 를 12로 생성하는 역활을 함.

1.26 Parallel Processing Parameters[편집]

Parallel Processing이 적용될 수 있는 범위는 매우 다양하며, 그 중에서도

- 단순 Select 작업 (Parallel Query)

- Table 및 Index의 생성, Index Rebuild 등의 DDL 작업

- 통계정보 생성 작업 (DBMS_STATS Package)

- Parallel DML (PDML) 작업

- Datapump(expdp/impdp)를 통한 Parallel 작업

- Parallel Recovery

등은 매우 빈번하게 사용된다.

Def. = FALSE
Deprecated Parameter in 11g

CPU
IO
integer||설정하지 않음 (자동 설정)
Def. = CPU 

MANUAL
LIMITED
AUTO	

Def. = MANUAL

 if PARALLEL_AUTOMATIC_TUNING is

Def. = 16384 (OSVersion Dependent)

Def. = FALSE 

Def. = FALSE
 PARALLEL_DEGREE_LIMIT로 대체

 Def. = 0 (%)

AUTO
integer||설정하지 않음
Def. = AUTO (30 seconds)
 PARALLEL_DEGREE_POLICY가 AUTO 또는 LIMITED인 경우에 한함

 PARALLEL_DEGREE_POLICY가