SQL Tuning 10g 정리

Pga_ Aggregate_target 용량이 넉넉히 잡혀 있어야한다.
 
1-14
유저 서버연결 = connection
서버 와 데이터 베이스 연결 = session
 
1-16
Execute 에서 데이터 검색
옵티마이저 실행계획을 dynamic 시 execute 에서 잡고 아닐시 컴파일단계에서 parse 에서 잡는다. (그림 참조)
 
1-17
하드 파싱과 소프트 파싱 용어
하드 파싱 은 맨 처음 실행한사람의 경우에 새로운 실행 계획 작성등등
 
소트 파싱 메모리에 올라간 경우 invaliation 테이블의 변경사항으로 파싱을 다시 한다. 메모리에 있는 pcode 가 다시 한번 파싱이 발생 하는 것.
예) 유저가 틀릴 때 두 유저가 똑 같은 테이블이 있어 똑같은 셀렉트 실행시 문장이 같아 커서 가 공유되는데 스키마가 다르므로 공유 안됨. 문장에대한 재파싱으로 새로운 커서 생성.
 
2.바인드변수를 where 절에 := 썻을 때 달라짐.
 
1-20
1.       patch는 셀렉트 단계에만 실행
2.       Insert update delete 는 execute 까지만 됨 fetch 가 실행되지 않는다.
 
37 페이지.
옵티마이져 : 실행계획을 세우는 프로세스.
Expre condition 은 where 절 검색 조건부분의 컬럼들을 체크하고 검색 조건들 체크하고 해서 가장 최적의 실행계획 파악
1.       테이블에대한 정보와 where 절의 컬럼에 인덱스 걸런것여부 체크 시피유 타임 , 디스크 어레이 메모리 상태 같은 시스템 통계정보를 같이 옵티마이저가 통계정보 파악후 실행 계획만듬
2.       데이터 어떻게 억세스 : 풀테이블스켄 , 인덱스 스켄으로 렌덤하게 대이터 검색 , 클러스터나 다른 뷰를 통해 억세스 방법을 다르게 할수 있음
3.       조인 방법 : 3가지 mandatory lested , sort merge ,hash / 조인 옵셔널 anti , semi 조인 / 방법에 따라 검색 방법이 틀려진다.
4.       비용계산 위의 여러 가지 정보를 가지고 비용 게산을 한다. 계발자는 가장 최고의 실행 계획 할수 있게 도와줘야함, 통계정보를 잘 만들 어주거나 데이터 객 체에 대한 통계정보를 만들어주거나, sql 문장의 문법 자체를 수정 예) 인덱스 안썼음 써준다던지 웨어절에 인덱스 써주던지. 풀테이블 스켄을 강제로 하게 한다던지 해줌. 80~90 % 는 옵티마이저가 잘하게 통계정보 만들어주는게 가장중요.
 
38 페이지
퍼포먼스 이슈 = 네트웍 문제 조취
풀링 디비커넥션 만들어주고 연결 요청시 해줌
미들 웨어 = 웹서버 (오라클은 웹 로직을 사용하고 있음)
Sql 문장의 문법 자체 잘못
프로세시스 : 외부에서 들어오는 서버 프로세스들의 개수를 제한한다. 잘못 들어오면 퍼포먼스에 문제발생 웨이팅발생
i/o 이슈 : 디스크에서 던지 메모리 에 올라갈 때 사이즈 문제
풀테이블 스캔 오래 할시 : 빠르게 하기 위해 db_file_multiblock_read_count 를 사용 한다. 블록 값을 설정하고 한번 i/o 발생(디스크-> 메모리)시 50k 잡을시 커져서 크게 올라감. 이런 파라메터이용시 풀테이블 스켄 사용.
디스크에서 sort 시 문제 : pga 공간 부족시 temporary 테이블 스페이스 사용 하게 되어서 문제가 된다. 좋은 기능을 하지만 많이 사용 안하는게 좋다 물리적인 i/o 를 하기 때문에
-         Select * from emp 던지면 서버프로세스는 내부적으로 internal 한 sql 문장을 만들어 실행한다. Emp 라는 테이블 시멘틱스 첵크해야하는데 이것을 recursive sql 이라고 한다.
Sql 문장이 복잡할 때 테이블 100개면 재귀 문장이 많이 생겨 퍼포먼스에 문장이 생김. Sql 문장을 단순하게 하는 것이 중요하다.
-         스키마 오류는 모델링 이 잘못된 것. 옵티마이저 문제는 오라클 문제이다
 

2장 튜닝 방법론
43 페이지
퍼포먼스 야기하는 문제들
1.       Cpu 안좋을때
2.        장치 문제시
3.       데이터 커뮤니 케이션 네트워크
4.       많은 데이터를 처리할시
5.       경합이 발생할시 한사람이 많은 리소스 사용시, 락 때문에 발생 x 락 xx 락등등 해서 락이 걸리는데 예) 9i 이전 까지는 마스터 테이블 디테일 테이블에서 마스터에서 딜리트 실행시 포링키 체크해서 테이블 락이 걸려 테이블의 데이터 처리 못햇는데 10g 는 테이블 락이안걸리고 레코드 락이 걸려 퍼포먼스적으로는 낳아짐
 
