Chapter06.Generating and Displaying Execution Plans

6.1 Introduction to Execution Plans
Oracle Database가 명령문을 실행하는 데 사용하는 단계의 조합은 실행 계획입니다. 
각 단계는 데이터 행을 데이터베이스에서 실제로 검색하거나 명령을 행하는 사용자를 위해 준비합니다. 
실행 계획에는 명령문이 액세스하는 각 테이블에 대한 액세스 경로와 해당 조인 메소드로 
테이블의 순서(조인 순서)가 포함됩니다.
 
6.2 About Plan Generation and Display
EXPLAIN PLAN 문은 최적화 프로그램이 SELECT, UPDATE, INSERT 및 DELETE 문에 대해 선택하는 실행 계획을 표시합니다.
 
6.2.1 About the Plan Explanation
명령문 실행 계획은 데이터베이스가 명령문을 실행하기 위해 수행하는 일련의 조작입니다.
 
행 원본 트리가 실행 계획의 핵심입니다.
트리에는 다음 정보가 표시됩니다.
• 문에서 참조하는 테이블의 순서 지정
• 문에 언급 된 각 테이블에 대한 액세스 방법
• 문에서 조인 작업의 영향을받는 테이블에 대한 조인 방법
• 필터, 정렬 또는 집계 같은 데이터 연산
행 원본 트리 외에도 계획 테이블에는 다음에 대한 정보가 들어 있습니다.
• 각 작업의 비용 및 카디널리티와 같은 최적화
• 파티션 된 파티션 (예: 액세스 된 파티션 세트)
• 조인 입력의 분산 방법과 같은 병렬 실행
EXPLAIN PLAN 결과를 사용하여 옵티마이저가 중첩 된 루프 조인과 같은 특정 실행 계획을 선택했는지 여부를 판별 할 수 있습니다. 
또한 결과는 옵티마이저가 해시 조인 대신 중첩 된 루프 조인을 선택하는 이유와 같은 옵티 마이저 결정을 이해하는 데 도움이 됩니다.

6.2.2 Why Execution Plans Change
실행 계획은 기본적인 옵티마이저 입력이 변경 될 때 변경 될 수 있습니다.
EXPLAIN PLAN 출력은 명령이 실행 될 때 데이터베이스가 SQL을 실행하는 플랜을 보여줍니다.
이 플랜은 실행 환경 및 Explain 플랜 환경의 차이로 인해 SQL 문이 사용하는 실제 실행 계획과 다를 수 있습니다.
 
Note:
실행 계획 변경으로 인해 발생할 수 있는 SQL 성능 저하를 피하려면 SQL 계획 관리 사용을 고려하십시오.
 
6.2.2.1 Different Schemas
스키마는 여러 가지 이유로 다를 수 있습니다. 주된 이유는 다음과 같습니다.
• 실행 및 설명 계획은 다른 데이터베이스에서 수행됩니다.
• 명령문을 설명하는 사용자는 명령문을 실행하는 사용자와 다릅니다.
두 명의 사용자가 동일한 데이터베이스의 다른 개체를 가리켜 다른 실행 계획을 초래할 수 있습니다.
• 두 작업 사이의 스키마 변경 (종종 인덱스 변경).
 
6.2.2.2 Different Costs
스키마가 동일하더라도 옵티마이저는 비용이 다른 경우 다른 실행 계획을 선택할 수 있습니다.
비용에 영향을 미치는 몇 가지 요소는 다음과 같습니다.
• 데이터 볼륨 및 통계
• 변수 유형 및 값 바인딩
• 전역 또는 세션 수준에서 초기화 매개 변수 설정
 
6.2.3 Guideline for Minimizing Throw-Away 
Explain plan을 검토하면 다음과 같은 경우에 throw-away을 찾을 수 있습니다.
• 전체 스캔
• 비 선택 범위 스캔
• 후기 술어 필터
• 잘못된 조인 순서
• 후기 필터 조작
예제 6-1 (6-3 페이지)의 계획에서 마지막 단계는 76563 번 실행되고, 11432983 행에 액세스하고, 
99%를 버리고, 76563 행을 유지하는 매우 비 선택적 범위 스캔입니다. 
왜 76323 행만 필요하다는 것을 깨닫기 위해 11432983 행에 액세스합니까?
보기 6-1 Explain Plan에 Throw-Away 찾기
 
