Oracle ORA-27102: 内存不足IBM AIX RISC系统/6000错误: 12: 空间不足

嗨，

我们在Windows 10上通过SQL Developer和SQL * Plus执行了几个SQL查询，但出现以下错误。

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数据库19c标准版2版本19.0.0.0.0-生产版本19.3.0.0.0
没有AMM的单实例
2- OS version:IBM AIX 7.2
3- Physical RAM:32克
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-所有这些查询都可以通过数据库服务器上的SQL * PLUS成功执行。
2-某些查询可以通过Windows 10上的SQL * PLUS ou SQL Developer成功执行两次。有些查询一直存在内存不足错误。
3-在alert.log中没有此错误的警报。

任何建议将不胜感激!

这通常会在操作系统级别达到某种物理 (或软，即配置) 限制。

(我知道这是显而易见的声明 :-))

但请查看MOS Doc ID 250262.1。我们有一个工具，它将对您的操作系统运行验证检查，并尝试检测常见的配置未对齐。

如果它是干净的，那么是时候在支持下记录一个电话了。

我很好奇你设置 “_ pga_max_size” 的动机