[20210723]Minimum sga_target.txt

[20210723]Minimum sga_target.txt

https://nenadnoveljic.com/blog/minimum-sga_target/

min_sga_target = k * cpu_count + C

k = 20.47M , C = 120M

2576=120*k+c
1348=60*k+c

k= (2576-1348)/60  = 20.46666666666666666666
c= 1348-60*20.46666666666666666666 = 120.0000000000000000004

--//以上是作者的測試結果.

--//我的測試如下:
--//我的測試環境是cpu_count=24
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ cat inittest.ora
cpu_count=1
db_name=xxxx
instance_name=xxxx
sga_target = 100M

$ export ORACLE_SID=test
$ seq 25 | xargs -IQ  echo "sed -i \"1s/cpu_count=.*/cpu_count=Q/\" inittest.ora  ; sqlplus -s -l / as sysdba <<<'startup nomount'" | bash | grep "^ORA-00821"| awk '{print NR,$NF}'
--//注意sed使用-i替換檔案.
1 168M
2 172M
3 188M
4 204M
5 208M
6 224M
7 228M
8 248M
9 252M
10 268M
11 272M
12 288M
13 292M
14 308M
15 312M
16 328M
17 336M
18 356M
19 360M
20 380M
21 392M
22 408M
23 412M
24 436M
25 436M

$ seq 25 | xargs -IQ  echo "sed -i \"1s/cpu_count=.*/cpu_count=Q/\" inittest.ora  ; sqlplus -s -l / as sysdba <<<'startup nomount'" | bash | grep "^ORA-00821"
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 168M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 172M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 188M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 204M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 208M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 224M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 228M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 248M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 252M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 268M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 272M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 288M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 292M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 308M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 312M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 328M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 336M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 356M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 360M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 380M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 392M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 408M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 412M
ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 436M