SQL 튜닝 (1) - 실행계획

실행계획

SQL 튜닝이란?	내가 작성한 SQL의 내부로직을 알기, 쓸데없는 작업 없애나가기. (병목현상 제거하기) 인덱스 변경/조인 변경/액세스 변경 /오브젝트, 처리 프로세스 변경
SQL의 병목현상?	Response Time은 사용자가 기다리는 시간이다. 만약에 SQL 기능 수행이 2초만 걸렸지만 wait time 1초가 생겨 사용자는 응답을 3초 후 받을 수 있다.
서버프로세스가 하는일	parse → bind → execute → fetch
1.Parse	syntax check 를 수행 테이블명, 컬럼명을 확인하고 권한, 보안 검사가 끝나면 parse tree를 생성한다.(컴파일 코드를 생성한다) 어느 방법으로 수행해야 빠른지 (최적화 수행) 1차적으로 튜닝을 해준다. → 즉, execution plan을 결정한다. 서버프로세스의 optimizer(최적화기)가 1차적으로 튜닝을 해준다는 점. DB에서 1차적으로 튜닝을 해준다는 점을 기억하면 된다. 컴파일 된 코드들은 soft parsing을 하기 위해서 shared pool에 저장이 된다. ㄴsoft parse란? 동일한 sql을 수행한 과거가 있는 경우 재사용한다. shared pool의 의미? ㄴ똑같은 문장을 여러번 컴파일 안해도 되기 때문에 절약할 수 있다. 결론: 쿼리가 대소문자, 띄어쓰기 그리고 라인수(엔터까지) 포함해서 쿼리가 같아야 아스키 코드가 비교돼서 원래 있던 쿼리로 인식되어서 soft parse가 되어서 parsing 시간을 이득볼 수 있다. SGA란? 서버 RAM이다. 모든 사용자들이 공유하는 공간임. 서버프로세스처럼 유저당 1개가 아니다. Q. SGA 메모리 꽉차면 어떡하죠? sga라는 공간은 메모리가 꽉차면, LRU라는 자동으로 선입선출로 해주는 친구가 있다. Q. 라인 수 정도는 옵티마이징 해주는줄 알았는데, 어떤 디비인가요? mysql? 모든 디비에 달린 서버프로세서 안의 기능인 optimizer가 무조건 1차적으로 튜닝을 해주는거라, 예를들어 이전 쿼리의아스키 코드의 합이 2300 이라고 하면 새로 수행되는 쿼리의 아스키 코드합이 2300이면 그때 shared poold에 해당 값 존재하는지 비교 하고 soft parsing을 하는듯 하다.
2.bind	변수 값을 지정하는 과정. bind 성능 개선은 우리가 신경 쓸 필요가 없다. 1,2 parse, bind를 수행하면 sga의 shared pool 안에 parse tree, execution plan이 적재된 상태이다. ex) select ename from emp where empno=1; → 서버 프로세스에 쿼리가 전달됨.
3. execute (실행)	실행계획 (Execution Plan) 적용. 실행계획에 나나온 방법대로 SQL을 처리한다. disk DB에서 필요한 자료가 있는 Block을 DB cache로 복사한다. ( 특징: 실행할때 필요한 데이터를 블록 단위로 메모리에 올려서 작업한다. ) 그래서 튜닝 처리량 확인할때, block갯수를 확인한다. 튜닝 처리량 확인? CR, Buffers 확인 방법은 trace파일에 cr이라는 값이 있음. cr값 의미가 몇개를 뒤져서 몇개를 메모리로 갖다 두었는지 나온다. buffers라는 값도 있다. (몇 칸의 데이터를 뒤져봤습니다.) buffers값이 작으면 작업량이 작았다는 소리.(속도가 좋았다.) 칸단위로 비교한다. - db buffer cache가 모든 작업을 execute하기 위해서 사용되는 공간이다.
4. fetch (인출)	사용자가 원하는 자료만 DB Buffer Cache에서 복사하여 보여주는 작업. 즉 Query작업시에만 수행된다. (select문) 배열크기 단위로 Row를 처리한다. 처리하는 Row가 포함된 Data Block은 Database Buffer Cache에 로드된다. Private SQL Area(in PGA) ActiveSet(Record Set)을 생성한다. fetch에서는 몇개씩 운반해 주나요? 운반단위가 중요함. ex. select 결과가 몇만개 등 엄청 많을때 얼마나 옮길지 운반단위가 중요한데, array size로 조정할 수 있다. array size가 10이면, 데이터가 10개 쌓이면 사용자에게 보내주자. 라고 판단한다. 결과데이터가 엄청 많을때 array size가 10이면 너무 많은 왕복을 수행한다. 사용자 보여주고 또 데이터 찾고.. 또 array size가 크다고 좋은것은 아님. 뷰가 10개 보기인데 100개를 전달해봤자, 90개는 날라간다. array size는 한 페이지에 보여주는 크기 만큼 페이징 사이즈에 맞게끔 하는것이 좋다. 화면이나 프로그램 종류에 따라서 전략적으로 선택할 것.
db buffer cache	모든 작업을 execute하기 위해서 사용되는 공간이다. disk에서 다이렉트로 보는게 아니라 거기서 읽어서 buffer cache 메모리에 적재된 데이터를 읽는 다는 점.
PGA란? (서버프로세스의 PGA MEMORY)	구성 : session info: ex.요청한 사람이 A인것을 기억하고 A에게 전달. sort area : order by 수행시 여기서 함. hash area order by 와 같은 정렬은 개인에게만 적용된다. 공용 공간인 SGA에서 수행되는것이 아니다. 소팅작업할때 여기 pga메모리를 사용한다.
라이브러리 캐시	라이브러리 캐시 데이터 딕셔너리 캐시 = 테이블 명, 컬럼 명 제대로 되어있는지확인 할때 데이터 덱셔너리에 해당 테이블, 컬럼 존재 여부를 요약된 정보를 여기서 확인한다.
redo log	redo log ← 변경이 일어날 때만 쓰인 ㅡㄴ 곳. Update문을 돌리면 알 수 있다.
logical read vs physical read	logical read vs physical read ex) select ename from emp where empno=1; 디스크 블럭을 한번만 메모리에 올리고 이걸로 체크하기 때문에, empno가 2,3 번 쿼리면 메모리에서 수행한다. 만약 메모리에 없으면 physical read ( data file에서 읽기 때문에) 만약 메모리에 있으면 logical read 근데!! 튜닝 목표는 logical read 가 아님.. 튜닝에서 중요하는건 몇개를 뒤져봤지 임 왜냐면 logical read는 랜덤인게 예를들어서 sga 메모리가 1기가인데 data file가 3TB라고 했을때 조회했던 비슷한 데이터가 항상 메모리에 남아 있을리가 없다 . 운좋으면 logical read 하는거임 cr 작업량 절대값이 작은게 튜닝이지 logical/physical이 중요한게 아님.
SGA	-Shared Pool -Database Buffer Cache : Data Block을 저장하고(캐싱하고) 변경내용을 기록 -Redo Log Buffer : Redo Entry기록
PGA	(Program Global Area) -Private Area = cursor: SQL문 처리시 사용되는 메모리 공간 -Session Memory : Logon정보, Session 관련정보 -SQL Work Area: Sort, Hash-Join, Bitmap 생성 및 병합 작업 사용되는 메모리 공간
select문 처리과정	1.Buffer Cache에서 해당 Row를 찾는다. 2.없으면 Datafile에서 읽은 후, Buffer Cache에 Load한다. 3.Buffer Cache에서 읽는다.
DML문 처리과정	1. Buffer Cache 에서 해당 Row를 찾는다.없으면 Datafile에서 필요한 Block을 Buffer Cache로, Undo Block을 할당 받아서Buffer Cache로 복사한다. (Cache 이후, Row Lock 설정) 2. Log Buffer에 Old Image, New Image를 기록한다. 3. Undo Segmen의 Undo Block에 Data block 의 Old Image를 기록한다. 4. Buffer Cache의 Data Block에 New Image를 업데이트 한다.

 

update emp
set ename ='Tom'
where empno = 3;

해당 쿼리를 돌리면

shared pool에 select, update쿼리가 적재된 상황이다.

1.변경할 자료 block을 db cache로 옮김

2. undo block을 DB cache로 복사 ( 롤백을 하기 위한 용도로 원래 ex. 값이 tom이었다. )

3. 변경할 row에 lock을 건다.

4. redo log에 작업 내용을 쓴다. ( 서버 뻑났을 때 복구 용도 ex. 00-00-00에 어디 값 업데이트 됐다. )

5. undo block에 변경 전 값을 복사한다. ( db cache에 undo block이 차지하는 영역에 업데이트한다.)

6. db block값 수정한다.

select문에서는 lock이 걸리지 않고, update와 같은 DML에서만 lock이 걸린다는점. 컬럼 별이 아니고 줄단위로 lock 걸음!

지금 튜닝에서 중요한 목적은 execute 속도 향상 !! 결정된 처리방법 = execution plan

 

 

[관계 예시] ex.

SQL : 목적지

서버프로세스 : 네비게이션