Hi all,
So, I started seen this on a client environment. Researching on the case after no crear reference on MOS, I noticed some high PGA allocation as per below.
SYS@proddb AS SYSDBA PROD> select pid, serial#,category, allocated/1024/1024 MB, used/1024/1024 MB_used, max_allocated/1024/1024 MB_MAX_ALLOCATED_ON_PGA 2 from v$process_memory where pid=852; PID SERIAL# CATEGORY MB MB_USED MB_MAX_ALLOCATED_ON_PGA ---------- ---------- --------------- ---------- ---------- ----------------------- 852 91 SQL .086807251 .00806427 .672416687 852 91 PL/SQL .087730408 .078926086 .126182556 852 91 Freeable .5625 0 852 91 Other 2.25187302 2.25187302
Seems a match MOS ORA-04030 Error With High “kkoutlCreatePh” (Doc ID 1618444.1).
The solution? Simply disabled the following parameter:
"_b_tree_bitmap_plans"=false
Trace for additional info:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1 System name: Linux Node name: proddb.local Release: 4.1.12-94.8.3.el7uek.x86_64 Version: #2 SMP Wed Apr 25 19:57:32 PDT 2018 Machine: x86_64 Instance name: proddb Redo thread mounted by this instance: 1 Oracle process number: 854 Unix process pid: 28899, image: oracle@proddb.local *** 2019-01-09 11:20:09.130 *** SESSION ID:(5429.55092) 2019-01-09 11:20:09.130 *** CLIENT ID:() 2019-01-09 11:20:09.130 *** SERVICE NAME:(XXXXX) 2019-01-09 11:20:09.130 *** MODULE NAME:(JDBC Thin Client) 2019-01-09 11:20:09.130 *** CLIENT DRIVER:(jdbcthin) 2019-01-09 11:20:09.130 *** ACTION NAME:() 2019-01-09 11:20:09.130 [TOC00000] Jump to table of contents Dump continued from file: /u01/app/oracle/diag/rdbms/proddb/proddb/trace/proddb_ora_28899.trc [TOC00001] ORA-04030: out of process memory when trying to allocate 34392040 bytes (kkoutlCreatePh,ub1 : kkoabr) [TOC00001-END] [TOC00002] ========= Dump for incident 2710965 (ORA 4030) ======== [TOC00003] ----- Beginning of Customized Incident Dump(s) ----- ======================================= TOP 10 MEMORY USES FOR THIS PROCESS --------------------------------------- 72% 18 GB, 1356 chunks: "free memory " SQL kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0 27% 6809 MB, 3366 chunks: "permanent memory " SQL kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0 0% 100 MB, 894 chunks: "permanent memory " SQL kkoutlCreatePh ds=0x7fa8cce16708 dsprt=0x7fa8df330220 0% 23 MB, 589515 chunks: "chedef : qcuatc " TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0 0% 18 MB, 150124 chunks: "opndef: qcopCreateOpnViaM " TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0 0% 16 MB, 214829 chunks: "logdef: qcopCreateLog " TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0 0% 11 MB, 241 chunks: "free memory " top call heap ds=0x7fa8df49dbe0 dsprt=(nil) 0% 9643 KB, 4623 chunks: "qkkele " SQL kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0 0% 6534 KB, 58578 chunks: "optdef: qcopCreateOptInte " TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0 0% 4134 KB, 15399 chunks: "kccdef : qcsvwsci " TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0 ======================================= PRIVATE MEMORY SUMMARY FOR THIS PROCESS --------------------------------------- ****************************************************** PRIVATE HEAP SUMMARY DUMP 25 GB total: 25 GB commented, 794 KB permanent 12 MB free (0 KB in empty extents), 24 GB, 1 heap: "kxs-heap-c " 67 KB free held ------------------------------------------------------ Summary of subheaps at depth 1 25 GB total: 203 MB commented, 6809 MB permanent 18 GB free (0 KB in empty extents), 6398 MB, 9243 chunks: "allocator state " 6398 MB free held 3758 MB, 4623 chunks: "qkkele " 3749 MB free held 3650 MB, 4623 chunks: "qkkkey " 3650 MB free held ========================================= REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS ----------------------------------------- Dump of Real-Free Memory Allocator Heap [0x7fa8df317000] mag=0xfefe0001 flg=0x5000003 fds=0x0 blksz=65536 blkdstbl=0x7fa8df317010, iniblk=524288 maxblk=524288 numsegs=318 In-use num=2965 siz=641597440, Freeable num=0 siz=0, Free num=254 siz=3586195456 ========================================== INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY ------------------------------------------ Dumping Work Area Table (level=1) ===================================== Global SGA Info --------------- global target: 102400 MB auto target: 62376 MB max pga: 2048 MB pga limit: 4096 MB pga limit known: 0 pga limit errors: 0 pga inuse: 33104 MB pga alloc: 35238 MB pga freeable: 1225 MB pga freed: 433664026 MB pga to free: 0 % broker request: 0 pga auto: 12 MB pga manual: 0 MB pga alloc (max): 35238 MB pga auto (max): 12284 MB pga manual (max): 2 MB # workareas : 0 # workareas(max): 551
I solved this problem with this link
For testing : sysctl -w vm.max_map_count=524288
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=359545741841766&id=1325100.1&_afrWindowMode=0&_adf.ctrl-state=tvv2gnzba_4
Great catch, zolttix!
Sounds a match to this problem, possibly more efficient than my original solution. Will look for this on next maintenance window and refer here back.