Study & Project repository

• 커서 공유 방법

1. 같은 문장에 바인드 변수 사용
2. 상수를 사용

• 바인드 변수 피킹 - 처음 데이터를 넣었을 때만 parsing하고 다음 데이터들 부터는 첫번째 실행계획을 사용

• SQL Plan Management

• 검증된 실행계획을 저장
• SQL 계획 Baseline

1. 첫 번째 실행계획이 baseline에 저장
2. 새로운 실행계획이 만들어져도 실행시키지는 못하고 baseline에 있는 실행계획을 사용
3. 검증과정을 거쳐서 다른 실행계획이 검증된다면 baseline에 넣어줌 -> 기존에 저장되어 있던 계획도 그대로 사용
4. baseline 안의 실행계획들중 어떤 것을 사용할지 cost를 계산하여 사용

• 중요한 baseline sql 계획 속성

1. ACC - baseline에 저장되었는지
2. FIX : 사용중인 실행계획인지

SQL Tuning Advisor

• SQL문 자동 튜닝

• SQL Tuning Advisor

1. 통계를 만듦
2. SQL을 튜닝하여 최적의 실행계획을 찾아냄
3. 찾아낸 실행계획을 프로파일로 저장 -> 힌트, 통계의 집합
4. 프로파일을 적용하면 성능이 좋아짐
5. 인덱스를 만듦 - create index 
6. 누락된 index 추가 -> access advisor 실행
7. SQL 재구성

• Materialized View

1. 데이터가 제한되게 보임 -> 보안성
2. 일부의 데이터 뷰를 만들어 놓고 그 범위 내에서만 조회 가능하도록 권한을 줌
3. refresh를 시키는 방법
   • complete : 전체를 refresh
   • fast(log) : log만 반영
4. Materialized View를 생성할 때, 사용했던 select 문을 조회해도 table이 아닌 view를 읽어옴 -> query rewrite

• WITH 절

• sql 문장의 의미를 이해하기 편해짐
• 한 번만 기술 하여 사용함으로써 가독성이 좋음
• 저장된 내용을 사용하기 때문에 좋은 성능을 가짐
• 문장 실행시 가장 먼저 실행됨

• Analytic function(분석 함수)

• over(partition by deptno) : 부서별로 묶어서 처리 - ex) 부서별 누적

• row_number() : 순위 함수

• rank() 와 dense_rank()

1. rank() 함수는 공동 순위를 매기고 그만큼 다음 순위를 밀어냄
2. dense_rank() 함수도 공동 순위를 매기지만 그 다음 순위는 그대로 +1
3. 부서 내에서 순위를 매길 수 있음

• windowing 절

1. 행들의 그룹 : window
2. 행들의 그룹들 중 제한해서 보여줌
3. rows 와 range 가 있음
4. rows는 물리적, range는 논리적

• row : 물리적 행 그룹의 제한

1. window의 범위를 앞, 뒤 한 줄씩으로 지정 - rows between 1 preceding and 1 following
2. sum(sal)을 앞, 뒤의 값만 수행
3. unbounded preceding and current row - 계속 누적 그냥 over과 동일

• range : 논리적 행 그룹의 제한

1. 기준이 되는 column의 범위(여기서는 -100 ~ +100)내의 값들을 사용
2. sum(sal)이므로 앞, 뒤로 100이 차이나는 범위의 empno를 가진 사원들의 sal을 sum

• order by 시에 null값을 어떻게 할 것인가

• 가정 함수

• 분석함수는 where보다 나중에 수행되므로 select절에 사용할 경우 잘못된 값이 나올 수 있음, 따라서 from절에서 inline view를 사용해야 함
• LAG 함수와 LEAD 함수

1. 바로 직전에 입사한 선임과 바로 이후에 입사한 후임을 알 수 있음
2. 위와 같이 선임의 입사 날짜, 후임의 입사 날짜를 확인 가능

• LISTAGG

1. 조건에 일치하는 컬럼의 값들을 한줄에 출력
2. 여러 행으로 나온 컬럼들을 한 컬럼으로 출력

• ratio_to_report

1. 전체 비율(100%) 중 어느정도를 차지하고 있는지를 보여줌
2. over안에 partition by를 사용하면 그룹으로 묶은 값들의 총 합 중 어느정도를 차지하는지 보여줌

• NTILE

1. 전체를 등분하는 함수
2. NTILE(5) over(order by sal desc) : sal을 내림차순 한 것을 5등분하여 숫자를 매김

• CUME_DIST(cumulative distribution) / PERCENT_RANK

1. percent_rank : (rank-1)/(count(*)-1)
2. cume_dist : (rank or row_number) / count(*)

읽기 일관성

• Transaction

• ACUD - 원자성, 일관성, 격리성, 영속성

1. 원자성 : 쪼개질 수 없는 단위
2. 일관성 : 데이터를 읽는 읽기 일관성

• 격리성 문제

1. Dirty read : commit이 안된 데이터를 읽는 것 - Read uncommited
2. Non repeatable read : 한 트랜잭션 내에서 두 번의 쿼리를 수행했는데 수정된 다른 값이 검색되는 것 -> 트랜잭션 레벨의 읽기 일관성을 사용(시작과 끝을 지정)
3. Phantom read : select를 여러번 한 결과가 다름 -> insert 되었기 때문
4. read commited : commit된 결과만 읽음 -> 문장이 실행 될 때마다 커밋이 되었는지 확인(dirty read 방지)
5. repeatable read : commit을 했으나 예전 데이터를 읽을 수 있음 -> dirty read 방지, non repeatable 방지
6. serializable read : dirty read 방지, non repeatable 방지, phantom reads

• DB 제작시 maintenance autotask 설정에 자동으로 통계 수집
• chain_cnt : migration의 횟수
• Noworkload 통계 : 인스턴스를 진행하며 1번 수집 
• workload 통계 : 자동으로 수집
• 시스템 통계는 제대로 된 workload에서 한번만 수집해 놓음
• 테이블 통계

