ORACLE ASMM与AMM的下结论ca88官方会员登录

 

 

概念比较介绍

概念比较介绍

 

 

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

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

 

 

从ORACLE公布的版本历史(时间轴)来看,ORACLE的内部存款和储蓄器管理的差不多历程如下:

从ORACLE宣布的版本历史(时间轴)来看,ORACLE的内部存款和储蓄器管理的光景历程如下:

 

 

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

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

ORACLE
10g      PGA自动管理,SGA自动管理(ASMM,自动共享内部存款和储蓄器管理)

ORACLE
10g      PGA自动管理,SGA自动管理(ASMM,自动共享内部存款和储蓄器管理)

ORACLE
11g      PGA,SGA统一机动管理(AMM,自动内部存款和储蓄器管理)

ORACLE
11g      PGA,SGA统一机动管理(AMM,自动内部存款和储蓄器管理)

ORACLE
12c      跟11g同一,没有变动

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

 

 

 

 

官方文书档案的牵线资料如下:

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

 

 

·        
Oracle
9i

·        
Oracle
9i

 

 

·        
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:

·        
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:

o   Dynamic
Memory resizing

o   Dynamic
Memory resizing

o   DB_CACHE_SIZE
instead of DB_BLOCK_BUFFERS

o   DB_CACHE_SIZE
instead of DB_BLOCK_BUFFERS

o   DB_nK_CACHE_SIZE
for multiple block sizes

o   DB_nK_CACHE_SIZE
for multiple block sizes

o   PGA_AGGREGATE_TARGET
Introduction of Automatic PGA Memory management

o   PGA_AGGREGATE_TARGET
Introduction of Automatic PGA Memory management

 

 

·        
Oracle
Database 10g

·        
Oracle
Database 10g

 

 

·        
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.

·        
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.

 

 

·        
Oracle
Database 11g

·        
Oracle
Database 11g

 

 

·        
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

·        
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

·        
Oracle
Database 12c

·        
Oracle
Database 12c

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

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

 

 

 

 

