🔑SQL 튜닝 - CH 1. SQL 처리과정 & I / O

진정한 튜닝 ❓  모델링 부터

🔑 SQL 튜닝 :  튜닝 자체는 기술적으로 쉬움  , BUT   업무를 몰라서 어려움.

▶ 기술적 20 %  ( 추가 쉽고 , 변경이 어려움 )
▶ 업무를 몰라서 80 %

 

---

 

Statement  /  Prepared Statement   차이점

• Query 재사용 
• 성능과 보완
• 캐시 사용 여부

 

---

 

1️⃣. SQL 파싱과 최적화

1 - 1. SQL 최적화
SQL 을 실행하기전 최적화 과정 세분화

◾ SQL 파싱 : 사용자로부터 SQL 전달을 받으면 SQL 파서(Parser)가 파싱을 진행한다 
   ◽ 파싱 트리 생성 : SQL문을 이루는 개별 구성요소를 분석해서 파싱트리 생성
   ◽ Syntax 체크 : 문법적 오류가 없는지 확인  Ex. 순서 , 누락된 키워드
   ◽ Semantic 체크 : 의미상 오류가 없는지 확인 , 존재하지 않는 테이블 또는 컬럼을 사용했는가  Ex . 말이되는지 확인

SQL Parser - SQL 문장을 분석하고 해석하는 도구 ,  프로세스를 뜻함
Syntax  - '구문'이라는 의미 언어나 코드의 형식이나 구조에 관한 규칙 
Semantic - '의미'를 뜻함 코드나 구문의 의미  / 구조적 무결성 , 문법적 올바른지 
Parsing - DB에서  SQL 파싱은 SQL 쿼리를 처리하기 위해 쿼리를 분석

 

 

◾ SQL 최적화
   ◽ 옵티마이저(Optimizer)가 그 역할을 맡는다 
   ◽ 미리 수집한 시스템 , 오브젝트 통계정보를 바탕으로 가장 효율적인 하나를 선택함
   ◽ 데이터베이스 성능을 결정하는 가장 핵심 엔진

Optimizer 는 데이터베이스 관리 시스템(DBMS) 내부의 중요한 구성 요소로서,  SQL 쿼리를 가장 효과적이고 효율적으로 실행할 수 있는 방법을 결정하는 역할을 한다

 

◾ 로우 소스 생성
SQL 옵티마이저가 선택한 경로를 실제 싱행 가능한 코드 , 프로시저 형태로 포맷팅하는 단계

프로시저??
데이터베이스에서 저장 프로시저라고 불리는데
SQL 문장들을 하나의 단위로 묶어놓은 것으로 DB에 저장되어있다.
이것을 호출하면 반복적으로 사용할수 있다고 한다

 

"프로시저 형태로 포맷한다" ???,  코드를 DB의 저장 프로시저 형식에 맞게 변환하는 것을 의미함
해당 코드를 DB에서 직접 호출하고 실행할 수 있는 형태로 만드는 작업을 뜻함

 

프로시저의 주요 특징

1. 효율성 : 한번 정의하면 여러번 호출해서 사용 가능
2. 보안 : 접근 제한 하면서 사용자가 직접 테이블에  액세스하지않고 특정 작업을 수행
3. 유지보수 : 변경이 필요할 때는 해당 프로시저만 수정

 

DELIMITER//
CREATE PROCEDURE GetSalary(IN min_salary INT)
BEGIN
    SELECT name FROM employees WHERE salary >= min_salary;
END//
DELIMITER;


CALL GetSalary(5000);

// 메서드 만들듯이 만든뒤 호출해서 씀
// 이것이 프로시저


DELIMITER//   -  클라이언트에게 명령어의 끝을 나타내는 문자열 ' // '로 변경
END//  - 저장 프로시저의 끝을 나타냄
DELIMITER; -  명령어의 끝을 나타내는 문자열을 다시 기본값인 ;로 변경