1. 행 수, 블록 수, 평균 행 길이, 통계 상태
2. 상태 : 마지막 통계 이후에 얼마나 변했는가

• 인덱스 통계

1. B*-트리 레벨
2. 최하위 블록 수 -> 트리 레벨과 합치면 깊이
3. 인덱스 클러스터링 요소 : 데이터를 읽을 때, 한 블럭안에 모여있어서 그것만 읽어서 결과를 볼 수 있다면 좋음

• 컬럼이 조건절에 사용되는 비중까지 고려해서 통계 수집 가능
• 통계 수집시 설정한 기본값은 저장됨
• exec dbms_stats.gather_table_stats('HR','EMPLOYEE', method_opt=>'for all columns size auto') -> 위치지정 방식과 이름지정 방식 = hybrid

• 통계 Lock

• 자동 수집을 방지 -> 변화하는 것을 원치 않음
• 주로 휘발성 테이블에 사용됨
• 통계 복원 : 과거의 통계로 되돌리는 기능

• lock 실습

1. exec dbms_stats.gather_table_stats(user,'SALES'); -> 현재 유저의 SALES 테이블의 통계 수집
2.  select table_name, stattype_locked, last_analyzed
3. from dba_tab_statistics
4. where owner='USER01' and table_name='SALES'; -> last_analyzed가 방금 시간으로 되어있음
5. exec dbms_stats.delete_table_stats(user,'SALES'); -> 현재 저장되어 있는 통계를 제거
6. 다시 통계 생성 후 lock 걸기 -> exec dbms_stats.lock_table_stats(user,'SALES');
7. lock이 걸렸을 때는 통계를 수집 못함
8. unlock -> exec dbms_stats.unlock_table_stats(user,'SALES');

• 통계 복원 실습

1. dba_tab_stats_history : 31일간의 통계가 자동으로 저장되어 있음
2. delete시 현재 통계만 지워지고 기록은 남아있음
3. dba_tab_stats_history에서 통계가 생성된 시간을 복사
4. exec dbms_stats.restore_table_stats(user,'SALES',as_of_timestamp=>'시간');

• 통계 export 및 import

1. 데이터 뿐만 아니라 통계도 함께 export, import
2. DBMS_STATS 프로시저 사용
3. create_stat_table은 통계 테이블을 생성
4. export_*_stats는 통계를 통계 테이블로 집어 넣음
5. data pump로 통계 테이블을 이동
6. import_*_stats는 통계를 데이터 딕셔너리로 이동

바인드 변수 사용

• 커서 공유 및 다양한 리터럴 값

• 라이브러리 캐시에 서로 다른 sql문장들의 실행계획들이 저장됨
• 커서 공유하지 않아 실행계획이 동일하더라도 각각 parsing

• 커서가 공유되어 parsing을 새로하지 않고 1개의 실행계획으로 처리 가능
• 한 sql 문이 반복적으로 실행되는 경우
• 실행시간이 짧은 경우
• 더 좋은 실행계획이 존재하더라도 미리 만들어진 실행계획을 사용하는 경우가 생김
• 실습

1. alter system flush shared_pool; -> shared_pool을 비움
2. alter system flush buffer_cache; -> 버퍼를 비움
3. select sql_id, sql_text, parse_calls, loads, executions, invalidations
4. from v$sql
5. where sql_text like '%CURSOR_TEST%'
6. and sql_text not like '%v$sql%'; -> parsing된 것들 확인 가능
7. select /*+ CURSOR_TEST */ * from emp where empno=101; -> where절의 숫자를 바꾸며 여러번 실행하더라도 cursor 공유가 되지않아 parsing을 매번 실행함
8. 바인드 변수를 사용하면 cursor 공유 가능 -> variable b_no number
9. exec :b_no = 7788;
10. select /*+ CURSOR_TEST */ * from emp where empno = :b_no;
11. 7788, 7900 등 값들 입력
12. 다시 v$sql을 확인해보면 하나의 parse만 존재하는 것을 확인 가능
13. parse_calls : 몇 번 시도 했는지

• 바인드 변수 피킹

• 첫 번째 문장을 parsing할 때, 바인드 변수를 확인하여 실행계획을 공유
• 오라클이 알아서 판단하여 실행하기 때문에 현실 데이터와는 동떨어진 parsing을 할 수 있음
• 현실적인 값을 반영하기 위해 사용
• 처음에 오는 값이 현실성과 동떨어진 데이터라도 그 실행계획을 그대로 사용하기 때문에 문제가 생김

• Adaptive 커서 공유

• 적응적 커서 공유
• 첫 번째 실행 계획이 현실과 동떨어진 실행계획이라고 판단되면 다시 실행계획을 세움
• 데이터의 분포도에따라 여러 실행계획을 작성해 놓고 데이터에 맞게 사용
• 조건

1. bind variable 사용
2. bind variable peeking 사용
3. histogram 정보가 제공되거나 범위 연산일때

• 뷰

1. IS_BIND_SENSITIVE : 커서가 바인드에 따른 커서인지
2. IS_BIND_AWARE : 커서가 바인드 인식 커서인지

• 예제

1. 서로 다른 실행계획, 맞는 실행계획으로 실행
2. 바인드 변수를 사용했을 때의 이야기

• 바인드 변수를 사용하지 않는 경우

1. 변수가 아닌 상수들로 sql을 반복 실행 할 때
2. exact(기본 값) : 완전히 같을 때만 공유
3. force : 바인드 변수를 사용할 때처럼 
4. similar : 바인드 변수를 사용할 때처럼 하지만 안전한 리터럴에 대한 공유만 발생
5. 상수를 쓰지만 오라클 내부에서는 바인드 변수로 바꿔서 사용
6. cursor sharing을 exact가 아닌 다른 설정들로 사용한다면 adaptive cursor sharing으로 수행될 것임