44 페이지
퍼포먼스 관리하기위한요소
스키마 ( 객체들) 의 관리가 필요
디자인 이 잘되야 하고 erd 및 정규화 같은게 잘되어 있어야 함. 1차 정규 2차 정규 해서 검증 끝내야함
인덱스 도 중요 : 잘못 사용시 btree 인덱스(일반적) biitmap 인덱스. Reverse key, 펑션베이스 인덱스를 용도에 맞게 사용
-Sql 에서는 문장 관리를 잘해야함
-Procedural 은 if loop 같은 pl/sql , 자바는 자바코드 에 주의
 
User expectation : 사용자들이 기대하는수준 insert 시 응답이 어느정도 나와야한다는 것을 관리 해주어야 한다. 시스템 데이터 이정도면 최대 이정도라는 것을 협상
 
46페이지
튜닝의 목표
1.       응답시간을 줄이는 것 같은시간의 데이터가 작아야함 , oltp 성 작업시 중요 , 예) 한건 씩 레코드 처리를 하는데 바로 바로 응답해야한다. / 줄이기 위해 index 를 사용 실행계획을 옵티마이저가 만들려고 함 / 1만건에서 1,2 건 처리해서 응답시간 중요.
2.       , 2. 시간당 처리량을 최대화 해야한다. 같은 시간안에 처리되는 데이터 량을 최대화 해야한다. 월마다 예) 급여같은 것 배치 작업을 통계작업 을 만들어 주는 것. Olab (online lab 분석작업) / 줄이기 위해 단위시간당 처리량 크게 하는 풀테이블 스켄을 주로 하게 된다. 디비 파일 멀티블럭 사용함. / parallerlize 는 2개 3개 의 slave 프로세스를 띄워서 동시에 조각을 내서 읽어들인다. (풀테이블 스켄시만 사용.
l       풀테이블 스켄할것을 인덱스 스켄시 row id 찾고 테이블 가고 또 row id 찾고 테이블 찾기 위해 계속 돌기 때문에 테이블 전체읽는거 보다 느려지게 된다.
 
48 페이지
Sql 튜닝은 원인 파악이 중요하고 원인에 대해 분석을 해야하는데
매뉴얼 방식과 오토메틱 방식을 사용한다.
메누얼 방식은 실제 자기가 조회를 해서 실행계획도 바꾸고 하는것임
오토방식을 자주 사용함
 
적용시
매뉴얼과 오토방식
 
변경은 sql 변경 , 인덱스를 만들거나 지우거나 해서 만들어줌
 
50페이지
매뉴얼 튜닝은 em 을 사용 안하고 하는 방식
 
51페이지
Sql 문장실행시 sql 문장 정보(코드)과 실행 계획도 v$sqltext 에 들어감.
Sql 문장이사용한느 테이블, 인덱스(유니크/논유니크)인지를 확인
통계정보 : 10g는 자동으로 수집이됨 자동수집위해 job 을 걸어줌. 데이터의 량 , 분포도 , 테이블 객체관련 내용 들어감. Selectivity 가좋은경우는 유니크한경우 좋고 9개가 중복된 데이터이면 선택도가 안좋음 reponse 타임 중요시 선택도가 안좋음 옵티마이저가 사용 안할려고함. 자동 및 수동 으로 설정 가능 자동은 저녁 시간에 설정 해서 job 으로 옵티마이저 가 프로그램 실행 되어 통계정보 잡아줌 / 수동으로 낮시간에 큰변화 발생시 1건서 100만건시 수동으로 통계정보 잡음 sql 옵티마이저가 잘 실행해줌.
 
6.       view 같은것 sql 에 사용시 view 가주는 데이터가 없는데 view 에대한 select 의 테이블의 통계정보를 체크하는데 이것을 체크해야한다. 옵티마이저는 그렇게 확인후 실행 계획 수립 뷰가 여러 개 있을 때 테이블에대해 계속 체크해야함.
 
* 위의 것은 2,3,4 번은 sql 관련 통계정보 잘되있는지 확인 하는 것. 통계정보는 딕셔너리에 잡히는데 테이블양과 딕셔너리 데이터 량이 다르면 같게 맞추어 주어야한다. 정확한 레코드의 수를 딕셔너리로 잡아야 좋은 통계정보를 해주므로 잘잡아야함.
 
7.       Explain plan & tkprof 이란 : 문제가 있다고 판단한 sql 을 다시한번 실행후 옵티마이저가 어떻게 실행계획을 잡아주는지 보여주는 것 / 이것이 마음에 안들면 힌트나, 인덱스사용 잡거나 한다. / 여기서 코스트와 처리 예상 시간 나오는데 수동적으로 잡아줌.
10g 는 em 에서 설정 할수 있게 해준다.
 
 
52페이지
 
옵티마이저에서 통계정보 잡는 것 중요
-         모든 테이블에 통계정보 를 모으는것이중요
-         기존의 통계정보가 오래됬을시 refresh 필요
 
통계정보는 주기적 수집필요 :
Statistics _level = typical(default) 인설트 업데이트 딜리트시 마다 정보가 수집이 된다.
설정시 자동으로 인설트 업데이트 내용이 계속 잡힘(데이터의 양이 조정 되는 것 이므로)
User_tab_ = 어떤 테이블 인설트 업데이트 발생을 알수있음
Dbms_stats _ = 텀을 업애고 인설트 업데이트를 커밋과 같은 작업을해줌
l       10g 에서는 job 프로그램에 의해 자동으로 수집이 된다. / 자동으로 통계수집시 통계수집 업거나 오래되면 자동으로 해주는데 매뉴얼작 업 했을 때 자동으로 통계수집을 안 할수도 할필요가 없을 수 있다.
53페이지
Gather_tats_job 은 db만들 때 만들어지고 maintenace window(여기서 수정가능) 안에서 매일 밤10부터 낮 6시까지 통계정보를 잡는다. 주말에는 24시간 돈다. 즉 평일 안된 통계수집을 주말에 수집함. / 이것을 믿을수 없다. 데이터 량이 많아지면 잘 수집 안되 낮에 수동으로 할 필요 있음.
통계수집 잘안 됨 옵티마이저 문제있을수 있다.
 
54페이지
실행계획 리뷰 : reponse time 줄이기 위해 사용, 검색된 레코드의 수를 최소화 하는 것이 중요
드라이빙 테이블 = 셀렉트 테이블 2개 3개 만들시 어떤 테이블을 먼저 억세스 할것이냐 예) 조인시 dept냐 emp 중 어떤 것을 먼저냐를 옵티마이저에서 잡아줌.
 
