고민의 흔적

Optimizer_mode

• RULE
  • RBO(Rule-based optimizer) 규칙 기반 옵티마이저
• CHOOSE
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
• FIRST_ROWS
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 옵티마이저가 인덱스를 가장 많이 사용할 수 있도록 하는 모드 중 하나이다.
• FIRST_ROWS_N
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • N은 1, 10, 100, 1000개 행을 의미하고 최초의 N건의 응답 속도를 최적화할 때 사용하는 모드 중 하나이다. B2C 고객의 수가 엄청 많다면 FIRST_ROWS_1을 추천한다.
  • FIRST_ROWS_1을 사용한다면 optimizer_index_caching과 optimizer_index_cost_adj를 변경해주어야 한다.
  • optimizer_index_caching는 인덱스를 사용해 접근한 블록을 오라클 메모리, 버퍼 캐시 디비 캐시 사이즈 파라미터에서 찾을 수 있는 비율을 말한다. 보통 90 ~ 95
  • optimizer_index_cost_adj는 테이블을 Full Scan을 많이 할지 아니면 인덱스를 더 많이 사용할지를 결정하는 파라미터이다. 보통 30 ~ 35
• ALL_ROWS(DEFUALT)
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 데이터의 모든 ROW를 빠르게 가져오고 테이블 Full Scan을 선호하는 경향이 있다.
  • 소형 프로그램이거나 DBA가 없을 경우 권장한다.

Adaptive Query Optimization

Oracle 자동 SQL 최적화 관리이고 Oracle 12C부터 적용 되었다. 데이터 베이스가 내부적으로 분석해서 SQL 실행 계획을 최적화하는 방식이다.

• 오라클 옵티마이저 통계 및 통계 어드바이저
• SQL Plan 관리
• 정확도가 비교적 높은 SQL 처리

Adaptive Query Optimization를 false로 처리할려면

• 완벽에 가까운 모델링(테이블 설계)
• 아름다운 인덱스 설계
• 확실한 테이블, 인덱스 통계정보 관리
• 환상적인 SQL 튜닝

Adaptive Query Optimization 기능을 제어하는 대표적인 파라미터

• optimizer_adaptive_plans (Defualt: true)
  • 옵티마이저가 adaptive_plan을 고려 대상으로 판단할지 여부를 결정한다.
• optimizer_adaptive_statistics (Defualt: false)
  • 통계 피드백을 비활성화해서 SQL Plan 변경을 방지한다.
• optimizer_adaptive_reporting_only (Defualt: false)
  • 어뎁티브 기능에 필요한 정보만 수집하고 SQL Plan을 변경할지 여부를 결정한다.
• _optimizer_adaptive_cursor_sharing (Defualt: true)
  • 변숫값에 따라서 SQL Plan을 변경하는 파라미터이다.
• _optimizer_aggr_groupby_elim (Defualt: true)
  • 특정 기준 조건으로 group by를 할 때 안에 group by 있고 밖에 group by가 또 있다고 하면 두 개의 group by가 아닌 하나의 group by만 사용하는 경우가 있다. 이럴 경우 문제가 있을 수도 있어 false로 놓고 많이 사용한다.
• _add_col_optim_enabled (Defualt: true)
  • 오라클 내부에서 테이블 칼럼을 추가하면 빠른 속도로 처리가 된다. 하지만 Not null 조건에 defualt라고 하면 약간 문제가 있을 수 있어서 false로 놓고 많이 사용한다.

참고

https://www.youtube.com/watch?v=Y7scjyhY_1E&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=4 

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 

DBA_DATA_FILES에서 DATAFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES';

DBA_TEMP_FILES에서 TEMPFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일과 템프 파일 테이블 합쳐서 AUTOEXTENSIBLE이 YES인 것만 출력하기

SELECT 
    'ALTER DATABASE DATAFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES'
UNION ALL
SELECT 
    'ALTER DATABASE TEMPFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일 템프 파일 AUTOEXTEND 설정 끄기

ALTER DATABASE DATAFILE '경로' AUTOEXTEND OFF;

파라미터 변경

cd $ORACLE_HOME/dbs

-- 파라미터 백업
cp spfileSTR.ora spfileSTR.ora_20220706

