EXPLAIN PLAN(실행계획)

☞EXPLAIN PLAN(실행계획)

● SQL문의 실행계획을 보면 비효율이 발생한 원인을 알 수 있고 좋은 실행계획이 수립될 수 있도록 하는 방법을 찾을 수 있다. 이러한 실행계획을 자주 참조하는 것이 바로 옵티마이저를 이해할 수 있는 최선책이며, SQL문장을 튜닝할 수 있는 능력을 키울 수 있는 가장 좋은 방법이다. ● 사용자들이 SQL문의 액세스 경로를 확인하고 튜닝을 할 수 있도록 SQL문을 분석하고 해석하여 실행계획을 수립한 후 실행계획을 테이블(Plan_table)에 저장하도록 해준다. ● EXPLAIN PLAN 명령은 오라클 옵티마이저에 의해서 SELECT, UPDATE, INSERT 그리고 DELETE문의 실행계획을 보여준다. ● 실행계획은 데이타를 축출하기 위해 오라클이 SQL 문장을 차례로 실행하는 작업 방법을 말한다.

♣ PLAN_TABLE의 생성

▶ PLAN_TABLE 생성 스크립트 create table PLAN_TABLE statement_id varchar2(30), -- 사용자가 지정한 제목 timestamp date, -- 실행계획이 수립된 날짜와 시간 remarks varchar2(80), -- 사용자가 부여한 주석(comments) operation varchar2(30), -- AND-EQUAL,INDEX, SORT 등과 같은 실행 연산자 options varchar2(30), -- BY ROWID, JOIN, FULL 등과 같은 실행 옵션 object_node varchar2(128), -- 사용한 데이타베이스 링크 object_owner varchar2(30), -- 객체를 생성한 소유자 object_name varchar2(30), -- 테이블, 인덱스, 클러스터 등의 객체의 이름 object_instance numeric, -- SQL문의 FROM절에 기술된 객체를 좌에서 우로 부여한 번호 object_type varchar2(30), -- UNIQUE, NON-UNIQUE INDEX 등의 객체의 종류 optimizer varchar2(255), -- CHOOSE, FIRST_ROW 등의 현재의 옵티마이져 모드 search_columns numeric, -- 현재 사용하지 않음 id numeric, -- 수립된 각 실행단계에 붙여진 일련번호 parent_id numeric, -- 부모단계의 일련번호 position numeric, -- 부모 ID를 가지고 있는 자식 ID간의 처리순 cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), other long); -- 다른 필요한 텍스트를 저장하기 위한 필드

♣ EXPLAIN PLAN 명령의 사용

