TARGET官方解释.,ca88亚洲城网站10g      PGA自动管理

o   DB_nK_CACHE_SIZE
for multiple block sizes

如下所示,首先检查参数文件类型,然后修改参数sga_target、memory_max_target、memory_target。因为中间有些参数为静态参数,所以在修改参数后,要求重启数据库。

  1. 测试环境
    OS: RHEL5U5(32bit)
    DB: Oracle 11.2.0.3.0(32bit)

  2.   极度原因. 
    2.1 oracle 11g默认sga_target为0,如下图, Oracle
    10g开始,引入SGA_TARGET开始化参数, 让oracle自动管理SGA中(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))的内存自动分配,即ASSM(Automatic Shared Memory
    Management). 如下为引自oracle官方文档解释.如下.

 

   
手工共享内存管理      : memory_target=0 and
sga_target=0 
指定
share_pool_size 、db_cache_size 等 sga 参数


ORACLE
11g      PGA,SGA统一机动管理(AMM,自动内存管理)

SQL> !

[oracle@DB-Server ~]$ ipcs -m

 

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0x00000000 4128770    root      644        80         2                       

0x00000000 4161540    root      644        16384      2                       

0x00000000 4194309    root      644        280        2                       

0xfc5d1940 7012369    oracle    660        1075838976 49     

SGA_TARGET

Property Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable ALTER SYSTEM
Range of values 64 MB 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)

If these automatically tuned memory pools are set to nonzero values,
then those values are used as minimum levels by Automatic Shared Memory
Management. You would set minimum values if an application component
needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected
by Automatic Shared Memory Management:

  • Log buffer

  • Other buffer caches, such as KEEP, RECYCLE, and other block sizes

  • Fixed SGA and other internal allocations

2.2 给SGA_TARGET设置与SGA_MAX_SIZE(632M)相册的值,打开ASSM功效.
SYS> alter system set sga_target=632M SCOPE=SPFILE;
System altered.

3 重启DB
3.1  SYS> startup force
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be
at least 644M
SYS> show parameter pga;
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 54 Serial number: 5

3.2 依照如上报错音讯.能够看出SGA设置大了. 导致PGA小了,
故报错. MEMORY_TARGET=PGA+SGA.   SGA的最少值为10M,最大值为 4096 GB – 1,
见官方文档.

 

 

SYS> show parameter sga;

SQL>

SQL>


假使设置了SGA_TARGET并且PGA_AGGREGATE_TARGET没有设置,数据库仍旧会自动调整那两个参数。
PGA_AGGREGATE_TARGET将被伊始化为MEMORY_TARGET –
SGA_TARGET的值。

ORACLE
11g      PGA,SGA统一机动管理(AMM,自动内存管理)

SGA_TARGET官方解释.

MEMORY_TARGET (SGA_TARGET) or HugePages – which to
choose?

           
Automatic Memory Management keeps the same behaviour as in 11g.

lock_sga                             boolean        FALSE
pre_page_sga                     boolean       FALSE
sga_max_size                      big integer   632M
sga_target                           big integer    0
SYS> 

 

1:当MEMORY_TARGET大于0的事态下,可以安装SGA_TARGET、PGA_AGGREGATE_TARGET的值为非0,对应的含义分别如下:

PGA_AGGREGATE_TARGET

Property Description
Parameter type Big integer
Syntax PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value 10 MB or 20% of the size of the SGA, whichever is greater
Modifiable ALTER SYSTEM
Range of values Minimum: 10 MB

Maximum: 4096 GB – 1

Basic Yes

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory
available to all server processes attached to the instance.

3.3 查看ORA-00838 报错, ORACLE解释.

jietestdb<*test11g*/u01/product/oracle/dbs>$oerr ora 00838
00838, 00000, “Specified value of MEMORY_TARGET is too small, needs to
be at least %sM”
// *Cause: The specified value of MEMORY_TARGET was less than the sum
of the 
//         specified values for SGA_TARGET and
PGA_AGGREGATE_TARGET.
// *Action: Set MEMORY_TARGET to at least the recommended value.

由上ORA-00838,ORACLE解释可以看出, 裁减SGA值,或附加MEMORY_TARGET值,
或还原修改前情状即可解决问题.
3.1 第一种缓解措施:
还原先前意况,不做SGA_TARGET或MEMORY_TARGET值改变.
     3.1.1 此DB已经被shutdown了, 直接startup DB不可能启动, 如下报错.
SYS> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be
at least 644M
      3.1.2 因DB在关门状态下,也得以由spfile创造pfile,或由pfile
成立spfile. 此时我们测试DB
spfiletest11g.ora中,已经包蕴sga_target值,如下图. 

jietestdb<*test11g*/u01/product/oracle/dbs>$
jietestdb<*test11g*/u01/product/oracle/dbs>$strings
spfiletest11g.ora 
test11g.__db_cache_size=155189248
test11g.__java_pool_size=4194304
test11g.__large_pool_size=4194304
test11g.__oracle_base=’/u01/product’#ORACLE_BASE set from
environment
test11g.__pga_aggregate_target=230686720
test11g.__sga_target=432013312
test11g.__shared_io_pool_size=0
test11g.__shared_pool_size=251658240
test11g.__streams_pool_size=8388608
*.audit_file_dest=’/u01/product/admin/test11g/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/product/oradata/test
11g/control01.ctl’,’/u01/product/oradata/test11g/control02.ctl’,’/data/test11g/control03.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’test11g’
*.db_recovery_file_dest=’/u01/product/fast_recovery_area’
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest=’/u01/product’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11gXDB)’
*.fast_start_mttr_target=0
*.local_listener=’LISTENER_TEST11G’
*.log_archive_dest_1=’LOCATION=/data/test11g/arch/’
*.log_archive_format=’%t_%s_%r.arc’
*.memory_target=661651456
*.open_cursors=1000
*.processes=1000
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.sessions=1105
*.sga_target=662700032
*.undo_tablespace=’UNDOTBS1′

     3.1.3 此时由 SPFILETEST11G.ORA创建INITTEST11G.ORA