• 옵티마이저가 최적의 실행계획을 생성할 수 있도록 제공해주는 테이블(객체) 및 시스템 분석정보
• 객체 통계 : talble, column, index
• 시스템 통계 : host의 CPU와 I/O Time에 대한 통계
• 자동 
  1. DB 생성시 maintenance autotask 설정에 의해(statistics_level=typical 또는 all) 매일 객체 통계수집이 실행되고 있음
  2. 새로 수집된 통계는 DD에 overwrite되며 이전 통계는 30일치를 저장하고 있음
  3. 통계가 empty(dynamic sampling을 해야 함) 또는 state(마지막 수집 후 10% 이상 변경됨)을 대상으로 수집
• gather_database_stats의 파라미터들

1. 개별적인 테이블들의 특성을 입력하여 통계를 수집할 수 있도록 파라미터 설정
2. 입력된 파라미터들을 저장
3. cascade : 테이블을 만들 때, 인덱스 통계도 만들라는 의미 -> estimate persent가 정해져 있어서 일정량마다 통계를 만들게 되므로 index 통계가 정확했으면 하는 경우 cascade를 no로 하고 한꺼번에 제작할 수 있음
4. no_invalidate : 만들어진 통계를 즉시 data dictionary에 넣게 되는데 이때 새로 만들어진 통계에 의해 shared pool의 parsing된 것들이 모두 incalidate되어 성능이 떨어지게 됨, 이 파라미터를 설정하면 즉시 게시하지 않음 -> auto 가 default(5시간에 한 번 게시)
5. granularity : 얼마나 나눠서 통계를 수집할 것인지
6. incremental : 파티션을 합쳐서 만들것인지
7. stale_percent : 설정한 만큼의 변경이 일어난 것만 통계 수집
8. dba_tab_stat_prefs에서 조회 가능
9. publish : 통계를 딕셔너리에 게시할 것인지, 아니면 이전 보류 영역에 저장할 것인지 결정

• 옵티마이저 통계 환경설정

1. select * from dba_tab_stat_prefs; - 설정 내용 확인
2. exec dbms_stats.set_table_perfs('SH','SALES','STALE_PERCENT',13); - 설정
3. select dbms_stats.get_perfs('STALE_PERCENT','SH','SALES') from dual; - 설정 내용 확인
4. 통계 환경설정 : db전체, schema, table level에 특성에 맞는 통계환경 설정을 할 수 있음

• 옵티마이저 통계 유형

• 시스템 통계

1. NoWorkload
2. workload : 데이터 베이스의 작업량에 따라 통계를 냄
3. CPU 속도 CPUSPEEDNW
4. IOSEEKTIM
5. IOTFRSPEED -- Noworkload
6. 평균 SBIO time SREADTIM -- 이하는 Workload 1/1000 초 ms
7. 평균 MBIO time MREADTIM
8. 평균 MBRC MBRC
9. CPUSPEED
10. MAXTHR IO 서브시스템의 최대처리량 -초당 바이트
11. SLAVETHR 병렬슬레이브의 평균처리량

• 테이블 통계(DBA_TAB_STATISTICS)

• 인덱스 통계

1. null값을 뺀 저장된 행의 갯수
2. BLEVEL : 최하위 블록 조회 비용을 계산하는데 사용되며 루트 블록에서 최하위 블록까지의 인덱스 깊이를 나타냄

• 인덱스 클러스터링

1. 인덱스 컬럼의 순서대로 데이터 블럭에 행이 정렬된 정도
2. 데이터의 순서와 인덱스의 순서가 동일하다면 cf가 blocks에 수렴
3. cf가 num_rows에 근접하면 가장 나쁨 -> 블록 스위치가 많다는 의미
4. clustering factor : 데이터가 모여있는 정도
5. create table ... as select ... order by ... 

• 히스토그램

• 빈도 히스토그램

1. 도수 분포표와 같음
2. 누적된 값이 표시됨

• 높이로 균형을 맞춘 히스토그램(high balanced histogram)

1. bucket의 개수가 num_distinct보다 작으면 만들어짐
2. 데이터가 한 쪽으로 치우친 경우 사용 - 불균등하게 분포
3. 버킷 안에 범위내의 값들이 들어가 있음

• 다중 열 통계

• hidden coulmn처럼 사용됨
• 다중 컬럼에 대한 히스토그램을 만들어야 함

• 표현식 통계

• 데이터의 변형이 생길 경우 변형된 데이터에 대한 통계가 필요

• 시스템 통계 수집

• 시스템 통계를 사용하면 CBO가 CPU와 I/O 특성을 사용할 수 있음
• 시스템 통계는 정기적으로 수집되어야 하며 캐시에 저장된 계획을 무효화하지 않음
• 시스템 통계 수집은 지정된 시간 동안의 시스템 작업 분석과 같음
• 예제

1. 낮에 OLTP 업무, 밤에 OLAP 업무
2. 이름을 각각 따로 하여 통계를 모음
3. 다음날 모아놓은 통계를 Import 할 수 있음
4. 바꿔가면서 통계 수집 가능
5. start 부터 stop까지 통계 수집

• 정리

• estimate_percent : dbms_stats.auto_sample_size, null은 전수조사
• block_sample : false
• method_opt : for all columns size auto -> 모든 컬럼에 대해 다 만들어라, 알아서 만듦 => 컬럼이 조건절에 사용되는 비중까지 고려해서 결정(sys.col_usage$)
• for all columns size 75
• for all indexed columns size auto : index가 있는 컬럼은 치우치든 말든 무조건 만듦
• for columns col1 size 10, col2 size 20 : 컬럼마다 사이즈를 설정 가능
• for columns size 20 col1, col2 : 사이즈 20으로 컬럼을 만듦
• cascade : 인덱스 통계까지 같이 수집
• 인덱스 통계 따로 수집하려면 dbms_stats.gather_index_stats(user,'index 명', estimate_percent=>100);
• no_invalidate : dbms_stats.auto_invalidate( 10g~)
• _optimizer_invalidation_period=18000초(5시간) : 5시간 이내에 무효화
• v$sql_optimizer_env : 파싱시점의 모든 optimizer 모드 관련 환경

