과목 Ⅲ. SQL 고급 활용 및 튜닝
제1장 SQL 수행 구조
제1절 데이터베이스 아키텍처
제2절 SQL 처리 과정
제3절 데이터베이스 I/O 메커니즘
데이터베이스 아키텍처 | 데이터베이스 시스템의 구조와 구성요소를 설명 - 설계, 데이터 저장방식, 접근, 관리방법 architecture ; 설계방식 |
|||
구성요소 | DBMS | 데이터 저장, 검색, 업데이트, 삭제 데이터관리위한 소프트웨어 | ||
데이터 모델 | 데이터 구조, 형식 정의 | |||
스키마 | 논리구조 정의 - 테이블, 열, 인덱스, 뷰, | |||
데이터베이스 엔진 | 데이터 처리 관리 소프트웨어- 쿼리실행, 데이터 무결성, 트랜젝션 관리 | |||
사용자 인터페이스 | ||||
보안 및 권한 관리 | 사용자 인증, 권한 부여, 감사, 로그 | |||
Oracle 데이터베이스 |
물리적구조 | 데이터파일 | 실제 데이터 | |
제어파일 | 데이터 베이스 구조, 상태, 이름, 위치, 로그파일정보 | |||
로그파일 | 변경사항 기록, 손실시 복구위해 | |||
논리적구조 | 테이블스페이스 | 논리적 구분한영역, 저장공간관리 | ||
세그먼트 | 특정 데이터 객체에 할당된 논리적공간 | |||
익스텐트 | 세그먼트가 차지하는 연속된 블록의 집합 | |||
데이터블록 | 최하위 저장단위 | |||
메모리 구조 | 시스템 글로벌영역 SGA | 인스턴스 시작될때 할당되는 공유메모리영역 - 버퍼캐시 : 데이터 읽고쓸때 사용메모리영역 - 공유풀 : SQL 명령문, 사전정보 저장 - 로그 버퍼: (트랜잭션로그정보저장) |
||
백그라운드 프로세스 | SMON (system monitor) | 장애발생 시스템 재기동시 인스터스 복구, 임시 세그먼트 익스텐트 모니터링 | ||
PMON (Process Monitor) | 이상이 생긴 프로세스가 사용하던 리소스 복구 | |||
DBWn (Database Writers) | 버퍼 캐시에 있는 dirty 버퍼를 데이터파일에 기록 | |||
LGWR(Log Writer) | 로그 버퍼 엔트리를 Redo 로그 파일에 기록 | |||
ARCn( Archiver) | 꽉찬 Redo 로그 덮여 쓰여지기전에 Archive디렉터리 백업 | |||
CKPT (Checkpoint) | 체크포인트 프로세스는 이전 마지막 체크포인트 이후 변경사항 데이터 파일에 기록 트리거링, 데이터 파일 헤더 저장. | |||
인스턴스 | 공유메모리영역과 이를 엑세스하는 프로세스 집합 |
|||
서버-클라이언트 | 전용서버방식 Dedicated Server Mode |
별도프로세스할당 | 1. 연결요청 2. 프로세스 생성 및 연결요청상속 3. Resend 패킷전송 4. 연결 후 작업요청 5. 처리후 결과전송 |
|
공유서버방식 | 공용 프로세스풀에 전달, 요청을 처리하고 응답반환 | 1. 연결요청 2. 가용한 Dispatcher 포트전송 3. 연결 후 작업요청 4. 요청등록 5. 요청접수 6. 결과등록 7. 결과수정 8. 결과전송 |
SQL 처리과정 | Parser -> Optimizer -> Row-Source Generator -> SQL Engine | |||
Parser | SQL 개별 구성요소 분석, 파싱, 파싱트리를 만든다. SQL 문법오류, 의미상 오류 체크 |
|||
Optimizer | 최적화 1. 쿼리 수행위한 후보군 실행계획 찾기 2. 데이터 딕셔너리에 미리 수집한 오브젝트 통계 및 시스템 통계정보를 이용해 실행계획 예상비용 산정 3. 최저비용 실행계획 선택 옵티마이저 힌트 : 개발자가 인덱스 지정, 조인방식 변경 실행계획 유도 |
|||
Query Transformer | 파싱된 SQL을 표준적 형태로 변환 | |||
Estimator | 시스템 통계정보이용해 쿼리 수행단계의 선택도, 카디널리티, 실행계획 총 비용계산 | |||
Plan Generator | 하나의 쿼리를 수행할때, 후보군이 될만한 실행계획 생성 | |||
Row-Source Generator | 옵티마이저 생성실행계획을 SQL 엔진이 실제 실행할수있는 코드로 포멧팅 | |||
SQL Engine | SQL 실행 | |||
옵티마이저 힌트 Optimizer Hints (oracle.com) |
-- 전용 서버 방식으로 특정 인덱스를 사용하여 조회하는 쿼리 SELECT /*+ INDEX(Orders IX_OrderDate) */ * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.OrderDate >= '2024-01-01' OPTION (LOOP JOIN, MAXDOP 4); |
|||
최적화 목표 Optimization Goals 1. 응답시간 최소화 2. 자원사용 최소화 |
ALL_ROWS /*+ALL_LOWS */: ![]() |
전체 처리속도 최적화 | ||
FIRST_ROWS(n)![]() |
최초 N건 응답속도 최적화 | |||
CHOOSE![]() |
데이터 사전(데이터 딕셔너리)에 통계가 있으면 비용 기반 접근 방식을 사용하고, 통계가 없으면 규칙 기반 접근 방식을 사용 | |||
RULE![]() |
규칙 기반 최적화는 Oracle의 이전 버전에서 사용되었으나, 최신 버전에서는 비용 기반 최적화가 권장 | |||
액세스 경로 - 인덱스 스캔 |
FULL![]() |
인덱스 타지말고 바로 테이블 풀스캔 | ||
ROWID![]() |
||||
CLUSTER![]() |
||||
HASH![]() |
||||
INDEX![]() |
인덱스를 타라 | |||
INDEX_ASC![]() |
||||
INDEX_COMBINE![]() |
||||
INDEX_JOIN![]() |
||||
INDEX_DESC![]() |
인덱스를 ORDER BY DESC 역순으로 타라 (시간, 결과값등 최근인것 혹은 MAX값 구할때 좋음) | |||
INDEX_FFS![]() |
INDEX FAST FULL SCAN으로 타라 | |||
NO_INDEX![]() |
||||
INDEX_SS | INDEX SKIP SCAN | |||
AND_EQUAL![]() |
||||
쿼리 변환 - 서브쿼리, 뷰이용 |
USE_CONCAT NO_EXPAND REWRITE EXPAND_GSET_TO_UNION NOREWRITE MERGE NO_MERGE STAR_TRANSFORMATION FACT NO_FACT USE_CONCAT |
no_query_ | ||
조인 순서 | ORDERED STAR |
|||
조인 방식 | USE_NL USE_MERGE USE_HASH DRIVING_SITE LEADING HASH_AJ, MERGE_AJ, and NL_AJ HASH_SJ, MERGE_SJ, and NL_SJ |
|||
병렬 처리 | PARALLEL NOPARALLEL PQ_DISTRIBUTE PARALLEL_INDEX NOPARALLEL_INDEX |
|||
기타 | APPEND NOAPPEND CACHE NOCACHE UNNEST NO_UNNEST PUSH_PRED NO_PUSH_PRED PUSH_SUBQ NO_PUSH_SUBQ ORDERED_PREDICATES CURSOR_SHARING_EXACT DYNAMIC_SAMPLING |
call | |||
Static SQL | parse , execute | ||
Dynamic SQL | parse , execute | ||
Stored Procedures | call | ||
Functions | call | ||
Triggers | execute | ||
Cursor | Fetch | ||
작업요청구분 | parse | SQL 문을 데이터베이스에 보내 구문 분석 및 실행 계획 생성을 요청합니다. Static SQL과 Dynamic SQL에서 사용됩니다. | |
call | 저장 프로시저 또는 함수 호출 시 사용됩니다. 저장 프로시저와 함수에서 사용됩니다. | ||
execute | SQL 문을 실제로 실행하는 단계입니다. Static SQL, Dynamic SQL, 트리거에서 사용됩니다. | ||
patch | 커서 사용해서 결과집합의 각 행을 가져옴 | ||
발생위치 | User call | DBMS외부에서 요청되는콜. | |
Recursive call | DBMS내부에서 요청되는콜 | 하드파싱줄여야 | |
Fetch call 최소화 | 1. 부분범위처리 원리 | ||
2. Array Size 조정 | |||
페이지처리 | |||
분산쿼리 | |||
데이터베이스 모델링의 3단계진행 | |||
블록단위 I/O | 하나의 레코드를 읽어도 레코드가 속한 블록전체를 읽는다. SQL 성능지표는 엑세스하는 블록개수 ex) 버퍼캐시, 데이터 파일I/O |
||
메모리IO vs 디스크IO | 1. 디스크I/O 최소화하고 버퍼캐시 효율높이는 것이 I/O 튜닝목표 | 메모리I/O | 전기적신호로 빠름 |
디스크I/O | 암이 움직이면서 헤드통해 읽고씀 느림 | ||
2. 버퍼 캐시 히트율(BCHR) | 전체 읽은 블록 중 메모리 버퍼캐시에서 찾은비율 논리적(query+current) 블록 요청횟수를 줄이고, 물리적(disk)으로 디스크에 읽어야 할 블록수를 줄이는것. |
||
3. RAC 클러스터링 Real Application Cluster |
여러개 인스턴스가 하나의 데이터베이스 접근 | ||
4. 시퀀셜 엑세스 높이고 랜덤액세스 줄인다. | Sequential I/O | 레코드간 논리적, 물리적 순서(포인터) 따라 스캔 | |
Random I/O | 한건 읽기 위해 한 블록씩 접근하는 방식 | ||
5. Single Block I/O | 한번의 call 에 하나의 데이터 읽어적재 - 인덱스 스캔시 효율적 | ||
6. MultiBlock I/O | 한번의 call 에 인접한 블록 같이 읽어 적제 - 대량 데이터읽을때 | ||
'Data' 카테고리의 다른 글
[1028 SQL]Ⅲ. SQL 고급 활용 및 튜닝-제3장 인덱스 튜닝 (0) | 2024.07.05 |
---|---|
[1028 SQL]Ⅲ. SQL 고급 활용 및 튜닝-제2장 SQL 분석 도구 (0) | 2024.07.04 |
[1028 SQL]Ⅱ. SQL 기본과 활용-제3장 DML, TCL, DDL, DCL, 절차형SQL(프로시저) (0) | 2024.06.24 |
[1028 SQL]Ⅱ. SQL 기본과 활용-제2장 서브쿼리, 집합연산자, 그룹함수, 윈도우함수, Top N쿼리, 계층형, 뷰 (0) | 2024.06.24 |
[1028 SQL]Ⅱ. SQL 기본과 활용-제1장 SQL, ERD, 함수(집계함수 외), select 외 (0) | 2024.06.20 |