6.2.4 Guidelines for Evaluating Execution Plans
실행 계획 작업만으로는 잘 조정 된 명령문과 성능이 좋지 않은 명령문을 구별 할 수 없습니다.
예를 들어, 명령문이 인덱스를 사용한다는 것을 나타내는 EXPLAIN PLAN 출력이 
반드시 명령문이 효율적으로 실행된다는 것을 의미하지는 않습니다.
때때로 색인은 매우 비효율적입니다. 이 경우 다음을 검사하는 것이 좋습니다.
• 사용중인 색인의 열
• 선택도 (액세스되는 테이블의 일부)
EXPLAIN PLAN을 사용하여 액세스 플랜을 결정한 다음 나중에 테스트를 통해 이것이 최적의 플랜임을 입증하는 것이 가장 좋습니다.
계획을 평가할 때 계산서의 실제 자원 소비를 검사하십시오.
 
6.2.4.1 Guidelines for Evaluating Plans Using the V$SQL_PLAN Views
EXPLAIN PLAN 명령을 실행하고 계획을 표시하는 대신 V $ SQL_PLAN보기를 조회하여 계획을 표시 할 수 있습니다.
V$SQL_PLAN은 공유 SQL 영역에 저장된 모든 명령문에 대한 실행 계획을 포함합니다.
정의는 PLAN_TABLE과 유사합니다.
EXPLAIN PLAN에 비해 V$SQL_PLAN의 장점은 특정 명령문을 실행하는 데 사용 된 컴파일 환경을 알 필요가 없다는 것입니다. 
EXPLAIN PLAN의 경우, 명령문을 실행할 때 동일한 플랜을 얻으려면 동일한 환경을 설정해야 합니다. 
V$SQL_PLAN_STATISTICS 뷰는 출력 행 수 및 경과 시간과 같이 계획의 모든 작업에 대한 실제 실행 통계를 제공합니다. 
출력 행 수를 제외한 모든 통계는 누적됩니다. 예를 들어, 조인 조작에 대한 통계에는 두 입력에 대한 통계도 포함됩니다.
V$SQL_PLAN_STATISTICS의 통계는 STATISTICS_LEVEL 초기화 매개 변수를 ALL로 설정하여 컴파일 된 커서에 사용할 수 있습니다. 
V$SQL_PLAN_STATISTICS_ALL 뷰는 최적화 프로그램이 행 수와 경과 시간에 대해 제공 한 예상치를 나란히 비교할 수 있게 합니다.
이 뷰는 모든 커서에 대한 V$SQL_PLAN 및 V$SQL_PLAN_STATISTICS 정보를 결합합니다.
 
6.2.5 EXPLAIN PLAN Restrictions
Oracle Database는 날짜 바인드 변수의 암시 적 유형 변환을 수행하는 명령문에 대해 EXPLAIN PLAN을 지원하지 않습니다.
일반적으로 바인드 변수를 사용하면 EXPLAIN PLAN 출력이 실제 실행 계획을 나타내지 않을 수 있습니다. 
SQL문 텍스트에서 TKPROF는 바인드 변수의 유형을 판별 할 수 없습니다.
유형이 CHARACTER라고 가정하고, 그렇지 않으면 오류 메시지를 제공합니다.
SQL 문에 적절한 유형 변환을 넣으면 이 제한을 피할 수 있습니다.
 