SYS> create pfile from
spfile;

File created.
     3.1.4
VIM修改创造的inittest11g.ora文件,删除*.sga_target=662700032的值
(因为spfiletest11g.ora为二进制文件,不可能用VI/VIM命令直接修改),
 重新创立spfiletest11g.ora,此时spfiletest11g.ora中就不会包涵sga_target的值.
SYS> create spfile from
pfile
;
File created.
    3.1.5 启动DB OK
SYS> startup
ORACLE instance started.
Total System Global Area  661209088 bytes
Fixed Size                  1346980 bytes
ca88亚洲城网站,Variable Size             499122780 bytes
Database Buffers          155189248 bytes
Redo Buffers                5550080 bytes
Database mounted.
Database opened.
  
3.2 第二种方法,由如上格局成立出来的inittest11.ora PFILE开头化文档中,
直接助长*.sga_target=X (X为一个数值,
前提包证SGA的X值,被MEMORY_TARGET的值632M,减去剩余PGA的值超出10M(PGA最小值)),由修改后的PFILE成立SPFILE,
直接开行DB即可.
3.3 第三种方法,与第二种方法类似, 若是不想修改SGA_TARGET的值,
可以在pfile开始化文档中,
修改增大 *.memory_target=Y(Y为一个数值,Y值不可以超出物理内存大小)的值.
在由修改后的PFILE,创制SPFILE启动DB.

  1. 小结.
    4.1 假使是正规库PGA不要安装太小,要依照作业需求,
    假如用户进度有大的面世,排序等须要把PGA设置大点.
    正常PGA默许值为20%的MEMORY_TARGET值, SGA为80%的MEMORY_TARGET值.
    4.2 pfile与spfile如上文设置的新值,
    sga_target或memory_target在DB启动后,都会覆盖先前DB设置的sga_target或memory_target的值.

 

http://blog.itpub.net/28569596/viewspace-1172469/

 

 

https://docs.oracle.com/cd/B28359\_01/server.111/b28310/memory003.htm\#ADMIN11011

NAME                                 TYPE           VALUE

 

 

SGA_MAX_SIZE的值最好不要去修改,如若其值大于MEMORY_MAX_TARGET的话,就报ORA-00844 & ORA-00851
错误。如下所示

 

 

2:
没有安装SGA_MAX_SIZE,可是怎么SGA_MAX_SIZE一直有值,固然将其安装为0或利用reset
alter system set sga_max_size=0 scope=spfile; SGA_MAX_SIZE从来有值。官方关于SGA_MAX_SIZE的介绍如下:

 

 

 

The
simplest way to manage instance memory is to allow the Oracle Database
instance to automatically manage and tune it for you. To do so (on most
platforms), you set only a target memory size initialization parameter
(MEMORY_TARGET) and optionally a maximum memory size initialization
parameter (MEMORY_MAX_TARGET). The instance then tunes to the target
memory size, redistributing memory as needed between the system global
area (SGA) and the instance program global area (instance PGA). Because
the target memory initialization parameter is dynamic, you can change
the target memory size at any time without restarting the database. The
maximum memory size serves as an upper limit so that you cannot
accidentally set the target memory size too high, and so that enough
memory is set aside for the Oracle Database instance in case you do want
to increase total instance memory in the future. Because certain SGA
components either cannot easily shrink or must remain at a minimum size,
the instance also prevents you from setting the target memory size too
low.

ca88亚洲城网站 1

概念相比较介绍

 

 

·        
Automatic
Shared Memory Management (ASMM) was introduced in 10g. You enable the
automatic shared memory management feature by setting the SGA_TARGET
parameter to a non-zero value.

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL> show parameter memory_target ;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 0

SQL> show parameter memory_max_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_max_target                    big integer 0

SQL> 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1G

sga_target                           big integer 1G

 

 

 

·        
Oracle
Database 10g

 

 

 

           
Automatic Memory Management keeps the same behaviour as in 11g.

 

 

http://oracle-help.com/oracle-database/relationship-memory\_target-sga\_target-pga\_aggregate\_target/

http://oracle-help.com/oracle-database/relationship-memory\_target-sga\_target-pga\_aggregate\_target/

 

 

概念相比较介绍

 

o   PGA_AGGREGATE_TARGET
Introduction of Automatic PGA Memory management

*.memory_max_target=0

 

 

 

AMM and Linux Huge
Pages

自行共享内存管理(Automatic
Shared Memory Management ASMM)是ORACLE
10g启幕引入的的新技巧,ASMM用来贯彻SGA的机关管理。。当启用自动共享内存管理后,不再须求为每个内存组件设定值,当然若是您设置SGA_TARGET的同时,设置了db_cache_size、shared_pool_size这个参数,那么db_cache_size、shared_pool_size那些参数值会作为最小值要求。官方关于Automatic
Shared Memory Management的牵线如下:

  1. Automatic
    Shared Memory Management – For the SGA

 

 

 

 

·        
If
neither is set, they will be auto-tuned without any minimum or default
values. We will have a policy of distributing the total memory set by
MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during
initialization. The policy is to give 60% to the SGA and 40% to the PGA
at startup.

 

 

https://www.jianshu.com/p/9715280a4ced

自动内存管理(AMM)启动之后,系统共享段变为“虚拟”共享段。

File
created.