• partition : 테이블, 인덱스 등을 파티션 단위로 나누어서 저장

1. 관리상 장점 : 백업, 복구, 추가, 삭제 -> 작은 단위로 백업과 복구를 할 수 있게되어 용이
2. 성능상 장점 : 파티션 단위로 조회, 병렬 -> 많은 데이터가 있더라도 작은 단위의 파티션만 읽어서 원하는 값을 얻을 수 있다면 성능이 훨신 좋아질 것
3. 파티션 프루닝(pruning) -> 불필요한 파티션을 엑세스 하지 않고 필요한 파티션만 엑세스
4. 파티션 와이즈 조인 -> emp 파티션, dept 파티션 둘 다 파티션 테이블이라면 둘을 조인할 때, 해당 파티션끼리만 조인을 할 수 있음
5. 병렬처리를 할 수 있음 -> OLAP(분석) 과정에서 full scan을 할 때

• partition의 종류

1. range 파티셔닝 : 파티션 키값의 범위에 따라서 각각의 파티션에 데이터를 저장
2. hash 파티셔닝 : 해쉬함수를 이용해서 파티션 갯수만 정해주면 오라클서버가 알아서 데이터를 분배하는 방식
3. list 파티셔닝 : 특정 파티션에 저장 될 데이터에 대한 명시적 제어가 가능

• 클러스터

• index 클러스터

1. index로 만들어진 클러스터 -> 인덱스가 반드시 필요, 인덱스 없이 데이터를 스캔 할 수 없음
2. cluster index 필요 -> null 값을 가질 수 있음
3. emp 와 dept를 조인한다고 가정
4. emp_dept_clust 라는 빈 공간을 생성
5. emp라는 테이블의 데이터와 dept라는 테이블의 데이터를 emp_dept_clust에 넣겠다고 선언
6. ex) 10번 부서, accounting, NY 라는 dept 데이터 아래에 10번 부서에 근부하는 사원들의 데이터를 emp테이블에서 가져와 저장
7. 부서를 배정받지 않은 사원이 있을 수 있으므로 cluster index에 null값이 존재
8. 사용자는 어떤 방식으로 데이터가 저장되어 있는지 알 필요가 없음 -> 데이터의 투명성
9. 단점
   • 상대적으로 클러스터에서 읽는 데이터의 양보다 원래 테이블의 데이터 양이 더 적을 경우(parent 쪽을 읽을 경우)
   • 저장 공간의 사이즈를 예측하여 만들어야 함 -> 총 몇개의 키, 들어갈 데이터가 한 블럭에 저장될지 등
   • 만약 예측이 빗나가서 한 블럭 이상을 차지하게 된다면 overflow영역에 이어서 저장되어 i/o가 늘어남
10. join된 형태의 데이터를 자주 사용할 경우 좋음

• hash 클러스터

1. 데이터를 넣을 때, hash함수에 먼저 넣어보고 나온 결과값에 데이터를 저장
2. hashkeys : 몇 개의 hash key 값으로 저장을 할 것인지 -> ex) 2000개의 데이터 1000개의 해시 키, 1개의 동일한 해시 키에 2개의 데이터가 저장
3. 데이터가 매우 많은 경우 찾기가 쉬움
4. 데이터가 없더라도 예측한 만큼의 영역을 지정해 놔야 함
5. 사이즈가 꽉 차서 넘어가면 overflow가 생김
6. 재구축에 부담이 많음

• data - table(heap) - partition - cluster(hash, index) - IOT(index organized table)
• cluster 생성

1. hash cluster 생성
• CREATE CLUSTER bigemp_cluster
• (deptno number, sal number sort)
• HASHKEYS 10000 single table HASH IS deptno SIZE 50
• tablespace users;
2. hash cluster 적용
• create table bigemp_fact (
• empno number primary key,
• sal number sort,
• job varchar2(12) not null,
• deptno number not null,
• hiredate date not null)
• CLUSTER bigemp_cluster (deptno, sal);
3. index cluster 생성
• CREATE CLUSTER emp_dept (deptno NUMBER(3))
• SIZE 600
• TABLESPACE users;
4. index cluster 적용
• CREATE TABLE emp2
• ( empno NUMBER(7) ,
• ename VARCHAR2(15) NOT NULL,
• job VARCHAR2(9) ,
• mgr NUMBER(7) ,
• hiredate DATE ,
• sal NUMBER(7) ,
• comm NUMBER(7) ,
• deptno NUMBER(3))
• CLUSTER emp_dept (deptno);
• CREATE TABLE dept2
• ( deptno NUMBER(3) ,
• dname VARCHAR2(14),
• loc VARCHAR2(14),
• c VARCHAR2(500))
• CLUSTER emp_dept (deptno);
5. cluster 테이블에 데이터의 위치를 관리할 index 생성
• CREATE INDEX emp_dept_index
• ON CLUSTER emp_dept
• TABLESPACE users;

• 정렬 연산자

• sort
  1. aggregate : 실제 sort가 아니라 그룹 함수를 사용했다는 의미
  2. unique : 중복 행 제거 -> union all -> sort 없음, union -> sort 있음
  3. join : merge join 진행
  4. group by, order by : group by한 결과를 sort까지 한 것
• hash
  1. group by : return된 데이터를 sort없이 group by 한 것
  2. unique
• buffer sort 연산자 : join 조건없음

• Inlist Iterator

1. 동일한 equal 연산이 여러개(or) 일 경우 별도로 실행되어 결과가 순서대로 보여짐
2. concatenation을 사용하면 sort되지 않고 뒤에서 앞으로의 순서로 결과가 보여짐
3. /*+ use_concat(1) */

• View 연산자

• count stop key 연산자

• Top-N query란? - from절의 inline view에 order by를 사용하는 것

• Min/Max 및 First Row 연산자

• 첫 번째 줄만 읽어서 성능이 좋음
• 범위가 넓더라도 1줄의 결과가 나옴

