데이터베이스 강의 자료

이화식
- 대용량 데이터베이스솔루션


드라이빙 조건
 -  

2022.08.24

- 1.부분범위처리
- 실행계획에서  rows level 별로 내부 계획별 실행


- order by :드라이빙 역할시 인덱스 

- 힐클라이밍 알고리즘
- http://wiki.hash.kr/index.php/%EC%96%B8%EB%8D%95_%EC%98%A4%EB%A5%B4%EA%B8%B0_%EA%B2%80%EC%83%89

- 인덱스 디센딩 힌트 실행계획
- 


- 플랜에서 View : 내부적으로 임시 공간 


- 서브쿼리 내부 조건에서  Sort Unique 
- https://karisma3s.tistory.com/entry/%EC%84%B1%EB%8A%A5%EA%B3%A0%EB%8F%84%ED%99%94-52-%EC%86%8C%ED%8A%B8%EB%A5%BC-%EB%B0%9C%EC%83%9D%EC%8B%9C%ED%82%A4%EB%8A%94-%EC%98%A4%ED%8D%BC%EB%A0%88%EC%9D%B4%EC%85%98


- 드라이빙이 누구냐?? 범위 중요 최대한 줄이는게 좋음
- 디센딩과 다를 경우 문제발생

- 19p 추가적으로 디센딩 조건을 드라이빙으로 넣는 순간 개선 (온라인 일때는 빠름 배치일때는 아닐수 있음)

- min/max 20p
where 1=2
일경우 nvl 처리 하더라도 값이 안나옴 fail

- sum등의 그룹함수를 쓸 경우 결과가 산출됨


-21p. 아웃루프, 인어루프 순서대로 실행계획 수립



- 쿼리블럭 - 머리 몸통 다리 (예를들면 인라인뷰)
-http://dbcafe.co.kr/wiki/index.php/Query_Block 쿼리블럭


- RDB 에서 로우넘이 생기는 경우 
- 쓰기 작업이 있을 경우 로우 넘이 생김



- 테이블 설계시에 세부 분야부터 생각하지말고 큰 틀 (집합)을 먼저 생각하고 설계


- 옵티마이저 단위 
- SQL 



-연결고리 상태

- 연결고리 정상

- 연결고리 이상
- 인덱스를 제대로 못타고 역주행

- ex) x.dept_cd (var) = y.dept_cd (num)
 - 인덱스 역주행
 

- 인계점 을 단계로 차이가 발생할 수 있음.

NESTED LOOP 일때는 야당에서 조건 필터해도 큰 변화가 없음


SORT MERGE 
- 반대로 소트 머지는 오히려 느려 질 수 있음
- 어느 순간 급 느려짐


HASH JOIN
-최근에는 대다수가 SORT MERGE 보다는 HASH JOIN 으로 가는경우가 많음
- DW의 케이스의 경우 데이터가 많기 대문에 해쉬조인 사용 일정 시간간격으로 증가



-RANDOM -> SORT -> HASH 


HASH
-f(x) = 값 (위치)해쉬 을 이용해서 가르킴



-해시 버킷 
https://www.databricks.com/kr/glossary/hash-buckets


-인메모리 해시 조인 

- http://wiki.gurubee.net/pages/viewpage.action?pageId=26740416 

-

- 소트 작업은 소트 에어리어 
- 해시 해시 에어리어

-버킷의 존재 여부를 비트맵벡터로 알수 있음


-In -Memory Hash JOIN
- 가장 빠른 해쉬 조인

- 메모리 내에 해쉬 테이블을 만들어서 찾는 방식
- 작은 단위의 메모리 일수록 찾기 쉬워짐


위 해쉬 조인이 안되는 경우 [ex)DW]


-On-Disk Hash Join(Grace)
-partition pair에서
-partition duo를


하둡 - 해쉬조인




- 옵티마이징 수행절차
-컴파일 방식이아니고 텍스트를 인식해서 실행
-파서(사전) 문장을 해석



-view merging

-mview 물리적 데이터를 생성
-https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=musicovery12&logNo=220232911776

-

2022.08.25


- 4. 실행계획의 해석