大家上面照旧通过试验一一验证一下:

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> show parameter pga_aggregate_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> alter system set memory_target=696m scope=both;

 

System altered.

 

SQL> alter system set memory_target=600m scope=both;

 

System altered.

 

SQL> alter system set memroy_target=500m scope=both;

alter system set memroy_target=500m scope=both

                 *

ERROR at line 1:

ORA-02065: illegal option for ALTER SYSTEM

 

 

SQL>  alter system set memory_target=500m scope=both;

 alter system set memory_target=500m scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 600M

 

 

如下所示,首先检查参数文件类型,然后修改参数sga_target、memory_max_target、memory_target。因为中间有些参数为静态参数,所以在改动参数后,要求重启数据库。

 

设若设置了PGA_AGGREGATE_TARGET并且没有安装SGA_TARGET,数据库依然会自行调整那八个参数。
SGA_TARGET将被早先化为MEMORY_TARGET –
PGA_AGGREGATE_TARGET,并在那一个值和SGA_MAX_SIZE这么些区间范围内机关调整

 

 

 

·        
If
neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow
the same policy as we have today; PGA will be auto-tuned and the SGA
will not be auto-tuned and parameters for some of the SGA components
will have to be set explicitly (for SGA_TARGET).

此时内需转变对应spfile的pfile文件,然后找到*.sga_max_size那一个值,删除后重新生成对应的spfile,启动数据库实例即可,当然你也得以设置其值超出MEMORY_TARGET即可。

 

ORACLE
10G版本起初推出了ASMM,自动SGA管理,它的出现一定水平上扶持DBA解决了管住SGA的题目,通过设置参数SGA_TARGET来控制ASMM,其中SGA_TARGET为零意味禁用ASMM,非零值表示启用ASMM。可是在10G
R1等中期版本,ASMM还不够成熟,而且存在相比多的BUG,导致了相比多的题材。在ORACLE
10g R2后续版本中,ASMM才日渐周密并成熟。

The
simplest way to manage instance memory is to allow the Oracle Database
instance to automatically manage and tune it for you. To do so (on most
platforms), you set only a target memory size initialization parameter
(MEMORY_TARGET) and optionally a maximum memory size initialization
parameter (MEMORY_MAX_TARGET). The instance then tunes to the target
memory size, redistributing memory as needed between the system global
area (SGA) and the instance program global area (instance PGA). Because
the target memory initialization parameter is dynamic, you can change
the target memory size at any time without restarting the database. The
maximum memory size serves as an upper limit so that you cannot
accidentally set the target memory size too high, and so that enough
memory is set aside for the Oracle Database instance in case you do want
to increase total instance memory in the future. Because certain SGA
components either cannot easily shrink or must remain at a minimum size,
the instance also prevents you from setting the target memory size too
low.

   
自动共享内存管理(ASMM): 在memory_target=0 and
sga_target为非0的景观下是电动内存管理

*.memory_max_target=0

自动内存管理(AMM)启动之后,系统共享段变为“虚拟”共享段。

 

一般来说所示,当前试验环境下活动内存管理已被剥夺(memory_target=0)

 

AMM切换到ASMM

11g
MEMORY_TARGET Parameter Dependency

ORACLE
12c      跟11g一样,没有变化

 

ORACLE
11g AMM 的引入, 组合出来有 5 种内存管理形式.

[oracle@DB-Server ~]$ ipcs -m

 

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0x00000000 4128770    root      644        80         2                       

0x00000000 4161540    root      644        16384      2                       

0x00000000 4194309    root      644        280        2                       

0xfc5d1940 7077905    oracle    660        4096       0            

 

如上测试所示,即使设置了pga_aggregate_target和sga_target,那么pga_aggregate_target

 

ca88亚洲城网站 2

 

 

 

信任有些人会对ORACLE当中的AMM(Automatic
Memory Management)与ASMM(Automatic Shared Memory
Management)有些迷惑或歪曲,不清楚两者的异议,本文会从多少个方面来总括一下两岸的异同。如有不足或疏漏之处,敬请指正!

https://support.oracle.com/epmos/faces/DocumentDisplay?\_afrLoop=275836827924018&id=443746.1&\_afrWindowMode=0&\_adf.ctrl-state=149nyur949\_198

http://blog.ronnyegner-consulting.de/2010/03/31/memory_target-sga_target-or-hugepages-which-to-choose/

 

 

 

 

参考资料:

SQL>
create spfile from pfile;

 

·        
If
SGA_TARGET is set we will only auto-tune the sizes of the components of
the SGA. PGA will be autotuned independent of whether it is explicitly
set or not. However, the combination of SGA and PGA will not be
auto-tuned, i.e. the SGA and PGA will not share memory and resize as
with the case of MEMORY_TARGET being set to a non-zero value.

 

·        
 

  1. Automatic
    PGA Memory Management –For the Instance PGA

 

o   DB_CACHE_SIZE
instead of DB_BLOCK_BUFFERS

 

 

 

 

  1. Manual
    PGA Memory Management – For the Instance PGA

 

File
created.

 

那么有个问号,自动内存管理(AMM)情势下边,sga_max_size、sga_target、memory_max_target、memory_target、pga_aggregate_target那多少个参数的涉及是哪些的呢?其实官方文档已经有详实阐释

 

 

   
自动内存管理(AMM)   :
memory_target=非0,是自行内存管理,如若开始化参数 LOCK_SGA=TRUE,则
AMM 是不可用的。

在11g中,纵然运用ASMM,对应的内存共享段是实在的共享段。

 

 

ca88亚洲城网站 3

 

When
automatic SGA memory management is enabled, the sizes of the different
SGA components are flexible and can adapt to the needs of a workload
without requiring any additional configuration. The database
automatically distributes the available memory among the various
components as required, allowing the system to maximize the use of all
available SGA memory.