自行共享内部存款和储蓄器管理(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的牵线如下:

机动共享内部存款和储蓄器管理(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的牵线如下:

 

 

Automatic
Shared Memory Management

Automatic
Shared Memory Management

 

 

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.

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.

 

 

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.

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
10G版本开头生产了ASMM,自动SGA管理,它的出现一定水平上救助DBA消除了管制SGA的题目,通过设置参数SGA_TARGET来控制ASMM,其中SGA_TA君越GET为零象征禁用ASMM,非零值表示启用ASMM。但是在10G
中华V1等中期版本,ASMM还不够成熟,而且存在比较多的BUG,导致了比较多的题材。在ORACLE
10g 奥迪Q32后续版本中,ASMM才稳步完善并成熟。

ORACLE
10G本子开首生产了ASMM,自动SGA管理,它的出现一定水准上救助DBA化解了管理SGA的难题,通过安装参数SGA_TARGET来控制ASMM,其中SGA_TA大切诺基GET为零代表禁止使用ASMM,非零值表示启用ASMM。可是在10G
大切诺基1等早先时代版本,ASMM还不够成熟,而且存在相比多的BUG,导致了比较多的标题。在ORACLE
10g CRUISER2后续版本中,ASMM才慢慢完善并成熟。

 

 

到了11g之后,ORACLE又实现了PGA和SGA的合并机动管理
,那个名为自动化内部存款和储蓄器管理(Automatic Memory
Management,AMM)。从那些演变进程来看,ORACLE从最初叶的手动配置各种零部件参数,慢慢慢慢向智能化、傻瓜化、自动化的取向稳步前进。这几个是三个早晚的野史趋势。关于AMM的官方文书档案介绍如下:

到了11g过后,ORACLE又实现了PGA和SGA的统一机动管理
,那几个名叫自动化内部存款和储蓄器管理(Automatic Memory
Management,AMM)。从这么些演变历程来看,ORACLE从最起先的手动配置各样零部件参数,慢慢稳步向智能化、傻瓜化、自动化的矛头稳步前进。那一个是一个势必的历史趋势。关于AMM的法定文书档案介绍如下:

 

 

About
Automatic Memory Management

About
Automatic Memory Management

 

 

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.

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.

 

 

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.

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
11g AMM 的引入, 组合出来有 5 种内部存款和储蓄器管理情势.

ORACLE
11g AMM 的引入, 组合出来有 5 种内部存款和储蓄器管理格局.

 

 

   
自动内部存款和储蓄器管理(AMM)   :
memory_target=非0,是电动内部存款和储蓄器管理,假设开首化参数 LOCK_SGA=TRUE,则
AMM 是不可用的。

   
自动内存管理(AMM)   :
memory_target=非0,是机动内部存款和储蓄器管理,若是开始化参数 LOCK_SGA=TRUE,则
AMM 是不可用的。

   
自动共享内部存储器管理(ASMM): 在memory_target=0 and
sga_target为非0的情事下是机动内部存款和储蓄器管理

   
自动共享内部存款和储蓄器管理(ASMM): 在memory_target=0 and
sga_target为非0的情状下是自行内部存储器管理

   
手工业共享内部存款和储蓄器管理      : memory_target=0 and
sga_target=0 
指定
share_pool_size 、db_cache_size 等 sga 参数

   
手工业共享内部存款和储蓄器管理      : memory_target=0 and
sga_target=0 
指定
share_pool_size 、db_cache_size 等 sga 参数

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

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

    手动 PGA
管理         : memory_target=0 and
workarea_size_policy=manal 
然后内定 SO陆风X8T_AREA_SIZE 等 PGA 参数,一般不使用手动管理PGA。

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

 

 

 

 

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
Database 11g

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

 

 

 

 

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

  1. Automatic
    Shared Memory Management – For the SGA
  1. Automatic
    Shared Memory Management – For the SGA

  1. Manual
    Shared Memory Management – For the SGA
  1. Manual
    Shared Memory Management – For the SGA

  1. Automatic
    PGA Memory Management –For the Instance PGA
  1. Automatic
    PGA Memory Management –For the Instance PGA

  1. Manual
    PGA Memory Management – For the Instance PGA
  1. Manual
    PGA Memory Management – For the Instance PGA

 

 

 

 

 

 

 

 

 

 

ASMM切换到AMM

ASMM切换到AMM

 

 

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

如下所示,当前试行环境下自行内部存款和储蓄器管理已被剥夺(memory_target=0)

 

 

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
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

 

 

 

 

在11g中,借使使用ASMM,对应的内部存款和储蓄器共享段是真性的共享段。

在11g中,借使运用ASMM,对应的内部存款和储蓄器共享段是动真格的的共享段。

 

 

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     
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、memory_max_target、memory_target。因为中间有个别参数为静态参数,所以在修改参数后,需求重启数据库。

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

 

 

 

 

 

 

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 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> 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> 
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> 

 

 

机关内部存款和储蓄器管理(AMM)运维之后,系统共享段变为“虚拟”共享段。

自动内部存款和储蓄器管理(AMM)运行之后,系统共享段变为“虚拟”共享段。

 

 

[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            
[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
MEMORY_TARGET Parameter Dependency

11g
MEMORY_TARGET Parameter Dependency

 

 

那么有个问号,自动内部存款和储蓄器管理(AMM)情势上边,sga_max_size、sga_target、memory_max_target、memory_target、pga_aggregate_target那多少个参数的涉及是什么的呢?其实官方文书档案已经有详细阐释

那便是说有个疑问,自动内部存款和储蓄器管理(AMM)情势上面,sga_max_size、sga_target、memory_max_target、memory_target、pga_aggregate_target那多少个参数的涉嫌是何等的吗?其实官方文书档案已经有详尽阐释

 

 

If
MEMORY_TARGET is set to a non-zero value:

If
MEMORY_TARGET is set to a non-zero value:

 

 

·        
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.

·        
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.

·        
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.

·        
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.

·        
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.

·        
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.

·        
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.

·        
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.

 

 

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.

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.

 

 

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

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

·        
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.

·        
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.

·        
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).

·        
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).

·        
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.

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

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

 

 

咱俩上边依旧经过实验一一验证一下:

作者们上边照旧通过试验一一验证一下:

 

 

1:当MEMORY_TA陆风X8GET大于0的意况下,能够设置SGA_TARGET、PGA_AGGREGATE_TA奥德赛GET的值为非0,对应的意思分别如下:

1:当MEMORY_TA凯雷德GET大于0的状态下,能够设置SGA_TARGET、PGA_AGGREGATE_TAPAJEROGET的值为非0,对应的意义分别如下:

 

 

   
假诺设置了SGA_TARGET和PGA_AGGREGATE_TA大切诺基GET,它们分别表示SGA的的最小值和PGA的纤维值。MEMO奥迪Q5Y_TA宝马X5GET值的限制能够从SGA_TARGET

   
假若设置了SGA_TARGET和PGA_AGGREGATE_TA途观GET,它们各自代表SGA的的最小值和PGA的微小值。MEMO福睿斯Y_TA奥德赛GET值的限定能够从SGA_TARGET

  • PGA_AGGREGATE_TARGET到MEMORY_MAX_TARGET。
  • PGA_AGGREGATE_TARGET到MEMORY_MAX_TARGET。

    当然SGA_TARGET + PGA_AGGREGATE_TAEscortGET的和必须低于等于memory_target,其它,倘诺还要安装了sga_target、pga_aggregate_target的值,memory_target的值必须超越等于sga_target与pga_aggregate_target之和。如下测试所示:

    当然SGA_TARGET + PGA_AGGREGATE_TACRUISERGET的和必须低于等于memory_target,此外,假若还要设置了sga_target、pga_aggregate_target的值,memory_target的值必须超出等于sga_target与pga_aggregate_target之和。如下测试所示:

 

 

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> 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> 

ca88官方会员登录 1

ca88官方会员登录 2

 

 

 

 

 

 

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> 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

 

 

ca88官方会员登录 3

ca88官方会员登录 4

 

 

 

 

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
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

 

 

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

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

  • sga_target
    必须低于等于memory_target,另外,memory_target也必须当先等于(pga_aggregate_target
  • sga_target)
  • sga_target
    必须低于等于memory_target,另外,memory_target也亟须领先等于(pga_aggregate_target
  • sga_target)

 

ca88官方会员登录, 

若果设置了SGA_TARGET并且PGA_AGGREGATE_TAHavalGET没有安装,数据库如故会自动调整那七个参数。
PGA_AGGREGATE_TATucsonGET将被初阶化为MEMO奥迪Q5Y_TARGET –
SGA_TARGET的值。

若是设置了SGA_TARGET并且PGA_AGGREGATE_TA牧马人GET没有设置,数据库照旧会自行调整那多少个参数。
PGA_AGGREGATE_TA中华VGET将被发轫化为MEMO宝马X3Y_TARGET –
SGA_TARGET的值。

万一设置了PGA_AGGREGATE_TALX570GET并且没有安装SGA_TA奥迪Q3GET,数据库照旧会活动调整那四个参数。
SGA_TA兰德奔驰G级GET将被早先化为MEMO奇骏Y_TARGET –
PGA_AGGREGATE_TATiggoGET,并在这一个值和SGA_MAX_SIZE这一个区间范围内电动调整

借使设置了PGA_AGGREGATE_TA福睿斯GET并且没有设置SGA_TAEscortGET,数据库依旧会自行调整那多少个参数。
SGA_TAENCOREGET将被初阶化为MEMO昂CoraY_TARGET –
PGA_AGGREGATE_TA宝马X5GET,并在那一个值和SGA_MAX_SIZE那么些距离范围内自行调整

如果SGA_TARGET和PGA_AGGREGATE_TATiguanGET两者都不曾设置的话,则它们将被自动调谐而没有别的最小值或默许值。 大家将有1个策略,在起始化进度中,将由MEMO酷威Y_TAGL450GET参数设置的总内存以固定的比重分配给SGA和PGA。 政策是在运营时给予SGA
百分之六十和PGA 4/10给PGA。

如果SGA_TARGET和PGA_AGGREGATE_TA库罗德GET两者都不曾安装的话,则它们将被自动调谐而尚未别的最小值或暗许值。 大家将有多个国策,在初步化进度中,将由MEMO卡宴Y_TAEvoqueGET参数设置的总内部存款和储蓄器以固定的百分比分配给SGA和PGA。 政策是在运行时给予SGA
百分之六十和PGA 百分之四十给PGA。

 

 

 

 

2:
没有安装SGA_MAX_SIZE,不过为何SGA_MAX_SIZE一向有值,即便将其设置为0或选择reset
alter system set sga_max_size=0 scope=spfile; SGA_MAX_SIZE从来有值。官方关于SGA_MAX_SIZE的牵线如下:

2:
没有设置SGA_MAX_SIZE,可是怎么SGA_MAX_SIZE一向有值,固然将其安装为0或行使reset
alter system set sga_max_size=0 scope=spfile; SGA_MAX_SIZE一向有值。官方关于SGA_MAX_SIZE的牵线如下:

 

 

 

 

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

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

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.

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.

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

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

·        
 

·        
 

·        
60%
of MEMORY_TARGET, if specified

·        
60%
of MEMORY_TARGET, if specified

·        
 

·        
 

·        
60%
of MEMORY_MAX_TARGET, if specified

·        
60%
of MEMORY_MAX_TARGET, if specified

·        
 

·        
 

·        
25%
of the total available virtual address space

·        
25%
of the total available virtual address space

 

也就是说在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.)

 

 

 

也就是说在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.)

 

 

