DBMS_SQLTUNE.REPORT_SQL_MONITOR 실행 시 ORA-27196, ORA-06512 ERROR가 발생하면서 리포트 출력 안될 경우 해결 방안 (12cR2 미만 버전에서 발생)

DBMS_SQLTUNE.REPORT_SQL_MONITOR 실행 시 ORA-27196, ORA-06512 ERROR가 발생하면서 리포트 출력 안될 경우가 있음 

 

아래의 절차대로 수행 시 SQL MONITOR REPORT 출력이 불가능했던 SQL문의 REPORT가 출력될 수 있음 

 

<아래>

--1. 31156 트레이스 활성화 (해당 세션에서만 활성화 시키는 것임!)
ALTER SESSION SET events '31156 trace name context forever, level 0x400'; 

--2. SQL문 실행 
SELECT /*+ MONITOR */ /* KOLEE_TEST_20250526_1 */
       SUBSTR(JOIN_DT, 1, 4) AS JOIN_YYYY
     , COUNT(*) AS CNT
  FROM TUNER.TB_CUST
 GROUP BY SUBSTR(JOIN_DT, 1, 4)
 ORDER BY CNT DESC
;
--> MONITOR 힌트를 사용하여 _sqlmon_threshold 파라미터의 값과는 상관없이 SQL 모니터에 등록함 
--> _sqlmon_threshold 파라미터는 SQL MONITOR 추적 기준임 (디폴트값 5 --> 실행 시간이 5초 이상시 모니터링)

--3. 방금 실행한 SQL문의 SQL_ID 찾기
SELECT A.SQL_ID, A.PLAN_HASH_VALUE, A.CHILD_NUMBER, SUBSTR(A.SQL_TEXT, 1, 50) AS SQL_TEXT_50
  FROM V$SQL A 
 WHERE 1=1 
   AND A.SQL_TEXT LIKE '%KOLEE_TEST_20250526_1%'  
   AND A.SQL_TEXT NOT LIKE '%V$SQL%'  
   AND A.SQL_TEXT NOT LIKE '%EXPLAIN PLAN%'  
;

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER SQL_TEXT_50                                                                                          
------------- --------------- ------------ ---------------------------------------------------------------------------------------------------- 
3509ydhmfxrhj      3116015395            0 SELECT /*+ MONITOR */ /* KOLEE_TEST_20250526_1 */                                                   
;

--4. SQL MONITOR 리포트 출력
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'3509ydhmfxrhj', EVENT_DETAIL=>'YES') FROM DUAL;
 
SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR */ /* KOLEE_TEST_20250526_1 */ SUBSTR(JOIN_DT, 1, 4) AS JOIN_YYYY , COUNT(*) AS CNT FROM TUNER.TB_CUST GROUP BY SUBSTR(JOIN_DT, 1, 4) ORDER BY CNT DESC

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                            
 Instance ID         :  1                                          
 Session             :  TUNER (1654:8425)                          
 SQL ID              :  3509ydhmfxrhj                              
 SQL Execution ID    :  16777216                                   
 Execution Started   :  05/26/2025 17:13:35                        
 First Refresh Time  :  05/26/2025 17:13:35                        
 Last Refresh Time   :  05/26/2025 17:13:35                        
 Duration            :  .161707s                                   
 Module/Action       :  Orange for ORACLE DBA /6.0.2 (Build:133,S) 
 Service             :  PRDB                                       
 Program             :  OrangeMain.exe                             
 Fetch Calls         :  2                                          

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.16 |    0.00 |     0.16 |     2 |  18219 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3116015395)
======================================================================================================================================
| Id |       Operation       |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                       |         | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
======================================================================================================================================
|  0 | SELECT STATEMENT      |         |         |      |         1 |     +0 |     1 |       10 |     . |          |                 |
|  1 |   SORT ORDER BY       |         |      1M | 5052 |         1 |     +0 |     1 |       10 |  2048 |          |                 |
|  2 |    SORT GROUP BY      |         |      1M | 5052 |         1 |     +0 |     1 |       10 |  2048 |          |                 |
|  3 |     TABLE ACCESS FULL | TB_CUST |      1M | 4992 |         1 |     +0 |     1 |       1M |     . |          |                 |
====================================================================================================================================== 

--5. 31156 트레이스 비활성화 
ALTER SESSION SET events 'alter session set events '31156 trace name context off'; 

--끝

 

아래의 버그와 관련이 있음 

Bug 22843562 - IMPORT OF A XML FILE WITH A COMMENT AT THE END FAILS WITH ORA-27163 (12cR2에서 Fixed)

 

참고 

https://forums.oracle.com/ords/r/apexds/community/q?question=getting-out-of-memory-error-for-xmltype-datatype-in-oracle-5099

Getting Error ORA-27163: out of memory for XMLTYPE Datatype

https://stackoverflow.com/questions/20728790/out-of-process-memory-error-when-writing-large-xmlfile-using-oracle-xmldb