·        
Oracle
9i

咱俩下边仍旧经过试验一一验证一下:

On
Windows 32-bit platforms, the default value of SGA_MAX_SIZE is the
largest of the following values:

If
you create your database with Database Configuration Assistant (DBCA)
and choose the basic installation option, automatic memory management is
enabled. If you choose advanced installation, Database Configuration
Assistant (DBCA) enables you to select automatic memory
management.

ORACLE
10G本子早先推出了ASMM,自动SGA管理,它的出现一定水准上帮助DBA解决了管理SGA的问题,通过安装参数SGA_TARGET来控制ASMM,其中SGA_TARGET为零象征禁用ASMM,非零值表示启用ASMM。可是在10G
R1等中期版本,ASMM还不够成熟,而且存在相比较多的BUG,导致了相比较多的题目。在ORACLE
10g R2后续版本中,ASMM才逐步完善并成熟。

 

 

https://support.oracle.com/epmos/faces/DocumentDisplay?\_afrLoop=275836827924018&id=443746.1&\_afrWindowMode=0&\_adf.ctrl-state=149nyur949\_198

 

 

·        
Automatic
Shared Memory Management (ASMM) was introduced in 10g. You enable the
automatic shared memory management feature by setting the SGA_TARGET
parameter to a non-zero value.

 

·        
60%
of MEMORY_TARGET, if specified

If
MEMORY_TARGET is set to a non-zero value:

·        
Oracle
9i

   
手工共享内存管理      : memory_target=0 and
sga_target=0 
指定
share_pool_size 、db_cache_size 等 sga 参数

 

正如所示,当前尝试环境下活动内存管理已被剥夺(memory_target=0)

深信有些人会对ORACLE当中的AMM(Automatic
Memory Management)与ASMM(Automatic Shared Memory
Management)有些迷惑或歪曲,不清楚两者的异议,本文会从多少个地点来总括一下两端的异议。如有不足或疏漏之处,敬请指正!

1:当MEMORY_TARGET大于0的动静下,可以安装SGA_TARGET、PGA_AGGREGATE_TARGET的值为非0,对应的意义分别如下:

 

 

 

SQL> alter system set pga_aggregate_target=200m scope=both;

 

System altered.

 

SQL> show parameter pga_aggregate_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> 

 

 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M    #后面讲述为什么sga_max_size为什么没有被置为0

sga_target                           big integer 0

SQL> alter system set sga_target=400m scope=both;

 

System altered.

 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> 

 

·        
Oracle
Database 12c

 

 

11g
MEMORY_TARGET Parameter Dependency

重启数据库后,检校对应参数。

 

ORACLE
12c      跟11g相同,没有变化

 

SQL> show parameter spfile;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.1.0

                                                 /dbhome_1/dbs/spfilegsp.ora

SQL> alter system set sga_max_size=0 scope=spfile;

 

System altered.

 

SQL> alter system set sga_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set pga_aggregate_target=0 scope=spfile;

 

 

SQL> alter system set memory_max_target=1G scope=spfile;

 

System altered.

 

SQL> alter system set memory_target=1G scope=spfile;

 

System altered.

 

SQL> 
SQL> show parameter target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 1552M

memory_target                        big integer 1552M

parallel_servers_target              integer     16

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

SQL> alter system set memory_max_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set memory_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set pga_aggregate_target=200m scope=spfile;

 

System altered.

 

SQL> alter system set sga_max_size=1g scope=spfile;

 

System altered.

 

SQL> alter system set sga_target=1g scope=spfile;

 

System altered.

 

SQL> 

 

 

SQL> startup

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1073741824 cannot be set to more than MEMORY_MAX_TARGET 0.

SQL> 

·        
60%
of MEMORY_MAX_TARGET, if specified

   

 

 

https://www.jianshu.com/p/9715280a4ced

设若设置了SGA_TARGET并且PGA_AGGREGATE_TARGET没有设置,数据库依旧会活动调整那三个参数。
PGA_AGGREGATE_TARGET将被起初化为MEMORY_TARGET –
SGA_TARGET的值。

On
64-bit platforms and non-Windows 32-bit platforms, when either
MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of
SGA_MAX_SIZE is set to the larger of the two parameters. This causes
more address space to be reserved for expansion of the SGA.

 

从ORACLE揭橥的版本历史(时间轴)来看,ORACLE的内存管理的大致历程如下:

 

http://www.dba-oracle.com/t_amm_automatic_memory.htm

 

   
即使设置了SGA_TARGET和PGA_AGGREGATE_TARGET,它们分别代表SGA的的最小值和PGA的细微值。MEMORY_TARGET值的限制可以从SGA_TARGET

Oracle
Database 11g

supports various memory management methods, which are chosen by
initialization parameter settings. Oracle recommends that you enable the
automatic memory management method.

ORACLE
11g AMM 的引入, 组合出来有 5 种内存管理方式.

File
created.

Oracle Memory Management and
HugePage

那么有个问号,自动内存管理(AMM)形式下边,sga_max_size、sga_target、memory_max_target、memory_target、pga_aggregate_target那多少个参数的关联是什么的吧?其实官方文档已经有详实阐释

ASMM切换到AMM

 

 

 

   
自动共享内存管理(ASMM): 在memory_target=0 and
sga_target为非0的图景下是全自动内存管理

  1. Automatic
    Memory Management – For Both the SGA and Instance PGA

 

·        
Oracle
Database 10g

 

