2020. 1. 20. 13:11ㆍOracle DBMS/Oracle 튜닝 실습 준비
환경은 CentOS 7 리눅스상의 오라클 DBMS 11G 기준으로 진행하며, 당연히 사전에 설치가 되어있어야 한다.
이 실습은 데이터 5000만건 ~ 1억건 수준의 대용량 데이터를 위해 오라클 DB 영역의 파티션은 반드시 용량확보를 많이 해놓아야 실습이 가능하다.
여기서는 파티션 용량을 넉넉하게 50G 이상 정도를 확보해놓겠다.
(*) 리눅스에서 df -ah 으로 파티션 용량 확인 가능
실습에 사용될 오라클 클라이언트는 SQL Developer로 진행하며,
필자는 오라클 DBMS 서버를 성능이 좋지않은 넷북으로 진행한다.
이유는 성능테스트는 최악의 상황에서 테스트해야 하기때문이다.
1. 관리자 권한으로 테이블 스페이스 생성
1) 관리자 권한(SYSDBA) 으로 로그인
sqlplus / as sysdba
2) 데이터 테이블 스페이스 생성 : MINOKUMA_DATA 라는 20G 짜리 테이블스페이스 생성
(*) 필자기준이므로, 본인이 원하는대로 수정해서 진행해도 무관하다.
(*) 당연하겠지만, 경로설정 관련 부분은 반드시 경로가 실제로 존재하는지 확인하는 습관을 갖고 진행해야 한다.
CREATE TABLESPACE MINOKUMA_DATA
DATAFILE '/app/oracle/oradata/{본인의 db명, 즉, sid}/minokuma.dbf' SIZE 20G
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
3) 임시 테이블 스페이스 생성 : MINOKUMA_TMP 라는 1G 짜리 임시 테이블스페이스 생성
CREATE TEMPORARY TABLESPACE MINOKUMA_TMP
TEMPFILE '/app/oracle/oradata/{본인의 db명}/minokuma_tmp.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
2. 관리자 권한으로 사용자 계정 생성 및 생성된 테이블 스페이스 지정
1) 관리자 권한(SYSDBA) 으로 로그인
sqlplus / as sysdba
2) 사용자 계정 생성
DATA 테이블스페이스와 TMP 테이블 스페이스를 기본으로 하는 minokuma 계정을 신규로 생성한다
이 minokuma 계정으로 로그인해서 생성하는 테이블 및 인덱스는 모두 minokuma_data에 생성된다
해당 계정으로 작업하다가 임시 영역이 필요하게되면 minokuma_tmp 영역을 사용하게 된다CREATE USER MINOKUMA IDENTIFIED BY ****
DEFAULT TABLESPACE MINOKUMA_DATA
TEMPORARY TABLESPACE MINOKUMA_TMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
3) 생성된 계정에 권한 주기
GRANT RESOURCE TO MINOKUMA;
GRANT CONNECT TO MINOKUMA;
GRANT CREATE VIEW TO MINOKUMA;
GRANT CREATE SYNONYM TO MINOKUMA;
테이블 스페이스를 갖고 권한을 부여받은 계정으로 실습을 진행한다.
2. 실습 테이블 구성
1) NOLOGGING 모드 설정
오라클에서 테이블에 NOLOGGING 모드를 설정 시 해당 테이블에 INSERT 작업 시 Redo 로그 작업 최소화
따라서 대용량의 데이터를 INSERT 작업할 때 데이터 입력시간을 줄일 수 있다.
ALTER TABLE 테이블명 NOLOGGING;2) APPEND 힌트
오라클이 테이블로 데이터를 입력 시 다음 절차를 거친다.
(1) 데이터 버퍼 캐시 공유
(2) 테이블 세그먼트의 비어있는 블록 검색
(3) 비어있는 블록에 데이터 저장
APPEND 힌트를 사용하면, 세그먼트의 HWM 바로 뒤부터 데이터를 입력하게 된다.
HWM는 세그먼트의 제일 끝부분이다.
또한 데이터 버퍼캐시를 거치지 않고, 바로 데이터를 저장하게되므로 데이터의 입력시간을 단축할 수 있다.
INSERT /*+ APPEND */ INTO 테이블명 3) 데이터 복제
대용량의 테이블을 구성하기 위해서는 테이블 복제 기법을 정확하게 알아야 한다.
[카티션 곱 조인] X건의 A테이블과 Y건의 B테이블을 아무런 조인없이 조인하면, X건 × M건의 데이터가 출력된다.
[계층형 쿼리 사용] 오라클에서 사용하는 계층형 쿼리를 이용해서 인위적으로 여러 개의 행을 출력할 수 있다.
다음 예제는 총 1000개의 행을 출력하게 된다
SELECT * FROM DUAL
CONNECT BY LEVEL <= 1000;
[카티션 곱 조인과 계층형 쿼리의 혼용]
카티션 곱 조인과 계층형 쿼리를 혼용하면 특정 테이블 내용을 복제할 수 있다.
다음 예제에서 테이블 A에 100건이 있다고 치면, 총 200건(100건 X 2 = 200건)의 행이 생기고, 테이블 A의 내용을 복제한다.
SELECT * FROM A, (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2);4) RANDOM 함수의 사용
테이블 구성 시 특정값을 인위적으로 만들기위해 RANDOM 함수를 이용한다.
1~100까지의 숫자중 특성 실수 숫자를 리턴하게 되는데, TRUNC 함수로 덮어씌어주면 정수 숫자로 리턴하게 된다
SELECT TRUNC(DBMS_RANDOM.VALUE(1, 100)) FROM DUAL;5) RANDOM 문자열
대문자로 된 10자리의 랜덤 문자열을 리턴하는 랜덤 문자열이다.
SELECT DBMS_RANDOM.STRING('U', 10) FROM DUAL;
소문자로 된 10자리의 랜덤 문자열을 리턴한다.
SELECT DBMS_RANDOM.STRING('L', 10) FROM DUAL;3. 실행 계획 및 통계정보 생성
1) 실행 계획
오라클의 옵티마이저는 사용자가 호출한 SQL에 대해 최적의 실행 계획을 도출한다.
실행계획대로 도출되면, 해당 실행 계획대로 SQL문에 대한 연산을 수행하게 된다.
오라클의 옵티마이저는 타 DBMS보다 월등한 성능을 자랑하며
아무리 복잡한 SQL문이라도 최소한의 비용으로 해당결과를 도출할 수 있다.
물론 모든 SQL문에 대해서 최적의 실행계획을 도출하는 것은 아니다.
가끔 옵티마이저도 비효율적인 실행 계획을 도출하며, DBMS에 과부하의 원인이 되기도 한다.
실행 계획을 분석해서 옵티마이저가 미처 최적화하지 못한 부분을 찾아 튜닝을 하는 것이 주요 목적이다.
2) 실행 계획 분석
[원칙 1. 연산 항목 중에서 제일 오른쪽에 있는 문자열부터 수행한다]
[원칙 2. 연산 항목 중에서 제일 오른쪽에 있는 문자열이 두 개 이상이면(같은 Depth 이면) 위에서부터 수행]
| ID | Operation | Name | Cost |
| 0 | SELECT STATEMENT | DEPT | 3 |
| 1 | HASH JOIN | DEPT | 3 |
| 2 | TABLE ACCESS FULL | DEPT | 3 |
| 3 | TABLE ACCESS FULL | EMP | 3 |
수행순서 : 2 > 3 > 1 > 0
2 : DEPT 테이블을 테이블 풀 스캔(TABLE ACCESS FULL) 한다.
3 : DEPT 테이블을 테이블 풀 스캔(TABLE ACCESS FULL) 한다.
1 : 2번과 3번 연산을 해시조인(HASH JOIN)을 사용. 옵티마이저의 판단은 해시 조인
0 : SELECT절에 대한 연산 수행
3) 통계정보 생성
오라클의 옵티마이저가 최적의 실행 계획을 생성하기 위해서는 통계정보가 미리 생성되어 있어야 한다.
통계정보의 생성 방법
(1) 테이블 통계정보 생성
EMP 테이블에 대한 통계정보를 생성한다.
ANALYZE TABLE EMP COMPUTE STATISTICS;
(2) 인덱스 통계정보 생성
PK_EMP 인덱스에 대한 통계정보를 생성한다.
ANALIZE INDEX PK_EMP COMPUTE STATISTICS;
(3) 특정 테이블과 테이블 내의 인덱스에 대한 통계정보 생성
EMP 테이블과 EMP 테이블이 갖고있는 모든 인덱스에 대한 통계정보를 생성한다.
ALALYZE TABLE EMP COMPUTE STATISTICS FOR TABLE
FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;