SQLite 설치하기 위해 하기 사이트로 접근합니다.
저 같은 경우 MacOS에 M2이기 때문에 해당 버전에 맞게 설치를 했습니다.
SQLite 설치하기 위해 하기 사이트로 접근합니다.
저 같은 경우 MacOS에 M2이기 때문에 해당 버전에 맞게 설치를 했습니다.
옵티마이저가 SQL문을 가장 효율적으로 처리하기 위해 실행하는 계획을 선택하는 것을 의미한다.
테이블 전체 데이터를 읽어 조건에 맞는 데이터를 추출하는 방식
ROWID기준으로 데이터를 추출하는 방식으로 단일행 접근시 가장 빠르다
원하는 데이터 추출을 위해 인덱스를 사용하여 검색하는 방식
FULL TABLE SCAN이 무조건 성능에 좋지 않고 INDEX SCAN이 성능에 좋다는 것이라는 생각은 잘못 된 것이다.
데이터가 많은 테이블로부터 일부의 데이터를 추출해야하는 INDEX SCAN이 유용한 건 사실이지만 반대로 테이블에 있는 대부분의 데이터를 추출해야하면 FULL TABLE SCAN이 더 효과적일 수 있다.
ORACLE에서 실행 계획을 테이블에 저장하게 되는데 해당 테이블 이름은 PLAN_TABLE이다.
EXPLAIN PLAN -- EXPLAIN PLAN 선언부
SET STATMENT_ID = 'EP_TEST' -- SQL에 임의 ID 부여
FOR
SELECT E.empno, E.ename, D.deptname
FROM emp E, dept D
WHERE E.deptno=D.deptno;
해당 SQL은 SQL의 실행 계획을 저장하는 것일 뿐 실제로 SQL이 실행되는 것이 아니다.
이거는 옵션에 따라 직접 실행시키면서 SQL 실행 계획을 저장할 수도 있다.
PLAN_TABLE에서 DBMS_XPLAN 패키지를 이용해 조회가 가능하다
실행 계획 순서는 위에서 아래로 진행되며 가장 들여쓰기가 많이 된 곳에서 부터 시작되며 실행 순서는 위에서 아래로 진행된다. 같은 수준에서 위에서 아래로 실행된 게 그 이후에 없으면 상위로 올라가며 반복된다.
해당 실행 계획의 순서는 5 → 3 → 4 → 2 → 1 → 0 이다
STARTS | 실행 계획 정보중 기본적인 최소한의 정보를 출력 |
A-ROWS | 실제 SQL이 실행된 후 수집되는 행 수 |
A-TIME | 실제 SQL 수행에 소요된 시간 |
E-ROWS | SQL에 대한 예측되는 행의 수 |
E-BYTES | SQL 실행 시 접근하는 데이터 바이트 수의 예측값 |
USED_MEM | 최근 실행 시 실제 메모리 사용한 양 |
그외 더 다양한 정보가 있습니다.
실행 계획은 물론 여러 세션에서 수행한 SQL 통계 정보 수행 시간, 결과 등을 TRACE로 기록하여 파일 형태로 저장하는 방법이다. 이걸 이용해 필요한 부분의 SQL문의 실행 정보를 수집할 수도 있다.
🔗 참고 및 출처
https://harris91.vercel.app/query-plan
https://myjamong.tistory.com/237
발췌 : 실전 사례로 살펴보는 SQL 튜닝 비법
[Database] SQLite 설치하기 (0) | 2023.08.01 |
---|---|
[Database] SQL 튜닝 비법 Oracle 기반 (1) [SQL 튜닝이란, 옵티마이저, SQL 실행 순서, 옵티마이저 종류(규칙기반, 비용기반), 동작 방식, 옵티마이저의 한계] (0) | 2023.06.20 |
[Database] 데이터베이스 SQL문 들여쓰기(QUERY 가독성 높이기) (0) | 2022.11.09 |
최소한의 CPU I/O 메모리를 사용해 최대한 빠른 시간내 원하는 데이터 작업을 수행시키는 것
아무리 쿼리의 성능을 튜닝해도 근본적인 스키마 구조가 문제라면 밑빠진 독에 물 붓기이기 때문에 이런 경우 아예 근본적인 스키마 구조부터 뜯어고쳐야할 수 있다.
데이터 모델링을 할 때는 일반적으로 사용자의 이용 빈도(자주 사용하는 화면)를 고려하며 설계 하는 게 좋다
DBMS의 두뇌라고 표현할 수 있다. SQL이 들어오면 문법 에러 및SQL문을 처리하는 최적의 방법을 도출해서 실행하게 한다.
예를 들어 아래와 같은 쿼리문이 존재할 때 emp_no가 PRIMARY KEY 설정(인덱스)가 안 되어있는 경우 15번의 FULL TABLE SCAN 방식으로 실행될 것이고 있는 경우는 4번의 방식으로 실행되게 된다.
SELET E.e_name
FROM emp E
WHERE E.emp_no = '12345';
우선 순위가 정해져있기 때문에 미리 예측을 할 수 있다는 장점이 있다. 그렇기 때문에 SQL을 통제하는 것이 가능하다
하지만 이러한 규칙이 항상 유리하지 않다
성별인 남자이고 입사일이 2013년 1월 1일 ~ 2013년 1월 3일인 사람을 검색하는 SQL을 작성하고자할 때 아래 SQL과 같이 작성하는 경우 성별인 남자인 직원을 걸러내는 인덱스를 사용하는 것보다 3일 사이에 입사한 직원을 걸러내는게 더 효율적이다.
→ 남자인 사람에서 입사일을 걸러내는 것보다 입사일에서 남자인 사람을 걸러내는게 불필요한 데이터를 많이 읽게 되는 걸 줄일 수 있다
SELECT E.e_name
FROM emp E
WHERE E.gender = '남자'
AND E.hiredate BETWEEN '20130101' AND '20130103'
위와 같은 쿼리를 좀더 효율적으로 하기 위해 인덱스를 안 타게끔 아래와 같이 조정이 가능하긴 하다.
SELECT E.e_name
FROM emp E
WHERE E.gender || '' = '남자'
AND E.hiredate BETWEEN '20130101' AND '20130103'
또한 규칙기반 옵티마이저에서는 이러한 단점들이 존재한다.
PARSING 과정을 거친 SQL은 PARSING 트리 형태로 변형되어 Query Transformer는 넘겨 받은 SQL을 보고 같은 결과를 도출하되 좀 더 나은 실행 계획을 갖는 SQL로 변형이 가능한지 판단해 변환 작업을 수행
예를 들면 복잡한 서브쿼리나 뷰를 사용한 SQL을 일반적인 조인 형태의 SQL로 변환해 실행 계획을 도출하기 좋은 상태로 만든다.
Query Transformer를 통해 변환 작업을 마치고난 SQL은 Estimator로 넘겨지게 되는데 이때 수행하는데 드는 총 비용을 계산한다.
Estimator를 통해 계산된 값들을 토대로 후보군 실행계획 도출 Row Source Generator를 통해 출력이 가능한 코드 형태로 바꾼다.
비용기반 옵티마이저는 CHOOSE, FIRST_ROWS와 같은 모드도 존재하지만 현재는 사용하지 않고 DBMS에서도 FIRST_ROWS_n을 권장하며 실행 결과를 출력하는데 걸리는 응답 속도를 최적화 하는게 목표이다.
SQL 실행 결과 전체를 빠르게 처리하는데 최적화된 실행 계획을 세우는 것입니다. ORACLE의 경우 10g 이후에 이값이 기본적으로 설정되어있다.
옵티마이저의 경우 통계 정보를 따라 자동적으로 최적의 실행계획을 세운다 이용하는 통계정보는 아래와 같다
테이블 | 테이블 전체 행의 수 |
테이블이 차지하고 있는 전체 블록 수 | |
테이블의 행들이 갖는 평균 길이 등 | |
컬럼 컬럼 값의 종류 | |
컬럼 내 NULL 값의 분포도 | |
컬럼 값의 평균 길이 | |
컬럼 내 데이터 분포의 추정치 등 | |
인덱스 | LEAF BLOCK 수 : 데이터 보관하는 블록 수 |
LEVELS : 인덱스 트리의 LEVELS 정보 | |
CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도 | |
시스템 | I/O 성능 및 사용률 |
CPU 성능 및 사용률 등 |
시스템 운영중 DBMS 전체의 옵티마이저 모드를 변경하는 작업은 가급적 하지 않는 것이 좋다.
복잡한 쿼리문때문에 정확하지 않은 통계자료 발생
비용 예측시 한 개의 SQL만 실행된다는 전제로 진행하게 되는데 실제 운영 환경에서 같은 블록을 접근하는 등 상황이 발생해 대기상태가 될 수 있다.
날짜 유형의 데이터를 날짜 데이터 타입이 아닌 문자 데이터 타입을 컬럼으로 사용할 경우 비용 계산 방식이 달라져 예측이 어려워질 수 있다
예를 들면 날짜 데이터를 DATE타입으로 선언하면 특정 월에는 1부터 최대 31까지의 날짜만 존재하지만 YYYYMMDD 형식을 갖는 VARCHAR 타입의 데이터를 선언하면
이는 연속된 문자열이기 때문에 31일 이후의 날짜가 존재할 수 있다고 판단한다. 그 결과 예측되는 최종비용 또한 정확성이 떨어지게 된다.
발췌 : 실전 사례로 살펴보는 SQL 튜닝 비법
[Database] SQLite 설치하기 (0) | 2023.08.01 |
---|---|
[Database] SQL 튜닝 비법 Oracle 기반 (2) [실행계획, FULL TABLE SCAN, ROWID SCAN, INDEX SCAN, INDEX SCAN 종류, PLAN_TABLE, SQL TRACE] (0) | 2023.06.22 |
[Database] 데이터베이스 SQL문 들여쓰기(QUERY 가독성 높이기) (0) | 2022.11.09 |
SELECT
A.ID AS ADMIN_ID -- 아이디
, A.REG_DATE AS REG_DATE -- 수정일자
, A.COMPANY AS COMPANY -- COMPANY
, A.WRITER AS WRITER -- 작성자
FROM COVID_VIEW A
WHERE A.ID = '1'
OR A.ID = '2'
AND A.REG_DATE > 20220104
GROUP BY A.COMPANY
ORDER BY A.REG_DATE DESC