🔑 결론 프로시저 : SQL 쿼리문을 메서드 처럼 만든것

 

---

1 - 2. SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할수 있는 최적의 데이터 액세스 경로를 선택해주는
DBMS의 핵심엔진이다.

옵티마의 최적화 단계

◾ 사용자로부터 전달받은 쿼리를 수행하는데 후보가 될만한 실행계획들을 찾아낸다

◾ 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 , 시스템 통계쩡보를 이용해서 예상비용 산정

◾ 최저 비용을 나타냄

🔑 모든 경우의 수를 트리로 만듬

---

1 - 3. 실행 계획과 비용

SQL 옵티마이저는 자동차 내비게이션이랑 비슷하다
경로를 검색하고 이동경로를 미리 확인하는 기능 , 경로가 마음에 들지 않으면 사용자가 원하는 경로로 바꿀수 있다.

SQL 옵티마이저가 생성한 처리 절차를 사용자가 확인할수 있게 아래와 같이 트리 구조료 표현한것이 
실행 계획이다. 

---

1 - 4. 옵티마이저 힌트

자동차 내비게이션이 대체로 좋은 선택을 하지만 항상 최선은 아니다
데이터 또는 업무특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾아낼수 있다.

이럴때 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀수 있다.

힌트사용 방법은 주석기호에  + 를 붙이면 된다.

< 추천 힌트>

SELECT /*+ INDEX(A 고객_PK)*/
고객명 , 연락처 , 주소 , 가입일시
FROM 고객A
WHERE 고객 ID = '0000000008'

 

< 비추천 힌트>

SELECT --+ INDEX(A 고객_PK)
고객명 , 연락처 , 주소 , 가입일시
FROM 고객A
WHERE 고객 ID = '0000000008'

 

✔ 주의사항

힌트 안에 인자를 나열할때 , (콤마)를 사용할수 있지만 힌트와 힌트사이에는 사용하면 안된다

/**+ INDEX(A A_X01)  INDEX(B,B_X03)*/   -- 모두 가능
/*+ INDEX(C) , FULL(D) */ -- 첫번째 힌트만 가능



// 테이블을 지정할때는 스키마 이름 까지 명시하면 안됨
SELECT /*+ FULL(SCOTT.EMP) */  -- 무효
FROM EMP

// SCOTT 은 스키마 혹은 사용자 이름 이고 EMP는 테이블 이름 이다
//쿼리에서 테이블에 AS 줌 , AS를 옵티마이저 힌트에 사용
SELECT /*+ FULL(e) */ 
FROM SCOTT.EMP e

// 스키마 이름을 생략하고 테이블 이름만 사용
SELECT /*+ FULL(EMP) */ 
FROM SCOTT.EMP



// FROM 절에서 테이블이름 옆에 AS를 지정했다면 힌트에도 AS 사용
SELECT /*+ FULL(EMP) *. -- 무효
FROM EMP E

// 테이블 이름에 AS 했으니
SELECT /*+ FULL(E) */
FROM EMP E


//이렇게 힌트에도 AS로 박음

 

 

---

 

2️⃣. SQL 공유 및 재사용

2 - 1. 소프트 파싱 VS 하드 파싱

SQL 쿼리를 실행하기 전에 DB는 해당 쿼리를 "파싱"을 해야하는데.
파싱은 쿼리의 문법과 의미를 검사하고, 실행을 위한 최적의 방법을 결정하는 과정을 알려준다

SQL 파싱 , 최정화 , 로우소스 생성 과정을 지나 생성한 내부 프로시저를 반복해서 사용 할수 있게
캐싱해두는 메모리 공간을 '라이브러리 캐시' 라고 한다 .

아래 사진에 보면 라이브러리 캐시 는 SGA (모든 사용자)는 서버 프로세스와 백그라운드 프로세스가 공통으로
액세스 하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다