▶ EXPLAIN PLAN문을 사용하려면 먼저 PLAN_TABLE이라는 테이블을 생성한다. ■대부분은 Oracle_Home_directory/rdbms/admin/utlxplan.sql이라는 스크립트를 실행한다. ■ 이 테이블(PLAN_TABLE)과 동일한 칼럼을 가지는 별도의 테이블을 만들어서 사용해도 된다.(INTO문에 사용) ▶ PLAN_TABLE 테이블을 생성했다면 수행속도를 향상시키고 동일한 Statement_id가 생기지 않도록 다음과 같은 인덱스를 생성시킨다.(생략가능) SQL>CREATE UNIQUE INDEX PLAN_INDEX ON PLAN_TABLE(statement_id, id); ▶ EXPLAIN PLAN 명령의 문법 EXPLAIN PLAN [SET STATEMENT_ID = 'text'] [INTO [schema.]table[@dblink]] FOR SQL_statement STATEMENT_ID ='text' 30자 이내로 사용자가 부여할 수 있는 해당 실행문의 제목 INTO table 출력물을 지정할 테이블 이름. PLAN_TABLE을 사용하지 않고 사용자가 생성한 테이블에 저장하고자 하는 경우에만 사용 FOR SQL_statement 실행계획을 세우고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)을 기술한다. ▶ EXPLAIN PLAN 명령을 실행 EXPLAIN PLAN SET STATEMENT_ID='aa' FOR SELECT ENAME, SAL FROM EMP, DEPT WHERE emp.deptno=dept.deptno AND NOT EXISTS( SELECT * FROM EMP WHERE sal<1000); Explained. ▶ SELECT 문으로 실행계획을 본다. SQL> SELECT operation, options, object_name, id, parent_id, position 2 FROM plan_table 3 WHERE statement_id='aa' 4 ORDER BY id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION ------------------- --------------- ----------------- ------- -------------- ------------- SELECT STATEMENT 0 40 FILTER 1 0 1 NESTED LOOPS 2 1 1 TABLE ACCESS FULL EMP 3 2 1 TABLE ACCESS FULL DEPT 4 2 2 TABLE ACCESS FULL EMP 5 1 2 ▶ 또는, PLAN_TABLE을 SELECT하는 아래의 SQL문을 만들어서 실행계획을 볼 수 있다. SELECT lpad(operation, length(operation)+2*(level-1))|| decode(id,0,'Cost Estimate:'||decode(position,'0', 'N/A',position), null)||''||options|| decode(object_name,null,null,':')||rpad(object_owner, length(object_name)+1,',')||object_name|| decode(object_type,'UNIQUE',' (U) ', 'NIN_UNIQUE', '(NU)',null)||decode(object_instance,null,null,'('||object_instance||')') FROM PLAN_TABLE START with ID=0 AND STATEMENT_ID='&&id' CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='&&id'; ▶ 실행시킨 EXPLAIN PLAN 문의 STATEMENT_ID=에서 부여한 제목을 id에 지정해 주고 다음과 같은 실행하면 실행계획이 생성된다. SQL>DEF ID=aa SQL>@plan old 9: START with ID=0 AND STATEMENT_ID='&&id' new 9: START with ID=0 AND STATEMENT_ID='aa' old 10: CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='&&id' new 10: CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='aa' LPAD(OPERATION,LENGTH(OPERATION)+2*(LEVEL-1))||DECODE(ID,0,'COSTESTIMATE:'||DECO ------------------------------------------------------------------------------------- SELECT STATEMENTCost Estimate:54 FILTER HASH JOIN TABLE ACCESSFULL:SCOTTDEPT(2) TABLE ACCESSFULL:SCOTEMP(1) TABLE ACCESSFULL:SCOTEMP(3) 6 rows selected. SQL>

♣ OPERATION과 OPTION의 추가적인 설명

OPERATION OPTION 설명 AGGREGATE GROUP BY 그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추출되도록 하는 처리 AND-EQUAL 인덱스 머지를 이용하는 경우 중복 제거, 단일 인덱스 칼럼을 사용하는 경우 CONNECT BY CONNECT BY를 사용하여 트리구조로 전개 CONCATENATION 단위 액세스에서 추출한 로우들의 합집합을 생성(UNION-ALL) COUNTING 테이블의 로우 수를 센다. FILTER 선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업 FIRST ROW 조회 로우 중에 첫 번째 로우만 추출한다. FOR UPDATE 선택된 로우에 락(LOCK)을 지정한다. INDEX* UNIQUE SCAN UNIQUE 인덱스를 사용(단 한 개의 로우를 추출) RANGE SCAN NON-UNIQUE한 인덱스를 사용(한개 이상의 로우) RANGE SCAN DESCENDING RANGE SCAN하고 동일하지만 역순으로 로우를 추출 INTERSECTION 교집합의 로우를 추출한다.(같은 값이 없다) MERGE JOIN+ 먼저 자신의 조건만으로 액세스한 후 각각을 소트하여 머지해 가는 조인 OUTER 위와 동일한 방법으로 OUTER JOIN을 한다. MINUS MINUS 함수를 사용한다. NESTED LOOPS+ 먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인 OUTER 위와 동일한 방법으로 OUTER JOIN을 한다. PROJECTION 내부적인 처리의 일종 REMOTE 다른 분산 데이타베이스에 있는 객체를 추출하기 위해 데이타베이스 링크를 사용하는 경우 SEQUENCE 시퀀스를 액세스한다. SORT AGGREGATE 그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추출되도록 하는 처리 UNIQUE 같은 로우를 제거하기 위한 소트 GROUP BY 액세스 결과를 GROUP BY하기 위한 소트 JOIN 머지 조인을 하기 위한 소트 ORDER BY ORDER BY를 위한 소트 TABLE ACCESS* FULL 전체 테이블 스캔 CLUSTER 클러스터 액세스 HASH 키값에 대한 해쉬 알고리즘을 사용 BY ROWID ROWID를 이용하여 테이블을 추출 UNION 두 집합의 합집합을 구한다.(중복없음) 항상 전체 범위를 구한다. VIEW 어떤 처리에 의해 생성되는 가상의 집합(뷰)에서 추출한다.(주로 서브쿼리에 의해서 수행된 결과) * access methods + join operations