⚙️설치

• Oracle XE 다운로드 / 설치
• Oracle SQL Developer 설치

기본 Architecture

오라클은 데이터베이스와 이를 액세스하는 프로세스 사이에 메모리 캐시 영역(SGA)이 있다.

• 디스크 I/O 에 비해 메모리 캐시 I/O 는 매우 빠르다.
• 많은 프로세스가 동시에 데이터를 액세스 하기 때문에 사용자 데이터를 보호하는 Lock 과 공유 메모리 영역인 SGA 에 위치한 데이터 구조 액세스를 직렬화 하기 위한 Latch도 필요.
• 오라클은 블록 단위로 I/O 하며, DBWR/CKPT 가 주기적으로 캐시-데이터파일 동기화 수행.

Auto Trace

개요

SQL 수행 시 실제 일량 측정 및 튜닝하는데 유용한 정보들을 많이 포함하는 도구

AutoTrace 옵션

아래와 같은 옵션에 따라 필요한 부분만 출력해 볼 수 있음

1. set autotrace on: SQL을 실행하고 그결과와 함께 실행 계획 및 실행통계를 출력
2. set autotrace on explain: SQL을 실행하고 그결과와 함께 실행 계획을 출력
3. set autotrace on statistics: SQL을 실행하고 그결과와 함께 실행통계를 출력
4. set autotrace traceonly: SQL을 실행하지만 그 결과는 출력하지 않고, 실행계획과 실행통계만을 출력
5. set autotrace traceonly explain: SQL을 실행하지않고 실행계획만을 출력
6. set autotrace traceonly statistics: SQL을 실행하지만 그 결과는 출력하지 않고, 실행통계만을 출력

인덱스 기본 원리

B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프블록까지의 수직적 탐색 과정을 거쳐야 함

인덱스 사용이 불가능 하거나 범위 스캔이 불가능한 경우

• 정상적인 인덱스 범위 스캔이 불가능한 경우(Index Full Scan은 가능)
  • 인덱스 컬럼을 조건절에서 가공: WHERE SUBSTR(업체명, 1, 2) = '대한'
  • 부정형 비교: WHERE 직업 <> '학생'
  • IS NOT NULL 조건도 부정형 비교에 해당: WHERE 부서코드 IS NOT NULL
    • '부서코드'에 단일 컬럼 인덱스가 존재한다면 인덱스 전체 스캔을 통해 얻은 레코드는 모두 '부서코드 IS NOT NULL' 조건을 만족.
• 인덱스 사용이 불가능한 경우
  • IS NULL 조건만으로 검색할 때: where 연락처 is null
    • 예외적으로 해당 컬럼이 not null 제약이 있을 경우 Table Full Scan을 피하기 위해 사용.
  • IS NULL 조건을 사용하더라도 다른 인덱스 구성 컬럼에 is null 이외의 조건식이 하나라도 있으면 Index Range Scan 가능 (인덱스 선두 컬럼이 조건걸에 누락되지 않아야 한다) emp_idx : job + deptno where job is null and deptno = 20

용어

• RBO (Rule-Based Optimizer)
• CBO (Cost-based Optimizer)

Index 관련 자료

• https://use-the-index-luke.com/
• http://studybyyourself.com/seminar/sql/course/?lang=en
• https://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm
• https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics
• https://www.siue.edu/~dbock/cmis565/module12-indexes.htm
• http://www.dba-oracle.com/art_9i_indexing.htm
• http://www.remote-dba.net/t_op_sql_btree_indexes.htm
• https://www.morganslibrary.org/reference/indexes.html
• https://dev.to/databasestar/a-guide-to-indexes-in-oracle
• https://www.1keydata.com/sql/sql-index.html
• https://www.tutorialspoint.com/sql/sql-indexes.htm
• https://d2.naver.com/helloworld/1155
• https://www.geeksforgeeks.org/sql-indexes/
• http://www.dba-oracle.com/t_reverse_key_indexes_dml_insert.htm
• http://wiki.gurubee.net/pages/viewpage.action?pageId=26741180
• https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/
• http://wiki.gurubee.net/pages/viewpage.action?pageId=26741180

Hint

기본 사용예

SELECT /*+ INDEX(idx_col1) */ 
  id, password, name
FROM 
  emp;

SELECT /*+ ORDERED  INDEX(b idx_col1) */ 
  id, password, name
FROM
  emp a, depart b

trace

1. 테스트 테이블 생성

CREATE TABLE big_table AS 
SELECT 
  * 
FROM dba_tables, (SELECT level FROM DUAL CONNECT BY level <= 1000)
WHERE 1=1;

Partitioning

Range Partitioning

typically by date

CREATE TABLE 주문(주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객 ID VARCHAR2(5) )
PARTITION BY RANGE(주문일자) (
	PARTITION P2009_Q1 VALUES LESS THAN ('20090401')
,	PARTITION P2009_Q2 VALUES LESS THAN ('20090701')
,	PARTITION P2009_Q3 VALUES LESS THAN ('20091001')
,	PARTITION P2009_Q4 VALUES LESS THAN ('20100101')
,	PARTITION P2010_Q1 VALUES LESS THAN ('20090701')
,	PARTITION P9999_MX VALUES LESS THAN (MAXVALUE)
);

JOIN

방식

• NESTED LOOP JOIN

• SORT MERGE JOIN

• HASH JOIN

OUTER JOIN

NESTED LOOP JOIN

• SQL 문

SELECT A.DNAME, B.ENAME, B.SAL
  FROM EMP B, DEPT A
 WHERE A.DEPTNO = B.DEPTNO
   AND B.SAL > 200
   AND A.LOCATION = 'DALLAS'

학습목표

• 데이터베이스 성능 상의 문제점 분석
• 성능개선 목표 설정 및 수행
• 방법의 정의, 성능개선 결과를 정량적 평가
• 각 단계별 산출물 및 수행 활동을 규정 방법 공부