SQL 자격 검정 실전문제 SQL 수행구조(데이터베이스 아키텍쳐, SQL 처리과정, 데이터베이스 I/O), SQL 분석도구(예상 실행계획, SQL 트레이스, 응답 시간 분석)

SQL 수행구조는  총 3절이다. 데이터베이스 아키텍처, SQL 처리 과정, 데이터베이스 I/O 메커니즘이다.

 

 데이터베이스 아키텍처, SQL 처리과정, 데이터베이스 I/O

*1. 실행계획을 통해서 알 수 있는 정보 액세스 기법, 질의 처리 예상 비용, 조인순서이다.
> 실제 처리 건수는 트레이스 정보로 알 수 있다.

*2. 오라클에서 I/O는 블록 단위로 이루어진다. 블록 단위로 I/O 한다는 것은, 하나의 레코드에서 하나의 칼럼만 읽으려 해도 레코드가 속한 블록 전체를 읽게 됨을 뜻한다.

*3-1. 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.

*3-2. 실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현된다.

*3-3. 조인 순서, 방법, 인덱스 선택 등과 같이 실행방법에 따라 SQL 출력순서는 변경될 수 있다.

> 실행계획이 변경되더라도 SQL 출력 순서는 동일하다(x)

*3-4. CBO(cost based optimizer)의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.

*4. 버퍼에 캐시된 이후 변경이 발생했지만, 아직 디스크에 기록되지 않아 데이터 파일 블록과 동기화가 필요한 상태의 버퍼 블릭을 더티 버퍼라고 한다. 이 상태의 버퍼 블록을 재사용하기 위해 디스크에 기록하는 순간이 버퍼는 프리 버퍼가 된다.

*5-1. 오라클의 규칙 기반 옵티마이저에서 가장 우선순위가 높은 규칙은 Single row by rowid 액세스 기업이다.

*5-2. 비용기반 옵티마이저는 테이블, 인덱스, 칼럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행 계획이 달라질 수 있다.

*5-3. 오라클의 실행계획에 나타나는 기본적인 join기법으로는 NL join, hash join, sort join, merge join 등이 있다.

*5-4. NL join은 OLTP의 목록 처리 업무에 많이 사용된다. DW 등의 데이터 집계 업무에서 많이 사용되는 JOIN rlqjqdms hash join sort join이다.

*6 I/O 효율화 원리

*6-1. 동일한 데이터를 중복 액세스하지 않도록 한다

*6-2. 액세스 패턴을 분석한 효율적인 인덱스는 꼭 필요하지만, 불필요한 인덱스는 지양해야 한다.

*6-3. 옵티마이저에 정확한 통계정보를 제공한다.

*6-4. 옵티마이저 힌트를 사용해 최적의 액세스 경로를 유도한다.

*7 데이터베이스 연결

*7-1. 다중 사용자 환경에서 서버와 모든 클라리언트 간 연결상태를 지속하면 서버 자원이 낭비하게 된다. 그렇다고 SQL을 수행할 때마다 연결 요청을 반복하면 서버 프로세스(또는 스레드)의 생성과 해제도 반복하므로 성능에 좋지 않다. 따라서 OLTP성 애플리케이션 커넥트 폴링 기법의 활용이 필수적이다.

*7-2. 연결 요청에 대한 부하는 스레드 기반 아키텍처보다 프로세스 기반 아키텍처에서 더 심하게 발생한다.

*7-3. 전용 서버 방식으로 오라클 데이터베이스에 접속하며 사용자가 데이터베이스 서버에 연결 요청을 할 때마다 서버 프로세가 생성된다.

*7-4. 공유 서버 방식으로 오라클 데이터베이스에 접속하면 사용자 프로세스는 서버 프로세스와 직접 통신하지 않고 dispatcher 프로세스를 거친다.

*8. 오라클 SQL sever와 같은 데이터베이스의 저장 구조
*8-1. 데이터를 읽고 쓰는 단위는 블록(=페이지)이다.

*8-2. 데이터 파일에 공간을 할당하는 단위는 익스텐트다.

*8-3. 익스텐트 내 블록들은 서로 인접하지만, 익스텐트끼리 서로 인접하지 않는다.

*8-4. SQL server에서는 한 익스텐트에 속한 페이지들을 여러 오브젝트가 나누어 사용할 수 있다.

*9. Write Ahead Logging : 버퍼 캐시 블록을 갱신하기 전에 변경 사항을 먼저 로그 버퍼에 기록해야 하며, 더티 버퍼를 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록해야 한다.

