10g、11g内存自动管理
SGA自动管理：
1、如何查看SGA是否是自动管理？
在10G中，通过设置sga_target为零和非零值，可以完成手工和自动管理切换，如果sga_target非0，那么就是自动管理。
而在11G 中，查看sga是否是自动管理，可和sga_target没有关系了，因为11g新特性中出现了memory_target参数，memory_target结合和sga和pga的管理（Oracle9i引入pga_aggregate_target，可以自动对PGA进行调整；
Oracle10引入sga_target，可以自动对SGA进行调整。Oracle11g则对这两部分进行综合，引入memory_target，可以自动调整所有的内存）
2、10g查看sga自动管理
C:\Users\xiaomao>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 14 10:11:40 2
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter sga
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
432M
sga_target big integer
432M
解析：这里sga_target是一个非0的值，因为这里看sga是自动管理的，而sga_target的值是小于等于sga_max_size的值得，如果我们手动将sga_target的值改大，是不可以的。
SQL> alter system set sga_target=440M;
alter system set sga_target=440M
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00823: 指定的 sga_target 的值大于 sga_max_size
SQL> alter system set sga_target=440M scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 461373440 bytes
Fixed Size 1248512 bytes
Variable Size 134218496 bytes
Database Buffers 318767104 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter sga
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
440M
sga_target big integer
440M
官方文档解释是说：
It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases, in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accomodate SGA_TARGET. After startup, SGA_TARGET can be decreased or increased dynamically. However, it can not exceed the value of SGA_MAX_SIZE that was computed at startup
大概意思是说如果设置sga_target的值大于sga_max_size，在重启db时，sga_max_size的值就会被覆盖。
3、10g中，sga_target参数的官方解释
SGA_TARGETProperty Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
•Buffer cache (DB_CACHE_SIZE)
•Shared pool (SHARED_POOL_SIZE)
•Large pool (LARGE_POOL_SIZE)
•Java pool (JAVA_POOL_SIZE)
•Streams pool (STREAMS_POOL_SIZE)
解析：这里说如果sga_target的值被指定，那么这些pool_size就被动态管理
SQL> select name,value from v$parameter where name like '%pool_size%' or name like 'db_cache_size' ;
NAME VALUE
-------------------- ----------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
db_cache_size 0
然后我们现在如何更快的查看此时这些值的大小呢？那就要通过查看一些隐含参数了
SQL> select a.ksppinm name,b.ksppstvl value
2 from x$ksppi a,x$ksppcv b
3 where a.indx=b.indx
4 and (a.ksppinm like '%pool_size%'
5 or a.ksppinm like '%target%' or a.ksppinm like '%db_cache_size%')
6 ;
NAME VALUE
---------------------------------------- --------------------------------------------------
_NUMA_pool_size Not specified
__shared_pool_size 130023424
shared_pool_size 0
__large_pool_size 4194304 --这里就是large_pool_size的大小
large_pool_size 0
__java_pool_size 4194304
java_pool_size 0
__streams_pool_size 0
streams_pool_size 0
_io_shared_pool_size 4194304
_backup_io_pool_size 1048576
sga_target 469762048
__db_cache_size 322961408
db_cache_size 0
_db_mttr_sim_target
archive_lag_target 0
_fast_start_instance_recovery_target 0
_target_rba_max_lag_percentage 90
fast_start_io_target 0
fast_start_mttr_target 0
NAME VALUE
---------------------------------------- --------------------------------------------------
db_flashback_retention_target 1440
global_context_pool_size
pga_aggregate_target 149946368
olap_page_pool_size 0
_olap_page_pool_hit_target 100
25 rows selected
4、11g中查看sga是否自动管理
主要是查看memory_target这个参数。
SQL> show parameter mem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 6464M
memory_target big integer 6464M
shared_memory_address integer 0
而如果我们把memory_target的值擅自手动改大些，也会出现10g里sga_target的问题
5、11g中查看sga和pga大小
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2336M
sga_target big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
由于sga和pga都是动态管理了，所以要看大小的话，还是需要看隐含参数的
SQL> select a.ksppinm name,b.ksppstvl value
2 from x$ksppi a,x$ksppcv b
3 where a.indx=b.indx
4 and (a.ksppinm like '%sga_target%'
5 or a.ksppinm like '%pga%target%');
NAME VALUE
-------------------------------------------------------------------------------- --------------------------------------------------
sga_target 0
__sga_target 2449473536
pga_aggregate_target 0
__pga_aggregate_target 4328521728
以上为我的简单理解~~如果误理解，请指正
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24500180/viewspace-713299/ ，如需转载，请注明出处，否则将追究法律责任。
