Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Overview of oracle 11g automatic memory Management". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1: Overview of Oracle 11g automatic memory management

The new memory management feature in oracle11g automates memory management (automatic memory management). Under this new feature, SGA and PGA will automatically expand and contract as needed. You only need to set two memory management related parameters memory_target and memory_max_target.

1) memory_target: this parameter sets the amount of memory that can be used by the entire oracle database instance. The oralce database instance will automatically adjust the size of SGA and PGA according to the requirements. You can use the corresponding alter system command to dynamically modify the value of memory_target. This parameter is a dynamic initialization parameter.

2) memory_max_target: this parameter sets the maximum amount of memory that an oracle instance can use. The following constraint relationship memory_target show parameter target must be maintained when adjusting the value of memory_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 62848M

Memory_target big integer 62848M

Parallel_servers_target integer 512

Pga_aggregate_target big integer 4G

Sga_target big integer 8G

Second: how to allocate each memory at a certain time when Oracle is running?

You can view the following via create pfile from spfile:

[oracle@JoinSpider dbs] $cat initora11g.ora

Ora11g.__db_cache_size=2281701376

Ora11g.__java_pool_size=939524096

Ora11g.__large_pool_size=805306368

Ora11g.__oracle_base='/files/oracle'#ORACLE_BASE set from environment

Ora11g.__pga_aggregate_target=57310969856 # # this is the value of the current actual allocation pga

Ora11g.__sga_target=5637144576 # # this is the value of the current actual allocation sga

Ora11g.__shared_io_pool_size=0

Ora11g.__shared_pool_size=3758096384

Ora11g.__streams_pool_size=0

* .audit_file_dest='/files/oracle/admin/ora11g/adump'

* .audit_trail='db'

* .compatible='11.2.0.4.0'

* .control_files='/files/oracle/oradata/ora11g/control01.ctl','/files/oracle/fast_recovery_area/ora11g/control02.ctl'

* .db_block_size=8192

* .db_domain=''

* .db_name='ora11g'

* .db_recovery_file_dest='/files/oracle/fast_recovery_area'

* .db_recovery_file_dest_size=4385144832

* .diagnostic_dest='/files/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=ora11gXDB)'

* .memory_max_target=65899345920

* .memory_target=65899345920

* .open_cursors=300

* .pga_aggregate_target=4294967296 # this is the minimum

* .processes=1500

* .remote_login_passwordfile='EXCLUSIVE'

* .sga_target=8589934592 # this is the minimum

* .undo_retention=3600

* .undo_tablespace='UNDOTBS1'

Third, how to enable Oracle11g memory management:

1: set automatic memory management during database creation: if you build the database manually, you only need to set the following initialization parameters:

Memory_target = 1000m

Memory_max_target = 1500m

If you are using DBCA to create a database, if you are creating a new library and an advanced installation, you can choose the automatic memory management option. If you choose a basic installation, the default is automatic memory management.

2: after database creation: you can add memory_max_target and memory_target after database creation. Although memory_target is a dynamic parameter, you cannot swap to automatic memory management when the database is running. You must restart the database to apply the parameters to the instance startup process.

Fourth, the meaning and principles of setting parameters related to Oracle11g memory management:

When MEMORY_TARGET is set to a nonzero value:

1: if SGA_TARGET and PGA_AGGREGATE_TARGET are set, they are treated as minimum values for SGA size and PGA size, respectively.

2: if SGA_TARGET is set but PGA_AGGREGATE_TARGET is not set, these two parameters are still automatically optimized. PGA_AGGREGATE_TARGET is initialized to the following values: (MEMORY_TARGET-SGA_TARGET).

3: if PGA_AGGREGATE_TARGET is set but SGA_TARGET is not set, these two parameters are still automatically optimized. SGA_TARGET is initialized to the value min (MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if the user has set it), and the system automatically optimizes the subcomponents.

4: if no parameters are set, these two parameters can be automatically optimized without a minimum or default value. There is a strategy of assigning MEMORY_TARGET to SGA and PGA at a fixed rate during initialization. The details are as follows:

Sga_target = memory_target * 60%

Pga_aggregate_target=memory_target * 40%

If MEMORY_TARGET is not set, or if it is explicitly set to 0 (the default value in 11g is 0), the role of memory_target is eliminated, which is exactly the same as 10g in memory management. :

1. If SGA_TARGET is set, the system automatically optimizes only the subcomponent size of SGA. Automatic optimization of PGA is independent of whether or not to explicitly set PGA. However, the entire SGA (SGA_TARGET) and PGA (PGA_AGGREGATE_TARGET) are not automatically optimized, that is, SGA and PGA do not automatically grow or contract.

two。 If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, the size of each component in SGA should be clearly set and cannot be resized automatically. PGA does not automatically grow and contract.

3. If only MEMORY_MAX_TARGET is set, MEMORY_TARGET defaults to 0 when manually set using a text initialization file. The auto-tuning behavior of SGA and PGA is the same by default as in 10g R2.

4. If SGA_MAX_SIZE is not set by the user, if the user sets MEMORY_MAX_TARGET, the system will set it to MEMORY_MAX_TARGET internally (regardless of whether the user sets SGA_TARGET)