*10-1. DB 버퍼 캐시는 데이터 파일로부터 읽어 들인 데이터 블록을 담는 캐시 영역이다.

*10-2. /* append */ 힌트를 사용하면 insert 시 db 버퍼 캐시를 거치지 않고 디스크에서 직접 쓴다.

*10-3. 클러스터링 팩터가 좋은 인덱스를 사용하면 버퍼 핑잉 효과로 I/O를 줄일 수 있다.

*10-4. table full scan 한 데이터 블록은 LRU end에 위치하기 때문에 버퍼 캐시에 오래 머물지 않는다.

*11. I/O 튜닝의 핵심 원리는 멀티블록 I/O 액세스에 의한 선택 비중을 높이고 싱글블록 I/O 액세스 발생량을 줄이는 것이다.

*12. 테이블 블록을 스캔할 때는 random I/O 방식을, 인덱스 블록을 스캔할 때는 sequential I/O 방식을 사용한다.

*16. 효율화 튜닝 방안

*16-1. 필요한 최소 블록만 읽도록 쿼리를 작성한다.

*16-2. 전략적인 인덱스 구성은 물론 DBMS가 제공하는 다양한 기능을 활용한다.

*16-3. 변경이 거의 없는 테이블까지 매일 통계정보를 수집할 필요는 없다.

*16-4. 필요하다면 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.

*17-1. 랜덤 블록 I/O는 인덱스를 통해 테이블을 액세스 할 때 주로 발생한다.

*17-2. Direct Path I/O는 일반적으로 병렬쿼리로 Full Scan을 수행할 때 발생한다.

*17-3. Single Block I/O는 병렬로 인덱스를 통해 테이블을 액세스 할 때 주로 발생한다.

*17-4. Mutiblock I/O는 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 주로 발생한다.

*18. 데이터베이스 I/O 원리

*18-1. 한 쿼리 내에서 같은 블록을 반복하여 액세스 하면 버퍼 캐시 히트율은 높아진다.

*18-2. 멀티 블록은 한 번의 CALL로 여러 데이터 블록을 읽어 메모리에 적재하는 방식이다.

*18-3. 테이블 Full Scan 할 때, 테이블이 작은 Extent로 구성되어 있을수록 더 많은 call이 발생한다.

*18-4. 멀티블록 방식으로 읽더라도 extent 범위를 넘어서까지 읽지는 않는다. 따라서 작은 Extent로 구성된 테이블을 Full Table Scan 하면 call이 더 많이 발생한다.

*19-1. 단 하나의 레코드를 읽더라도 해당 레코드가 속한 블록을 통째로 읽는다.

*19-2. I/O를 수행할 때 익스텐트 내에 인접한 블록을 같이 읽어 들이는 것을 멀티블록이라고 한다.

*19-3. Sequential 방식은 테이블이나 인덱스를 스캔할 때 사용한다. random 방식은 인덱스를 스캔하면서 테이블을 액세스 할 때 사용한다.

*MPP 방식의 데이터베이스 제품에선 각 프로세스가 독립적인 메모리 공간을 사용하며, 데이터를 저장할 때도 각각의 디스크를 사용한다. 읽을 때도 동시에 각각의 디스크를 액세스 하기 때문에 병렬 I/O효과가 극대화된다.

 예상 실행계획, SQL 트레이스, 응답 시간 분석

*20, 21. Response Time = Servie Time + Wait Time = CPU Time + Queue Time

*22. 가장 최근에 수행한 SQL에 대한 실제 실행계획을 확인할 수 있는 오라클 성능관리 도구 DBMS_XPLAN 패키지 함수 : DBMS_XPLAN.DISPLAY_CURSOR

*23. SQL의 예상 실행계획을 출력하는 명령문 : set showplan_text on

*24. 오라클에서 SQL의 실행계획을 좀 더 편하고 이해하기 쉽게 출력하기 위해 제공하는 것
: utlxpls.sql 스크립트, utxplp.sql 스크립트, DBMS_XPLAN 패키지

*25. SQL Sever에서 SQL 트레이스를 설정하기 위해 on으로 설정해야 하는 옵션

: statistics profile, statistics io, statistics time 옵션은 on으로 설정

*26. 오라클에서 어떤 SQL이 수행 중일 때, 해당 SQL이 참조 중인 객체에 다른 사용자가 DDL 문장을 수행할 때 발생할 수 있는 대기 이벤트 : library cache lock

*27. 오라클에서 응답 시간 분석에 사용되는 도구인 AWR(automatic workload repository)이 제공하는 첫 장 요약 보고서에 포함되는 정보 : 누적 대기 시간이 가장 짧았던 대기 이벤트들