-         드라이빙 테이블을 잘골라야한다.
-         익스큐션 플랜을 어떤테이블 조회후 그다음 테이블 조회시 많이 걸러줘야한다. Dept 에서 emp 조회시 조건 dept 10번 인 것이면 10번만 데이터 가 들어가게됨. …..
앞에서부터 필터링 필요 ( sql 튜닝의 가장중요는 최소한의 block i/o 검색 데이터가 작아야한다. 최소한의 데이터만 읽어야한다.)
-         뷰를 잘 사용해야한다.(인라인뷰) 검색 범위가 좁아져서 퍼포먼스 높아짐.
-         테이블 효과적 억세시
-         카티션 프로덕스사용금지
-         풀테이블 스켄이 response에서 별로 안좋음
 
Departments                      employees
Id                                     id        dept id
10                                     200         10
20                                     201         10
(새로운 데이터)                202         10
60                                     203         20
70                                     204         20
                                                     (새로운 데이터)
                                                     30
                                                     40
Select d.id, e.id
From departments d, employees e
Where d.id=e.dept_id
(추가) and e.dept_id=10
 
4.(추가2) dept 에 index 있을시 emp 가 dept 의 index 를 찾아갈려고 함 / 데이터량이 작을 경우 옵티마이저는 인덱스 사용하고 데이터량이 많을시 인덱스 사용 안할려고 한다.
 
1.Depart 가 드라이빙이면 10을 읽고선 emp dept 10으로 가서 같으냐 확인해서 같다면 10 에 200 출력하고 계속 검색해 10이나오면 201 , 10이면 202 나오고 20 가서 20 찾아 찍고, 찍고
 
 
2.만약 emp 는 데이터 많고 dept 는 데이터가 적을시 emp 에서 dept 찾을때 탐색 시간이 많아질수 있어 dept (마스터 테이블) 이 드라이빙이 됨 즉 서치하는 데이터가 작아야 된다.
 
3.(추가) dept 에서 emp 를 찾게 된는것이다 즉 범위가 적은거에서 큰쪽으로 찾게 됨.
 
Emp 가 드라이빙이면 200 10 을 읽어 10번 검색 해 출력 201 10 을 10과 찾아 읽고
이경우 어떤게 먼저 드라이빙 좋으냐 즉 무엇을 먼저하는게 좋으냐?
 
56페이지
ㄴsql 문장 퍼포먼스 높이기
-         Or 보다는 and 나 = 을 사용 하는것이좋다.(크다 작다는 떨어뜰리수 있다.)
-         트랜스폼( 펑션같은것 사용한는것) wher 절 사용 금지
-         Implict type conversion :  salary = 을 숫자 값을 안주고 문자값을 주면 내부적으로 컨버젼 되어 이것도 퍼포먼스 영향을 줌 wherwe 컬럼과 데이터 는 같은 것을 사용
-         Sql 문장을 복잡하게 사용하지마라. 서브 쿼리 같은 것을 떼내서 개별적 실행할수있게 사용.(pl/sql을 사용함)
-         Exists 나 in 을 서브쿼리 사용시 써라
-         힌트(사용자가주는것)를 어떤테이블 드라이빙해라 등 조심하게 사용해라.
 
58페이지
인덱스 재 생성
 
