실행계획확인

1. DISPLAY_CURSOR

--힌트
/+gather_plan_statistics/

--조회하기
SELECT * FROM TABLE (dbms_xplan.display_cursor(sql_id, child_number, 'advanced allstats last'));

--SQL_ID, CHILD_NUMBER찾기
select sql_id,
child_number,
sql_text
PARSING_SCHEMA_NAME "USER",
PLAN_HASH_VALUE,
OPTIMIZER_MODE,

parse_calls,
USERS_OPENING,
USERS_EXECUTING,
loads,
executions,
invalidations,
decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,
FIRST_LOAD_TIME,
LAST_LOAD_TIME
from v$sql
where sql_text like '%vsql%';

2. AUTOTRACE사용

autotrace사용.
set autotrace on
sql 실행
set autotrace off