Oracle性能,Oracle管理,自动共享内存模式（sga_target>0），是否可以设置db_cache_size, shared_pool_size等详细参数，昨天这样对一个数据库作出这样的修改后，数据库无法启动。不知道是系统的原因还是这样设置的原因。所以想做实验测试一下。
实验：自动共享内存模式下（sga_target>0），是否可以设置(db_cache_size,shared_pool_size）
官方解释：
automatic SGA memory management. In ASMM mode, you needn't setup db_cache_size,shared_pool_size,java_pool_szie and large_pool_size, etc. all these size are set by the server automatically.
原因：昨天这样对一个数据库作出这样的修改后，数据库无法启动。
当时报警日志：
Thu Apr 20 19:49:15 2023
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Thu Apr 20 19:49:15 2023
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 40
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Thu Apr 20 19:49:22 2023
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Apr 20 19:49:23 2023
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 141236
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Thu Apr 20 19:49:26 2023
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Thu Apr 20 19:49:26 2023
Stopping background process VKTM
Thu Apr 20 19:49:31 2023
Instance shutdown complete
Thu Apr 20 19:50:28 2023
Adjusting the default value of parameter parallel_max_servers
from 1280 to 470 due to the value of parameter processes (500)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 32
Number of processor cores in the system is 32
Number of processor sockets in the system is 2
Picked latch-free SCN scheme 3
Error: Failed to allocate SGA granule addr 00000004E4000000 size 67108864
mode 131073 locality 0
Errors in file d:\oracle\diag\rdbms\eas\eas\trace\eas_ora_5432.trc:
ORA-27102: out of memory
OSD-00026: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
Error: Failed to allocate SGA granule addr 00000004E0000000 size 67108864
mode 131073 locality 0
Errors in file d:\oracle\diag\rdbms\eas\eas\trace\eas_ora_5432.trc:
ORA-27102: out of memory
OSD-00026: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
Error: Failed to allocate SGA granule addr 00000004DC000000 size 67108864
mode 131073 locality 0
Errors in file d:\oracle\diag\rdbms\eas\eas\trace\eas_ora_5432.trc:
ORA-27102: out of memory
OSD-00026: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
Error: Failed to allocate SGA granule addr 00000004D8000000 size 67108864
mode 131073 locality 0
Errors in file d:\oracle\diag\rdbms\eas\eas\trace\eas_ora_5432.trc:
ORA-27102: out of memory
OSD-00026: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
不知道是系统的原因还是这样设置的原因。
所以想做实验测试一下。
...
实验步骤如下：
--备份参数文件
--生成pfile
create pfile from spfile;
--复制pfile和spfile备份
--查看原有设置
show parameter sga;
show parameter pga;
show parameter mem;
--当前设置：
--sga_max_size=30g，
--sga_target=20g,
--pga_aggregate_target=11g,
--memory_max_target=0,
--memory_target=0
--修改参数文件中设置
--sga_target=18g,db_cache_size=10g,shared_pool_size=6g
alter system set sga_target=18G scope=spfile;
alter system set db_cache_size=10G scope=spfile;
alter system set shared_pool_size=6G scope=spfile;
alter system set pga_aggregate_target=8G scope=spfile;
--关闭数据库
shutdown immediate;
--启动数据库
startup
--查看内存设置
show parameter sga;
show parameter pga;
show parameter mem;
show parameter db_cache;
show parameter shared_pool;
--如果能正常启动，并且设置生效，说明可以这样设置。
实验过程：
SQL> create pfile from spfile;
File created.
SQL> --查看原有设置
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 30G
sga_target big integer 20G
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 11G
SQL> show parameter mem;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> alter system set sga_target=18G scope=spfile;
System altered.
SQL> alter system set db_cache_size=10G scope=spfile;
System altered.
SQL> alter system set shared_pool_size=6G scope=spfile;
System altered.
SQL> alter system set pga_aggregate_target=8G scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3.2068E+10 bytes
Fixed Size 2297064 bytes
Variable Size 2.0133E+10 bytes
Database Buffers 1.1878E+10 bytes
Redo Buffers 55214080 bytes
Database mounted.
Database opened.
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 30G
sga_target big integer 18G
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 8G
SQL> show parameter mem;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL>
SQL> show parameter db_cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 10G
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 322122547
shared_pool_size big integer 6G
SQL>
SQL>
数据库正常启动，设置也生效了！
SQL> --SGA整体检查
SQL> select sg.name,sg.value as value_b,round(sg.value/1024/1024,2) as value_m from v$sga sg;
NAME VALUE_B VALUE_M
-------------------- ---------- ----------
Fixed Size 2297064 2.19
Variable Size 1966289794 18752
Database Buffers 1234803097 11776
Redo Buffers 55214080 52.66
SQL> --查询主要SGA参数
SQL> SELECT NAME,round(VALUE/1024,2) M_value
2 FROM v$parameter
3 WHERE NAME IN ('sga_max_size','db_cache_size',
4 'shared_pool_size','shared_pool_reserved_size','large_pool_size','java_pool_size',
5 'db_block_size','db_block_buffers','log_buffer','sort_area_size','sort_area_retained_size',
6 'hash_area_size','sessions','open_cursors','sga_target'
7 ) ORDER BY NAME;
NAME M_VALUE
-------------------------------------------------------------------------------- ----------
db_block_buffers 0
db_block_size 8
db_cache_size 10485760
hash_area_size 128
java_pool_size 0
large_pool_size 0
log_buffer 52184
open_cursors 1.46
sessions 2.22
sga_max_size 31457280
sga_target 18874368
shared_pool_reserved_size 314572.8
shared_pool_size 6291456
sort_area_retained_size 0
sort_area_size 64
15 rows selected
SQL> --查看SGA情况
SQL> SELECT NAME,BYTES AS VALUE_B,BYTES/1024/1024 AS VALUE_M FROM V$SGAINFO order by name;
NAME VALUE_B VALUE_M
-------------------------------- ---------- ----------
Buffer Cache Size 1234803097 11776
Fixed SGA Size 2297064 2.19065093
Free SGA Memory Available 1288490188 12288
Granule Size 67108864 64
Java Pool Size 134217728 128
Large Pool Size 201326592 192
Maximum SGA Size 3206844006 30582.8476
Redo Buffers 55214080 52.65625
Shared IO Pool Size 0 0
Shared Pool Size 6442450944 6144
Startup overhead in Shared Pool 423011752 403.415443
Streams Pool Size 0 0
12 rows selected
好像也可以这样用！
Tag: Oracle性能 Oracle管理