• 기타 N-Array 연산

• FILTER : 데이터를 filtering 하여 조건에 맞는 값만 걸러냄

• concatenation 연산

1. 비교하고자 하는 두 컬럼 모두 index가 있는 경우 두 index를 다 씀
2. LNNVL : 조건에 맞는 값이 오면 false
3. union all 방식으로 변형
4. select /*+ use_concat */ * from emp
5. where job='ANALYST' or deptno=10;
6. /*+ no_expand */ -> concatenation을 사용하지 않는 힌트
7. /*+ use_concat(1) */ : 뒤에서 앞으로
8. /*+ use_concat(8) */ :  sort

• Result Cache 연산자

1. parsing 하기 전에 실행된 결과가 있는지 확인
2. 이미 저장되어 있다면 cache에서 가져와서 출력
3. DB level, Table level, 문장 level
4. i/o가 일어나지 않음
5. select /*+ RESULT_CACHE */ deptno, avg(sal)
6. from emp
7. group by deptno;

사례 연구 : Star Transformation

• OLAP 환경에서 사용되는 모델링

• Star 스키마 모델

• DW 환경에서 사용 가능
• 사실값(fact) 테이블 : 분석하고자 하는 데이터가 한꺼번에 들어있음
• 차원(demension)/조회 테이블 : 어떤 제품인지, 어디서 판매되었는지, 고객 정보, 날짜 등 자세한 정보
• 예제

1. 모든 조건에 sales가 들어감
2. 행을 줄이기 어려움 -> where 절의 줄일 수 있는 조건들이 모두 demension

• star transformation

1. demention의 조건을 서브쿼리로 변환 -> sales 테이블의 행을 줄일 수 있음
2. 자동으로 바꿔줌

1. bitmap 인덱스 생성 후 연산

1. 줄인 행들을 가지고 조인
2. 결과 출력

• bitmap join 인덱스 사용

1. 조인할 데이터의 양이 줄어듦
2. 비트 방식 작업을 제거하는데 사용
3. 사용되는 조인 인덱스가 이미 사전 계산되어 있는 경우 조인해야 하는 데이터의 양을 줄일 수 있음
4. 조인 인덱스에 차원 테이블이 여러 개 포함되어 있으면 기존 비트맵 인덱스를 사용하는 star transformation에 필요한 비트 방식 작업이 제거될 수 있음

• Snowflake 스키마

• 차원 테이블의 카테고리를 만들 수 있음 -> 세분화
• 차원 테이블이 가지를 뻗어 나가는 형태

옵티마이저 통계

• 옵티마이저가 실행계획을 세울때 참고하는 통계

1. 객체 통계 : table, column, index
2. 시스템 통계

• 객체 통계

• 수집 방법

1. 수동
2. 자동

• analyze table 

1. analyze table emp2 compute statistics; -> empty_blocks, avg_space, chain_cnt 정보는 dbms_stats로는 수집되지 않음
2. exec dbms_stats.gather_table_stats('USER01','EMP3');
3. chain_cnt : migration 된 행의 개수 + chain 된 행(한 블럭에 한 행이 모두 저장되지 못해 다른 블럭에 이어서 저장)의 개수

• nologging : 대량의 데이터를 추출하여 옮기는 경우 redo를 최소화
• Clustering Factor : index column의 순서대로 테이블 데이타가 모여있는 정도

• Table을 저장하는 방식

1. Heap Table : 데이터를 계속 적재하며 저장하는 방식
2. Clustered Table : Cluster key를 기준으로 동일한 저장소에 저장
3. Index Organized Table : primary key 컬럼을 기준으로 index segment에 저장
4. Partitioned Table : 하나의 테이블을 둘 이상의 세그먼트로 서로 다른 테이블 스페이스에 저장
5. Temporary Table

• 클러스터

• 테이블 데이터를 저장하기 위한 선택적 방식
• 인덱스 클러스터
• 해시 클러스터

• Partitioned table

• 관리상 장점 : 백업, 복구, 추가, 삭제

1. 백업 : 테이블 전체를 백업하는 것이 아니라 세그먼트별로 백업 가능
2. 복구 : 복구 또한 세그먼트 단위로 가능
3. 추가, 삭제 -> 병렬 : 한 곳에만 데이터가 몰려있는 경우 성능이 좋지 않으므로 몰린 데이터를 나누는 것도 가능

• 성능상 장점 : 파티션 단위로 조회, 병렬

1. 불필요한 파티션에 접근하지 않고 필요한 파티션만 조회
2. 조인을 처리하는 경우 파티션 단위로 조인할 수 있어 성능이 좋아짐
3. 병렬 처리 : 모든 데이터가 균등해야 좋음, 여러 디스크에 병렬 저장

• Range Partition

1. 파티션을 나누고 각각에 들어갈 데이터의 기준을 정함(날짜 등)
2. 마지막 기준은 maxvalue라고 지정함으로써 혹시라도 데이터가 들어가지 않는 경우를 예방
3. select 하는 경우 파티션의 기준을 where절에서 사용하면 파티션 제거 효과를 볼 수 있음
4. 값이 골고루 저장 될 수 없음 -> 나중에 spread 시켜줄 수 있음

• List Partition

1. 리스트 별로 파티션을 나눔 - ex) 지역별 - '서울, 경기' , '전북, 전남, 강원' ... 등
2. 값이 골고루 저장 될 수 없음

• Hash Partition

1. hash 함수에 값을 넣고 리턴되는 값(위치) - ex) hash(100) -> 1, hash(200) -> 4 ...
2. 파티션 갯수만 정해주면 오라클 서버가 알아서 데이터를 분배
3. 파티션 키와 갯수만 정해짐

• interval-partition

1. 일정한 기준값 마다 자동으로 파티션이 만들어짐
2. ex) 1개월 마다 날짜에 따른 파티션이 생성

