SQL Plan 오류 없이 조회하기

Oracle 2023. 9. 21. 18:58

검색해보면 온통 다음 방식으로 플랜을 조회하는 것으로 설명되어 있다.

EXPLAIN PLAN FOR ${SELECT STMT}
SELECT * FROM table (DBMS_XPLAN.DISPLAY);

그러나 실제로는 커서가

검색해보면 온통 다음 방식으로 플랜을 조회하는 것으로 설명되어 있다.

EXPLAIN PLAN FOR ${SELECT STMT};
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST');

그러나 다음과 같은 오류가 발생하면서 플랜이 나오지 않는 경우가 발생할 수 있다.

  NOTE: cannot fetch plan for SQL_ID: ***********, CHILD_NUMBER: 0
    Please verify value of SQL_ID and CHILD_NUMBER;
    It could also be that the plan is no longer in cursor cache (check v$sql_plan)

set serverouput=on 인 경우, 쿼리 수행 이후에 DBMS_OUTPUT 버퍼에 출력할 내용이 있는지

sqlplus가 추가적인 call을 하는 단계가 끼어들면서 이런 현상이 발생하는데,

set serveroutput=off 하면 일반적으로 이런 증상을 해결할 수 있다.

이런 현상을 해결하기 위해 DISPLAY_CURSOR() 대신, DISPLAY() 를 사용하라거나

v$sql_plan에서 방금 수행한 SQL을 찾아 cursor와 child_number를 일일이 찾는 방식을 추천하는 글도 많은데

이럴 때, 다음과 같이 loop로 묶어버리면 중간에 다른 동작이 개입될 일 없이

항상 플랜을 조회할 수 있다.

--alter session set current_schema=________;
set lines 4000
set pages 0 embedded on
-- execute dbms_session.set_sql_trace(true);
set serveroutput on size unlimited;
alter session set STATISTICS_LEVEL='ALL';
--alter session set optimizer_use_invisible_indexes=true;
--alter session set "_OPTIM_PEEK_USER_BINDS"=true;

-- var v1 varchar2(20);
-- exec :v1 := '________';

begin
for SQLQuery in (
-- /*+ GATHER_PLAN_STATISTICS */

${플랜 조회할 SQL문을 여기에, 맨 끝에 ";" separator 없이}

)
loop
null;
end loop;

-- dbms_xplan query
for SQLPlan in
(select plan_table_output pto from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last +peeked_binds -projection -alias -outline +remote')))
loop
dbms_output.put_line(SQLPlan.pto);
end loop;
end;
/