오라클 파라미터 관리(이론)

Parameter

파라미터는 오라클을 설정할 수 있는 환경 변수 값들이 모여있는 곳들을 말한다. 서버 파라미터 파일인 spfileDB이름.ora 안에 환경 변수 값들이 있다.

AMM

Automatic Memory Management로 오라클 데이터 베이스 메모리의 대표적인 SGA, PGA를 자동으로 관리하는 방식이다.

ASMM

Automatic Shared Memory Management로 오라클의 메모리 영역인 SGA를 자동으로 관리하는 방식이다. DB의 사용자가 적고 트렌젝션이 별로 없거나 DBA가 없고 개발자가 관리한다면 ASMM을 사용하는 것을 권장하긴 한다.

파라미터 변경

ALTER SYSTEM SET 파라미터명=값 SCOPE=MEMORY|SPFILE|BOTH;

MEMORY는 오라클의 메모리에 즉시 적용, SPFILE은 SPFILE에만 적용, BOTH는 둘다 적용

파라미터 값 복구

ALTER SYSTEM RESET 파라미터명 SCOPE=MEMORY|SPFILE|BOTH;

가능한 SPFILE로 변경하는 것이 좋다.

pfile, spfile 백업

cp spfileSTR.ora spfileSTR.ora_20220711
mv initSTR.ora initSTR.ora_20220706

sqlplus / as sysdba
create pfile from spfile;
exit

mv initSTR.ora initSTR.ora_20220711

파라미터 값 확인

SELECT * FROM V$PARAMETER;

파라미터 값 변경하기 전에 모든 파라미터 값 조회한 것을 엑셀 파일로 백업한다.

오라클 메모리 자동 관리 확인

show parameter statistics_level

위의 명령어를 치면 TYPICAL로 나오는 것을 확인할 수 있다. TYPICAL 값이 DEFUALT 값이다.

오라클 대표 파라미터

• sga_max_size : SGA 메모리의 최대 크기를 변경할 때 사용
• sga_target : ASMM 관리할 때 사용하는 방식 이 파라미터를 0으로 저장하면 메모리가 관리 방식이 수동으로 바뀐다.
  • shared_pool_size : 라이브러리 캐시, 데이터 딕셔너리 캐시가 저장되는 장소
  • shared_pool_reserved_size : shared_pool의 공간을 예약한다.
  • db_cache_size : 데이터 파일에서 불러온 블럭을 저장한다.
  • java_pool_size
  • large_pool_size : 오라클의 페러럴 처리 작업이나 RMAN 백업, 복구할 때 사용
  • streams_pool_size : 데이터 백업과 관련있다. 버퍼링 된 큐 메시지 저장하거나 캡처 프로세스 및 적용 프로세스를 위한 메모리 공간 중 하나이다.
• pga_aggregate_target : DB 인스턴스에 연결된 모든 서버 프로세스가 사용할 수 있는 PGA 메모리 영역 중 하나이다.

데이터 베이스에 동시에 접속할 수 있는 숫자를 결정하는 대표적인 파라미터

• processes
• sessions
• transactions
• open_cursors

옵티마이저와 관련된 파라미터

• optimizer_dynamic_sampling : 테이블 통계정보가 없는 상태에서 테이블의 특정 조건이 맞으면 자동으로 통계 정보가 갱신된다. 이 파라미터에 따라서 SQL Plan이 변경될 수 있다.
• "_optim_peek_user_binds" : SQL 사용할 때 바인드 변수를 많이 넣는데 이 파라미터에 따라서 SQL Plan이 변경될 수 있다.
• "_optimizer_use_feedback" : 새로운 SQL이 실행될 때 예측 값과 실측 값이 있다. 이 두 값의 차이가 발생하면 실행 계획이 변경될 수 있다.

앞에 _가 붙은 파라미터는 히든 파라미터라고 부른다.

업계에서 권장하는 메모리 값은 물리적인 서버 메모리 대략 10~20% 수준에서 오라클 메모리 파라미터 초기 설정을 하고 테스트를 통해서 계속 변경한다. 파라미터 적용할 때 spfile에 적용했다가 DB를 내렸다 올리는 게 좋다.

히든 파라미터 조회

-- Hidden Parameter 조회
SELECT 
    a.ksppinm "Parameter", 
    b.KSPPSTDF "Default Value",
    b.ksppstvl "Session Value", 
    c.ksppstvl "Instance Value",
    decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
    decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'
AND    a.ksppinm IN ('_optim_peek_user_binds','_optimizer_use_feedback');

SGA, PGA Hit Ratio 상태 정보 조회

-- SGA, PGA Hit Ratio 상태 정보
SELECT 
    'SHARED_POOL_SIZE(LIBRARY CACHE)' AS "MEMORY_PARAMETER", 
    ROUND((1-SUM (reloads)/SUM(pins))*100,2) AS "HIT_RATIO"
From V$LIBRARYCACHE
UNION ALL
SELECT 
    'SHARED_POOL_SIZE(DATA DICTIONARY)', 
    ROUND((1-SUM(getmisses)/SUM(gets))*100,2)
FROM V$ROWCACHE
UNION ALL
SELECT 
    'DB_CACHE_SIZE',
    ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0)) / 
    (SUM(DECODE(name, 'db block gets', value,0))+(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) 
FROM V$SYSSTAT
UNION ALL
SELECT 
    'PGA_AGGREGATE_TARGET',
    ROUND(VALUE,2) 
FROM V$PGASTAT
WHERE NAME = 'cache hit percentage';

Hit Ratio값이 높으면 거의 사용 안한다는 뜻이다. 값이 낮아지면 사용량이 높아지는 거여서 확인하면서 메모리 값을 변경해야 한다.

참고

https://www.youtube.com/watch?v=aBBgIYxKSkE&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=3