- 실행계획 순서
-안에서 밖으로 실행됨


- 해쉬조인 안에 인메모리 

- INLIST <- IN() 에 들어가있는 값 만큼 반복

-하향식 (Top-Down) 방식

-DFS 깊이우선


-BFS 너비우선

-상향식 (Bottom-Up) 방식




- elapsed 타입이 오래걸릴경우
- 대기 타임이 오래 걸릴수 있음
- io 가 많을 경우에도 웨이팅 될 수 있음



- partitioning 전략
-

- 옵티마이저 모드
  - 모드부터 설정되있어야함
  - ALL_ROWS, FISRT_ROWS, CHOOSE, RULE   
 - https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=rnarnsms73&logNo=40152252489
 - http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=80


- OLTP : FIRST_ROWS

- DW : ALL_ROWS

- 통계정보 옵티마이저의 COST 
 - 참고 : https://blog.sengwoolee.dev/88
 


WHERE 문장
- 드라이빙
- 필터
- 인덱스 사용불가로 인한 비효율적 원인

- FBI 펑션 베이스 인덱스
 - http://wiki.gurubee.net/pages/viewpage.action?pageId=1507454
 
- NULL은 인덱스를 찾아갈수 없음

- 비트맵인덱스의 경우 NULL을 인덱스로 사용가능

- 날짜나 짜른거 인덱스를 꼭 태워야하면 FUNCTION BASE INDEX로 사용



- 인덱스 미사용
 -88P,~90P 
 
 
- SKIP_SCAN 힌트에 
- 마땅 한 인덱스를 못탈 때 COL1+ COL2 합쳐서 가있을때 인덱스를 태우는 방법
- https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=weekamp&logNo=221600846268


- index skip scan 인덱스 전부 스캔하지 않고 SKIP해서 스캔하는 엑세스 방법


- /*+ INDEX_SS(table index) */


Filtering 인덱스 미사용
- 드라이빙 종류가 만들어져야 됨응로 어중간한 드라이빙 케이스 만들지말것

-  안좋은 케이스
chuldate like :chuldate || '%'
and custno like :chuldate || '%'


- 정 안될 경우 union 하면서 각 조건에 like


클러스터링 펙터
- 물리적 디스크파일에서 데이터가 모여있는 정도를 나타낸다.
https://velog.io/@kw78999/DB-Clustering-Fector



인덱스나 힌트를 사용할 시에 손익분기점을 계산해여 cost 산출하면서 사용여부 확인


A B C

(=  = like) <-드라이빙 


(= LIKE)<-드라이빙 =

(LIKE) =  =


- 인덱스 종류 https://myjamong.tistory.com/237


-http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=102

오라클 규칙 기반 옵티마이저(RBO, Rule-Based Optimizer)
최근에는 거의 사용되지 않는다.
오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 
OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우
(11g이후 CBO로 동작) 인데 
DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 
파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다





- 99p join의 효율화 **

- 제공자역할(서브쿼리)의 경우 1의 건수가 되어야하며
- 확인자 역할을 N건의 데이터가 될수 있다



- cost 베이스로 가는데 통계정보가 없을 경우에 실행계획이 엉망일 수 있음



- 부분범위처리일 경우


- Join Cost 를 감속하기 위해서는
- or를 사용하지않고
스칼라 서브쿼리, hash 나 sort 를 이용하여서 조인 방법으로 선택

- 내부 테이블 조건에서 드라이빙 테이블의 cost를 감소화 시키는경우가 좋음 103p


- 실행계획 확인 방법 XPLAN, AutoTrace, SQL Trace
- https://myjamong.tistory.com/236


 - Execute, Fetch Call 발생 유형 
https://m.blog.naver.com/bkbspark0725/222034230005
01 Call 통계
1) Parse Call   : 커서를 파싱하는 과정( 실행계획을 생성하거나, 찾는 과정 )을 수행한 횟수
2) Execute Call : 커서를 실행하는 과정을 수행한 횟수
3) Fetch Call   : select문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 출력한 횟수

# select 문일 때 Execute Call 단계에서는 커서만 오픈하고, 실제 데이터를 처리하는 과정은 
  모두 Fetch 단계에서 일어난다.