6.2.6 Guidelines for Creating PLAN_TABLE
PLAN_TABLE은 자동으로 글로벌 임시 테이블의 공용 동의어로 작성됩니다.
이 임시 테이블은 모든 사용자에 대해 EXPLAIN PLAN 문의 결과를 보유합니다.
PLAN_TABLE은 EXPLAIN PLAN 문이 실행 계획을 설명하는 행을 삽입하는 기본 샘플 출력 테이블입니다.
PLAN_TABLE 테이블이 각 사용자마다 자동으로 설정되는 동안 SQL 스크립트 catplan.sql을 사용하여 수동으로 
전역 임시 테이블과 PLAN_TABLE 동의어를 작성할 수 있습니다.
이 스크립트의 이름과 위치는 운영 체제에 따라 다릅니다.
UNIX 및 Linux에서이 스크립트는 $ORACLE_HOME/rdbms/admin 디렉토리에 있습니다.
예를 들어 SQL*Plus 세션을 시작하고 SYSDBA 권한으로 연결 한 후 다음과 같이 스크립트를 실행하십시오.
@$ORACLE_HOME/rdbms/admin/catplan.sql
열이 변경 될 수 있기 때문에 데이터베이스 버전을 업그레이드 한 후 로컬 PLAN_TABLE 테이블을 
삭제하고 다시 작성하는 것이 좋습니다.
이로 인해 테이블을 지정하는 경우 스크립트가 실패하거나 TKPROF가 실패 할 수 있습니다.
PLAN_TABLE이라는 이름을 사용하지 않으려면 catplan.sql 스크립트를 실행 한 후 새 동의어를 작성하십시오.
예 :
CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$
 
6.3 Generating Execution Plans
EXPLAIN PLAN.을 사용하면 옵티마이 저가 SQL 문에 대해 선택한 실행 계획을 조사 할 수 있습니다.
명령문이 실행되면 옵티마이 저는 실행 계획을 선택하고 계획을 설명하는 데이터를 데이터베이스 테이블에 삽입합니다.
EXPLAIN PLAN 문을 발행 한 다음 출력 테이블을 조회하십시오.
 
6.3.1 Executing EXPLAIN PLAN for a Single Statement
EXPLAIN PLAN 문을 사용하는 기본 사항은 다음과 같습니다.
• SQL 스크립트 CATPLAN.SQL을 사용하여 스키마에 PLAN_TABLE이라는 샘플 출력 테이블을 생성합니다.
• SQL 문 앞에 EXPLAIN PLAN FOR 절을 포함시킵니다.
• EXPLAIN PLAN 문을 실행 한 후 Oracle Database에서 제공하는 스크립트 또는 패키지를 사용하여 
가장 최근의 계획 테이블 출력을 표시하십시오.
• EXPLAIN PLAN 출력의 실행 순서는 가장 오른쪽으로 들여 쓰기 된 줄로 시작됩니다.
다음 단계는 해당 행의 상위입니다.
두 줄을 똑같이 들여 쓰면 정상적으로 맨 위 줄이 먼저 실행됩니다.
 
Note:
-이 장의 EXPLAIN PLAN 출력 테이블은 utlxpls.sql 스크립트와 함께 표시됩니다.
-이 장의 EXPLAIN PLAN 출력의 단계는 시스템에 따라 다를 수 있습니다.
옵티마이저는 데이터베이스 구성에 따라 다른 실행 계획을 선택할 수 있습니다.
 
SQL 문을 설명하려면 명령문 바로 앞에 EXPLAIN PLAN FOR 절을 사용하십시오.
예:
EXPLAIN PLAN FOR
SELECT last_name FROM employees;
 
이것은 PLAN_TABLE 테이블에 대한 계획을 설명합니다.
그런 다음 PLAN_TABLE에서 실행 계획을 선택할 수 있습니다.
 
6.3.2 Executing EXPLAIN PLAN Using a Statement ID
다중 명령문을 사용하면 명령문 식별자를 지정하고이를 사용하여 특정 실행 계획을 식별 할 수 있습니다.
SET STATEMENT ID를 사용하기 전에 해당 명령문 ID에 대한 기존 행을 제거하십시오.
예 6-2에서 st1은 명령문 식별자로 지정됩니다.
예 6-2 STATEMENT ID 절과 함께 EXPLAIN PLAN 사용
EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR
SELECT last_name FROM employees;
 
