<도서정리> 친절한 sql 튜닝 1
1. SQL 처리 과정과 I/O 옵티마이저 – SQL 최적화 기능, 효율적인 액세스 경로 선택, 옵티마이저 힌트, 실행계획 SGA(시스템 글로벌 애리아) – 서버 프로세스와 백그라운드 프로세스가 공통으로 사용하는 메모리 (DB 버퍼캐시, 리두 로그 버퍼, 라이브러리 캐시 등이 포함되어 있음) Ø 라이브러리 캐시 (코드 캐시; sql 함수 프로시저 실행계획 등) 소프트 파싱 – 캐시에서 해결되는 파싱; 라이브러리 캐시의 특장점 하드 파싱 - 하드디스크까지 가서 최적화와 로우소스까지 생성해야 하는 파싱 Ø DB 버퍼캐시 (데이터 캐시; 디스크에서 읽은 데이터 블록) I/O call 줄임 I/O 튜닝 프로세스는 준비-실행-대기 상태를 반복하기 때문에 디스크 I/O call이 발생하면 그 동안 프로세스는 대기 상태가 됨. 즉, 디스크 I/O가 SQL 성능을 좌지우지함 블록 : DBMS가 데이터를 읽고 쓰는 단위 (레코드 하나가 아닌 레코드 속한 블록을 통째로 읽음) 시퀀셜 액세스 : 순차적 액세스, full table scan, 다중 블록 I/O 랜덤 액세스 : 랜덤으로 한 블록씩 접근; 인덱스는 single block I/O Sql 튜닝을 통해 읽어들이는 총 블록 개수를 감소시켜야 함 실제 성능을 향상시키려면 건들일 수 없는 물리적 I/O가 아닌 통제 가능한 논리적 I/O를 감소시켜야 함 Table Full Scan (대량 데이터 호출) VS Index Range Scan (소량 데이터 호출; ROWID 제공) 2. 인덱스 1. 인덱스 스캔 효율화 튜닝 2. 랜덤 액세스 최소화 튜닝 ☆ 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야한다!! 시작점을 찾아야 일부만을 스캔하기 때문이다. 인덱스는 데이터가 “정렬”되어 있기 때문에 range scan 할 수 있다. 즉, 소트 연산 생략 효과가 있다. - 인덱스 구성대로 order by col1, col2하면 소트 연산 생략되지만 order by col1 || col2하면 생략 안됨 - 테이블 alias 사용 시에는 order by alias.col로 해야함 - (자동)형변환도 가공으로 취급됨 - null값도 처리해주기 인덱스 full scan도 인덱스 컬럼 순으로 정렬됨 >> 이것은 소트 연산 생략 의도로 사용하기도 함 힌트 first_rows를 사용하면 거의 모든 범위의 테이블 액세스가 일어나 성능 면으로 좋지 않지만부분범위 처리 시에는 소트 연산을 생략함으로써 일부를 빠르게 출력할 수 있음 (table full scan, index range scan 이외에도 index unique scan, index skip scan, index fast full scan, index range scan descending 있음) 3. 인덱스 튜닝 Rowid는 논리적 주소이며 포인터가 아니다. 버퍼 캐시에서 DBA(데이터파일번호+블록번호)를 통해 테이블 블록을 찾는 구조이다. (고비용 구조) 인덱스 클러스터링 팩터 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도이것이 안 좋은 인덱스를 사용하면 그만큼 블록 I/O가 발생 인덱스 손익분기점 index range scan에 의한 테이블 액세스가 table full scan보다 느려지는 지점이것이 CF클러스터링 팩터와 관련이 있음 테이블 파티셔닝(ex) date 기준) -> full scan을 빠르게 처리할 수 있음 인덱스 컬럼 추가 인덱스에 컬럼을 추가하는 것만으로도 성능 향상.하지만 추가해야할 컬럼은 많아지고, 인덱스가 많을수록 정렬 때문에 성능에 안 좋음 Include 인덱스 컬럼에 추가하는 것이 아닌 index 생성 시 끝에 include(column)하면 됨그러면 기존 방식으로 추가한 컬럼은 수직적 탐색에 사용하고include로 추가한 컬럼은 리프 블록에만 저장하여 수평적 탐색에 필터 조건으로 사용 가능 클러스터형 인덱스 테이블을 인덱스 구조로 형성인덱스 리프 블록이 곧 데이터 블록Create table index_ (col1 number, col2 varchar(10),Constraint index_pk primary key(a) )Organization index; 클러스터 테이블 같은 공간에 저장해 둘 뿐, 위와 같이 정렬하지 않음랜덤 액세스가 적고, 클러스터에 도달하면 시퀀셜 방식으로 스캔 - 인덱스 클러스터 Create cluster c_dept ( eptno number(2) ) index;Create index c_dept on cluster c_dept;Create table dept ( deptno number(2) not null, dname varchar2(14) not null, loc varchar2(13) )Cluster c_dept( deptno ); - 해시 클러스터 Create cluster c_dept ( eptno number(2) ) hashkeys 4;Create table dept ( ... ); 부분범위 처리 활용 아무리 데이터가 많아도 빠른 응답속도 큰 사이즈로 데이터 호출하여 fatch call 최소화 – 인덱스 스캔 효율화 액세스 조건(스캔 범위 결정)과 필터 조건(테이블 액세스 결정) 선행 컬럼들이 모두 = 조건이면 조건이 모두 모여있어 인덱스 스캔 범위가 됨 In-List 유용 > In list iterator : in 개수만큼 index range scan 반복함 good but In-list는 원래 '필터' 조건. 본질적으로는 = 조건과 다름 데이터가 대용량으로 굉장히 많아야 = 조건으로 유용하다 힌트 NUM_INDEX_KEYS(a 고객별가입상품_X1 1) 사용 : 인덱스의 첫 번째 컬럼(세 번째 인자에 명시)까지 인덱스 액세스 조건으로 사용 소트 연산을 생략하기 위해 In-list가 액세스 조건이 아닌 필터 조건으로 풀려야할 때가 있음 -> 이 경우에는 인덱스의 맨 끝으로 위치시키면 됨! Between 조건을 in-list로 전환할 때는 데이터가 멀리 떨어져 있거나 데이터가 적어야 유용 Index skip scan을 사용하면(힌트 INDEX_SS) 선두컬럼을 in이 아닌 between에 사용해도 블록만을 스캔하고 효율적으로 스캔Like보다 Between이 좋음. 하지만 범위검색 조건도 남용하면 안 좋음OR 조건은 인덱스가 아닌 컬럼에 사용하면 됨인덱스 선두 컬럼에 between, like 사용 금물Union all 사용 – 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용함 good Null값 해결을 위해 NVL(IFNULL), DECODE 사용 (but OR expansion 한 번만 가능) 조건절의 컬럼이 모두 인덱스에 있으면 함수도 모두 액세스 조건으로 한 번만 수행됨 good - 인덱스 구성 조건 : 데이터량이 많으면 적절한 인덱스 구성이 꼭 필요 1. 선두 컬럼을 조건절에 반드시 사용하고, 2. 조건절에 많이 사용하고 3. = 조건으로 자주 조회하는 컬럼 4. 수행 빈도도 큰 영향 ex) 일자/일시 조건을 선두에 두고, 자주 사용하는 필터 조건을 뒤쪽에 추가 소트연산 생략하기 위한 인덱스 구성 = 연산자로 사용한 조건절 컬럼 ORDER BY 절에 기술한 컬럼이어야 함 + 다른 조건절일 경우 데이터 분포 고려해서 인덱스에 추가하기 인덱스 생성 시 컬럼의 선택도가 충분히 낮은지도 살펴봐야함 <> 변별력 높은 컬럼인지 확인! 카디널리티가 높은 컬럼(변별력이 낮음)은 테이블 액세스가 많이 발생한다 인덱스 생성 시에는 선택도가 중요하고 컬럼 간 순서를 결정할 때는 필수 조건 여부, 연산자 형태가 더 중요하다