• range + hash : 값을 골고루 저장할 수 있음(range 안에 hash)
• local : 테이블 파티션 키와 인덱스 파티션 키가 같음

1. 관리상에 장점

• prefixed : 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음

1. 성능상에 장점 : 파티션 제거 가능
2. local prefixed : 성능이 가장 좋음

• non-prefixed

1. local nonprefixed

• Global

1. 베이스 테이블의 파티션 키와 다른 파티션 키를 가짐
2. 관리상의 장점이 없음 -> 베이스가 깨지면 전체를 다시 만들어야 함
3. 파티션 제거효과는 있음 -> 베이스 테이블과는 다른 조건을 비교할 경우 사용
4. global nonprefixed는 아무런 장점이 없음

• OLTP : 트랜잭션이 만들어지는 업무

1. 트랜잭션 : 한 단위로 처리 되어야할 작업의 단위(insert, update, delete 등)
2. Tx -> 데이터가 쌓임
3. 쌓인 데이터를 분석하기 위해 OLAP로
4. 데이터의 범위가 좁음

• OLAP : 트랜잭션 없이 분석하기 위한 업무

1. staging DB 과정을 통해 OLTP를 OLAP로 옮길 수 있음(CTAS, Insert Select 등)
2. Data Warehousing
3. DSS : 의사결정 지원 시스템
4. BI : 비즈니스 인텔리젼스
5. Data Mart
6. Data Mining
7. 의사결정을 하기 위함
8. 데이터의 범위가 넓음

• composite 파티셔닝

1. range-hash -> range내부에서 hash로 병렬처리 효과를 봄
2. range-list
3. range-range -> range내부에서 또다른 range로 나눔
4. list-hash
5. list-list
6. list-range
7. hash가 먼저 나오는 경우는 없음

• 11g partition

1. Reference 파티션
2. interval 파티션

• 해당 파티션만 읽을 수 있음

1. select * from range_sales partition(sales_q4_1999);

• 조인 컬럼에 인덱스가 존재하지 않는다면 hash join 방식을 가장 우선
• -> Nested Loops Join을 사용하게 하면 반복적인 Inner Table의 접근으로 I/O가 크게 증가할 수 있음
• swap_join_input() 힌트를 사용하여 driving을 바꿀 수 있음

• Subquery

• Nested Subquery : where 절에 사용되는 subquery 

1. Subquery unnesting(괄호를 풂) -> 서브 쿼리로 작성되었지만 조인 방식으로 해결될 수 있음
2. Single Row Subquery
   • 사원 141의 job_id와 동일한 job_id를 가진 사원을 출력 -> where job_id = (select job_id from employees where employee_id = 141);
3. Multiple row subquery
   • 각 부서의 최소 급여와 같은 급여를 받는 사원들의 이름과 급여 출력 -> where salary = (select min(salary) from employees group by department_id); -> 단일 행 하위 질의에 2개 이상의 행이 리턴되었다는 error 발생
   • = 대신 in을 사용 -> 각 부서별로 최소 급여를 받는 사원이 나오지 않고 다른 부서의 값과 같은 값을 가지는 사원들까지 모두 나오게 됨 -> multi culmn subquery를 사용해야 정확한 결과가 나옴
4. Multiple Column Subquery
   • pairwise(쌍 비교) 방식 - where (department_id, empno) in (...)
   • non-Pairwise 방식 - where department_id in (...) and empno in (...)
5. Correlated subquery(상관 서브 쿼리)
   • 메인 쿼리의 컬럼 값이 서브 쿼리 안에서 비교되는 경우
   •  

• Inline View : from 절에 나타나는 subquery

1. Simple View
   • View Merging : 결과 값들이 결합됨
   • 조건절과 조인문만을 포함
2. Complex View
   • group by, distinct를 포함하는 view
3. Non-mergeable View
   • Merging이 불가능

• Scalar Subquery : 하나의 컬럼 값 하나를 리턴

1. 딱 하나의 값만을 리턴해야 하는 쿼리
2. select 문 안의 서브 쿼리는 여러 값이 아닌 단 하나의 값이 나와야 사용 가능

• Subquery Tuning

• exists : 존재하는 값이 하나라도 있으면 결과를 출력하기 때문에 테이블을 하나하나 읽다가도 앞부분에 원하는 값이 존재한다면 index를 사용하는 것보다 더 빠른 성능을 보일 수도 있음
• Antijoin : 조인에서 반환된 내용의 정반대

• 인덱스 관리를 위한 지침

• 테이블 데이터 삽입(대량) 후 인덱스를 생성
• 올바른 테이블과 열 인덱스화

1. 큰 테이블에서 15% 미만의 행을 자주 검색하는 경우 인덱스를 생성
2. 다중 테이블 조인에 대한 성능을 향상하려면 조인에 사용되는 열을 인덱스화
3. 작은 테이블은 인덱스가 필요 없음

• 인덱스화에 적합한 열

1. 열에서 값이 비교적 고유(유니크)
2. 넓은 범위의 값(일반 인덱스)
3. 작은 범위의 값(비트맵 인덱스)
4. 열에 많은 널값이 있지만 query에서 값을 가진 모든 행을 종종 검색

• 인덱스화에 적합하지 않은 열

1. 열에 많은 널이 있고 not null 값에 대해 검색하지 않음
2. LONG 및 LONG RAW 열은 인덱스화할 수 없음
3. 가상 열

• 성능을 위해 인덱스 열의 순서 지정 : create index 문의 열 순서는 query 성능에 영향을 미칠 수 있음, 가장 자주 사용되는 열을 맨 앞에 지정
• 테이블당 인덱스 수 제한 : 테이블에는 임의 개수의 인덱스가 존재, 인덱스 수가 많을수록 테이블을 수정할 때 발생하는 오버헤드가 커짐, 테이블에서 데이터를 검색하는 속도와 테이블을 갱신하는 속도 사이에 절충점을 찾아야함
• 각 인덱스에 대한 테이블스페이스 지정 : 테이블과 인덱스에 동일한 테이블스페이스를 사용하면 테이블스페이스 백업과 같은 데이터베이스 유지 관리를 훨씬 더 편리하게 수행할 수 있음
• 인덱스 생성 병렬화 고려 : create index문에 nologging을 지정하면 인덱스를 만들고 최소 리두 로그 레코드를 생성할 수 있음, 이 인덱스는 아카이브되지 않으므로 인덱스를 생성한 후 백업을 수행