59페이지
실행계획을 저장하는 기능 : 수동으로 설정
Stored outlines 실행계획 정보는 계속 sql 문장에 의해 바뀌는데 저장해서 가져다가 쓰는 방법
Stored statistics 통계정보도 저장
Locking statistics 테이블 락을 걸어 통계정보가 다시 refresh 하지 않게 함게함
 
60 페이지
자동으로 sql tuning
-         자동 작성으로 어드바이저로 다시 구성
……..
툴들을 통해서 사용
 
62페이지
튜닝어드바이저 : sql 문장 자체에대한 신텍스 / where 절조건을 이렇게 주는 것이 좋겟다 등
Access advisor : 객체에대한
 
63
Sql 어드바이저는 addm awr, 캐쉬에있거나, 한 sql 문장을 가저와서 사용
……..
 
 
 
3장
퍼포먼슬르 위해서 디자인하는거 및 개발하는 것
 
69 페이지
Scalabiity : 확장성 / 처리 데이터량 증가로 확장성 요구됨에 따라서 시스템의 작업량(workload) 가 많아 져 퍼포먼스가 중요하게됨.
 
70 페이지
Scalability : 확장시 중요한 체크
 
스키마 디자인 잘못시 문제 : sql튜님뿐아니라 schema 튜닝이 중요함 예) 모델링 프리렌서는 확장시 모델링을 새로하는 것
트랜잭션 디자인 : 프로그램으로 해결 여러 프로그램이 하나의 프로그램에 집중되게 해야함/ locking 문제 발생할수 있기 때문에.
 
71 페이지
풀 트랜잭션의 해결책 : a프로그램-> b,  b->c 실행시 lock 이 걸리게 되어 기다리게 된다 따라 짧은트랜잭션 단위로 commit 과 롤백을 자주해라. 그래야 가용성 높아짐.
 
Oltp 작업 : 즉각적으로 응답이 들어오는 시스템, 처리 시간이 중요함
Olap 작업 : 많은 데이터량을 빠른 시간안에 처리하는 것.
 
72 페이지
-사전 튜닝 방법론
-디자인을 심플하게 해라(복잡한 sql 쓰지마라)
-데이터 모델링을 잘해라
-테이블과 인덱스에대한 통계정보를 잘만들어서 옵티마이저가 최적의 실행계획 잡게해라
-뷰를 적절히 사용해라
-sql 문장의 문법을 효율적으로 사용 해라
-커서 쉐어링 해라 어떤사람이 실행한 sql을 커서로 만들어 여러 사람이 공유하게 해라. 쉐어드 풀의 공간이 넉넉해야함
-바인드변수사용해라(권궈하지않음) where 절 := 해서 사용하는 것.
장점 : 공유할 수 있는 여지가 높아짐. 커서로 생성시 어떤값이 들어가든지 커서는 공유가 되게 할수 있는데 따로 parsing 을 안해서 좋아진다.
단점 : 실행계획도 공유한다는 것인데 바인드변수 어떤값이 들어가느냐에 따라 데이터량이 달라 실행계획도 달라져야 하는데 달라지지가않는다. 맨처음 만든 실행 계획을 사용해 단점
-pl/sql 을 쓰면 여러 sql 을 하나의 프로그램으로 처리하여 퍼포먼스가 좋아짐
-dynamic sql 은 pl/sql 사용해 excute immediate ‘’ 실행하면 프로그램 실행할 때 마다 sql 문장이 실행계획을 다시 세워 그때그때 통계정보를 참조해서 실행계획을 새로 만들므로 퍼포먼스 좋아짐.
 
73페이지
프로그램 하는데 단순화
-테이블 심플화 sql 문장을 복잡하게 사용하지마라
Sql 문장을 작성할 때 최대한 단순하게 하더라도 많아지는 경우가 있는데 그래도 단순하게 해라?;;
-sql 문장을 잘사용해야한다.
Sql 문장을 길게 쓰면 옵티마이저가 실시간적으로 옵티마이즈할 때 아마 나쁜 실행 계획 잡아줄수있거나 잡더라도 퍼포먼스적 영향을 줄수없을수 있다. / 길다면 sql 문장을 나눠 pl/sql 이요해서 연결 해버린다.
-인덱스는 필요할때만 써라 테이블의 어떤 컬럼에 인덱스 사용하는데 이컬럼이 다른 인덱스를 만들수 있는데 그러면 나중에 옵티마이저가 혼돈되거나 정확한체크가 안될수 있다.
-필요한 인포메이션만 수집해라 where 절을 잘주어 불필요한 검색을 줄여라.
 
74페이지
테이블 설계잘해라
정규화 잘해라.
 
75페이지
노말라이제이션 : 테이블을 나누는 것 목표는 데이터의 중복성을 없애는것이다.
디노말라이제이션 : 데이터의 중복성을 만드는 것 / 어플리케이션 프로그래머가 필요로하는경우.
 
퍼포먼스특징사용
머트롤 라이즈 뷰 : 실제 데이터를 가지고 있는 것
첵크는 : 어떤 데이터를 못들어오게 하는 것.
 
