오라클 19c ORA-04031 에러를 발생시키는 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c ORA-04031 에러를 발생시키는 방법

ORA-04031 메세지는 shared pool이 부족할때 발생하는 치명적인 메세지임

운영DB에서 이 메세지가 발생한 경우 정말 shared pool 이 부족해서 발생하거나 또는 버그로 인해 발생하기도함

정말 부족해서 발생한 경우 어플리케이션에서 실행한 쿼리가 정상적으로 실행되지 못해 장애로 이어질수 있음

 

 

ORA-04031 에러를 발생시키는 방법을 설명함(테스트 서버에서만 실행해야함)

 

 

open_cursors 파라미터를 거의 최대치로 변경

SQL> alter system set open_cursors = 65533 scope=both
 
System altered.

 

 

샘플 테이블 생성

SQL> 
create table sample_t (id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
)
nologging;

 

 

샘플 데이터 삽입(5회 실행하여 50만으로 생성함)

SQL> 
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
   COMMIT;
END;
/

 

 

아래 스크립트 실행

SQL>
DECLARE
   l_query VARCHAR2(1000);
   l_handle NUMBER;
BEGIN
   FOR i IN 1..50000 LOOP
      l_query := 'SELECT count(*) FROM sample_t WHERE id1 = ' || i;
      l_handle := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(l_handle, l_query, DBMS_SQL.NATIVE);
   END LOOP;
END;
/

 

 

alert log 모니터링

$ vi alert.log
2023-01-25T05:27:08.649787+09:00
Errors in file /app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_ora_29913.trc  (incident=130027):
ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^f5f698ce","opn: qkexrInitOpn")
Incident details in: /app/oracle/diag/rdbms/orcl19/ORCL19/incident/incdir_130027/ORCL19_ora_29913_i130027.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-01-25T05:27:08.678469+09:00
Errors in file /app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_cjq0_8565.trc  (incident=129915):
ORA-04031: unable to allocate 152 bytes of shared memory ("shared pool","select i.obj#,i.ts#,i.file#,...","SQLA^d892ecef","qeeOpt: qeesCreateOpt")
Incident details in: /app/oracle/diag/rdbms/orcl19/ORCL19/incident/incdir_129915/ORCL19_cjq0_8565_i129915.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

정상적으로 ORA-04031 메세지가 발생함

 

 

trace 로그 확인

$ vi /app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_ora_29913.trc
Trace file /app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_ora_29913.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /app/oracle/product/19.0.0
System name:    Linux
Node name:      ORACLE19
Release:        4.14.35-2047.509.2.2.el7uek.x86_64
Version:        #2 SMP Mon Nov 15 15:45:36 PST 2021
Machine:        x86_64
Instance name: ORCL19
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 29913, image: oracle@ORACLE19 (TNS V1-V3)
 
 
*** 2023-01-25T05:27:08.523608+09:00
*** SESSION ID:(189.20808) 2023-01-25T05:27:08.523662+09:00
*** CLIENT ID:() 2023-01-25T05:27:08.523667+09:00
*** SERVICE NAME:(SYS$USERS) 2023-01-25T05:27:08.523672+09:00
*** MODULE NAME:(SQL*Plus) 2023-01-25T05:27:08.523677+09:00
*** ACTION NAME:() 2023-01-25T05:27:08.523682+09:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-01-25T05:27:08.523686+09:00
 
AUTO MEM: PGA get fail 65 for 24, 54, 1, 16777216, 754974720, 402653184, 24, 1157627904, 3
 
*** 2023-01-25T05:27:08.651120+09:00
2023-01-25T05:27:08.651070+09:00
Incident 130027 created, dump file: /app/oracle/diag/rdbms/orcl19/ORCL19/incident/incdir_130027/ORCL19_ora_29913_i130027.trc
ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^f5f698ce","opn: qkexrInitOpn")
 
TOC00000 - Table of contents
TOC00001 - Error Stack
TOC00002 - Dump for incident 130027 (ORA 4031)
| TOC00003 - Beginning of Customized Incident Dump(s)
| | TOC00004 - child_cursor_dump
| | TOC00005 - Dump Cursor sql_id=0sbbcuruzd66f xsc=0x7fbff825d650 cur=0x7fbff6ab63d8
| | | TOC00006 - child_cursor_dump
| | | TOC00007 - child_cursor_dump
| | | TOC00008 - child_cursor_dump
| | | TOC00009 - parent_cursor_dump
| | | | TOC00010 - cursor_sharing_diagnostics_dump
| | | TOC00011 - Starting SQL statement dump
| | | | TOC00012 - Current SQL Statement for this session (sql_id=0sbbcuruzd66f)
| | | | | TOC00013 - PL/SQL Stack
| | | | TOC00014 - Optimizer state dump
| | | | | TOC00015 - Compilation Environment Dump
| | | | | | TOC00016 - Bug Fix Control Environment

 

 