1. alter index emp_ename_idx coalesce;
2. alter index emp_ename_idx rebuild; -> 새로 만드는 것, 사용중일경우 불가능 -> online을 붙이면 가능
3. alter index emp_ename_idx rebuild online nologging;

• 옵티 마이저 : 조인 연산자

• DB 구축

1. 분석 : ERD
2. 정규화(Normalization) - 1NF, 2NF, 3NF
3. 설계 : 관계형 데이터베이스 table
4. 구축

• from 절에 2개 이상의 테이블을 참조하는 것이 조인
• 조인 조건을 쓰는 이유는 두 테이블이 조인했을 때, 쓸모없는 데이터를 걸러내기 위함
• Nested Loop Join

1. Driving 행 소스가 스캔됨
2. 반환된 각 행이 Inner 행 소스에서 조회를 구동
3. 조인 행이 반환
4. outer의 값과 inner의 값을 비교하며 조인 조건과 일치하는 데이터를 출력하므로 작업이 모두 끝나지 않아도 일부를 먼저 보여줄 수 있음
5. optimizer_mode를 first_rows_1 모드로 확인하면 좋음
6. 더 작은 테이블을 outer table로 사용하지만 추가적인 조건이 있는 경우 달라질 수 있음
7. join 컬럼에 index가 있는 table을 inner table 로 선정
8. 행을 줄여줄 수 있는 비조인 조건이 있다면 그 테이블을 driving(outer)로 지정
9. 부분 범위 처리
10. IRS -> 좁은 범위

• Nested Loops Join : Prefetching

1. 데이터 블럭을 불러올 때, 하나씩이 아니라 가까운 곳의 데이터 블럭도 읽어옴
2. physical read(물리적)의 지연을 줄이기 위해 사용
3. 사용하다가 효율성이 떨어진다고 판단되면 알아서 중단
4. 실행계획이 바뀜

• Nested Loops Join : 11g 구현

1. NLJ Batching 기법
2. Nested Loop 위에 Nested Loop가 하나 더 생김

• Sort Merge Join

1. 비교적 넓은 범위
2. sort를 위해 PGA의 workarea 사용
3. 자동 PGA관리로 workarea의 메모리를 조절하여 사용 가능
4. 두 테이블을 sort 후에 데이터를 찾기 때문에 결과행이 많더라도 빠르게 원하는 값들만 가져올 수 있음
5. outer가 상대적으로 덜 중요
6. 양쪽 모두 sort하는 것이 부담되니 한쪽만 sort하여 다른 한쪽은 index를 사용할 수 있음

• Hash join

1. 작은쪽 테이블을 outer로 설정
2. outer table = hash table -> 해쉬맵을 그리는 테이블
3. inner table = probe table
4. 첫 번째 행을 읽어서 hash 함수에 넣으면 hash value가 나옴
5. hash value를 hash map에 넣음, 다음 행 반복
6. hash map에는 join된 결과처럼 데이터들이 저장됨
7. equal join만 가능

• 실행계획을 확인할 수 있는 도구

• explain plan for select ... -> plan_table에 계획이 저장, 권한이 많이 필요하지 않음 -> dbms_xplan.display

1. $ORACLE_HOME/rdbms/admin/utlxplan 을 실행시키면 실행계획 테이블이 생성됨(세션이 바뀌어도 지워지지 않도록)
2. explain plan for select * from emp; -> 실행계획 저장
3. conn test/test -> 세션이 바뀌므로 원래는 위의 테이블이 삭제되어야 하는데 지워지지 않음
4. drop table plan_table
5. select * from table(dbms_xplan.display);

• set autotrace on - plustrace role 필요 -> plan table

1. autotrace를 쓰고자 하는 유저들에게 plustrace role을 부여
2. @$ORACLE_HOME/sqlplus/admin/plustrce.sql -> role을 생성
3. grant plustrace to 사용자; -> 이 사용자는 set autotrace on을 사용할 수 있음
4. sql 실행 (결과 집합)
5. 실행계획
6. 실행통계
7. set autotrace traceonly explain -> 결과와 실행계획만 보여줌(plustrace role 없이도 실행 가능)
8. set autot on explain -> 실행계획만 보여줌(role 없이 가능)

• 방금 실행한 문장이 메모리에 어떻게 저장되었는지 확인

1. v$sql
2. v$sql_plan
3. dbms_xplan.display_cursor(sql_id, child_cursor, format) -> 실행계획이 여러 개라면 child number가 늘어남
4. iostats
5. memstats
6. allstats = iostats + memstats -> 이러한 설정들을 사용하면 오류가 나는데 아래의 것들을 사용해야 함
7. /*+ gather_plan_statistics */ -> 힌트
8. statistics_level = all;
9. alter system으로 instance 변경 -> alter session set statistics_level=all;

• Table Access

• table access full

1. 조건절(where)이 없을 때 index를 사용하지 못함
2. index가 없는 경우

• table access by index rowid

1. 인덱스가 알려준 rowid로 테이블을 검색

• table access by user rowid

1. 유저가 찾아낸 rowid로 테이블을 검색
2. select dept.*, rowid from dept;
3. select문으로 찾아낸 rowid를 where문에 비교

• Sample

• 무작위로 원하는 만큼의 데이터를 확인할 수 있음

1. select count(*) from sales sample(10); - 10%만 보여줌
2. plan을 확인 -> index sample fast full scan(sales_ix04)
3. select * from sales sample block(10); -> table access sample
4. 계속 다른 결과가 나옴
5. select * from sales sample block(10) seed(1); -> seed 숫자가 같으면 같은 데이터를 리턴
6. plan 확인 -> table access sample