77페이지
클러스터 2개이상의 테이블을 하나의 테이블로 묵어주는 것
인덱스 는 프라이머리키나 유니크 같은 것을 컨스트레인트 걸 때 자동으로 만들어지고 수동으로 만드는 경우도 있다.
포링키는 자동으로 안됨 수동으로 걸어줌. Reponse 타임을 빠르게 하는데 좋음.
 
78페이지
뷰사용해라
 
79페이지
Parsing 을 최소화 하자
-pl/sql 쓰거나(프로그램을 통해 sql을 사용), 바인드변수를 사용(커서쉐어링이 잘되므로(메모리확보가중요 sga_target = shared pool))
 
80 페이지
 
82페이지
커서 쉐어링에 대한 옵션
exact
Similar : sql 문장 비슷한 것임 재사용. 실행계획 공유로 2번째 사용시 잘 맞지 않을수 있다.
Force : similar 와 비슷. 커서는 공유를 하되 explain(실행계획)는 다시 재정의 함.
 
83페이지
초기화 파라미터 정의
스토리지 옵션 중요 locally 시 extent 가 자동으로 잡힘. Dictinory 시에는 조각모음을 해야함
Sort 시 pga 를 사용하므로 ……….
테이블 이동시 row id 가바뀌므로 인덱스(row id 로만 구성) 도 다시 만들어야함

4장
 
88페이지
옵티마이저 : 실행계획을세움 (통계정보를 이용, 어떻게 억세스 조인등을 할지 실행계획 수립)
가장 적은 비용이 드는 플렌을 수립.
비용 계산법 :
 
옵티마이저가 하는 일 : sql문장을 파싱(셀렉트문장 쿼리)컴파일한다. -> sql 문장의 select 를 변환을 시킨다. 예) 뷰는테이블의 실제 대이터를 가져오는것을하는데 뷰이름을 실제 테이블로 바꾸어 셀렉트 문장을 제구성하는 것. -> 딕셔너리의 통계정보로 estimate(비용 측정 및 시간예측) 해서 가장 비용이적은 것을 실행계획수립 -> 실행계획을 만들어낸다.
 
92페이지
셀렉트문장 하나에대해 여러게의 쿼리 블록으로 만든다(서브쿼리, 뷰같은게 여러게있으면 각각에 실행계획을 따로 잡는다) 하나의 쿼리 블록에 관련해서 잡을 수 있는 실행계획을 만들어주고 비용계산하고 하나를 선택하는 것을 하나의 블록단위로 한다. 쿼리블럭에 대한 코스트와 전체 블록블 코스트를 본다.
 
93페이지
선택도
옵티마이저가 최적의 실행계획을 잡는 코스트 계산할 때 1) 첫번재가 셀렉트비티이다 (범위 0.0 ~0.1 에서 결정이된다.) 선택도의 값이 크면 코스트는 올라간다.(나쁘다는 것이다) 왜냐면. 선택도란 ? 어떤던진 값에 대한 범위(백분율로표시)이다.
테이블 억세스 할 때 100권의 데이터에서 찾을게 where 절 id 유니크한 값을 10건이면 100개가 10개 읽어와 셀렉트비티는 100분의 10이 된다.
선택도가 크면은 풀테이블 할것이고, 작으면 인덱스를 사용해서 데이터 가져올려고 할것이다.
정확하게 셀렉트가 안되는 경우는 히스토 그램을 사용하는데 히스토 그램 안하게 되면 10이란 데이터가 99권 1건만 20이라할 때 히스토 그램 사용 안함 2/1 로계산 50% distinc 한값이 10이 99 개라도 10과 20 , 2개만 있어 2/1 로 할수 있다. 50%는 잘못된 것 실데이터는 99개가 있으므로… 따라서 히스토 그램을 만든다. 100개의 데이터 있을 때 히스토그램 20주면 20으로 5개(버킷)로 짜른다 그럼 5개 나오는데 1이들어간 것 1개 이므로 5/4 로 계산을 한다. 즉 히스토 그램사용은 비대칭값 즉 중복 된값이 많이 있는경우에 세세하게 버킷이라는 것을 만들어 나누어 주어 사용하게된다.
 
# rows satisfying a condition
선택도    --------------------------------
              Total # of rows
 
 
만약에 통계정보가 없엇을 경우 옵티마이저는 다이나믹 셈플링(몇%만 읽어옴)을 한다. 일부 데이터만 가지고 수행해 부정확 할수 있다. 좋은점은 유니크한값이 많이 있을 때, 컬럼의 데이터의 분포도가 일정한경우에 좋다. 비대칭할때는 안좋다
셈플링 사이즈를 정해준다 : Optimizer_dynamic sampling (통계정보가 없을 때 유용하다)
 
94페이지
Cardinality
선택저와 다른 것은 순수하게 레코드의 수를 표현 하는 것이다.
선택도가 30%데 10건 데이터 찾으면 카디널리티는 3이라고 나온다.(실제 데이터의 량이 어떠냐를 보는것) 하지만 선택도는 다르다.
멀티 블록 의 개수보다 카디널리티가 적개 나오면 풀테이블 스캔을 한다. 한번에 읽을수 있기 때문이다.
코스트 는 카디널리티와 선택도의 부가적으로 통계정보를 만드는 것 코스트가 가장적은게 실행된다. 즉 실행계획의 결과 값으로 볼수 있다.
 
