[20230320]ORA-00855 PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.txt

--//http://mvelikikh.blogspot.com/2023/03/ora-00855-pgaaggregatetarget-cannot-be.html链接说明在启动数据库后设置
--//pga_aggregate_target很大遇到的情况.给出了这样的情况pga_aggregate_target设置的最大值.重复测试看看.

1.环境：
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.237:1521/orcldg> sho parameter ga%target
PARAMETER_NAME       TYPE        VALUE
-------------------- ----------- -------
pga_aggregate_target big integer 4G
sga_target           big integer 7504M

$ grep Mem /proc/meminfo
MemTotal:       16129872 kB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MemFree:          632896 kB
MemAvailable:    6159872 kB

--//16129872*1024 = 16516988928
--//16129872*1024 = 0x3d87d4000

(gdb) printf "0x%lx\n", (long) ksmc_physmem_pga_target(0x3d87d4000)
0xffffffffd0852999

(gdb) printf "0x%x\n", (long) ksmc_physmem_pga_target(0x3d87d4000)
0xd0852999

SYS@orcldg> oradebug setmypid
Statement processed.
SYS@orcldg>  oradebug call ksmc_physmem_pga_target 0x3d87d4000
Function returned D0852999

--//D0852999 = 3498387865
--//3498387865/1024/1024/1024 = 3.25812759343534708023G.
--//PGA_AGGREGATE_TARGET  最大设置 3498387865

--//计算公式如下：
max PAT = (TotalMemory * _pga_limit_physmem_perc / 100 - SGA_TARGET) * 100 / _pga_limit_target_perc

_pga_limit_physmem_perc: the parameter limiting total PGA and SGA (90% by default, in other words Oracle reserves
10% for OS and everything else):

_pga_limit_target_perc: the default percent of PAT for pga_aggregate_limit (200% by default):

SYS@192.168.100.237:1521/orcldg> @ hide _pga_limit_physmem_perc
NAME                    DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_pga_limit_physmem_perc default percent of physical memory for pga_aggregate_limit and SGA TRUE          90            90           FALSE IMMEDIATE

SYS@192.168.100.237:1521/orcldg> @ hide _pga_limit_target_perc
NAME                    DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_pga_limit_target_perc  default percent of pga_aggregate_target for pga_aggregate_limit    TRUE          200           200          FALSE IMMEDIATE

max PAT = (TotalMemory * _pga_limit_physmem_perc / 100 - SGA_TARGET) * 100 / _pga_limit_target_perc
        =  (16129872*1024*90/100-7504*1024*1024 ) *100/200 = 3498387865.6

--//3498387865.6与调用ksmc_physmem_pga_target函数的结果接近.
--//而实际上我的设置是pga_aggregate_target=4G.大于3498387865.6.

SYS@192.168.100.237:1521/orcldg> show spparameter pga_aggregate_target
SID      NAME                 TYPE        VALUE
-------- -------------------- ----------- ------
*        pga_aggregate_target big integer 4G

SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=4G scope=memory;
alter system set pga_aggregate_target=4G scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.

SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=3498387866 scope=memory;
alter system set pga_aggregate_target=3498387866 scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.

SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=3498387865 scope=memory;
System altered.

--//原始链接有一段话:
--//http://mvelikikh.blogspot.com/2023/03/ora-00855-pgaaggregatetarget-cannot-be.html
Please note that this formula applies when the value is set while the instance is up and running. It is still possible
to set a higher value in spfile and bounce the instance.
请注意，当实例启动和运行时设置值时，适用此公式。仍然可以在spfile中设置一个更高的值并弹跳实例。