ca88官方会员登录 5

ca88官方会员登录 6

 

 

   

   

SGA_MAX_SIZE的值最好不要去修改,如若其值超越MEMOLX570Y_MAX_TA大切诺基GET的话,就报ORA-00844 & ORA-00851
错误。如下所示

SGA_MAX_SIZE的值最好不要去修改,假诺其值超越MEMORAV4Y_MAX_TA兰德酷路泽GET的话,就报ORA-00844 & ORA-00851
错误。如下所示

 

 

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> 
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> 

 

 

 

 

那会儿急需扭转对应spfile的pfile文件,然后找到*.sga_max_size这么些值,删除后再一次生成对应的spfile,运转数据库实例即可,当然你也足以设置其值大于MEMO猎豹CS6Y_TARGET即可。

此时亟需扭转对应spfile的pfile文件,然后找到*.sga_max_size这些值,删除后再次生成对应的spfile,运行数据库实例即可,当然你也得以设置其值大于MEMOLX570Y_TARGET即可。

 

 

 

 

AMM切换到ASMM

AMM切换到ASMM

 

 

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> 
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> 

 

 

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

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

 

 

SQL>
create pfile from spfile;

SQL>
create pfile from spfile;

 

 

File
created.

File
created.

 

 

 

 

*.memory_max_target=0