incident 로그 확인

$ vi /app/oracle/diag/rdbms/orcl19/ORCL19/incident/incdir_130027/ORCL19_ora_29913_i130027.trc
Dump file /app/oracle/diag/rdbms/orcl19/ORCL19/incident/incdir_130027/ORCL19_ora_29913_i130027.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /app/oracle/product/19.0.0
System name:    Linux
Node name:      ORACLE19
Release:        4.14.35-2047.509.2.2.el7uek.x86_64
Version:        #2 SMP Mon Nov 15 15:45:36 PST 2021
Machine:        x86_64
Instance name: ORCL19
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 29913, image: oracle@ORACLE19 (TNS V1-V3)
 
 
*** 2023-01-25T05:27:08.651848+09:00
*** SESSION ID:(189.20808) 2023-01-25T05:27:08.651866+09:00
*** CLIENT ID:() 2023-01-25T05:27:08.651871+09:00
*** SERVICE NAME:(SYS$USERS) 2023-01-25T05:27:08.651876+09:00
*** MODULE NAME:(SQL*Plus) 2023-01-25T05:27:08.651881+09:00
*** ACTION NAME:() 2023-01-25T05:27:08.651886+09:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-01-25T05:27:08.651908+09:00
 
[TOC00000]
Jump to table of contents
Dump continued from file: /app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_ora_29913.trc
[TOC00001]
ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^f5f698ce","opn: qkexrInitOpn")
 
[TOC00001-END]
[TOC00002]
========= Dump for incident 130027 (ORA 4031) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
 
*** 2023-01-25T05:27:08.652988+09:00
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors.  This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting:  04014fff
  SGA Heap Dump Interval=3600 seconds
  Dump Interval=300 seconds
  Last Dump Time=01/25/2023 05:27:08
  Dump Count=1
Allocation request for: opn: qkexrInitOpn
  Requested from SQLA^f5f698ce, Heap: 0x72b35578, size: 128
******************************************************
HEAP DUMP heap name="sga heap"  desc=0x60145548
 extent sz=0xfe0 alt=336 het=32767 rec=9 flg=0x82 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x1 heap=(nil)
 fl2=0x64, nex=(nil), idx=0
 pdb id=0, src pdb id=0
 ds for latch 1: 0x60146ea0                       0x6014b7a8
 reserved granule count 0 (granule size 16777216)
******************************************************
HEAP DUMP heap name="SQLA^f5f698ce"  desc=0x72b35578
 extent sz=0xfe8 alt=32767 het=608 rec=0 flg=0x2 opc=0
 parent=0x60145548 owner=0x72b35428 nex=(nil) xsz=0x1000000 heap=(nil)
 fl2=0x27, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0
 dsx first ext=0x67d52b08
 dsx empty ext bytes=0  subheap rc link=0x67d52b80,0x67d52b80
 dsx heap size=20256, dsx heap depth=1
 pdb id=0, src pdb id=0

 

 

v$shared_pool_reserved 확인

SQL>
col requests for 999,999,999
col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "
col last_miss_size for 999,999,999 head "LAST MISS|SIZE "
col pct for 999 head "HIT|% "
col request_failures for 999,999,999,999 head "FAILURES"
select requests,
decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size
from v$shared_pool_reserved;
 
          HIT              LAST MISS LAST FAILURE
    REQUESTS   %      FAILURES      SIZE           SIZE
------------ ---- ---------------- ------------ ------------
      28,466  100        15          0        4,096

 

 

dbms_shared_pool.dump_memory 수집 시도

SQL> exec dbms_shared_pool.dump_memory();
BEGIN dbms_shared_pool.dump_memory(); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","select obj#,type#,ctime,mtim...","SQLA^a0d627f0","opn: qkexrInitOpn")

ORA-04031 이 또 발생하며 실패함

 

 

끝!

 

 

참조 : 2207914.1, 396940.1, 430473.1, 1381442.1, 62143.1

https://positivemh.tistory.com/143

ORA-00600, ORA-07445 를 만드는 방법

https://www.dba-oracle.com/tp_Oracle_shared_pool_routine_flush.htm

https://oracle-base.com/articles/misc/purge-the-shared-pool