Fifth: how to confirm the value of MEMORY_TARGET if a 11g library system without automatic memory management is changed to automatic memory management?

1. Enabling automatic memory management on a system is actually very simple. You don't need to do much in advance. You can use the following formula to calculate MEMORY_TARGET: MEMORY_TARGET=SGA_TARGET+GREATEST (PGA_AGGREGATE_TARGET, "maximum PGA allocated")

The following query shows you the relevant information and how to calculate the required memory_ target value:

1. Method 1: use sql to find out the values of SGA_TARGET, PGA_AGGREGATE_TARGET and maximum PGA allocated, and then calculate the result according to the formula MEMORY_TARGET=SGA_TARGET+GREATEST (PGA_AGGREGATE_TARGET, "maximum PGA allocated"). Note that the function GREATEST is to find the maximum of the two values:

SELECT name, value/1024/1024

FROM v$parameter

WHERE name IN ('pga_aggregate_target',' sga_target')

UNION

SELECT 'maximum PGA allocated' AS name, TO_CHAR (value) / 1024 AS value

FROM v$pgastat

WHERE name = 'maximum PGA allocated'

Method 2: directly use a sql to find out the value of memory_target (in mb)

SELECT (sga.value + GREATEST (pga.value, max_pga.value)) / 1024 AS memory_target

FROM

(SELECT TO_NUMBER (value) AS value FROM v$parameter WHERE name = 'sga_target') sga

(SELECT TO_NUMBER (value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga

(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga

I think MEMORY_TARGET should be equal to the actual value of sga plus the maximum allocated value of PGA, which is the most real, but it is generally considered that MEMORY_TARGET=SGA_TARGET+GREATEST (PGA_AGGREGATE_TARGET, "maximum PGA allocated") is OK.

If you want to more realistically determine the size of the MEMORY_TARGET, you need to determine the actual size of the instance's sga_target, the current setting minus the remaining values. And then use the previous formula.

SQL > select (

(select sum (value) from v$sga)

(select current_size from v$sga_dynamic_free_memory)

) / 1024 sga_target 1024 Universe

From dual

two。 If memory_target or memory_target=0 is not set for the instance and SGA_TARGET is set to 0, it means that automatic shared memory (sga) allocation, a new feature of 10g, is disabled, so we need to set the value of each sga memory component separately. In this case, how can we determine the value of memory_target to enable automatic memory management of Oracle11g?

SQL > select value/1024/1024 as value from v$pgastat where name='maximum PGA allocated'

VALUE

-

45.7822266

Pga_aggregate_target=46M can be seen to determine the maximum capacity of PGA since the oracle instance is launched, which can be understood as the maximum usage.

SQL > SELECT ((SELECT SUM (value) FROM V$SGA)-

(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET"

FROM DUAL

SGA_TARGET

-

313626624

It can be seen that the size of SGA_target is 300m

1) if you modify the memory_target=352M report immediately

A hint of an illegal range of values

SQL > alter system set memory_target=352M

Alter system set memory_target=352M

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

400M

Because to satisfy memory_target > = pga_aggregate_target+sga_target.

But the value of pga_aggregate_target is 100m, while the value of SGA_target is 300m.

352M alter system set pga_aggregate_target=0

Alter system set pga_aggregate_target=0

ERROR at line 1:

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

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

If you change the value of pga_aggregate_target directly, the above error will be reported. Because we have not set the value of momory_target yet, if we directly set the size of PGA to 0m, it is tantamount to not allocating memory to PGA. So we use the above query to determine the maximum memory size of 46m allocated to PGA since the instance is started, and first set the value of PGA. Make

Memory_target > = pga_aggregate_target+sga_target. That is, 352m > = 50M+300M

SQL > alter system set pga_aggregate_target=50M

System altered.

SQL > alter system set memory_target=352M

System altered.

SQL > alter system set pga_aggregate_target=0M

System altered.

This is where we have enabled automatic memory management for oracle and set the value of the SGA_target and pga_aggregate_target parameters to 0. Oracle will not allocate the minimum memory size for SGA and PGA at this time. Oracle automatically resizes SGA and PGA based on the workload.

6: Oracle 11g set memory-related parameters principle:

1. If Oracle11g wants to enable automatic memory management, you need to set the value of MEMORY_TARGET. If the server only runs Oracle, you can set it according to the following principles:

MEMORY_TARGET= physical memory x 80%

MEMORY_MAX_SIZE= physical memory x 80%

two。 If you do not plan to turn on automatic memory management, that is, MEMORY_TARGET=0:

For OLTP systems:

SGA_TARGET= (physical memory x 80%) x 80%

SGA_MAX_SIZE= (physical memory x 80%) x 80%

PGA_AGGREGATE_TARGET= (physical memory x 80%) x 20%

For DSS systems:

SGA_TARGET= (physical memory x 80%) x 50%

SGA_MAX_SIZE= (physical memory x 80%) x 50%

PGA_AGGREGATE_TARGET= (physical memory x 80%) x 50%

This is the end of the introduction to "Overview of oracle 11g automatic memory Management". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.