ORACLE
11g始于推出AMM,它是ORACLE在ASMM的功底上的尤其内存管理自动化的朝秦暮楚。ASMM是活动管理SGA,而AMM则是将SGA与PGA联合起来活动管理、调整。只要求设置memory_target一个参数就能够成功全体数据库实例内存的配置。可是那个功效没被大规模运用,因为AMM最大的问题在于无法应用规范大页。有时候为了利用标准大页功用,可能有点系统会禁用AMM.那么到底是用AMM依然利用大页呢?很三个人(大师)倾向使用大页功用而非AMM,关于那么些可以参见上边博文

参考资料:

 

·        
If
SGA_MAX_SIZE is not user set, it is internally set to
MEMORY_MAX_TARGET.

http://blog.ronnyegner-consulting.de/2010/03/31/memory_target-sga_target-or-hugepages-which-to-choose/

 

 

If
you create your database with Database Configuration Assistant (DBCA)
and choose the basic installation option, automatic memory management is
enabled. If you choose advanced installation, Database Configuration
Assistant (DBCA) enables you to select automatic memory
management.

o   DB_nK_CACHE_SIZE
for multiple block sizes

到了11g过后,ORACLE又达成了PGA和SGA的联结机动管理
,那么些叫做自动化内存管理(Automatic Memory
Management,AMM)。从那个衍生和变化历程来看,ORACLE从最起头的手动配置各样零部件参数,逐渐逐渐向智能化、傻瓜化、自动化的方向逐步前进。这一个是一个早晚的历史趋势。关于AMM的官方文档介绍如下:

About
Automatic Memory Management

 

 

About
Automatic Memory Management

 

 

 

 

·        
Automatic
Memory Management is being introduced in 11g. This enables automatic
tuning of PGA and SGA with use of two new parameters named
MEMORY_MAX_TARGET and MEMORY_TARGET

 

SQL> alter system set sga_max_size=1025M scope=spfile;

 

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-00844: Parameter not taking MEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1090519040 cannot be set to more than MEMORY_TARGET 637534208.

SQL> 

 

*.memory_target=0

 

ORACLE
10g      PGA自动管理,SGA自动管理(ASMM,自动共享内存管理)

 

 

 

 

·        
Oracle
Database 12c

 

官方文档的介绍资料如下:

 

 

ORACLE
11g起来生产AMM,它是ORACLE在ASMM的底蕴上的愈来愈内存管理自动化的演进。ASMM是自行管理SGA,而AMM则是将SGA与PGA联合起来活动管理、调整。只要求安装memory_target一个参数就足以做到总体数据库实例内存的配备。然而这么些功效没被大规模拔取,因为AMM最大的问题在于不能够动用正式大页。有时候为了利用正式大页功效,可能有些系统会禁用AMM.那么究竟是用AMM照旧使用大页呢?很三个人(大师)倾向利用大页作用而非AMM,关于这几个可以参照上面博文

·        
If
SGA_TARGET is set we will only auto-tune the sizes of the components of
the SGA. PGA will be autotuned independent of whether it is explicitly
set or not. However, the combination of SGA and PGA will not be
auto-tuned, i.e. the SGA and PGA will not share memory and resize as
with the case of MEMORY_TARGET being set to a non-zero value.

 

·        
25%
of the total available virtual address space

 

·        
If
PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still
auto-tune both parameters. SGA_TARGET will be initialized to the
minimum non-zero value of MEMORY_TARGET – PGA_AGGREGATE_TARGET and
SGA_MAX_SIZE and will auto tune its components.

  1. Automatic
    PGA Memory Management –For the Instance PGA

If
MEMORY_TARGET is set to a non-zero value:

 

 

 

http://www.dba-oracle.com/t_amm_automatic_memory.htm

  • sga_target
    必须低于等于memory_target,另外,memory_target也必须高于等于(pga_aggregate_target
  • sga_target)

ASMM切换到AMM

 

变迁对应的spfile的pfile文件,然后删除memory_max_target和memory_target三个参数后,然后生成对应的spfile,最终重启数据库实例即可。

File
created.

Automatic
Shared Memory Management

 

    自动 PGA
管理         : memory_target=0 and
workarea_size_policy=auto 
and PGA_AGGREGATE_TARGET=值

·        
Oracle
Database 11g

 

也就是说在64位平台和非Windows 32位平台上,当指定MEMORY_TARGET或MEMORY_MAX_TARGET时,SGA_MAX_SIZE的默认值将设置为两个参数中较大的一个。 这导致更多的地址空间被保留用于SGA的扩展。

这也是之前一直让我迷惑的地方。另外,官方文档建议:当切换到AMM,即使用MEMORY_TARGET时,不应该设置参数SGA_MAX_SIZE(用于ASMM),因为这样做会修复SGA的大

小,因此与MEMORY_TARGET的预期用法相冲突。(原文:Check also for SGA_MAX_SIZE being set. When switching to AMM, i.e. using MEMORY_TARGET,

the parameter SGA_MAX_SIZE (used for ASMM) should not be set as doing so fixes the size of the SGA, and hence conflicts

with the intended use of MEMORY_TARGET.)

 

 

AMM切换到ASMM

 

  1. Automatic
    Shared Memory Management – For the SGA

·        
If
neither is set, they will be auto-tuned without any minimum or default
values. We will have a policy of distributing the total memory set by
MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during
initialization. The policy is to give 60% to the SGA and 40% to the PGA
at startup.

如果SGA_TARGET和PGA_AGGREGATE_TARGET两者都没有设置的话,则它们将被自动调谐而从不别的最小值或默认值。 大家将有一个方针,在伊始化进度中,将由MEMORY_TARGET参数设置的总内存以固定的比例分配给SGA和PGA。 政策是在启动时给予SGA
60%和PGA 40%给PGA。

 

