출처 : http://blog.naver.com/onlywin7788?Redirect=Log&logNo=140155221360
출처 : http://cafe.naver.com/ocmkorea/book2025209/9365
/*
--> 방식 1. (이 sql문은 이 방식임)
1. 테이블 전체 목록 조회
SQL> select * from tab;
2. 수행한 쿼리의 Plan을 조회
SQL> @xplan.sql
3. 정확한 통계를 위해 힌트 실행
SQL> select /*+ gather_plan_statistics */ * from tabs;
* 오타 나지 않도록 주의함.
--> 방식 2
1. 실행시 Plan 수집
SQL> EXPLAIN FOR
SELECT * FROM TAB;
2. 실제 정보 조회
SQL> select * from tab(dbms_xplan.display());
or
SQL> select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))
/
--> 인자 정보
dbms_xplan.display_cursor(인자1, 인자2, 인자3);
인자1 : plan table 명을 입력하며, 따로 지정하지 않았을 때는 기본으로 'PLAN_TABLE'이다.
인자2 : Execution Plan시 Set STATEMENT_ID를 지정한 경우 이를 불러올 수 있다.
값이 Null일 경우 마지막에 실행된 문장을 불러온다.
인자3 : 출력 포맷 형태 지정한다.
--> 세번째 인자의 포맷 정보
1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller임.
적용하지 않더라도 자동으로 기본값으로 적용된다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
basic : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.
typical : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.
출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Imformat(Operation 별로 Access 및 Filter 정보)이다.
serial : typical과 같으나 Parallel 쿼리 사용시 관련정보가 나오지 않는다.
all : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.
advanced : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2) 세부 Format Controller : 기본 포맷정보에 의해서 표시되거나 생략되는 세부적인 포맷을 Control한다.
이 Control은 +표시로 추가되거나 - 표시로 생략이 가능하다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
alias : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.
bytes : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.
출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Information (Operation 별로 Access 및 Filter 정보)이다.
cost : typical과 같으나 Parallel쿼리 사용시 관련 정보가 나오지 않는다.
note : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.
outline : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.
parallel : Parallel 쿼리인 경우 TQ, IN-OUT, PQ Distribute 등의 정보를 Control한다.
partition : Partition Access가 포함된 경우
peeked_binds : Bind 변수의 값을 control 한다. 단, _optim_peek_user_binds 파라미터의 값이 true로 되어 있는 경우에만 해당되며 파라미터는 세션 단위로 수정이
가능하다. (Explain Plan은 출력되지 않음)
predicate : Predicate Information을 Control한다. Operation 별로 Access 및 Filter 정보를 나타낸다.
일반적인 튜닝시 가장 눈 여겨봐야 할 정보이다.
projection : Projection Information을 Control한다. Operation 별로 Select 되는 컬럼 정보를 나타낸다.
remote : DB Link를 사용할 때 Remote 쿼리의 수행 정보를 Control한다.
rows : Plan상의 E-Rows 수를 Control한다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
3) 실행통계 Format Controller : 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.
이 정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다.
왜냐하면 Explain Plan은 실제 수행되는 것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 이나 DBMS_XPLAN.DISPLAY_AWR등의 함수 수행시에도 GATHER_STATISTICS 힌트를 주거나 아니면 STATISTICS_LEVEL 파라미터를 ALL로 설정해야 출력이 가능하다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계 정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp 등)를 동시에 Control 한다.
iostats : I/O 통계정보(Buffers, Reads, Writes)를 Control 한다.
last : 실행 통계 출력시 이 Control을 명시하면 가장 마지막에 수행된 실행 통계를 출력한다.
이 Control을 명시하지 않으면 실행 통계츼 누적치를 출력하므로 주의가 필요합니다.
memstats : PGA 통계정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp등)를 동시에 Control한다.
runstats_last : iostats과 last Control 과 동일하다.
이 Control은 Oracle 10g Release 1에서만 사용할 수 있다.
runstats_tot : iostats Control과 동일하다.
이 Control은 oracle 10g Release 1 에서만 사용할 수 있다.
* runstats_last와 runstats_tot를 제외한 4가지의 Control은 Oracle 10g Release 2 이상에서만 사용할 수 있다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
4) format 사용 예제
Format중 가장 많은 정보를 출력 할 수 있는 포맷은 'advanced allstats last'이며 출력 가능한 모든 내용이 출력되게 되므로 상황이나, 자신에 맞는 포맷을 사용해야 적절함.
4-1) 쿼리 변형이 없는 단순 쿼리 튜닝의 경우(최대한 단순한 포맷형태 사용)
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last-rows + predicate');
=> 포맷을 'allstats last-rows + predicate'로 주었으므로 예측 Row 수(E-row)가 생략되고 실행통계와 Predicate Information 만 출력된다.
4-2) 쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리 블러과 힌트 정보를 추가로 출력
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last-rows + alias + outline + predicate');
=> 'allstats last-rows + alias + outline + predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
+ alias는 쿼리블럭 정보를 추가하는 것이며, + Outline는 오라클이 내부적으로 사용하는 힌트정보 출력의 의미이다.
쿼리 변형이 발생한 경우나 뷰(혹은 인라인뷰) 등을 튜닝할 경우 적합한 옵션이다.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
10046 + tkprof 와 동일한 정보를 조회는
select /*+ gather_plan_statistics */ * from tab;
select *
from table(dbms_xplan.display_cursor(null, null, 'advanced ALLSTATS LAST +alias +outline +predicate'))
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
AWR 정보를 이용
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID입력,NULL,NULL, 'ADVANCED ALLSTATS LAST'));
사용 권한 뷰
DISPLAY_CURSOR 함수 : V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL 뷰에 대한 SELECT 권한 필요
DISPLAY_AWR 함수 : DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, V$DATABASE 뷰에 대한 SELECT 권한 필요
DISPLAY_SQLSET 함수 : ALL_SQLSET_STATEMENTS, ALL_SQLSET_PLANS 뷰에 대한 SELECT 권한 필요
*/
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))
/
wget.exe