추가설명
SGA는 Oracle DB에 메모리 구조 중 하나.
'모든 사용자'라는 표현은 여러 사용자나 프로세스가 공통으로 접근 가능하다는 것을 의미해서 그렇다함

SGA에는 중요한 데이터와 데이터베이스 정보가 저장되어 있기 때문
DB작업을 빠르게 수행하기 위해 자주 사용되므로, 중앙에 모아 둔 것이라고 한다.

'라이브러리 캐시'는 SGA 내의 하나의 영역으로, 쿼리나 명령어와 같은 실행 정보를 임시로 저장. 
저장하게되면 반복된 쿼리나 명령의 빠른 실행을 도와줌

요약
SGA는 Oracle DB의 중요한 정보를 저장하는 메모리 공간, 여러 사용자나 프로세스가 동시에 접근할 수 있음
라이브러리 캐시는 그 안의 일부로서, 쿼리와 명령어의 실행 정보를 저장합니다.

 

사용자가 SQL 문을 전달하면 DBMS는 SQL을 파싱한후 ( 파싱 = SQL 쿼리를 처리하기 위해 쿼리를 분석 )

SQL이 라이브러리 캐시에 존재하는지 확인부터 한다

 

SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 '소프트 파싱' 찾는데 실패해 최적화 및 로우소스 생성단계 까지

모두 거치는 것을 ' 하드파싱 '

◾ 소프트 파싱 (Soft Parsing)

• SQL 쿼리가 파싱될 때, DB는 같은 쿼리가 파싱되었는지 확인한다. 만약  파싱되어 실행 계획이 캐시에 저장된 쿼리라면, 그 계획을 재사용한다
•  CPU 및 시스템 리소스를 절약하는 데 도움이 된다.
• 쿼리의 실행 계획이 캐시에 이미 존재하고 있다면, 실제 파싱 과정을 건너뛰고 바로 캐시된 실행 계획을 사용한다

◾ 하드 파싱 (Hard Parsing)

• 쿼리가 캐시에 없거나 캐시된 실행 계획이 더 이상 유효하지 않은 경우, DB에서 쿼리를 처음부터 파싱해야 한다.
• 문법 검사, 의미론적 검사, 최적화 및 실행 계획 생성을 포함하고 있다.
• 하드 파싱은 시스템 리소스에 더 큰 부담을 주며, 가능하면 피해야 한다. 

---

2 - 2 바인드 변수의 중요성

◾ 이름없는 SQL 

사용자가 만든 함수/프로시저 , 패키지 등은 생성할 때 이름을 정한다.
삭제하지 않는 한 영구적으로 보관하는데 실행 행때 라이브러리 캐시에 임시 저장되어 있어
여러 사용자가 재사용할수 있다

그런데 SQL은 이름이 따로 없다 . 전체 SQL 텍스트가 이름역할을 한다

---

◾ 공유가능 SQL

라이브러리 캐시에서 SQL을 찾기위해 사용하는 키 값이 'SQL문 그자체' 이므로 
아래는 모두  다른  SQL 이다

SELECT * FROM emp WHERE empno = 7900;
select * from EMP where EMPNO = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;
select * from emp where empno = 7900   ;
select /* comment */ * from emp where empno = 7900;
select /*+ first_rows */ * from emp where empno = 7900;

 

---

SELCET * FROM CUSTOMER WHERE LOGIN_ID = 'Han';
SELCET * FROM CUSTOMER WHERE LOGIN_ID = 'kim';
SELCET * FROM CUSTOMER WHERE LOGIN_ID = 'Kim';
SELCET * FROM CUSTOMER WHERE LOGIN_ID = 'tony';
SELCET * FROM CUSTOMER WHERE LOGIN_ID = 'mkcv';

Create procedure LOGIN_HAN() {}
Create procedure LOGIN_KIM() {}
Create procedure LOGIN_kim() {}
Create procedure LOGIN_tony() {}
Create procedure LOGIN_mkcv() {}