·        
If
SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered
the minimum values for the sizes of SGA and the PGA respectively.
MEMORY_TARGET values can range from SGA_TARGET +
PGA_AGGREGATE_TARGET to MEMORY_MAX_TARGET.

 

SGA_MAX_SIZE
specifies the maximum size of the SGA for the lifetime of the
instance.

 

选择AMM还是HugePages

 

法定文档的牵线资料如下:

 

https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams230.htm

 

 

 

 

 

 

也就是说在64位平台和非Windows 32位平台上,当指定MEMORY_TARGET或MEMORY_MAX_TARGET时,SGA_MAX_SIZE的默认值将设置为两个参数中较大的一个。 这导致更多的地址空间被保留用于SGA的扩展。

这也是之前一直让我迷惑的地方。另外,官方文档建议:当切换到AMM,即使用MEMORY_TARGET时,不应该设置参数SGA_MAX_SIZE(用于ASMM),因为这样做会修复SGA的大

小,因此与MEMORY_TARGET的预期用法相冲突。(原文:Check also for SGA_MAX_SIZE being set. When switching to AMM, i.e. using MEMORY_TARGET,

the parameter SGA_MAX_SIZE (used for ASMM) should not be set as doing so fixes the size of the SGA, and hence conflicts

with the intended use of MEMORY_TARGET.)

 

 

SQL>
create spfile from pfile;

SQL> show parameter memory_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 600M

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> show parameter pga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> 

SQL> 

SQL> alter system set pga_aggregate_target=201m scope=both;

alter system set pga_aggregate_target=300m scope=both

*

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

SGA_MAX_SIZE的值最好不要去修改,若是其值当先MEMORY_MAX_TARGET的话,就报ORA-00844 & ORA-00851
错误。如下所示

If
MEMORY_TARGET is not set or set to set to 0 explicitly (default value
is 0 for 11g):

How
To Convert A Database Using AMM (Automatic Memory Management) To A
Database That Has Been Configured With Hugepage

·        
25%
of the total available virtual address space

 

    自动 PGA
管理         : memory_target=0 and
workarea_size_policy=auto 
and PGA_AGGREGATE_TARGET=值

·        
If
SGA_MAX_SIZE is not user set, it is internally set to
MEMORY_MAX_TARGET.

 

 

o   Dynamic
Memory resizing

怎么样从AMM切换来HugePage,可以参见官方文档(ID
2128928.1)

·        
 

 

 

 

什么从AMM切换来HugePage,可以参见官方文档(ID
2128928.1)

·        
Oracle
Database 11g

  1. Manual
    Shared Memory Management – For the SGA

 

 

SQL> alter system set sga_max_size=1025M scope=spfile;

 

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-00844: Parameter not taking MEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1090519040 cannot be set to more than MEMORY_TARGET 637534208.

SQL> 

 

 

 

 

[oracle@DB-Server ~]$ ipcs -m

 

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0x00000000 4128770    root      644        80         2                       

0x00000000 4161540    root      644        16384      2                       

0x00000000 4194309    root      644        280        2                       

0xfc5d1940 7077905    oracle    660        4096       0            

 

 

 

到了11g之后,ORACLE又完成了PGA和SGA的合并机动管理
,那么些叫做自动化内存管理(Automatic Memory
Management,AMM)。从那些演变进度来看,ORACLE从最开首的手动配置各样零部件参数,渐渐逐渐向智能化、傻瓜化、自动化的取向逐步前进。那些是一个毫无疑问的野史趋势。关于AMM的官方文档介绍如下:

 

·        
60%
of MEMORY_MAX_TARGET, if specified

 

 

o   DB_CACHE_SIZE
instead of DB_BLOCK_BUFFERS

 

 

活动共享内存管理(Automatic
Shared Memory Management ASMM)是ORACLE
10g始于引入的的新技巧,ASMM用来兑现SGA的全自动管理。。当启用自动共享内存管理后,不再须要为各种内存组件设定值,当然假若您设置SGA_TARGET的同时,设置了db_cache_size、shared_pool_size这几个参数,那么db_cache_size、shared_pool_size这一个参数值会作为最小值需要。官方关于Automatic
Shared Memory Management的牵线如下:

If
MEMORY_MAX_TARGET has not been explicitly set, but MEMORY_TARGET has,
the instance automatically sets MEMORY_MAX_TARGET to the same value as
MEMORY_TARGET. If MEMORY_TARGET has not been explicitly set, but
MEMORY_MAX_TARGET has, then MEMORY_TARGET defaults to 0. After
instance startup, it then is possible to dynamically change
MEMORY_TARGET to a non-zero value, provided that it does not exceed the
value of MEMORY_MAX_TARGET.

 

 

 

On
64-bit platforms and non-Windows 32-bit platforms, when either
MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of
SGA_MAX_SIZE is set to the larger of the two parameters. This causes
more address space to be reserved for expansion of the SGA.

·        
Automatic
Memory Management is being introduced in 11g. This enables automatic
tuning of PGA and SGA with use of two new parameters named
MEMORY_MAX_TARGET and MEMORY_TARGET

ca88亚洲城网站 4

 

 

·        
If
SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still
auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to
a value of MEMORY_TARGET – SGA_TARGET.

SQL>
create pfile from spfile;

 

   
借使设置了SGA_TARGET和PGA_AGGREGATE_TARGET,它们各自代表SGA的的最小值和PGA的蝇头值。MEMORY_TARGET值的限定可以从SGA_TARGET

SQL> show parameter target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 1552M

memory_target                        big integer 1552M

parallel_servers_target              integer     16

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

SQL> alter system set memory_max_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set memory_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set pga_aggregate_target=200m scope=spfile;

 

System altered.

 