• Index access

• 저장 방법에 따라 달라짐
• B - 트리 인덱스

1. 루트 블럭 아래로 가지들이 존재
2. 트리구조
3. 벨런스(양쪽의 균형)가 맞음
4. 벨런스가 맞지 않게 되면 한쪽으로만 깊어지고 성능이 떨어짐
5. 한 노드에서 다음 노드로 연결되어 있음(link) -> range 검색에 효과
6. 자신보다 큰 값, 작은 값으로의 link
7. rowid가 존재 - 몇 번 파일의 몇 번째 블록에 존재한다는 물리적인 주소
8. index 헤더 존재
9. 인덱스의 순서대로 길이, 값이 순서대로 저장
10. sequencial access : 루트에서 브랜치로 수직적인 탐색 - ex) 10번 부서의 analyst 직업을 가진 사원
11. 검색한 데이터로 random access

• 인덱스 유니크(고유) 스캔

1. 딱 한 번만 인덱스를 읽고 끝냄
2. 데이터가 한 건
3. pk가 걸려있어야 함
4. emp 테이블에서 empno가 unique 함
5. select * from emp where empno = 7788;
6. 실행계획 확인 -> index unique scan
7. unique culomn을 equal 조건으로 검색해야만 함
8. hint 사용 : /*+ index ( employees (employee_id) ) */ -> employee_id의 index를 사용하라는 힌트
9. /*+ index (employees employees_ix01) */ -> 더 이전 버전의 방식
10. index (테이블명 (컬럼명)) or index (테이블명 인덱스명)

• index range scan(인덱스 범위 스캔)

1. 어떤 인덱스를 스캔하느냐에 따라 cost가 바뀌고 수행 시간도 달라짐
2. 또한 찾아낸 데이터의 순서도 변경될 수 있음

• 기본적으로 ascending으로 접근

• B* Tree Index

• index 항목과 rowid가 있음
• 컬럼의 값, rowid가 저장되며 항상 컬럼의 값으로 정렬된 상태를 유지
• 컬럼의 값은 null을 제외한 모든 값 보유 -> null값은 index에 저장되지 않음
• Root, Branch, Leaf block을 이용하여 Tree 구조로 DML이 일어나면 오라클에 의해서 자동으로 유지 관리
• 생성 : PK, Unique => 자동 생성, defferable(지연)이라면 생성 x
• 수동 생성도 가능

1. create index emp_lname_idx on employees(last_name);
2. select last_name, rowid
3. from employes
4. order by last_name
5. last_name이 null인 사람이 결과값에 표시되지만 index에는 없음
6. 한 블럭에 모든 데이터가 저장됨 - AAASOxAAAAAAACr...

• 인덱스를 사용하지 못하는 사례와 해결책

• 조건식의 부재, 잘못 사용된 조건식 ==> 조건식 생성

1. select deptno, sum(sal)
2. from emp
3. group by deptno    
4. having sum(sal)>9000
5. and deptno>20; --> 이 조건을 where절에 작성해야 함

• full scan 하는 경우

1. No index -> 인덱스 생성
2. index 사용 시 cost가 더 많이 나옴

•  컬럼의 변형

1. select *
2. from emp
3. where upper(ename) = 'SCOTT';
4. 데이터가 변형되었기 때문에 index를 사용하지 않음
5. function을 씌운 값 자체를 index로 만들면 사용 가능 -> Function Based Index

• Function Based Index

1. create index emp_ename_fbi on emp(upper(ename));
2. select * from emp where upper(ename) = 'SCOTT';
3. 1번에서 만든 index를 사용하여 탐색함

• 잘못 사용된 조건식 => 인덱스를 사용하는 조건식으로 변형

1. select * from emp where substr(ename,1,1) = 'S'; -> index를 사용하지 않음
2. select * from emp where ename like 'S%'; -> index를 사용함
3. select * from emp where substr(ename,2,1) = 'C'; -> index를 사용하지 않음

• is null, is not null 

1. select * from emp where comm is null; -> index에 저장이 되어 있지 않음(table full scan)
2. select * from emp where comm is not null; -> index에 저장되어 있지만 index 전체를 스캔(index full scan)
3. select count(*) from emp where comm is not null; -> aggregate 함수(그룹함수, 다중행 함수)를 사용(count (*))
4. select comm, sal from emp where comm is not null; -> 테이블의 값을 골라 오므로 index에서 찾은 rowid로 table 확인
5. select comm, sal from emp where comm is not null order by comm; -> sort과정이 일어나야 하지만 index를 full scan 하므로 sort를 할 필요가 없음
6. select comm, sal from emp where comm is not null order by sal; -> sal을 기준으로 sort 하는데 index는 comm을 읽었으므로 sort가 일어남

• 부정형 비교 => 조건식을 긍정형으로 변경

1. 부정형 : select * from emp where deptno <> 20; -> table access full
2. 긍정형 : select * from emp where deptno in (10,30); -> index range scan

• 암시적 형 변환 => 암시적 형 변환이 일어나지 않도록 변경

1. select * from emp where empno like '778%' -> number 형식을 문자 형식으로 변경했기 때문에 index 사용 불가
2. select * from emp where empno between 7780 and 7789; -> index 사용 가능(index range scan)

• index fast full scan

• 멀티 행 읽기(Multi Block IO)가 가능 -> sort(정렬된 결과)를 보장하지 않음
• /*+ index_ffs(...) */ : 힌트

• select * from emp where comm is not null order by comm; -> sort를 하지 않아도 index에서 그대로 값을 가져오기 때문에 정렬이 되어있음

• 내림차순

• range scan descending
• select * from emp where deptno>20 order by deptno desc

• 복합 인덱스

• where deptno = 10 and job = 'CLERK'; -> (deptno, job)의 복합 인덱스를 생성하여 사용
• 인덱스 skip scanning : index의 앞부분이 없더라도 index를 사용하는 방식