# group by 문장은 Fetch 지점에서 처리가 일어남


- 세미조인 


- unnesting
- 종속관계인 메인과 서브쿼리를 동격관계 로 최적화
- 데이터량이많을 경우 선처리 후 서브쿼리로 조건 추가 가능

- unnest
- 계급장 때자

- no_unnest
- 계급장 인증 후 처리

-확인하기
- http://wiki.gurubee.net/pages/viewpage.action?pageId=26741721

- filter

- nested



- /*+ unnest */  무조건 서브쿼리를 먼저 돌리는 형식이아니고 계급장을 때고 판단하는 것



- copy_t

https://dev4u.tistory.com/132 copy_t



-오라클 메모리(Shared Pool)

https://1duffy.tistory.com/19



- 오라클 아우트라인 outline data
- 컨닝 페이퍼
https://bae9086.tistory.com/381
- 버전이 바뀔경우 이전 버전으로 튜닝기준 설정



2022.08.26

- SQL 수행 성능 분석

- Trace 파일 출력 및 분석

- 

- Array Processing 활용
-http://wiki.gurubee.net/pages/viewpage.action?pageId=26279996


- 모든 I/O 단위는 블록단위 

- block 단위에서 메모리 상태에서 문제가 발생할 시에 redo log로 롤백을 한다!


- snapshot too old  : batch 잡 을 실행할 때 오래된 시점의 데이터가 사라지면서 나타나는 현상
-https://atoz-develop.tistory.com/entry/Oracle-ORA-01555-Snapshot-too-old-%EC%97%90%EB%9F%AC-%EC%9B%90%EC%9D%B8%EA%B3%BC-%ED%95%B4%EA%B2%B0-%EB%B0%A9%EB%B2%95

- 먼저 ORA-01555가 발생할 수 있는 시나리오의 예를 타임라인을 통해 보겠습니다. UNDO 세그먼트, 일관적인 읽기 등의 용어에 대해 익숙치 않은 경우에도 우선 다음 내용을 읽어보시길 바랍니다.

- 튜닝 할 수 있음

- 주의 loop 안에 널고 commit을 찍을 경우 블락 카피가 훨씬 더 많이 생길 수 있음


- disk 

- [Oracle] SQL 실행 계획 확인 방법 2편 (SQL Trace, TKPROF)
- https://estenpark.tistory.com/303



- SCN (시스템 커밋 넘버)

- Consistent Mode
- https://myjamong.tistory.com/195



- Overall Total 분석대상 (전체적인 문제점 분석)
- OVERALL TOTALS 값을 통해 진단 대상시스템의 SQL 성능 및 활용 정도를 추정 할 수 있음

1 SQL당 수행시간
- total cpu / execute count (2 % 1) [0.01초 이내]
- total elapsed / execute count (3 % 1) [0.03초 이내]

- Parse Overhead

- Execute vs Fetch
- decrare 부분을 많이 사용하였는 지 loop 부분에서 많이작업햇는지 확인 옵티마이징 전략분석

- 스캔 방식은 멀티블락방식

- 인덱스 스캔방식은 원블락

- 스캔방식은 8블락

- 랜덤일 경우 낫개의 블락을 수행

- Db 특성상 커밋이 안된 상황에도 디스크에 들어가게끔 작업해서 저장하는 방식 만약 취소될 시에는 rollback 처리



- parse -> Execute -> fetch
  (구문분석)  (실행)  

-https://notemusic.tistory.com/17


- 악성 SQL의 분포 추정
 - TKPROF utility 에 의해 처음 생성된 파일의 크기와 Full scan 및 cpu time 에 의해 추출된결과파일의 크기를 비교하여 전체 SQL 대비 악성 SQL의 비율을 추정할 수 있음
 - http://www.gurubee.net/lecture/1842
 

- RDB는 포크레인에 비유 (모아서 한번에 처리방식)

- Index 최적화
 - 

 - 테이블이 사용하고있는 ACCESS PATH 유형 분석부터 해야함


- Access Path 
- 데이터베이스에 저장된 데이터에 접근(액세스)하는 방법 또는 경로
-