SQL> alter system set sga_max_size=1g scope=spfile;

 

System altered.

 

SQL> alter system set sga_target=1g scope=spfile;

 

System altered.

 

SQL> 

 

 

SQL> startup

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1073741824 cannot be set to more than MEMORY_MAX_TARGET 0.

SQL> 

 

·        
If
PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still
auto-tune both parameters. SGA_TARGET will be initialized to the
minimum non-zero value of MEMORY_TARGET – PGA_AGGREGATE_TARGET and
SGA_MAX_SIZE and will auto tune its components.

 

 

SQL> show parameter spfile;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.1.0

                                                 /dbhome_1/dbs/spfilegsp.ora

SQL> alter system set sga_max_size=0 scope=spfile;

 

System altered.

 

SQL> alter system set sga_target=0 scope=spfile;

 

System altered.

 

SQL> alter system set pga_aggregate_target=0 scope=spfile;

 

 

SQL> alter system set memory_max_target=1G scope=spfile;

 

System altered.

 

SQL> alter system set memory_target=1G scope=spfile;

 

System altered.

 

SQL> 

·        
60%
of MEMORY_TARGET, if specified

Oracle Memory Management and
HugePage

 

o   PGA_AGGREGATE_TARGET
Introduction of Automatic PGA Memory management

SQL>
create pfile from spfile;

 

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  517816320 bytes

Fixed Size                  2214776 bytes

Variable Size             159384712 bytes

Database Buffers          348127232 bytes

Redo Buffers                8089600 bytes

Database mounted.

SQL> show parameter memory

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1G

memory_target                        big integer 1G

shared_memory_address                integer     0

SQL> 

变迁对应的spfile的pfile文件,然后删除memory_max_target和memory_target八个参数后,然后生成对应的spfile,最后重启数据库实例即可。

    当然SGA_TARGET + PGA_AGGREGATE_TARGET的和必须低于等于memory_target,别的,如若还要设置了sga_target、pga_aggregate_target的值,memory_target的值必须当先等于sga_target与pga_aggregate_target之和。如下测试所示:

 

ca88亚洲城网站 5

 

·        
 

·        
 

 

 

Automatic
Shared Memory Management

·        
If
neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow
the same policy as we have today; PGA will be auto-tuned and the SGA
will not be auto-tuned and parameters for some of the SGA components
will have to be set explicitly (for SGA_TARGET).

 

 

 

 

  • sga_target
    必须低于等于memory_target,另外,memory_target也务必大于等于(pga_aggregate_target
  • sga_target)

 

    手动 PGA
管理         : memory_target=0 and
workarea_size_policy=manal 
然后指定 SORT_AREA_SIZE 等 PGA 参数,一般不利用手动管理PGA。

 

If
MEMORY_MAX_TARGET has not been explicitly set, but MEMORY_TARGET has,
the instance automatically sets MEMORY_MAX_TARGET to the same value as
MEMORY_TARGET. If MEMORY_TARGET has not been explicitly set, but
MEMORY_MAX_TARGET has, then MEMORY_TARGET defaults to 0. After
instance startup, it then is possible to dynamically change
MEMORY_TARGET to a non-zero value, provided that it does not exceed the
value of MEMORY_MAX_TARGET.

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> show parameter pga_aggregate_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> alter system set memory_target=696m scope=both;

 

System altered.

 

SQL> alter system set memory_target=600m scope=both;

 

System altered.

 

SQL> alter system set memroy_target=500m scope=both;

alter system set memroy_target=500m scope=both

                 *

ERROR at line 1:

ORA-02065: illegal option for ALTER SYSTEM

 

 

SQL>  alter system set memory_target=500m scope=both;

 alter system set memory_target=500m scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 600M
SQL> alter system set pga_aggregate_target=200m scope=both;

 

System altered.

 

SQL> show parameter pga_aggregate_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> 

 

 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M    #后面讲述为什么sga_max_size为什么没有被置为0

sga_target                           big integer 0

SQL> alter system set sga_target=400m scope=both;

 

System altered.

 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> 
SQL> !

[oracle@DB-Server ~]$ ipcs -m

 

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0x00000000 4128770    root      644        80         2                       

0x00000000 4161540    root      644        16384      2                       

0x00000000 4194309    root      644        280        2                       

0xfc5d1940 7012369    oracle    660        1075838976 49     

·        
Beginning
with Oracle9i, the dynamic SGA infrastructure allowed for the sizing of
the Buffer Cache, Shared Pool and the Large Pool without having to shut
down the database. Key features being:

·        
If
only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we
will not auto tune the SGA and PGA. It will default to 10gR2
behavior.

·        
If
only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we
will not auto tune the SGA and PGA. It will default to 10gR2
behavior.

  • PGA_AGGREGATE_TARGET到MEMORY_MAX_TARGET。

 

 

从ORACLE发表的本子历史(时间轴)来看,ORACLE的内存管理的光景历程如下:

此刻急需变更对应spfile的pfile文件,然后找到*.sga_max_size这一个值,删除后再度生成对应的spfile,启动数据库实例即可,当然你也足以设置其值当先MEMORY_TARGET即可。

 

 

 

 

 

 

在11g中,即便使用ASMM,对应的内存共享段是目不转睛的共享段。

2:
没有设置SGA_MAX_SIZE,可是怎么SGA_MAX_SIZE平昔有值,即使将其安装为0或利用reset
alter system set sga_max_size=0 scope=spfile; SGA_MAX_SIZE平昔有值。官方关于SGA_MAX_SIZE的介绍如下:

·        
If
SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still
auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to
a value of MEMORY_TARGET – SGA_TARGET.

Oracle
Database 11g

