Skip to Main Content
  • Questions
  • ORA-27102: out of memory IBM AIX RISC System/6000 Error: 12: Not enough space

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 11, 2020 - 2:23 pm UTC

Answered by: Connor McDonald - Last updated: September 18, 2020 - 4:14 am UTC

Category: Database Administration - Version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0

You Asked

Hi,

We executed several SQL queries via SQL Developer and SQL*Plus on Windows 10 but got the following error.

Error:
ORA-27102: out of memory
IBM AIX RISC System/6000 Error: 12: Not enough space
Additional information: 7195
Additional information: 16777216
27102. 00000 -  "out of memory"
*Cause:    Out of memory
*Action:   Consult the trace file for details


Example of SQL query
SELECT distinct *
FROM SLCACPOS A, SLCPTACT B, SLACTEUR C, SLAGTUAT D, SLUL E, SLCOLAPR F, SLDOCATR 
WHERE A.XPOSTE='D90000TU9Z' AND B.OID=A.XCPTAC AND B.EXISTE=1 AND C.OID=B.XACTEU AND D.XAGENT=C.XAGENT AND E.XUAT=D.XUAT AND F.XUL=E.OID AND SLDOCATR.XCOLAP=F.OID;


Environment
1- Oracle Database version:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0
Single instance without AMM
2- OS version: IBM AIX 7.2
3- Physical RAM: 32G
4- SGA and PGA configuration:
SQL> show parameter SGA
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 15G
sga_min_size                         big integer 0
sga_target                           big integer 15G
unified_audit_sga_queue_size         integer     1048576
SQL> show parameter PGA
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G

5-Oracle process memory parameter
SQL> select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm='_pga_max_size';
NAME                   VALUE
----------------------------------------------------
_pga_max_size          858992640

6- Shell Limits Configuration on IBM AIX
default:
        fsize = -1
        core = 2097151
        cpu = -1
        data = -1
        rss  = -1
        stack = -1
        nofiles = 2000

root:
daemon:
bin:
sys:
adm:
uucp:
guest:
nobody:
lpd:
esaadmin:
        stack = 393216
        stack_hard = 393216
oracle:
        data = -1
        rss = -1

7- The performance on AIX server seems good.
Topas Monitor for host:************          EVENTS/QUEUES    FILE/TTY
Fri Sep 11 16:15:43 2020   Interval:2           Cswitch    1033  Readch    68808
                                                Syscall     679  Writech   20394
CPU     User% Kern% Wait% Idle%   Physc  Entc%  Reads         8  Rawin         0
Total     0.3   0.4   0.0  99.3    0.02   4.48  Writes        5  Ttyout     1938
                                                Forks         0  Igets         0
Network    BPS  I-Pkts  O-Pkts    B-In   B-Out  Execs         0  Namei         5
Total    6.43K   73.50    2.50   4.27K   2.16K  Runqueue   2.00  Dirblk        0
                                                Waitqueue   0.0
Disk    Busy%      BPS     TPS  B-Read  B-Writ                   MEMORY
Total     0.0    16.0K    1.00       0   16.0K  PAGING           Real,MB   32768
                                                Faults        0  % Comp     18
FileSystem          BPS    TPS  B-Read  B-Writ  Steals        0  % Noncomp  42
Total             83.1K   7.50   67.1K   16.0K  PgspIn        0  % Client   42
                                                PgspOut       0
Name           PID  CPU%  PgSp Owner            PageIn        0  PAGING SPACE
oracle     12976546  0.2 11.7M oracle           PageOut       4  Size,MB   23392
topas      44630518  0.1 3.97M oracle           Sios          4  % Used      0
oracle      7471476  0.1 11.7M oracle                            % Free    100
oracle     44171550  0.0 11.9M oracle           NFS (calls/sec)
oracle     31130004  0.0 11.7M oracle           SerV2         0  WPAR Activ    0
oracle     11141478  0.0 21.9M oracle           CliV2         0  WPAR Total    0
oracle     13369794  0.0 13.5M oracle           SerV3         0  Press: "h"-help
getty      12714382  0.0  612K root             CliV3         0         "q"-quit
oracle      7602518  0.0 14.0M oracle           SerV4         0
oracle     35848594  0.0 11.4M oracle           CliV4         0
sshd       13238766  0.0 1.20M root
oracle      5702112  0.0 12.0M oracle
gil         2097476  0.0  960K root
oracle     13828530  0.0 42.4M oracle
rmcd       11927928  0.0 12.9M root
oracle     12779950  0.0 11.7M oracle
oracle     13566444  0.0 14.6M oracle
clcomd      7995648  0.0 1.71M root
lock_rcv    3932550  0.0  448K root
aioserve    8192270  0.0  448K oracle