-FULL TABLE SCAN
-ROWID SCAN
-INDEX SCAN
-CLUSTER SCAN
-공단(공간의 모임) 을 만들어서 스캔하는 방식 
 공단(쉽게 말해서 공장 부지)
 
-EXTENT 증설 시키는 개념

- 멀티 테이블 클러스터링
- 클러스터링 테이블
- https://ybhoon.tistory.com/entry/13-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%A7%81-%ED%85%8C%EC%9D%B4%EB%B8%94
-저장을 할때는 COST 가 발생하지만 ACCESS 할때는 효율적임

-SCAN 방식 (RANDOM 방식이 아님) 이 효율적

-클러스트 인덱스는 한클러스 당 인덱스 하나

- 


-HASH SCAN
- h(x) = 위치 값으로 같은 해쉬값을 갖는 모두 로우가 동일한 데이터 블록에 저장

- 인덱스가 없음 (같은 종목 모임)


- 인덱스의 구조가 잘못 설계되있는 경우
- 비효율적으로 많은 인덱스는 옵티마이저를 헷깔리게만들고 데이터 액세스량이 증설된다.


- 전략적으로 여당, 야당조건을 잘 판단하여서 적용 주류,비주류 분류 해서 사용


- Concatenation plan 실행계획 분기
- https://mentor75.tistory.com/entry/%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D-SQL-%EC%97%B0%EC%82%B0CONCATENATION

- 몇개를 잘라서 하나의 분기로 만든다


- Range scan : Skip scan 
-http://wiki.gurubee.net/display/DBSTUDY/Index+Skip+Scan



- 스타 조인 형제들끼리 기준테이블을 이용하여 조인하는 방식
- http://wiki.gurubee.net/pages/viewpage.action?pageId=14221327

- 한 테이블을 기준으로 다른 테이블들을 조인하는 방식

- skip scan에서 cardinality 영향도로

- /*+ index_ss(x index4) */ 방식을 이용하여서 스킵스캔 방식 이용가능



- 인덱스 설계 기준 
- 1. 항상 사용하는가?

2. 항상 '='로 사용하는가?

3. 어느 것이 분포도가 더 좋은가?
-Cardinality 종류

4. 자주 정렬되는 순서는?
- 부분범위 처리

5. 같이 자주 사용되고 있는가?
- 후보선수 선정


- 인덱스 최적화 절차
1. 프로그램에서 사용하고있는 모든 SQL 추출

2. 테이블 단위로 추출된 SQL을 근거로 Access 유형
-(패턴 Ex) like, and 등 중에서 영향을 줄수 있는 부분만 정리) 분석

3.  모든 Accress 유형을 만족할 수 있는 인덱스 생성
- 유형별 커멘트를 달아서 해결책 마련

4. 수립된 인덱스를 근거로 옵티마이저의 실행 계획 확인
- 유형별 확인

5. 수립된 인덱스를 잘못 사용하는 SQL문에 대한 실행계획제어를 위해 SQL 변경
- 선천성 불구를 만들지말라!


- 원리를 알고 이해하기


- 배치 프로세싱 형태

- 배치 처리를 위한 가이드

- 절차형 SQL의 개선 : 가능하면  CURSOR SQL에서 모든 처리가 이루어지도록
- 미,적분(?) sql
- 초대량의 테이블을 처리할 때는 partitioning 
- 파티션 처리 필수
- 파티션을 뭘로 짜를지 중요
- partition pruning 파티션 프루닝
- https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=tpgpfkwkem0&logNo=220616572811

- parallel 사용검토 (SQL parallel, Program Parallel)

- temp table 사용

- 병렬처리 실행계획 학습해보기

- 다중처리(Array Processing)나 Merge 문 사용

- 대량의 DML 처리를 위한 동적 환경 제어 : 처리 대상 건수 파악이 중요


-병렬,시리얼(?) 실행계획

- 대량 데이터는 항상 hash join!

- merge 를 이용해서 배치 프로그램 짜는 경우가 좋음

- group filter







그외 참고사이트


-이화식선생님 온라인 스터디 자료
http://bysql.net/d200907/56