이화식
- 대용량 데이터베이스솔루션
드라이빙 조건
-
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
'DataBase > Oracle' 카테고리의 다른 글
| 오라클 파티션 테이블 샘플(생성 수정 삭제) 및 주의사항 (0) | 2019.09.25 |
|---|---|
| 오라클 모니터링 세션 상태 체크 (0) | 2019.07.18 |
| 오라클 날짜 (0) | 2018.08.21 |
| 오라클 매월 말일 계산 (0) | 2018.08.21 |
| 유저 테이블 삭제 (0) | 2018.08.08 |
