Oracle initialization parameters and performance view

1. Database version

Leo1@leo1>select * from V$version;

BANNER

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

Oracle database11g Enterprise Edition release 11.2.0.1.0-64bit Production

Pl/sql release11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl version11.2.0.1.0-production

2. Set the Memory_target parameter and analyze the optimal memory size of the database by V$memory_target_advice

Memory_ Target:1. is a memory tuning parameter in the oracle11g, 11g to automate the management of memory and continue to strengthen, the original 10g in the SGA can be automatically managed and distributed, 11g that can automatically manage the SGA, but also can automatically manage the PGA, the two parts of the integrated management, automatic adjustment of all the size of the memory area 。 The default of 11g is 0

Now, let's enumerate the arguments, which are static parameters that need to restart the database to take effect.

Alter Systemset memory_max_target= 1000m Scope=spfile;

Alter system set memory_target= 1000m Scope=spfile;

Alter system set sga_max_size=600m Scope=spfile;

Alter system set pga_aggregate_target=400m Scope=spfile;

2.memory_max_target is to set the amount of space that Oracle can take up physical memory, and one is how much space in the Oracle SGA area can occupy the +PGA area of the largest memory space, Memory_max_target is the memory_target upper limit value, If the memory_max_target is set only and Memory_target is not set, Oracle believes that memory_target=0 does not use automatic memory management.

3. If you set Memory_target only, and Memory_max_target is not set, Oracle automatically sets Memory_max_target to Memory_target.

4. If both values are set, the upper bound value of the Memory_target is memory_max_target.

This is the value of the parameter on my database

Leo1@leo1> Showparameter Memory_max_target

NAME TYPE VALUE

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

Memory_max_target Big Integer 652M

Leo1@leo1> Showparameter Memory_target

NAME TYPE VALUE

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

Memory_target Big Integer 652M

5 10g Sga_max_size is dynamically allocated shared Pool Size,database buffer Cache,largepool,java pool,redo log buffer size, Reassign the SGA memory area size according to the Oracle run status. PGA needs to be set separately in 10g (i.e. manual management).

Experiment

Here are some of the following commands to let you know the relationship between Memory_target settings and the PGA and SGA

(1) Memory_target set to a value other than 0

Memory_target=sga_target+pga_aggregate_target, the size equals memory_max_size consistent.

Sga_target and Pga_aggregate_target are set to size, the two parameters will be the minimum starting values

Sga_target set size, pga_aggregate_target not set size

So Pga_aggregate_target initialization value =memory_target-sga_target

Sga_target not set size, Pga_aggregate_target set size

So Sga_target initialization value =memory_target-pga_aggregate_target

Sga_target and Pga_aggregate_target are not set size Oracle 11g will automatically allocate size based on database run state. However, when the database is started, there is a fixed percentage to allocate:

Sga_target =memory_target *60% pga_aggregate_target=memory_target *40%

(2) Memory_target is not set or equal to 0 (11g defaults to 0)

11g defaults to 0 in the initial state of the cancellation of the role of Memory_target, completely and 10g in memory management consistent, completely backward-compatible.

(There are three different scenarios for allocating the SGA and PGA sizes)

Sga_target set values, the shared pool,buffer Cache,redo Logbuffer,java Pool,larger pool memory area in the SGA is automatically tuned, and the PGA relies on Pga_aggregate_ Target size is set individually. SGA and PGA cannot automatically grow and shrink automatically.

Sga_target and Pga_aggregate_target are not set, the size of each memory area in the SGA must be set explicitly, not automatically adjust the size of each memory area. PGA cannot grow and shrink automatically.

Memory_max_target settings and Memory_target =0 This situation does not use memory automatic management like 10g

Leo1@leo1> Showparameter Target

NAME TYPE VALUE