95페이지
통계정보는 딕셔너리에 저장이된다.
1.만드는 방법은 dbms_stats 를 사용한다
2.샘플링으로 가져옮
 
96 페이지
옵티마이저 버전
Optimizer_features_enable 에 나옴 db 버전과 비슷하게 사용하는 것이 좋다.
 
97페이지
옵티마이저가 최적의 실행계획 만들 때 참조되는 파라미터
Exact similar force
db_file_multiblock_read_count 가 중요 : 한번에 i/o  할때 읽어드리는 크기 (풀테이블 스켄시만 사용)
optimizer_index_caching
0~100%까지 준다. db 버퍼캐쉬에 인덱스 블록의 퍼센티지이다. 인덱스 데이터가 올라가는 공간의 크기.
 
Optimizerindex cost adj
인덱스 코스트 계산. 인덱스를 사용할 때 코스트를 높게 준다. 이값을 크게주면 인덱스를 사용할 확률, 우선순위를 높게 준다.
 
Optimizer_mode
옵티마이저에게 목표를 주는것이다. 튜닝하는데 reponse 를중요시해라 또는 처리량을 중요시해라중요 잡아주는 것.
All_rows로 주면 옵티마이저가 처리량을 중요하게 함 (풀테이블스캔으로감)(default)
First_rows 지금 어떤 셀렉트 문자하는데 첫번째로 레코드를 가리키는 것을 가장 빨리 가져올수있는 실행계획수립해라/ 여러 개의 실행계획 될수 있다.
First_rows_n 은 n= 10 이면 10개중에 가장 빨리 가져올수있는 10건에 대한 실행 계획을 세워 빠르게 가져오는 것
 
 
Pga_aggregate_target (workarea_size_policy auto로 설정 되어야 함  = pga 사이즈를 자동으로 조정하겟다. )
 
99페이지
옵티마이저 Alter session 으로 변경가능 temp 는 alter user 로 변경가능
옵티마이저 힌트는 개발자가 sql 문장에다 힌트(어떻게 처리하라고 옵티마이저 모드를 무시하기위해) 를 걸어주는 것
옵티마이저 모드는 세션단위로 설정한다.
 
100  페이지
db
session
 
101 페이지
 
102 페이지
빠른 response 위한 옵티마이저
 
Throughput : 단위시간당 처리량
 
Fast respnse = 리스폰스가 중요.
 
 
104 페이지
옵티마이저에 의해 코스트가 가장 적개 발생하는 것을 선택하는 것 예)네스티드 루프 드라이빙 어떤것하고 소티머지 하면서 어떤 것을 드라이브하고……
코스트 계산시 aceesss path 인덱스 사용하느냐 등
Join order 어떤 테이블을 먼저 수행하느냐
Join method 어떤 조인 방법을 쓸거냐.
 
 
105 페이지
Accesspaths
-풀스캔
-Roid 가지고 다이렉트하게 찾아감
-Hash 스캔(실제 데이터 레코드에 가리키는값) : 해쉬 함수를 사용해서 찾아가 , 데이터 검색시 해쉬펑션으로 해쉬값만들어져 레코드 가리켜 row id 보다 훨씬 빠르다. 사용하기 위해서는 옵티마이저가 판단한다. 일레로 풀스켄 대안으로 나온것이다라고도 함.
 
106 페이지
Join order
조인순서를 예기 하는 것
Nested loopjoin : 옵티마이저 모드를 first row 또는 n 했을때 많이 사용. / 인덱스를 이용하는 조인이다./ 예) 두개의 테이블 조인시 …… 좋은경우느 oltp 작업시 좋음 하나 두개 레코드를 빠르게 가져오므로 불려지는 곳에 인덱스를 사용하게 인덱스 검색 해 row id 가지고 테이블 가서 검색 하므로 빠르다.
안좋을 때 : 처리 데이터 범위가 넓을 때.
Sort merge join : a와 b 조인시 두개가 따로 얷세스 2개를 풀테이블 스캔으로 모두 메모리(pga)에 테이블 데이터를 올림. 올라간 다음 다 솔팅하고, 비교를 하고, 머지를 해서 데이터를 가져옮. 옵티마이저 모드가 allows 일때 쓰임. 소트 머지시 데이터 소트 해야되서 시간이 걸리는데 대체조인이 해쉬 조인이다. 병렬도도 있어야함
 
Hash join : 소트조인과 다른점은 emp 와 dept 조인시 데이터가 많은 emp 를 모두 풀테이블 스캔후 파티션으로 조각조각 나누고 조각들을 소트 에리어에다 올리는데 소트하지 않고 각 레코드 별로 해쉬 벨류를 만들어 준다. 그것을 메모리에 올려 dept 를 읽으면서 10을 찾을 때 10값이 아닌 해쉬 함수의 해쉬 벨류로 emp 데이터를 찾게 된다.
 
 
5장
112 페이지
Access pass억세스 패스 에 대한 소개
풀테이블스캔
인덱스 스켄
샘플 데이터 스캔 : 다이네믹 샘플링 통계정보가 없을 때 다이네믹 셈플링을 정해줘서 샘플링해서 실행계획 억세스 패스를 잡는 것
 