로그인 할 때 마다 프로시저를 하나씩 만들어서 라이브러리 캐시에 저장하는중이다 

위에 있는 프로시저의 내부 처리 루틴은 모두 같다
여러개의 프로시저를 생성하지말고 로그인 ID를 매개변수 파라미터로 받는 프로시저를 하나 만들어서 재사용 하자!

Create procedure LOGIN(lgin_id in varchar2) {   }

 

String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";

//  "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?" - 바인드 변수


// Prepared Statement는 SQL을 안전하고 효율적으로 실행하기 위한 방법 중 하나
// 보안 , 쿼리 재사용성 , 성능

 

---

3️⃣. 데이터 저장 구조 및 I/O 메커니즘

I/O 튜닝이 곧 SQL 튜닝이라 할수 있다

3 - 1 SQL이 느린 EU

대체로 SQL 이 느린이유는 I/O 때문이다
구체적으로는 디스크 I/O

"I/O"는 "Input/Output"으로 알고있다

DB 에서 I/O는 주로 디스크에서 데이터를 읽거나 디스크에 데이터를 쓰는 작업을 의미한다고 한다.
디스크 I/O는 일반적으로 CPU 연산보다 훨씬 느린 작업으로 간주되기 때문에, DB의 성능에 영향을 준다.

 

❓ 그래서 I/O 이게 뭔데??

책에서는 'I/O = 잠' 이라고 설명하고 있다.
▶ 스레드 : 사용자가 입력을 하지 않으면 대기 상태

 

프로세스가 일하지 않고 잠을 자기 때문 그 이유는 여러가지가 있지만 I/O가 가장 대표 적이다

 프로세스는 실행중인 프로그램을 뜻하며 
생성 - 종료 까지 준비와 실행 대기 상태를 반복하는데

실행중인 프로세스는 interput에 의해 여러번 실행 준비 상태로 전환했다가 다시 실행 상태로 전환

이것은 여러 프로세스가 하나의 프로세스만 CPU를 사용 할수 있기 떄문에 이런 메커니즘이 필요하다.

▶ interput 없이 열심히 일하던 프로세스도 디스크에서는 데이터를 읽어야 할 때 CPU를 OS에
반환 하고 잠시 수면 상태에서 I/O가 완료 되기를 기다린다.

3 - 2 DB의 저장 구조   

그림실력 너무 별론데 ?

   

                       

 

    테이블 스페이스 ▶ 세그먼트 ▶ 익스텐트 ▶ 블럭

• 세그먼트는 테이블 , 인덱스 처럼 데이터 저장공간이 필요한 오브젝트다
• 테이블 , 인덱스를 생성할 때 데이터를 어떤 테이블 스페이스에 저장할지 지정
• 세그먼트는 여러 익스텐트로 구성됨
• 세그먼트가 파티션 구조가 아니라면 테이블 , 인덱스도 하나의 세그먼트
• 세그먼트가 파티션 구조라면 각 파티션이 하나의 세그먼트
• (아니 그러면 파티션이든 테이블 인덱스 전부 세그먼트네 ?)
• 익스텐트 는 공간을 확장하는 단위

인덱스 ❓ 특정 테이블의 행을 빠르게 검색하기 위한 자료구조 , 데이터 검색성능을 향상
                특정 열을 검색하는 타겟이네

테이블이나 인덱스에 데이터 입력하다가 공간이 부족하면 테이블 스페이스로부터 익스텐트를 추가받음
익스텐트 는 연속된 블럭들의 집합 

연속된 여러개의 데이터 블럭으러 구성된다.

 

익스텐트 단위로 공간을 확장하는데 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블럭
한블럭에 저장된 레코드는 모두 같은 테이블레코드

 

• 블록 :  데이터를 읽고 쓰는 단위
• 익스텐트 : 공간을 확장하는 단위 , 연속된 블록의 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트 , 오브젝트별로 저장하기 위해 
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터 파일 : 디스크상의 물리적인 OS파일 
  🔑 물리적인: 실제 디스크나 저장매체에 존재  , OS파일 : 운영체제에서 관리하고 접근할수 있는 파일

 

 

