实验出发点
1. 数据库刚启动时候的sga信息(show sga可查看)或者通过show parameter sga/pga/db_cache_size/memory_target等等和内存有关的参数,并不能真正反映相对应大小。
参数存在的意义是什么,要怎么查看数据库启动之后sga,pga等真正的大小。
2.如何启用AMM和ASMM
3.存在静态参数文件中类似proc.__db_cache_size、proc.__pga_aggregate_target、*.__sga_target、proc.__shared_pool_size有什么意义
环境说明:
1.操作系统版本。
- [oracle@oracle ~]$ uname -a
- Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
- [oracle@oracle ~]$ lsb_release -a
- LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
- Distributor ID: RedHatEnterpriseServer
- Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
- Release: 6.5
- Codename: Santiago
2.数据库版本。
- SYS@proc> select * from v$version where rownum=1;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
实验过程:
一、实验准备
1.通过静态参数文件将memory_max_target、memory_target、sga_max_size、sga_target、pga_aggregate_target、db_cache_size参数设置为0。
- [oracle@oracle dbs]$ cat initproc.ora
- *._in_memory_undo=TRUE
- *.audit_file_dest='/u01/app/oracle/admin/proc/adump'
- *.audit_trail='db'
- *.compatible='11.2.0.4.0'
- *.control_files='/u01/app/oracle/oradata/proc/control01.ctl','/u01/app/oracle/oradata/proc/control02.ctl'
- *.db_block_size=8192
- *.db_domain=''
- *.db_file_multiblock_read_count=128
- *.db_name='proc'
- *.processes=150
- *.remote_login_passwordfile='EXCLUSIVE'
- *.undo_tablespace='UNDOTBS1'
- memory_target=0
- memory_max_target=0
- sga_target=0
- sga_max_size=0
- pga_aggregate_target=0
- db_cache_size=0
2.使用新的参数文件设置启动数据库。
- SYS@proc> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SYS@proc> create spfile from pfile;
- File created.
- SYS@proc> startup;
- ORACLE instance started.
- Total System Global Area 175403008 bytes
Fixed Size 2251416 bytes
Variable Size 163579240 bytes
Database Buffers 4194304 bytes
Redo Buffers 5378048 bytes
Database mounted.
Database opened.
3.使用show parameter查看指定参数的状态数值。
- SYS@proc> show parameter memory%target
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- memory_max_target big integer 12M
- memory_target big integer 0
- SYS@proc> show parameter sga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- ...省略部分内容...
- sga_max_size big integer 168M
- sga_target big integer 0
- SYS@proc> show parameter pga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_target big integer 0
- SYS@proc> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 4M
说明:由于memory_target被设置为0,所以AMM此刻是被禁用的
由于memory_target为0的情况下,sga_target为0,所以ASMM此刻是被禁用的
二、查看数据库开启后各组件的真实大小以及依赖参数
1.动态查看sga组件大小。
- SYS@proc> select component,current_size from v$sga_dynamic_components;
- COMPONENT CURRENT_SIZE
- ---------------------------------------------------------------- ------------
- shared pool 159383552
- large pool 0
- java pool 4194304
- streams pool 0
- DEFAULT buffer cache 4194304
- ...省略部分内容...
- 14 rows selected.
- SYS@proc> show sga
- Total System Global Area 175403008 bytes
- Fixed Size 2251416 bytes
- Variable Size 163579240 bytes
- Database Buffers 4194304 bytes
- Redo Buffers 5378048 bytes
2.查看相对应得隐含参数数值大小。
- SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
- from sys.x$ksppi x,sys.x$ksppcv y
- where x.inst_id=userenv('Instance')
- and y.inst_id=userenv('Instance')
- and x.indx=y.indx
- and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');
- NAME VALUE DESCRIB
- ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
- sga_target 0 Target size of SGA
- __sga_target 0 Actual size of SGA --该值在AMM或者ASMM下真实反映数据库刚启动时候分配的sga大小
- memory_target 0 Target size of Oracle SGA and PGA memory
- memory_max_target 12582912 Max size for Memory Target
- __db_cache_size 4194304 Actual size of DEFAULT buffer pool for standard block size buffers
- db_cache_size 4194304 Size of DEFAULT buffer pool for standard block size buffers
- pga_aggregate_target 0 Target size for the aggregate PGA memory consumed by the instance
- __pga_aggregate_target 0 Current target size for the aggregate PGA memory consumed
- 8 rows selected.
说明:注意隐含参数的描述部分。比如“__sga_target”是Actual size of SGA
三、设置数据库为AMM模式,对比各个组件变化情况
1.准备工作
1.1设置memory_max_target和memory_target等于1G。遇到了疑似BUG的报错。
- SYS@proc> alter system set memory_max_target=1g scope=spfile;
- System altered.
- SYS@proc> startup force;
- ORACLE instance started.
- Total System Global Area 175403008 bytes
- Fixed Size 2251416 bytes
- Variable Size 163579240 bytes
- Database Buffers 4194304 bytes
- Redo Buffers 5378048 bytes
- Database mounted.
- Database opened.
- SYS@proc> alter system set memory_target=1g;
- alter system set memory_target=1g
- *
- ERROR at line 1:
- ORA-03113: end-of-file on communication channel
- Process ID: 4862
- Session ID: 16 Serial number: 3
- SYS@proc> select status from v$instance;
- ERROR:
- ORA-03114: not connected to ORACLE
- SYS@proc> startup;
- ORA-24324: service handle not initialized
- ORA-01041: internal error. hostdef extension doesn
1.2疑似BUG的报错对应的警告日志。
- Errors in file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_mman_5032.trc (incident=218646):
- ORA-00600: internal error code, arguments: [kmgsb_resize_memory_target_2], [43], [42], [], [], [], [], [], [], [], [], []
- Incident details in: /u01/app/oracle/diag/rdbms/proc/proc/incident/incdir_218646/proc_mman_5032_i218646.trc
- Use ADRCI or Support Workbench to package the incident.
- See Note 411.1 at My Oracle Support for error and packaging details.
- Errors in file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_mman_5032.trc:
- ORA-00600: internal error code, arguments: [kmgsb_resize_memory_target_2], [43], [42], [], [], [], [], [], [], [], [], []
- MMAN (ospid: 5032): terminating the instance due to error 822
- Sun Dec 11 08:00:40 2016
- System state dump requested by (instance=1, osid=5032 (MMAN)), summary=[abnormal instance termination].
- System State dumped to trace file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_diag_5026_20161211080040.trc
- Dumping diagnostic data in directory=[cdmp_20161211080040], requested by (instance=1, osid=5032 (MMAN)), summary=[abnormal instance termination].
- Instance terminated by MMAN, pid = 5032
说明:在mos上查找不到相关错误,百度有类似kmgsb_resize_sga_target_1的ora600,是一个BUG。猜测kmgsb_resize_memory_target_2也是一个BUG。
这里做记录即可。
1.3强制性在spfile中修改,然后重启即可达到目的。
- SYS@proc> startup;
- ORACLE instance started.
- Total System Global Area 175403008 bytes
- Fixed Size 2251416 bytes
- Variable Size 163579240 bytes
- Database Buffers 4194304 bytes
- Redo Buffers 5378048 bytes
- Database mounted.
- Database opened.
- SYS@proc> alter system set memory_target=1g scope=spfile;
- System altered.
- SYS@proc> startup force;
- ORACLE instance started.
- Total System Global Area 638889984 bytes --对比下边查出来的__sga_target可知从这里看大小并不真实。有机会做个10046(验证show sga的不准确)
- Fixed Size 2255872 bytes
- Variable Size 184550400 bytes
- Database Buffers 448790528 bytes
- Redo Buffers 3293184 bytes
- Database mounted.
- Database opened.
- SYS@proc> show parameter memory%target
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- memory_max_target big integer 1G
- memory_target big integer 1G
- SYS@proc>
- SYS@proc> col name for a40
- SYS@proc> col value for a30
- SYS@proc> col DESCRIB for a90
- SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
- from sys.x$ksppi x,sys.x$ksppcv y
- where x.inst_id=userenv('Instance')
- and y.inst_id=userenv('Instance')
- and x.indx=y.indx
- and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');
- NAME VALUE DESCRIB
- ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
- sga_target 0 Target size of SGA
- __sga_target 641728512 Actual size of SGA
- memory_target 1073741824 Target size of Oracle SGA and PGA memory
- memory_max_target 1073741824 Max size for Memory Target
- __db_cache_size 448790528 Actual size of DEFAULT buffer pool for standard block size buffers
- db_cache_size 0 Size of DEFAULT buffer pool for standard block size buffers
- pga_aggregate_target 0 Target size for the aggregate PGA memory consumed by the instance
- __pga_aggregate_target 432013312 Current target size for the aggregate PGA memory consumed
- 8 rows selected.
2.sga_target和pga_aggregate_target同时设置大小
- SYS@proc> alter system set sga_max_size=500m scope=spfile;
- System altered.
- SYS@proc> alter system set sga_target=500m;
- System altered.
- SYS@proc> alter system set pga_aggregate_target=525m; --1024-500=524,由于设置了sga_target=500m,可知pga_aggregate_target设置不能超过524
- alter system set pga_aggregate_target=525m --反过来假设先设置pga_aggregate_target=500,在设置sga_target超过524是可以的,不过startup报ORA-00838:"Specified value of MEMORY_TARGET is too small, needs to be at least %sM"
- *
- ERROR at line 1:
- ORA-02097: parameter cannot be modified because specified value is invalid
- ORA-00840: PGA_AGGREGATE_TARGET cannot be modified to the specified value
- SYS@proc> alter system set pga_aggregate_target=400m;
- System altered.
- SYS@proc> startup force;
- ...省略部分内容...
- Database opened.
- SYS@proc> show parameter pga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_target big integer 400M
- SYS@proc> show parameter sga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- ...省略部分内容...
- sga_max_size big integer 500M
- sga_target big integer 500M
- SYS@proc> col name for a40
- SYS@proc> col value for a30
- SYS@proc> col DESCRIB for a90
- SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
- from sys.x$ksppi x,sys.x$ksppcv y
- where x.inst_id=userenv('Instance')
- and y.inst_id=userenv('Instance')
- and x.indx=y.indx
- and (x.ksppinm like 'SYS@proc> SYS@proc> SYS@proc> 2 3 4 5 6 %sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');
- NAME VALUE DESCRIB
- ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
- sga_target 524288000 Target size of SGA
- __sga_target 524288000 Actual size of SGA
- memory_target 1073741824 Target size of Oracle SGA and PGA memory
- memory_max_target 1073741824 Max size for Memory Target
- __db_cache_size 331350016 Actual size of DEFAULT buffer pool for standard block size buffers
- db_cache_size 0 Size of DEFAULT buffer pool for standard block size buffers
- pga_aggregate_target 419430400 Target size for the aggregate PGA memory consumed by the instance
- __pga_aggregate_target 549453824 Current target size for the aggregate PGA memory consumed
- 8 rows selected.
- SYS@proc> alter system set sga_target=400m;
- System altered.
- SYS@proc> alter system set pga_aggregate_target=400m;
- System altered.
- SYS@proc> startup force;
- ...省略部分内容...
- Database opened.
- SYS@proc> show parameter sga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- sga_max_size big integer 500M
- sga_target big integer 400M
- SYS@proc> show parameter pga
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_target big integer 400M
- SYS@proc>select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
- from sys.x$ksppi x,sys.x$ksppcv y
- where x.inst_id=userenv('Instance')
- and y.inst_id=userenv('Instance')
- and x.indx=y.indx
- and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');
- NAME VALUE DESCRIB
- ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
- sga_target 419430400 Target size of SGA
- __sga_target 524288000 Actual size of SGA
- memory_target 1073741824 Target size of Oracle SGA and PGA memory
- memory_max_target 1073741824 Max size for Memory Target
- __db_cache_size 331350016 Actual size of DEFAULT buffer pool for standard block size buffers
- db_cache_size 0 Size of DEFAULT buffer pool for standard block size buffers
- pga_aggregate_target 419430400 Target size for the aggregate PGA memory consumed by the instance
- __pga_aggregate_target 549453824 Current target size for the aggregate PGA memory consumed
- 8 rows selected.
结论是:同时设置了sga_target和pga_aggregate_target两个值的话,
若这是sga_target+pga_aggregate_target>memory_target是更改不成功的,若是强制性更改(即加上scope=spfile),则startup会报错。
若sga_target+pga_aggregate_target<memory_target
若更改的sga_target<__sga_target,则实际sga的大小还是为原来更改之前__sga_target (__sga_target有点像高水位线)
若更改的sga_target>__sga_target,令__sga_target=sga_target,sga大小设置为__sga_target
而__pga_aggregate_target的实际的值则为memory_target-__sga_target,也就是__sga_target+__sga_target=memory_target。
3.sga_target和pga_aggregate_target同时设置大小(直接结论,实验过程略去,下同)
结论是:sga_target设置大小,pga_aggregate_target没有设置大小
那么pga_aggregate_target初始化值=memory_target-sga_target
4.sga_target没有设置大小,pga_aggregate_target设置大小
结论是:sga_target初始化值=memory_target-pga_aggregate_target
5.sga_target和pga_aggregate_target都没有设置大小
结论是:Oracle将对这两个值没有最小值和默认值。Oracle将根据数据库运行状况进行分配大小。但在数据库启动是会有一个固定比例来分配:
sga_target = memory_target*60%
pga_aggregate_target = memory_target*40%
四、其它
1.如何降低“高水位线”“__sga_target”呢,经测试只能修改静态参数文件里边proc.__sga_target的值,而无法通过alter system set "__sga_target"=500m scope=spfile;的方式修改(会同时将sga_target和sga_max_size也手动设置调小),虽然可以成功执行。
2.实验过程可得出AMM下,无论sga_target是否为0,ASMM是生效的。
有几种情况:
2.1 memory_target<>0也就是AMM模式下,包含ASMM
2.2 memory_target=0也就是10G一样的情况下
sga_target=0也就是禁用ASMM下,SGA中的各组件大小都要明确设定,不能自动调整各组件大小。
sga_target<>0(ASMM),系统自动调整SGA各组件大小。
PGA则依赖pga_aggregate_target的大小,因为10G模式下PGA不能自动调整。