ORACLE INSTANCE 가 START UP 시에 SPFILE과 PFILE을 읽어 들어 파라미터 값을 적용시킨다. SPFILE이 있으면 SPFILE을 읽게 되며, SPFILE가 존재하지 않으면 PFILE을 읽게 된다. 기본적으로 pfile은 initdXXXX.ora로 정의되어 있고 spfile은 spfileXXXX.ora로 지정되어 있다. 여기서 XXXX는 SID값이다.

-- pfile 생성 
create pfile from spfile;

spfile을 기본으로 pfile을 생성했다. 여기서 주의할 점은 pfile은 아스키 값이고 spfile은 바이너리 파일이다. 그래서 pfile은 직접 편집기로 수정해도 되지만, spfile은 명령어로 값을 변경하던지 pfile을 변경한 뒤 spfile로 만들어야 한다.

show parameter db_files
alter system set db_files=2000 scope=spfile;

디비를 내렸다 다시 올려야 db_files 파라미터 변경된다. 운영 시스템에서 절때 하면 안 된다!!!

SELECT 
    NAME,
    VALUE, 
    ISSYS_MODIFIABLE 
FROM V$PARAMETER
WHERE NAME = 'db_files';

V$PARAMETER 테이블에서도 db_files 파라미터가 변경된 것을 확인할 수 있다.

테이블 스페이스 구성 작업 후 PROFILE 설정 꼭 해줘야 한다. 설정하지 않으면 비밀번호 틀렸다고 시스템 계정이 접속이 안될 수도 있다.

SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    TEMPORARY_TABLESPACE,
    PROFILE
FROM DBA_USERS;

디비 처음 설치했을 때 생성되는 계정들과 PROFILE이 DEFAULT값으로 설정돼있는 것을 확인할 수 있다.

SELECT 
    * 
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT'
AND LIMIT <> 'UNLIMITED';

위의 테이블을 조회하면 DEFAULT 값이 상세하게 나온다.

ALTER PROFILE DEFAULT 
LIMIT 
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME    UNLIMITED
PASSWORD_LOCK_TIME    UNLIMITED
PASSWORD_GRACE_TIME   UNLIMITED;

UNLIMITED가 아닌 5개의 값이 나오는데 4개의 값만 UNLIMITED로 바꾼다. 엑셀 파일로 백업을 진행하고 설정을 변경한다. 항상 백업을 진행하고 설정을 바꿔야 한다.

• FAILED_LOGIN_ATTEMPTS 부정확한 password 허용 횟수 변경
• PASSWORD_LIFE_TIME 패스워드 유효기간
• PASSWORD_LOCK_TIME 암호 잠김 시간
• PASSWORD_GRACE_TIME 암호 변경 메시지 출력 일자

테이블 스페이스 생성

CREATE TABLESPACE TS_STR_D
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_D_001.DBF' SIZE 1G
AUTOEXTEND OFF;

CREATE TABLESPACE TS_STR_I
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_I_001.DBF' SIZE 1G
AUTOEXTEND OFF;

이름 규칙은 보통 테이블 스페이스명(TBS, TS) + DB 계정명 + 테이블(D), 인덱스(I)로 보통 사용한다.

DB계정 생성과 DB계정 권한

-- DB계정 생성
CREATE USER STR
IDENTIFIED BY "oracle"
DEFAULT TABLESPACE TS_STR_D;

-- DB계정 권한
GRANT CONNECT, RESOURCE TO STR;

DB계정 상태 & 권한 확인

-- DB계정 상태
SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    PROFILE
FROM DBA_USERS
WHERE USERNAME = 'STR';

-- 권한 확인
SELECT 
   * 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'STR';

테이블 생성 예시

-- 샘플 테이블 생성
CREATE TABLE STR.CUST
(
  CUST_NAME   VARCHAR2(20),
  MOB_NUMBER  VARCHAR2(20)
)
TABLESPACE TS_STR_D;

테이블, DB 계정, 테이블 스페이스 삭제

-- 테이블 삭제
DROP TABLE STR.CUST;

-- 휴지통 비우기(완전 삭제)
PURGE DBA_RECYCLEBIN;

-- DB계정 삭제
DROP USER STR CASCADE;

-- 테이블스페이스 삭제
DROP TABLESPACE TS_STR_D INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TS_STR_I INCLUDING CONTENTS AND DATAFILES;

참고

https://www.youtube.com/watch?v=bIRjupXHTvw&t=1041s