3 - 3 시퀀셜 액세스 vs 랜덤 액세스

테이블 또는 인덱스 블록을 액세스 (= 읽다) 방식으로 시퀀셜 , 랜덤 두가지가 존재함

1 ) 시퀀셜 액세스 ( 연속적 )

논리적으로 , 물리적으로 연결된 순서에 따라 차례대로 블록을 읽음
인덱스 리프블록은 앞뒤를 가리키는 주소값을 통해 서로 연결되어 있다.

이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 액세스 

2 ) 랜덤 액세스 

순서를 따르지 않고 레코드 하나를 읽기위해 한 블록씩 접근 하는 방식

 

❗ 굵은 선은 시퀀셜 액세스 , 점선 랜덤 액세스
그림에서 알수 있듯이 시퀀셜은 일자로 쭉 그어져있다.

3 - 4 논리적 I/O vs 물리적 I/O 

1 ) DB 버퍼캐시

앞에 말했는데 디스크 I/O가 SQL의 성늘을 결정한다.
SQL을 실행하는 과정에서 데이터 블록을 읽는데 자주 읽는 블록을 매번 디스크에서 읽는것은 비효율적이다

데이터를 캐싱하는 DB버퍼캐시는 SGA에서 중요한 요소중 하나이다 
라이브러리 캐시는 SQL과 실행계획 DB저장형 함수/프로시저 를 캐싱하는 '코드캐시'라고 한다
DB 버퍼 캐시는 '데이터 캐시' 라고 한다 , 같은 블록에 대한 I/O Call을 줄이는 목적

캐싱 ?  캐시 ?

캐시 - 자주 필요한 데이터나 값의 복사본을 일시적으로 저장 하기 위해 사용하는 공간
캐싱 - 캐시 + ing  캐시를 사용하는것
라이브러리 캐시 -  쿼리나 명령어와 같은 실행 정보를 임시로 저장. 

 

2 ) 논리적 I/O vs 물리적 I/O

논리적 블록 I/O - SQL을 처리하는 과정에 발생한 총 블록 I/O 
메모리상의 버퍼캐시를 경유하므로 메모리 I/O가 논리적 I/O

물리적 블록 I/O - 디스크에서 발생한 총 블록 I/O 
SQL 처리도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크 액세스 하므로 
논리적 블록 I/O 중 일부를 물리적으로 I/O 한다.

                                          

                                                                   

3 - 5 Single Block I/O  vs  Multiblock I/O           

한번에 한블록씩 요청 하기도 하고 , 여러 블록씩 요청하기도 한다.
1 ) 한번에 한 블록씩 요청 해서 메머리에 적재하는 방식을 Single Block I/O 
2 ) 많은 벽돌을 실어 나를때 손수레를 이용하는 것처럼 한번에 여러 블록씩 요청해서 적재하는 방식을 Multi Block I/O

❗ 한 줄만 읽어도 블록이 최소 단위이기 때문에 Single Block 

 

3 - 6 Table Full Scan  vs  Index Range Scan

테이블에 저장된 데이터를 읽는 방식 2가지 

1 ) 테이블 전체를 스캔해서 읽는 Table Full Scan

2 )  인덱스를 이용해서 읽는 Index Range Scan  (보통 '인덱스를 이용한 테이블 액세스' 라고 표현함)

❗  Index Scan이 항상 빠르지는 않음. 찾는 양에 따라 Full , Index 서로 다름.

 

***ROWID는 테이블 레코드가 디스크 상에 어디 저장되었는지 가리키는 위치 정보***

풀 스캔이 성능이 안좋을 것 같지만 사실은 그렇지 않다.

찾아야 하는 데이터가 상당히 많다면 오히려 풀 스캔의 성능이 좋다.

 

캐시 탐색 매커니즘