v$sql_plan

■ v$sql_plan 
library cache(shared pool) 에 있는 실행계획을 표시.

select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number
and s.username = 'HR';

 

#) 실제 실행계획

select * from table(dbms_xplan.display_cursor('2sgjc8u8ha0m4', 0, 'typical'));

#) 바로 직전의 SQL문장 실행계획을 보여준다.

select * from hr.employees where employee_id = 110;

#) 바로 직전의 실행된 SQL문장

select * from table(dbms_xplan.display_cursor);

#) 종료

set autotrace off

#) hr계정에서의 실행계획 확인

select * from hr.employees where employee_id = 110;

 

# 일반 유저들이 dbms_xplan.display_cursor 를 사용하려면 권한이 필요하다. 
#) 바로 직전의 실행계획은 확인되지 않는다.

select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

#) 권한 부여
SYS@ora11g> 

grant select on v_$session to hr;
grant select on v_$sql to hr;
grant select on v_$sql_plan to hr;
grant select on v_$sql_plan_statistics to hr;
grant select on v_$sql_plan_statistics_all to hr;

#) hr계정에서의 실행계획 확인

select * from hr.employees where employee_id = 110;

#) 실제 실행 계획 확인

select * from table(dbms_xplan.display_cursor);

#) sys session 에서 확인

select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number
and s.username = 'HR';

#) hr 계정에서 확인

select * from hr.employees where employee_id = 110;

#) 전부 확인

select * from table(dbms_xplan.display_cursor(null, null,'all'));

#) 힌트 정보 확인

select * from table(dbms_xplan.display_cursor(null, null,'advanced'));

#) 각각의 block 수를 확인
-> 마지막 SQL문장의 통계정보 확인

select * from hr.employees where employee_id = 110;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

#) 힌트 사용 : gather_plan_statistics

select /*+ gather_plan_statistics */* from hr.employees where employee_id = 110;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

#) full scan 유도

select /*+ gather_plan_statistics */ * from hr.employees;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

=> join  => 예측  => 실제   =>실제처리시간

설명) 
1) Id : 각 Operation 번호
2) Operation : 실행되는 job
3) Name : Operation이 수행되는 테이블, 인덱스
4) Starts : 각 Operation을 반복 수행하느 건수 / join, 서브쿼리할 때 많이 확인함.
5) E-Rows : 각 Operation 을 수행했을 때 return 건수(예상치)
6) A-Rows : 각 Operation 을 수행했을 때 return 건수(실제값)
7) A-Time : 실제 수행시간, 누적치
8) Buffers : 각 Operation 의 logical block의 수 (전체 수행한 block의 수, physical block 수 를 포함)
9) Reads : 각 Operation의 physical block의 수 (디스크에서 읽어들이는 block의 수)
10) Writes : 각 Operation의 disk에 write한 block 수

#) 20번 부서의 사원을 조회 

select /*+ gather_plan_statistics */ * from hr.employees where department_id = 20;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

-> buffer pinning : '3번 이상' block을 access하면 block pinning 걸림.
-> latch의 점유시간이 짧아짐.


#) index scan -> full scan 으로 변경
-> oracle의 옵티마이저가 값의 분포도에 따라 full scan vs index scan 이 나은지 통계정보를 보고 판단한다. 

select /*+ gather_plan_statistics */ * from hr.employees where department_id = 50;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

#) index scan 으로 하고 싶다면? -> 힌트로 변경

select /*+ gather_plan_statistics index (e EMP_DEPARTMENT_IX) */ * \
from hr.employees e where department_id = 50;

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      1 |     45 |     45 |00:00:00.01 |       9 | => 누적값 / buffer pinning 현상으로 인해 45번보다 5번으로 줄어들어 i/o 수가 줄어들었다.

#) random i/o가 줄이는게 성능이 좋아지는 것이다.