$ lsps -a
Page Space      Physical Volume   Volume Group    Size %Used   Active    Auto    Type   Chksum
paging00        hdisk1            datavg        8192MB     0     yes      no      lv       0
hd6             hdisk0            rootvg       15200MB     0     yes     yes      lv       0


PS:
1- All these queries can be executed successfully via SQL*PLUS on the database server.
2- Some queries can be executed successfully one in two times via SQL*PLUS ou SQL Developer on Windows 10. Some queries have the out of memory error all the time.
3- No alert for this error in alert.log.

Any suggestions would be greatly appreciated!


and we said...

This is typically hitting some sort of physical (or soft, ie, configuration) limit at the OS level.

(I know that is a statement of the obvious :-))

But check out MOS Doc ID 250262.1. We have a tool that will run a validation check against your OS and try detect common configuration misalignment.

If it comes up clean, then its time to log a call with Support.

I'm curious as to your motivation for setting "_pga_max_size"

and you rated our response

  (1 rating)

Reviews

September 17, 2020 - 9:12 am UTC

Reviewer: A reader

Hi Connor,

thanks for your reply.

I executed the RDA script on the server and the report looks good for me, there are no limit of memory.

Test "Oracle Database 19c Preinstall (AIX)" executed at 16-Sep-2020 17:01:29

Test Results
~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00100 OS Certified?        PASSED  Certified
A00200 HARDWARE_BITMODE 64? PASSED  is 64-bit
A00210 System mode 64-bit?  PASSED  64-bit kernel enabled
A01020 User in /etc/passwd? PASSED  userOK
A01040 Group in /etc/group? PASSED  GroupOK
A01050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/product/19.3.0/dbhome_1
A01060 ORACLE_HOME Valid?   PASSED  OHexists
A01070 O_H Permissions OK?  PASSED  CorrectPerms
A01410 oraInventory Permiss PASSED  oraInventoryOK
A01420 Other OUI Up?        PASSED  NoOtherOUI
A01430 Got Software Tools?  PASSED  ld_nm_ar_make_found
A01440 Other O_Hs in PATH?  FAILED  OratabEntryInPath
A02010 Umask Set to 022?    PASSED  UmaskOK
A02020 ulimits OK?          PASSED  ulimitOK
A02100 LDLIBRARYPATH Unset? PASSED  UnSet
A02120 LIBPATH Unset?       PASSED  UnSet
A02210 Kernel Parameters OK PASSED  Parameters OK
A02250 aio_maxreqs OK?      PASSED  Adequate
A02260 IOCP Enabled?        PASSED  OK
A03010 Temp Adequate?       FAILED  /tmp tooSmall TMPDIR lessThan5
A03020 Disk Space OK?       PASSED  DiskSpaceOK
A03050 Swap (in MB)         RECORD  23392
A03100 RAM (in MB)          PASSED  32768
A03150 SwapToRam OK?        PASSED  SwapToRamOK
A03510 IP Address           RECORD  192.168.181.72
A03530 Domain Name          RECORD  arsoeso.local
A03540 /etc/hosts Format    FAILED  No entry found
A03550 DNS Lookup           PASSED  Host correctly registered in DNS
A03620 TCP/UDP Ephemeral OK PASSED  Ephemeral OK
A04000 Got Packages?        PASSED  OSpackagesFound



I mentioned the setting "_pga_max_size" because I found that this is a hidden parameter defines the max size of a single session pga. As we got the out of memory error for some sessions, I just want to make sure that this is not the cause.
(Reference: http://ritukamboj.blogspot.com/2011/04/sort-area-in-pga.html )

Connor McDonald

Followup  

September 18, 2020 - 4:14 am UTC

That's also a link from 2011 :-)

I'd remove it, but also log a call with support because ideally we don't ever want to see OS errors.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database