*.memory_max_target=0

*.memory_target=0

*.memory_target=0

 

 

SQL>
create spfile from pfile;

SQL>
create spfile from pfile;

 

 

File
created.

File
created.

 

 

SQL>

SQL>

 

 

 

 

选择AMM还是HugePages

选择AMM还是HugePages

 

 

ORACLE
11g始发推出AMM,它是ORACLE在ASMM的基础上的尤其内部存款和储蓄器管理自动化的朝令夕改。ASMM是机关管理SGA,而AMM则是将SGA与PGA联合起来活动管理、调整。只须求设置memory_target1个参数就可以实现全体数据库实例内部存款和储蓄器的布局。然而这几个效果没被普遍利用,因为AMM最大的题材在于不可能采纳标准大页。有时候为了利用专业余大学页成效,恐怕有点系统会禁用AMM.那么到底是用AMM依旧利用大页呢?很几个人(大师)倾向使用大页效用而非AMM,关于那么些能够参照上边博文

ORACLE
11g开头生产AMM,它是ORACLE在ASMM的功底上的越发内部存款和储蓄器管理自动化的多变。ASMM是机关管理SGA,而AMM则是将SGA与PGA联合起来活动管理、调整。只要求安装memory_target三个参数就能够完毕全套数据库实例内部存储器的布局。可是那么些作用没被广大利用,因为AMM最大的难题在于无法利用规范大页。有时候为了选取规范大页作用,大概有点系统会禁止使用AMM.那么究竟是用AMM还是利用大页呢?很几人(大师)倾向使用大页功效而非AMM,关于这几个能够参照上面博文

 

 

MEMORY_TARGET (SGA_TARGET) or HugePages – which to
choose?

MEMORY_TARGET (SGA_TARGET) or HugePages – which to
choose?

AMM and Linux Huge
Pages

AMM and Linux Huge
Pages

Oracle Memory Management and
HugePage

Oracle Memory Management and
HugePage

 

 

 

 

什么样从AMM切换来HugePage,能够参见官方文书档案(ID
2128928.1)

什么从AMM切换来HugePage,能够参照官方文书档案(ID
2128928.1)

 

 

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

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

 

 

 

 

参考资料:

参考资料:

 

 

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

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

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

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

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

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

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

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

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

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

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/

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

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

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图