[1028 SQL]Ⅲ. SQL 고급 활용 및 튜닝-제1장 SQL 수행 구조-아키텍처, I/O

과목 Ⅲ. 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 에 인접한 블록 같이 읽어 적제 - 대량 데이터읽을때