113 페이지
Estimate 계산을 해 비용이 작은 것을 잡음
하나의 sql 문장이 여러게의 sql 문장으로 각각 나눠 코스트 계산을 하고 전체에대한 코스트 계산도 한다 즉 전체도 보고 각각도 봐야한다.
 
옵티마이저 힌트 : 옵티마이저 한테 실행게획을 세우라고 개발자가 힌트를 주는것이다. 힌트가 100% 적용은 보장 못함 가끔 무시하는 경우도있다 통계정보로 코스트 계산해서 실행 계획을 세세울 때 있다.
 
통계정보가 언제 만들어졋는지 볼 때 last_analyezed in all_tables 에 있다. 너무 오래 되면은 문제 가있으므로 통계정보 다시 잡아줌. 10g 는 자동으로 밤시간에 수집됨. 낮에 크게 변화되면 수동으로 잡아줘라
 
114
풀테이블 되는경우는 인덱스가 없을 때 first_row 사용하더라도 인덱스 컬럼 사용 안할 때 풀스캔함
셀렉트되는 데이터가 많을 때 풀테이블 스캔을 한다.
작은테이블(db file multi block read count) 의 경우 수행함
 
115 페이지
116 페이지
Row id 는 row id 값을 알아야하는데 테이블 이동시 바뀔수 있어 조회 조건에 넣어서하는 것은 어렵다. 주로 index 통해 row id 사용되서 억세스 되는 경우가 많다. 모든 것이 row id 되는것이 아님 bitmap 은 bit 정보로 레코드를 사용하게 된다.
 
117
l       실행계획시에 이러한 것이 나와 알아야함
인덱스 스켄 : 전체가 아니라 10부터 40 까지 범위 조건을 주는 것이다.
유니크 인덱스 스켄 : 중복된값이 없는 것
 
Range scan 은 작은값 -> 큰값이 보통인데 반대로 큰값 -> 작은값 스캔한다.
Index_desc 힌트 = 뒤에서부터 인덱스를 사용하겟다
 
Skip scan : 결합인덱스 하나인덱스에 2개 컬럼이 들어감. 8i 는 인덱스 순서가 중요했었다. 앞쪽 컬럼을 써야 인덱스 사용됫지만, 9i 부터는 뒷값을 써도 인덱스 사용 이 되는것이다.
Index_ss = 스킵 스캐닝을 하겠다.
 
119
인덱스 풀스켄 : 인덱스만 읽어 들이는 것
where 절 select 컬럼이 인덱스 로만 사용 할때도 이것을 사용한다.
 
Fast-full index scan : ….
 
Index join(merge) = index merge 인덱스의 비슷한값을 merge 하는것, 테이블 하나에 같은데이터 찾기 위해 인덱스 값을 조인/merge 하는 것. Index_join 인덱스 조인하고 인덱스 가지고 row id 가서 데이터 강제로 찾겟다
 
Bitmap join = bitmap 컬럼끼리 and, or 하는것. 비트맵 정보를 가지고 한다.
 
121
멀티 테이블 조인하는 것
 
여러 테이블시 앞의 데이터를 가지고 다음 테이블로 가고 해서 찾아가는 것
 
From a, b, c -> where 절 2개이상 조건 나올 때
 
a->b->c
a->c->b
b->c->a
 
같이 여러 경로가나오는데 검색된는데 결과 값은 같으므로 연결되는 레코드의 수 , 데이터가 작은 것을 찾아야 한다.(excution 플랜을 가지고 확인한다) 이 순서를 정해주는 hint 가 있는데 개발자가 판단해서 준다. 만약 테이블이 데이터 연결고리가 작더라도 너무 분산 되있다면 다른테이블 의 데이터가 많더라도 모여 있는 쪽을 가지고 결정하는  경우도 있다.(코스트 값을가지고 참고함)
 
 
122
Predicate = 술어 라고 함.
싱글 로어 술어는 조건에 의해 레코드가 하나만 선택되는 것…..
…….
 
123
조인에 대한 설명
 
124
Outer 조인에 대한 설명 : 실행계획에서 중요한 것은 100% 무조건 아우터 조인에 적어준 것이 드라이빙 된다. 왜? 조인되지 못한 데이터도 읽어야 하므로 처음에 모두 읽어버리는 것이다.
full outer 조인은 : 동시에 두개가 모두 읽혀 조인이 되므로 조인되지 못한 데이터 가 나올수 있다.
해쉬조인으로 사용이 되며 인덱스로는 거의 사용이 안된다.
 
129
조인에대한 실행 계획
From 절의 4개 테이블시 24개가 생성이 된다.
 
131
Nested loop join
드라이빙 테이블이 아주 중요. 소트머지는 필요하지 않음.
 
133
찾아지는 데이터량이 적은경우 nested join 이 좋다.
테이블의 억세스 되는순서가 중요하고
강제적으로 네스티드 루프 돌릴시 (use_nl…… 사용)
 
