V$SQL_MONITOR에 SQL 조회 안 될 시 해결 방법

배치 튜닝시 DBMS_SQLTUNE 패키지로 실시간 SQL 모니터링하여 "실행 계획 및 데이터 분배 점검"을 한다.

 

select dbms_sqltune.report_sql_monitor(sql_id=>'6f4131zagk5rg') from dual;

 

그런데 패키지 이용시 "SQL Monitoring Report" 문구만 출력되는 경우가 있다. 

이때 아래 1가지 설정 변경 후 해결이 되었다. 

 

1. _sqlmon_max_planlines  Parameter 수치 변경

   :  Parmeter 의미는 실시간 SQL 모니터링 될 수 있는 SQL의 "실행 계획 라인"의 최대 개수이다. 오라클 디폴트 값 300.

개인 경험으로는 금융권 배치 튜닝시 실행 플랜이 대부분 300 라인수를 넘어서 v$sql_monitor로 조회시 SQL의 실행플랜

을 획득할수 없었다. 그래서 아래와 같이 세션에 한해 "실행 계획 라인"의 최대 개수를 조정하여 원하는 실행 플랜을 얻었다.

ALTER SESSION SET "_SQLMON_MAX_PLANLINES" = 1000;

 

 

2. 테스트 해보기

[ 실행 계획 라인 수 확인 ]

SELECT COUNT(*) FROM V$SQL_PLAN WHERE SQL_ID ='0hkm373ca6hsj';

 

[   테스트 1 ]

 - SQL 실행계획 : 226줄  /  V$SQL_MONITOR 조회 됨

SELECT /*+ PARALLEL(2) Boty 1 */ -- 실행플랜 라인수 : 226, SQL_ID : 5q386yf972bn4
       COUNT(*)
  FROM DBA_OBJECTS  A 
     , DBA_OBJECTS  B 
     , DBA_OBJECTS  C 
WHERE A.OWNER = B.OWNER 
  AND A.OBJECT_NAME = B.OBJECT_NAME  
  AND A.OWNER = C.OWNER 
  AND A.OBJECT_NAME = C.OBJECT_NAME;

 

[  테스트 2 ]

 - SQL 실행계획 : 301줄  /  V$SQL_MONITOR 조회 됨

SELECT /*+ PARALLEL(2) Boty 2 */-- 실행플랜 라인수 : 301, SQL_ID : 5h9cz6mxm5rfn
       COUNT(*)
  FROM DBA_OBJECTS  A 
     , DBA_OBJECTS  B 
     , DBA_OBJECTS  C 
     , DBA_OBJECTS  D      
WHERE A.OWNER = B.OWNER 
  AND A.OBJECT_NAME = B.OBJECT_NAME  
  AND A.OWNER = C.OWNER 
  AND A.OBJECT_NAME = C.OBJECT_NAME  
  AND A.OWNER = D.OWNER 
  AND A.OBJECT_NAME = D.OBJECT_NAME;

 

[  테스트 3 ]

 - 실행계획 : 304줄  /  V$SQL_MONITOR 조회 안됨

SELECT /*+ PARALLEL(2) Boty 3 */ -- 실행플랜 라인수 : 304, SQL_ID : 0hkm373ca6hsj
       COUNT(*)
  FROM DBA_OBJECTS  A 
     , DBA_OBJECTS  B 
     , DBA_OBJECTS  C 
     , DBA_OBJECTS  D      
WHERE A.OWNER = B.OWNER 
  AND A.OBJECT_NAME = B.OBJECT_NAME  
  AND A.OWNER = C.OWNER 
  AND A.OBJECT_NAME = C.OBJECT_NAME  
  AND A.OWNER = D.OWNER 
  AND A.OBJECT_NAME = D.OBJECT_NAME    
  UNION ALL
  SELECT COUNT(*) FROM DUAL

 

[ 테스트 결과 ]

- Boty 2는 301 라인임에도 불구하고 v$sql_monitor에 조회가 되었고, Boty 3은 304 라인이기 때문에 v$sql_monitor에 등록이

 되지 못했다.  301이 왜 등록이 되었는지는 모르겠으나, 실행 플랜의 최상단 SELECT STATEMENT ID = 0 은 무시해서 그런것인

 아닌지 결과론적으로 의심해본다. 302 라인수도 맞춰서 해보고 싶으나, 너무 지엽적인 내용이므로 패스! 

 넉넉하게 수치줘서 튜닝에 집중하면 된다.

 

SELECT SQL_TEXT, SQL_ID, A.*
  FROM GV$SQL_MONITOR A 
 WHERE SQL_TEXT LIKE '%Boty%'

 

Boty 3은 304라인 실행수이기 때문에 sql_monitor에 등록되지 않음