6.3.3 Directing EXPLAIN PLAN Output to a Nondefault Table
다음 문과 같이 INTO 절을 지정하여 다른 테이블을 지정할 수 있습니다.
EXPLAIN PLAN
INTO my_plan_table FOR
SELECT last_name FROM employees;

다음 문과 같이 INTO 절을 사용할 때 문 ID를 지정할 수 있습니다.
EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table FOR
SELECT last_name FROM employees;
 
6.4 Displaying PLAN_TABLE Output
계획을 설명했으면 Oracle Database에서 제공하는 다음 SQL 스크립트 또는 PL/SQL 패키지를 사용하여 
가장 최근의 계획 테이블 출력을 표시하십시오.
• UTLXPLS.SQL
이 스크립트는 일련의 처리를 위해 계획 테이블 출력을 표시합니다.
예 6-4는 UTLXPLS.SQL 스크립트를 사용할 때 출력되는 계획 테이블의 예입니다.
• UTLXPLP.SQL
이 스크립트는 병렬 실행 열을 포함하여 계획 테이블 출력을 표시합니다.
• DBMS_XPLAN.DISPLAY 테이블 함수
이 함수는 계획 테이블 출력을 표시하는 옵션을 허용합니다.
다음을 지정할 수 있습니다.
- PLAN_TABLE과 다른 테이블을 사용중인 경우 계획 테이블 이름
- EXPLAIN PLAN으로 명령문 ID를 설정 한 경우 명령문 ID
- 세부 수준을 결정하는 형식 옵션 : 기본, 직렬, 일반 및 모두
DBMS_XPLAN을 사용하여 PLAN_TABLE 출력을 표시하는 예는 다음과 같습니다.
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
 
6.4.1 Displaying an Execution Plan: Example
예 6-3은 EXPLAIN PLAN을 사용하여 ID가 103보다 작은 직원의 employee_id, job_title, salary 및 department_name을 
선택하는 SQL 문을 검사합니다.
 
Example 6-3 Using EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;
 
Example 6-4 EXPLAIN PLAN Output
다음 출력 테이블은 예제 6-3에서 SQL 문을 실행하기 위해 옵티마이 저가 선택한 실행 계획을 보여줍니다.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
 
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3 | 189 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN | | 3 | 141 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|*6 | SORT JOIN | | 3 | 60 | 3 (34)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |
|*8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
|*9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
|10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."JOB_ID"="J"."JOB_ID")
filter("E"."JOB_ID"="J"."JOB_ID")
8 - access("E"."EMPLOYEE_ID"<103)
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 
6.4.2 Customizing PLAN_TABLE Output
명령문 ID를 지정한 경우 PLAN_TABLE을 조회하는 사용자 고유의 스크립트를 작성할 수 있습니다.
예 :
• ID = 0으로 시작하고 STATEMENT_ID가 주어집니다.
• CONNECT BY 절을 사용하여 트리를 부모에서 하위로 이동합니다. 
조인 키는 STATEMENT_ID = PRIOR STATEMENT_ID 및 PARENT_ID = PRIOR ID입니다.
• 하위 열을 들여 쓰기 위해 의사 열 LEVEL (CONNECT BY와 연결됨)을 사용합니다.
SELECT cardinality "Rows", lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'st1'
ORDER BY id;
 
Rows Plan
------- ----------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL EMPLOYEES
 
행 컬럼의 NULL은 옵티마이 저가 테이블에 대한 통계를 가지고 있지 않음을 나타냅니다.
표를 분석하면 다음과 같이 표시됩니다.
 
Rows Plan
------- ----------------------------------------
16957 SELECT STATEMENT
16957 TABLE ACCESS FULL EMPLOYEES
 
COST를 선택할 수도 있습니다. 이는 실행 계획을 비교하거나 최적화 프로그램이 다른 실행 계획을 
선택하는 이유를 이해하는 데 유용합니다.
 
Note :
이러한 단순화 된 예제는 재귀 SQL에는 유효하지 않습니다.