134
해쉬 조인 : 풀테이블 스켄시에 많이 사용, sort merge 대신으로 사용됨, 키값을 가지고 사용됨, 파티션이 pga -> sort area 에 값이 올라가 pga 사이즈가 중요하다.
데이터가 큰테이블이 올라와 파티션이 나눠줘 해쉬값이 만들어짐
해쉬 테이블 : 해쉬값을 가지는 임시테이블
 
136
언제 ? 많은 데이터가 조인되는 경우(풀테이블 수준으로)
선택도가 큰경우 셀렉트 범위가 클 때 사용
 
137
Sort merge join
풀테이블 스캔을 한다. 이것들이 소트가 된다는 것이다. 데이터들이 pga 올라와도 소트되고 소트된 데이터가 머지해서 데이터를 찾는것이다.
 
139
언제 사용하나
Rang 검색을 할 때 조인되는 범위가 넓어지는데 사용.
l       해쉬 조인은 이미 키값이 소트가 되있을 때 좋은것이다
 
140
Star joins
마스터 테이블 : products customers channel 등등 = demesion table
디테일 테이블 : sales = facts tables
서로간의 조인은 안하고 마스터 테이블이 디테일하고만 조인을 하는 것 / 트랜젝션 과정중에 발생되는 데이터들 스타 조인을 함 / 디테일 데이터 1개 가지고 마스터 데이터 여러 개가 조인을 하는것
 
141
옵티마이져는 비용계산 얼마나 , 코스트 계산 등등 참고해서 결정하는 것
 
143
서브쿼리 : 테이프르 컬러과 테이블 컬럼 비교기 때문에 조인으로 할수있다.
 
Not In = antijoin
Exitst = semijoin
…….
 
 
Multipass : 솔트작업 공간 pga 각 작아서 temp 로 내려오는 횟수 많을수록 안좋다.
 
Set autotrace traceonly explain 실행 계획 정보만 보여달라
 
27페이지
 
@ Dai
Customers 테이블에 대해서 인덱스가 걸려있는 것이 있으면 모두 지우라는 내용
 
@ws_01_01.sql
1030 의 고객에대한 정보 조회 index 가 없는 상태이다. Customers_pk 때문에 인덱스 삭제가 안됬다. 게다가 cost는 안나오고 실행 계획 만 보여준다.
 
@ws_01_01a.sql
커스트 id 인덱스 살아있는 상태에서 <> 로 같지않은 것 찾음 (<> 사용시 인덱스가 사용이 안된다)
 
@ws_01_02.sql
10보다 작은 범위 검색을 하는것이다
 
@ws_01_02b.sql
넓은 범위 검색하는 것 만보다 작은 것. 범위가 넓어 풀스캔을 하는 것이 좋다는 것을 보여줌
 
@ws_01_03.sql
Between and 를 사용 하여 범위가 좁아진다. 따라서 인덱스 가 사용 된다는것ㅇ르 보여줌
 
@atoff
오토 트레이스 기능 비활성화
 
@ci
Custormers 인덱스를 만들어주는 것
 
@ws_01_04
Explain plan for 이 sql 문장에 대해 실행계획을 잡아바라.
예전에는 set autotrace explain trace on 으로 자동으로 떳는데 위 방법도 사용할수 있음
 
 
@rp
분석하는 명령어
Expain plan 잡힌 실행계획을 보여달라
위 파일에 대해 테이블 스캔이 된다. 왜? 위에 계산식이나 펑션을 줫을 시(limit*1.10 = 11000) 에 인덱스가 사용이 안된다. 인덱스 사용 위해서 앞수치를 뒤로 옮긴다.(ws_01_05)
 
@ws_01_05
Customer_pk 풀스캔 하고 ~ limit_idx 를 index range scan 하고 해쉬값을 만들어 customer_id 인덱스 데이터 하고 비교함 ?....;;
 
@ci 만들고
 
@ws_01_06
서브스트링으로 약간 변경 햇을시 index 사용이 안된다는 것
 
@rp
로 실행
 
@ws_01_07
인덱스 사용 인덱스 사용됨.
 
 
@ws_01_07b
왼쪽에 함수 사용이 안되고 오른쪽에 함수를 써서 인덱스가 사용이된다.
 
@ws_01_08
인덱스가 사용이된다. last 에는 인덱스 가 걸려있어 인덱스 사용이됨
 
@ws_01_09
커스트id 가 숫자 타입인데 ‘7%’ 따옴표주면 문자타입으로 변경하면서 데이터타입 컨버젼으로 인덱스사용 안됨
인덱스 사용위해서는 Cust_id 의 데이터 타입을 바꾸는게 좋음
 
@ws_01_10a
Update cust_email 에 null 값을 줌. 풀스캔을 한다. 널 값은 인덱스에 제외가 된다. 따라 테이블로가서 억세스를 한다.
 
@ws_01_11
옵티마이저 결정이 selectivity 에 의해 결정되어 풀테이블 스캔이 좋아 인덱스 사용 안함.
사용할 수는 있다.