supports various memory management methods, which are chosen by
initialization parameter settings. Oracle recommends that you enable the
automatic memory management method.

 

   
自动内存管理(AMM)   :
memory_target=非0,是机关内存管理,假诺开始化参数 LOCK_SGA=TRUE,则
AMM 是不可用的。

·        
 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  517816320 bytes

Fixed Size                  2214776 bytes

Variable Size             159384712 bytes

Database Buffers          348127232 bytes

Redo Buffers                8089600 bytes

Database mounted.

SQL> show parameter memory

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1G

memory_target                        big integer 1G

shared_memory_address                integer     0

SQL> 

 

 

In
previous database releases, a database administrator (DBA) was required
to manually specify different SGA component sizes by setting a number of
initialization parameters, including the SHARED_POOL_SIZE,
DB_CACHE_SIZE, JAVA_POOL_SIZE, and LARGE_POOL_SIZE parameters.
Oracle Database 10g includes the Automatic Shared Memory Management
feature which simplifies the SGA memory management significantly. In
Oracle Database 10g, a DBA can simply specify the total amount of SGA
memory available to an instance using the SGA_TARGET initialization
parameter and the Oracle Database will automatically distribute this
memory among various subcomponents to ensure most effective memory
utilization.

 

ca88亚洲城网站 6

 

·        
 

*.memory_target=0

  • PGA_AGGREGATE_TARGET到MEMORY_MAX_TARGET。

ORACLE  9i 
    
PGA自动管理,SGA手动管理

 

 

o   Dynamic
Memory resizing

 

https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams230.htm

    手动 PGA
管理         : memory_target=0 and
workarea_size_policy=manal 
然后指定 SORT_AREA_SIZE 等 PGA 参数,一般不行使手动管理PGA。

选择AMM还是HugePages

 

ORACLE  9i 
    
PGA自动管理,SGA手动管理

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL> show parameter memory_target ;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 0

SQL> show parameter memory_max_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_max_target                    big integer 0

SQL> 

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1G

sga_target                           big integer 1G

 

 

How
To Convert A Database Using AMM (Automatic Memory Management) To A
Database That Has Been Configured With Hugepage

重启数据库后,检校对应参数。

ORACLE
10g      PGA自动管理,SGA自动管理(ASMM,自动共享内存管理)

 

·        
Beginning
with Oracle9i, the dynamic SGA infrastructure allowed for the sizing of
the Buffer Cache, Shared Pool and the Large Pool without having to shut
down the database. Key features being:

On
Windows 32-bit platforms, the default value of SGA_MAX_SIZE is the
largest of the following values:

 

SGA_MAX_SIZE
specifies the maximum size of the SGA for the lifetime of the
instance.

https://docs.oracle.com/cd/B28359\_01/server.111/b28310/memory003.htm\#ADMIN11011

假如设置了PGA_AGGREGATE_TARGET并且没有设置SGA_TARGET,数据库如故会活动调整那三个参数。
SGA_TARGET将被初步化为MEMORY_TARGET –
PGA_AGGREGATE_TARGET,并在那个值和SGA_MAX_SIZE那么些间隔范围内自动调整

   

如果SGA_TARGET和PGA_AGGREGATE_TARGET两者都并未设置的话,则它们将被自动调谐而没有其他最小值或默许值。 大家将有一个策略,在起始化进度中,将由MEMORY_TARGET参数设置的总内存以固定的百分比分配给SGA和PGA。 政策是在启动时给予SGA
60%和PGA 40%给PGA。

 

  1. Manual
    Shared Memory Management – For the SGA

In
previous database releases, a database administrator (DBA) was required
to manually specify different SGA component sizes by setting a number of
initialization parameters, including the SHARED_POOL_SIZE,
DB_CACHE_SIZE, JAVA_POOL_SIZE, and LARGE_POOL_SIZE parameters.
Oracle Database 10g includes the Automatic Shared Memory Management
feature which simplifies the SGA memory management significantly. In
Oracle Database 10g, a DBA can simply specify the total amount of SGA
memory available to an instance using the SGA_TARGET initialization
parameter and the Oracle Database will automatically distribute this
memory among various subcomponents to ensure most effective memory
utilization.

 

 

If
MEMORY_TARGET is not set or set to set to 0 explicitly (default value
is 0 for 11g):

 

SQL> show parameter memory_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 600M

SQL> show parameter sga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 496M

sga_target                           big integer 400M

SQL> show parameter pga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 200M

SQL> 

SQL> 

SQL> alter system set pga_aggregate_target=201m scope=both;

alter system set pga_aggregate_target=300m scope=both

*

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

    当然SGA_TARGET + PGA_AGGREGATE_TARGET的和必须低于等于memory_target,其余,假诺同时设置了sga_target、pga_aggregate_target的值,memory_target的值必须超越等于sga_target与pga_aggregate_target之和。如下测试所示:

MEMORY_TARGET (SGA_TARGET) or HugePages – which to
choose?

When
automatic SGA memory management is enabled, the sizes of the different
SGA components are flexible and can adapt to the needs of a workload
without requiring any additional configuration. The database
automatically distributes the available memory among the various
components as required, allowing the system to maximize the use of all
available SGA memory.

 

AMM and Linux Huge
Pages

·        
If
SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered
the minimum values for the sizes of SGA and the PGA respectively.
MEMORY_TARGET values can range from SGA_TARGET +
PGA_AGGREGATE_TARGET to MEMORY_MAX_TARGET.

 

 

 

  1. Manual
    PGA Memory Management – For the Instance PGA
  1. Automatic
    Memory Management – For Both the SGA and Instance PGA

如上测试所示,如若设置了pga_aggregate_target和sga_target,那么pga_aggregate_target

相关文章