SQL처리전 최적화과정
1) SQL 파싱 : 파싱트리 생성, 문법적오류/Syntax 체크(사용할수 없는 키워드, 순서가 바르지않거나 누락된 키워드확인), 의미상오류/Semantic체크(존재하지않는 테이블컬럼, 오브젝트권한)
2) SQL 최적화 : 옵티마이저는 미리 수집한 시스템/오브젝트 통계정보 바탕 다양한 실행경로 생성/비교 후 가장 효율적인 하나 선택
3) 로우소스 생성 : SQL 옵티마이저가 선택한 실행경로를 실제 실행가능한 코드 또는 프로시저 형태로 포매팅, Row-Source Generator가 그 역할을 맡는다.
SELECT
오라클클라이언트 SELECT 요청 > Server Process는 Shared Pool에 Library Cache를 확인해 실행계획이 있으면 Soft파싱 > 없으면 옵티마이저가 실행계획을 만들며 Hard파싱 >
ServerProcess는 DB Buffer Cache를 읽어 Select절에 해당하는 데이터가 있으면 DB Buffer Cache에서 바로 읽고 해당결과를 오라클클라이언트에 보여주고 >
없으면 ServerProcess가 데이터 파일로부터 읽어와 DB Buffer Cache에 올리고 오라클클라이언트에 보여줌.
INSERT
오라클클라이언트가 INSERT요청 > Server Process가 DB Buffer Cache에 데이터를 담는다. > 데이터담을때 LGWR은 그 변경내용을 Redo Log Buffer에 담게된다
(이때 시스템이 죽는경우 DWBn이 Data file을 내리기전이라도 commit이 되어있으면 리두로그파일을 이용해 복구가능/ 만약 commit이 안되어있으면 Redo Log Buffer의 undo를 이용해 복구) >
그리고 Redo Log Buffer에 담긴내용은 commit하거나 특정시간마다 LGWR이 Redo Log file에 내린다 > 이후 DBWn은 바로 Data File에 내리지 않고 체크포인트 발생시 DB Buffer Cache상에 모든 Dirty버퍼를 Data File에 저장한다.
버퍼캐시
- Free 버퍼 : 사용되지 않았거나 변경된 내용이 Disk에 내려쓰기가 완료되어 언제든지 덮어써도 무방한 버퍼블록
- Dirty 버퍼 : 버퍼에 캐시된 이후 변경이 발생했지만, 아직 디스크에 기록되지 않아 데이터 파일 블록과 동기화가 필요한 버퍼 블록
- Pinned 버퍼 : 읽기 또는 쓰기 작업을 위해 현재 액세스 되고 있는 버퍼
LGWR이 Redo log file로 기록하는 경우
- 3초마다 DBWR프로세스로부터 신호받을때
- 로그버퍼의 1/3이 차거나 기록된 Redo 레코드량이 1MB가 넘을때
- 사용자가 커밋 또는 롤백 명령을 날릴때.
옵티마이저 힌트 : 옵티마이저 힌트는 명령어 이므로 특별한 이유가 없는 한 그대로 실행됨, 힌트무시되는 경우는 아래와같음.
1. 문법적으로 맞지 않는 힌트
2. 잘못된 참조사용 : 없는 테이블이나 별칭(Alias)사용, 없는 인덱스명을 지정한경우
3. 논리적으로 불가능한 액세스경로
조인절에 등치(=) 조건이 하나도없는데 해시조건유도
테이블전체건수 count하는 쿼리에 null을 허용하는 단일컬럼으로 생성한 인덱스를 사용하도록 힌트지정
4. 의미적으로 맞지 않게 힌트 기술 : 서브쿼리에 unnest와 push_subq를 같이 기술하는등
5. 옵티마이저에 의해 내부적으로 쿼리가 변환된경우
6. 버그옵티마이저 힌트 유의점
1. 힌트안에 인자나열시 콤마를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면안된다.
2. 테이블을 지정할 때 스키마명까지 명시하면 안된다.
3. From절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다.- 옵티마이저 힌트 종류
