3.1 About SQL Processing SQL 처리는 구문 분석, 최적화, 행 소스 생성 및 SQL 문의 실행입니다. 설명에 따라 데이터베이스는 이러한 단계 중 일부를 생략 할 수 있습니다. 다음 그림은 SQL 처리의 일반 단계를 보여줍니다. Figure 3-1 SQL 처리 단계 3.1.1 SQL Parsing SQL 처리의 첫 단계는 구문 분석입니다. 구문 분석 단계에서는 SQL 문의 조각을 다른 루틴이 처리 할 수 있는 데이터 구조로 분리합니다. 데이터베이스는 응용 프로그램에서 지시 할 때 명령문을 구문 분석합니다. 즉, 데이터베이스 자체가 아닌 응용 프로그램만 구문 분석 수를 줄일 수 있습니다. 응용 프로그램이 SQL 문을 발행하면 응용 프로그램은 데이터베이스에 대한 구문 분석 호출을 작성하여 실행문을 준비합니다. 구문 분석 호출은 구문 분석 된 SQL 문 및 기타 처리 정보를 보유하는 세션 별 개인 SQL 영역의 핸들인 커서를 열거나 작성합니다. 커서 및 개인 SQL 영역은 PGA(Program Global Area)에 있습니다. 구문 분석 호출 중에 데이터베이스는 다음 검사를 수행합니다. • Syntax Check • Semantic Check • Shared Pool Check 선행 검사는 명령문 실행 이전에 발견 될 수 있는 오류를 식별합니다. 구문 분석을 통해 일부 오류를 발견 할 수 없습니다. 예를 들어, 데이터베이스는 명령문 실행 중에만 교착 상태 또는 데이터 변환 오류가 발생할 수 있습니다. 3.1.1.1 Syntax Check Oracle Database는 각 SQL 문에서 구문 유효성을 검사해야 합니다. 올바른 형식의 SQL 구문에 대한 규칙을 위반하는 명령문은 검사에 실패합니다. 예를 들어, 키워드 FROM의 철자가 FORM이므로 철자가 잘못되었습니다. SQL> SELECT * FORM employees; SELECT * FORM employees * ERROR at line 1: ORA-00923: FROM keyword not found where expected 3.1.1.2 Semantic Check 명령문의 의미는 그 의미입니다. 의미론적 검사는 명령문이 의미 있는지 여부를 판별합니다. (예: 명령문의 오브젝트와 컬럼이 있는지 여부). 구문 상 올바른 구문은 존재하지 않는 테이블에 대한 다음 쿼리 예제에서와 같이 의미론적 검사를 실패 할 수 있습니다. SQL> SELECT * FROM nonexistent_table; SELECT * FROM nonexistent_table * ERROR at line 1: ORA-00942: table or view does not exist 3.1.1.3 Shared Pool Check 구문 분석 중에 데이터베이스는 공유 풀 점검을 수행하여 자원 집중적 인 명령문 처리 단계를 건너 뛸 수 있는지 여부를 판별합니다. 이를 위해 데이터베이스는 해시 알고리즘을 사용하여 모든 SQL 문에 대한 해시 값을 생성합니다. 문장 해시 값은 V$SQL.SQL_ID에 표시된 SQL ID입니다. 이 해시 값은 Oracle Database 버전 내에서 결정적이므로 단일 인스턴스 또는 다른 인스턴스의 동일한 문장은 동일한 SQL ID를 갖습니다. 사용자가 SQL 문장을 제출하면 데이터베이스는 공유 SQL 영역을 검색하여 기존의 구문 분석된 문장이 동일한 해시 값을 갖는지 확인합니다. SQL 문의 해시 값은 다음 값과 다릅니다. • 명세서의 메모리 주소 Oracle Database는 SQL ID를 사용하여 조회 테이블에서 키순 읽기를 수행합니다. 이러한 방법으로, 데이터베이스는 구문의 가능한 메모리 주소를 가져옵니다. • 명령문의 실행 계획 해시 값 SQL 문장은 공유 풀에 여러 개의 계획을 가질 수 있습니다. 일반적으로 각 계획에는 다른 해시 값이 있습니다. 동일한 SQL ID에 여러 계획 해시 값이 있는 경우, 데이터베이스는 이 SQL ID에 대해 여러 계획이 있음을 알고 있습니다. 구문 분석 작업은 제출된 문의 유형 및 해시 검사의 결과에 따라 다음 범주로 분류됩니다. • Hard parse Oracle Database가 기존 코드를 재사용 할 수 없다면, 애플리케이션 코드의 새로운 실행 가능 버전을 빌드해야 합니다. 이 작업을 하드 구문 분석 또는 라이브러리 캐시 누락이라고 합니다. Note: 데이터베이스는 항상 DDL의 하드 구문을 수행합니다. 하드 구문 분석 중에 데이터베이스는 라이브러리 캐시와 데이터 사전 캐시를 여러 번 액세스하여 데이터 사전을 검사합니다. 데이터베이스가 이 영역에 액세스 할 때 필수 객체의 래치라는 직렬화 장치를 사용하므로 정의가 변경되지 않습니다. 래치 경합은 명령문 실행 시간을 증가시키고 동시성을 감소시킵니다. • Soft parse 소프트 파싱은 하드 파싱이 아닌 파싱입니다. 제출된 명령문이 공유 풀의 재사용 가능한 SQL 문장과 동일하면 Oracle Database는 기존 코드를 재사용합니다. 이 코드 재사용은 라이브러리 캐시 적중 이라고도 합니다. 소프트 파싱은 수행하는 작업량에 따라 다를 수 있습니다. 예를 들어, 세션 공유 SQL 영역을 구성하면 소프트 구문 분석에서 래칭(latching)의 양을 줄여 "소프트"하게 만들 수 있습니다. 일반적으로 데이터베이스가 최적화 및 행 소스 생성 단계를 건너 뛰고 곧바로 실행되기 때문에 간단한 구문 분석이 하드 구문 분석보다 바람직합니다. 다음 그래픽은 전용 서버 아키텍처에서 UPDATE 문장의 공유 풀 확인을 단순화 한 것입니다. Figure 3-2 Shared Pool Check 검사가 공유 풀의 명령문에 동일한 해시 값이 있다고 판단하면 데이터베이스는 의미 및 환경 점검을 수행하여 명령문이 동일한 의미를 갖는지 판별합니다. 동일한 구문으로는 충분하지 않습니다. 예를 들어, 두 명의 다른 사용자가 데이터베이스에 로그인하여 다음 SQL 문을 발행한다고 가정하십시오. CREATE TABLE my_table( some_col INTEGER ); SELECT * FROM my_table; 두 명의 사용자에 대한 SELECT 문장은 구문 상 동일하지만 두 개의 개별 스키마 객체에는 my_table이라는 이름이 지정됩니다. 이 의미상의 차이는 두 번째 명령문이 첫 번째 명령문의 코드를 재사용 할 수 없다는 것을 의미합니다. 비록 두 문장이 의미 상 동일하다 할지라도 환경적 차이는 어려운 해석을 강요 할 수 있습니다. 이 컨텍스트에서 옵티마이저 환경은 작업 영역 크기 또는 옵티마이저 설정 (예: 옵티마이저 모드)과 같은 실행 계획 생성에 영향을 줄 수 있는 전체 세션 설정입니다. 단일 사용자가 실행 한 다음 일련의 SQL 문을 고려하십시오. ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS; ALTER SYSTEM FLUSH SHARED_POOL; #optimizer environment 1 SELECT * FROM sh.sales; ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; #optimizer environment 2 SELECT * FROM sh.sales; ALTER SESSION SET SQL_TRACE=true; #optimizer environment 3 SELECT * FROM sh.sales; 앞의 예에서 세 가지 다른 옵티마이저 환경에서 동일한 SELECT 문이 실행됩니다. 결과적으로 데이터베이스는 이러한 명령문에 대해 세 개의 분리된 공유 SQL 영역을 작성하고 각 명령문을 강제로 구문 분석합니다. 3.1.2 SQL Optimization 최적화 단계에서 Oracle Database는 모든 고유 DML 문장에 대해 최소 한 번 이상 하드 구문 분석을 수행하고 이 구문 분석 동안 최적화를 수행해야합니다. 데이터베이스는 최적화가 필요한 하위 쿼리와 같은 DML 구성 요소가 포함되어 있지 않으면 DDL을 최적화하지 않습니다. "쿼리 최적화 프로그램 개념"에서는 최적화 프로세스를 보다 자세히 설명합니다. 3.1.3 SQL Row Source Generation 행 소스 생성기는 최적화 프로그램에서 최적의 실행 계획을 수신하고 나머지 데이터베이스에서 사용할 수 있는 반복 실행 계획을 생성하는 소프트웨어입니다. 반복 계획은 SQL 엔진에 의해 실행될 때 결과 집합을 생성하는 이진 프로그램입니다. 이 계획은 여러 단계의 조합으로 이루어진다. 각 단계는 행 세트를 리턴합니다. 다음 단계에서는 이 집합의 행을 사용하거나 마지막 단계에서 SQL 문을 실행하는 응용 프로그램에 행을 반환합니다. 행 원본은 행을 반복적으로 처리 할 수 있는 제어 구조와 함께 실행 계획의 단계에서 반환되는 행 집합입니다. 행 소스는 조인 또는 그룹화 조작의 테이블, 뷰 또는 결과 일 수 있습니다. 행 소스 생성기는 행 소스의 집합 인 행 소스 트리를 생성합니다. 행 원본 트리에는 다음 정보가 표시됩니다. • 명령문에서 참조하는 테이블의 순서 지정 • 문에 언급 된 각 테이블에 대한 액세스 방법 • 문에서 조인 작업의 영향을 받는 테이블에 대한 조인 방법 • 필터, 정렬 또는 집계 같은 데이터 연산 Example 3-1 Execution Plan 이 예는 AUTOTRACE가 사용 가능할 때 SELECT 문의 실행 계획을 보여줍니다. 성명은 성이 문자 A로 시작하는 모든 직원의 성, 직위 및 부서 이름을 선택합니다. 이 명령문의 실행 계획은 행 소스 생성 프로그램의 출력입니다. SELECT e.last_name, j.job_title, d.department_name FROM hr.employees e, hr.departments d, hr.jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.last_name LIKE 'A%';
3.1.4 SQL Execution 실행 중에 SQL 엔진은 행 소스 생성 프로그램에서 생성 한 트리의 각 행 소스를 실행합니다. 이 단계는 DML 처리의 유일한 필수 단계입니다. 그림 3-3은 Example 3-1의 계획에서 한 단계에서 다른 단계로 행 소스의 흐름을 보여주는 구문 분석 트리라고도하는 실행 트리입니다. 일반적으로 실행 단계의 순서는 계획의 순서와 반대이므로 계획을 아래에서 위로 읽습니다. 실행 계획의 각 단계에는 ID 번호가 있습니다. 그림 3-3의 번호는 예 3-1에 표시된 계획의 Id 열에 해당합니다. 계획의 작업 열에 있는 초기 공백은 계층 관계를 나타냅니다. 예를 들어 조작 이름 앞에 두 개의 공백이 있으면 이 조작은 하나의 공백 앞에 오는 조작의 하위입니다. 하나의 공백 앞에 오는 조작은 SELECT의 하위 항목입니다. 그림 3-3에서 트리의 각 노드는 행 소스로 작동합니다. 즉, 예 3-1의 실행 계획의 각 단계는 데이터베이스에서 행을 검색하거나 하나 이상의 행 소스에서 행을 입력으로 수락합니다. SQL 엔진은 다음과 같이 각 행 원본을 실행합니다. • 블랙 박스에 표시된 단계는 데이터베이스의 개체에서 물리적으로 데이터를 검색합니다. 이 단계는 액세스 경로 또는 데이터베이스에서 데이터를 검색하는 기술입니다. - 6 단계는 전체 테이블 스캔을 사용하여 departments 테이블에서 모든 행을 검색합니다. - 5 단계는 전체 테이블 스캔을 사용하여 작업 테이블에서 모든 행을 검색합니다. - 4 단계는 emp_name_ix 색인을 순서대로 스캔하여 시작하는 각 키를 찾습니다. 문자 A를 사용하여 해당 rowid를 검색하십시오. 예를 들어, Atkinson에 해당하는 rowid는 AAAPzRAAFAAAABSAAe입니다. - 3 단계는 4 단계에서 리턴 된 rowid의 행을 employees 테이블에서 검색합니다. 예를 들어, 데이터베이스는 rowid AAAPzRAAFAAAABSAAe를 사용하여 Atkinson 행을 검색합니다. • 지우기 상자에 표시된 단계는 행 소스에서 작동합니다. - 2 단계는 해시 조인을 수행하고 3 단계와 5 단계의 행 소스를 수락하고 5 행 소스의 각 행을 3 단계의 해당 행에 결합한 다음 결과 행을 1 단계로 리턴합니다. 예를 들어, 종업원 Atkinson의 행은 재고 클론(Stock Clerk)과 연관됩니다. - 1 단계는 다른 해시 조인을 수행하고 2 단계와 6 단계의 행 소스를 수락하고 2 단계에서 6 단계 소스의 각 행을 해당 행에 합치고 결과를 클라이언트에 반환합니다. 예를 들어, 직원 Atkinson의 행은 Shipping이라는 부서와 연관됩니다. 일부 실행 계획에서 단계는 반복적이며 다른 단계에서는 순차적입니다. 예제 3-1에 표시된 해시 조인은 순차적입니다. 데이터베이스는 조인 순서를 기반으로 전체 단계를 완료합니다. 데이터베이스는 emp_name_ix의 인덱스 범위 스캔으로 시작합니다. 인덱스에서 검색 한 ROWID를 사용하여 데이터베이스는 employees 테이블에서 일치하는 행을 읽은 다음 작업 테이블을 스캔합니다. 작업 테이블에서 행을 검색 한 후 데이터베이스는 해시 조인을 수행합니다. 실행 중에 데이터베이스는 데이터가 메모리에 없으면 디스크에서 메모리로 데이터를 읽습니다. 또한 데이터베이스는 데이터 무결성을 보장하는 데 필요한 잠금 및 래치를 가져와 SQL 실행 중 변경 사항을 기록합니다. SQL 문을 처리하는 마지막 단계는 커서를 닫는 것입니다. 3.2 How Oracle Database Processes DML 대부분의 DML 문에는 쿼리 구성 요소가 있습니다. 쿼리에서 커서를 실행하면 쿼리 결과가 결과 집합이라는 행 집합에 배치됩니다. 3.2.1 How Row Sets Are Fetched 결과 집합 행은 한 번에 한 행씩 또는 그룹으로 가져올 수 있습니다. 페치(fetch) 단계에서 데이터베이스는 행을 선택하고 쿼리에 의해 요청된 경우 행을 정렬합니다. 연속적인 각 페치는 마지막 행이 페치 될 때까지 결과의 또 다른 행을 검색합니다. 일반적으로 데이터베이스는 마지막 행을 가져올 때까지 쿼리에서 검색 할 행 수를 확인할 수 없습니다. Oracle Database는 다음에서 데이터를 검색합니다. 응답을 호출하여 데이터베이스가 읽는 행이 많을수록 수행하는 작업이 많아집니다. 일부 쿼리의 경우 데이터베이스는 가능한 한 빨리 첫 번째 행을 반환하지만 다른 경우에는 첫 번째 행을 반환하기 전에 전체 결과 집합을 만듭니다. 3.2.2 Read Consistency 일반적으로 쿼리는 Oracle Database 읽기 일관성 메커니즘을 사용하여 데이터를 검색합니다. 이 메커니즘은 쿼리에서 읽은 모든 데이터 블록이 단일 시점에 일관성을 유지하도록 합니다. 읽기 일관성은 실행 언두 데이터를 사용하여 이전 버전의 데이터를 표시합니다. 예를 들어, 조회가 전체 표 스캔에서 100개의 데이터 블록을 읽어야한다고 가정하십시오. 쿼리는 처음 10개의 블록을 처리하는 반면 다른 세션의 DML은 블록 75를 수정합니다. 제1세션이 블록(75)에 도달하면, 변경을 실현하고, 언두 데이터를 사용하여 데이터의 이전의 수정되지 않은 버전을 검색하고, 메모리에 블록(75)의 비 현행 버전을 구성한다. 3.2.3 Data Changes 데이터를 변경해야하는 DML 문은 수정 시작시 검색 기준과 일치하는 데이터 만 검색하기 위해 읽기 일관성을 사용합니다. 이후에 이러한 명령문은 현재 상태에서 존재하는 데이터 블록을 검색하고 필요한 수정을 수행합니다. 데이터베이스는 재실행 및 실행 취소 데이터 생성과 같은 데이터 수정과 관련된 다른 작업을 수행해야합니다. 3.3 How Oracle Database Processes DDL Oracle Database는 DML과 다르게 DDL을 처리합니다. 예를 들어, 테이블을 작성할 때 데이터베이스는 CREATE TABLE 문을 최적화하지 않습니다. 대신 Oracle Database는 DDL 문을 구문 분석하고 명령을 수행합니다. DDL은 데이터 딕셔너리에서 객체를 정의하는 수단이므로 DDL을 다르게 처리합니다. 일반적으로 Oracle Database는 DDL 문을 실행하기 위해 많은 재귀 SQL 문을 구문 분석하고 실행해야 합니다. 다음과 같이 테이블을 생성한다고 가정 해보십시오. CREATE TABLE mytable (mycolumn INTEGER); 일반적으로 데이터베이스는 이전 명령문을 실행하기 위해 수십 개의 재귀 문을 실행합니다. 재귀 SQL은 다음과 같은 작업을 수행합니다. • CREATE TABLE 문을 실행하기 전에 COMMIT를 실행하십시오. • 사용자 권한으로 테이블을 만들 수 있는지 확인하십시오 • 테이블이 있어야 할 테이블 공간을 결정합니다 • 테이블 공간 할당량을 초과하지 않았는지 확인하십시오 • 스키마에 같은 이름의 개체가 없는지 확인하십시오 • 테이블을 정의하는 행을 데이터 사전에 삽입합니다. • DDL 문이 성공하면 COMMIT를 발행하고 그렇지 않은 경우 ROLLBACK을 발행하십시오. | cs |
'Oracle Database SQL Tuning Guide 12c Release 2 (12.2)' 카테고리의 다른 글
| Chapter06.Generating and Displaying Execution Plans (0) | 2019.02.05 |
|---|---|
| Chapter05.Query Transformations (0) | 2019.01.01 |
| Chapter04.Query Optimizer Concepts (0) | 2019.01.01 |
| Chapter02.SQL Performance Methodology (0) | 2019.01.01 |
| Chapter01.Introduction to SQL Tuning (0) | 2019.01.01 |