[Oracle] 오라클 환경 세팅 (튜닝 세팅)

오라클 튜닝 환경 세팅

오라클에서 SQL 튜닝 셋팅을 위해선 다음과 같은 3가지가 필요하다.
- 대용량 데이터를 저장할 테이블 스페이스와 사용자 계정
- SQL문으로 대용량의 테이블을 구성
- 오라클 통계 정보 분석

테이블 스페이스 및 계정 생성

테이블스페이스란 테이블이 저장될 공간을 의미하며, 이를 구분지어 관리함으로 성능을 향상할 수 있다. 테이블은 기본 8G를 보관할 수 있고, 추가로 1G 단위로 확장할 수 있다.

오라클은 data block, extent, segment, tablespace 라는 논리적 개념으로 데이터를 관리한다.

data block : 데이터를 저장하는 최소의 논리적 단위
extent : data block이 모인 단위
segment : extent가 모인 단위
tablespace : segment가 모인 단위

sqlplus "/as sysdba"

dba 관리자 권한으로 접속한다.

SQL> 
CREATE TABLESPACE DBMSEXPERT_DATA
    DATAFILE 'data_space.dbf' SIZE 8G
    AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED 
    LOGGING
    ONLINE
    PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO 
FLASHBACK ON;

dbmsexpert라는 오라클 인스턴스에 총 8G의 테이블 스페이스를 생성하자.

SQL> 
CREATE TEMPORARY TABLESPACE DBMSEXPERT_TMP
TEMPFILE 'tmp_space.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

1G 용량의 임시 테이블 스페이스 생성

SQL> 
CREATE USER DBMSEXPERT IDENTIFIED BY DBMSEXPERT
DEFAULT TABLESPACE DBMSEXPERT_DATA
TEMPORARY TABLESPACE DBMSEXPERT_TMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

DBMSEXPERT 계정을 생성하여 디폴트 테이블 스페이스를 방금 생성한 테이블 스페이스로 지정하자. 해당 계정으로 작업하다 임시 영역이 필요한 경우 TMP 테이블 영역을 사용하게 된다.

SQL> 
GRANT RESOURCE TO DBMSEXPERT;
GRANT CONNECT TO DBMSEXPERT;
GRANT CREATE VIEW TO DBMSEXPERT;
GRANT CREATE SYNONYM TO DBMSEXPERT;

생성한 계정에 권한을 부여하자. 오라클 유저에게 기본적으로 주는 롤은 connect 와 resource가 있다.
- connect : 해당 유저로 접속할 수 있는 권한으로 없으면 login denied 발생
- resource : create 트리거, 시퀀스, 타입, 프로시저, 테이블 등 8가지 권한이 부여되어 있다.

NOLOGGING 모드 설정

ALTER TABLE 테이블명 NOLOGGING;

오라클에서 테이블에 NOLOGGING 모드를 설정하면 INSERT 작업 시 REDO 로그 작업을 최소화한다.

APPEND 힌트

오라클은 테이블에 INSERT할 때 다음과 같은 단계를 거친다.

데이터 버퍼 캐시를 경유한다.
테이블 세그먼트의 비어 있는 블록을 검색한다.
비어 있는 블록에 데이터를 저장한다.

INSERT /*+ APPEND */ INTO 테이블명

APPEND 힌트를 사용하면 세그먼트의 HighWaterMark 바로 뒤부터 데이터를 입력하게 된다. 즉, 데이터 버퍼 캐시를 경유하지 않고 바로 데이터를 저장하게 되므로 입력 시간을 단축할 수 있다.

데이터 복제

대용량의 테이블을 구성하기 위해선 데이터 복제 기법을 정확히 알아야 한다.

카티션 곱 조인

N건의 데이터로 구성된 'A'라는 테이블과 M건의 데이터를 가진 'B'라는 테이블을 아무런 조인 조건 없이 조인하면 N*M건의 데이터를 출력한다.

계층형 쿼리 사용

SELECT * FROM DUAL CONNECT BY LEVEL <= 1000;

오라클에서는 CONNECT BY LEVEL 을 이용하여 여러 행을 출력할 수 있다.

SELECT * FROM A, (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2);

카티션 곱 조인과, 계층형 쿼리를 혼용하여 특정 테이블의 내용을 복제할 수 있다. A의 데이터가 N개라면 총 N * 2 개의 데이터를 복제할 수 있다.

RANDOM 함수

테이블 구성 시 특정 값을 인위적으로 만들기 위해 RANDOM 함수를 사용한다.

SELECT TRUNC(DBMS_RANDOM.VALUE(1,100)) FROM DUAL;

실수를 리턴하기 때문에 TRUNC 함수로 덮어 정수로 만들 수 있다.

SELECT DBMS_RANDOM.STRING('U', 10) FROM DUAL; # UPPERCASE
SELECT DBMS_RANDOM.STRING('L', 10) FROM DUAL; # LOWERCASE

대, 소문자로 된 랜덤 문자열을 리턴한다.

실행 계획

오라클의 옵티마이저는 사용자가 호출한 SQL에 대해 최적의 실행 계획을 도출해준다. 타 DBMS보다 월등한 성능을 자랑하며, 복잡한 SQL문도 최소한의 비용으로 결과를 도출해준다.

하지만, 때로는 비효율적인 실행 계획을 도출하며, DBMS의 과부하의 원인이 되기도한다. 따라서 옵티마이저가 최적화하지 못한 부분을 찾아 튜닝하는 것이 중요하다.

실행 계획은 다음 두 가지 원칙을 기본으로 한다.

Operation 항목 중 가장 오른쪽에 있는 문자열부터 수행한다.
Opertaion 항목 중 가장 오른쪽에 있는 문자열이 두 개 이상이라면, 위에서부터 수행한다.

SELECT *
FROM
    EMP A, DEPT B
WHERE
    A.DEPTNO = B.DEPTNO;

다음과 같은 SQL 문의 실행 계획을 살펴보자.

수행 순서는 ID 기준 2-3-1-0 이 된다. 1번에서 해시 조인 연산이 수행된 것은 옵티마이저가 판단해서 적용시킨 것이다.

REFERENCE

https://www.hanbit.co.kr/store/books/look.php?p_code=E9267570814

저작자표시