Oracle initialization parameters & Performance View

Oracle initialization parameter performance view 1. database version LEO1 @ LEO1select * fromv $ version; BANNER --------------------------------------------------------------------------

Oracle initialization parameter performance view 1. database version LEO1 @ LEO1select * fromv $ version; BANNER --------------------------------------------------------------------------

Oracle initialization parameters & 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 use v $ memory_target_advice to analyze the optimal memory size of the database.

Memory_target: 1. it is a memory adjustment parameter in oracle11g, and 11G continues to strengthen the automatic management of memory. In the past 10g, SGA can be automatically managed and allocated, and 11g can automatically manage SGA, you can also automatically manage PGA and manage these two parts comprehensively to automatically adjust the size of all memory areas. The default value is 0 in 11g.

Now let's list the syntax of these parameters. This is a static parameter that needs to be restarted to take effect.

Alter systemset memory_max_target = 1000 m scope = spfile;
Alter system set memory_target = 1000 m scope = spfile;
Alter system set sga_max_size = 600 m scope = spfile;
Alter system set pga_aggregate_target = 400 m scope = spfile;

2. memory_max_target is used to set the maximum space occupied by Oracle to physical memory. One is the maximum space occupied by Oracle SGA + the maximum space occupied by PGA, and memory_max_target is the upper limit of memory_target. If only memory_max_target is set, oracle considers that memory_target = 0 does not use memory for automatic management.

3. If only memory_target is set and memory_max_target is not set, Oracle automatically sets memory_max_target to memory_target.

4. If both values are set, the upper limit of memory_target is memory_max_target.

This is the parameter value in my database.

LEO1 @ LEO1> showparameter memory_max_target

NAME TYPE VALUE

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

Memory_max_target big integer 652 M

LEO1 @ LEO1> showparameter memory_target

NAME TYPE VALUE

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

Memory_target big integer 652 M

5. the sga_max_size of 10 Gb is dynamically allocated to the Shared Pool Size, database buffer cache, largepool, java pool, and redo log buffer. The Size of each SGA memory zone is re-allocated based on the Oracle running status. PGA needs to be set separately in 10 GB (manual management ).

Lab

The following commands help you understand the relationship between the settings of memory_target and PGA and SGA.

(1) set memory_target to a non-0 value.

Memory_Target = SGA_TARGET + PGA_AGGREGATE_TARGET, which is the same as memory_max_size.

The sga_target and pga_aggregate_target parameters are set to the minimum start value.

The size of sga_target is set. pga_aggregate_target is not set.

Then pga_aggregate_target initialization value = memory_target-sga_target

No size is set for sga_target and pga_aggregate_target.

So the sga_target initialization value = memory_target-pga_aggregate_target

The size of sga_target and pga_aggregate_target is not set. Oracle 11g will automatically allocate the size based on the database running status. However, when the database is started, a fixed proportion is allocated:

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

(2) memory_target is not set or equal to 0 (0 by default in 11g)

If the default value is 0 in 11g, The memory_target function is canceled in the initial state. It is completely consistent with 10g in memory management and completely backward compatible.

(SGA and PGA are allocated in three cases)

The SGA_TARGET value automatically adjusts the shared pool, buffer cache, redo logbuffer, java pool, and larger pool memory areas in SGA. the PGA depends on the pga_aggregate_target size. Sga and pga cannot automatically increase or decrease.

Neither SGA_target nor PGA_AGGREGATE_TARGET is set. The size of each memory area in the SGA must be specified and cannot be adjusted automatically. PGA cannot automatically increase or contract.

Memory_max_target is set while memory_target is set to 0. In this case, the memory is not used for automatic management like 10 Gb.

LEO1 @ LEO1> showparameter 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 652 M

Memory_target big integer 652 M

Parallel_servers_target integer 8

Pga_aggregate_target big integer 0

Sga_target big integer 0

Now we can see that the values of sga_target and pga_aggregate_target are both 0, and oracle automatically adjusts the size. The sizes of memory_target and memory_max_target are 652 M.

LEO1 @ LEO1> select * from v $ memory_target_advice; analyze the optimal memory size of the database

MEMORY_SIZE MEMORY_SIZE_FACTORESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

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