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

This article mainly introduces "what are the memory management methods of Oracle". In the daily operation, I believe that many people have doubts about the memory management methods of Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what are the memory management methods of Oracle?" Next, please follow the editor to study!

Memory management has been greatly simplified in Oracle's 9i/10g, and 11g has taken it a step further by introducing a new concept, Automated memory Management (Automatic Memory Management,AMM). If DBA really wants to be lazy, you only need to set two parameters to leave all the troubles to Oracle (as long as DBA is wide enough). PGA took care of it with SGA. These two parameters are:

The maximum memory value that Oracle can use from the MEMORY_TARGET-- operating system's point of view. The maximum value that the dynamic parameter MEMORY_MAX_TARGET--MEMORY_TARGET can set. Non-dynamically adjustable.

Tip: if you use pfile and set MEMORY_TARGET without specifying the value of MEMORY_MAX_TARGET, the value of MEMORY_MAX_TARGET is equal to that of MEMORY_TARGET after the instance is launched. If MEMORY_MAX_TARGET is specified in the pfile but MEMORY_TARGET is not specified, the MEMORY_TARGET is 0 after the instance is started.

AMM starts a memory management (Memory Manager, mman) process in the background.

Because of the introduction of AMM, Oracle memory management is more flexible and diverse. There are 5 forms of memory management combined.

Automatic memory management

Automatic shared memory management

Manual shared memory management

Automatic PGA management

Manual PGA management

1) automatic memory management

The default installation of the instance is AMM. As follows

SQL > show parameters 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 1216M

Memory_target big integer 1216M pga_aggregate_target big integer 0

Sga_target big integer 0

Notice that the SGA_TARGET and sum are both 0.

two。 Automatic shared memory management (Automatic Shared Memory Management, ASMM)

This is the management method introduced by 10g. To use this method, you need to set the initialization parameter MEMORY_TARGET=0, and then explicitly specify the value of SGA_TARGET.

SQL > alter system set sga_target=1024m scope=both

Alter system set sga_target=1024m scope=both

*

ERROR at line 1:

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

ORA-00839: SGA_TARGET cannot be modified to the specified value

SQL > alter system set memory_target=0 scope=both

System altered.

SQL > alter system set sga_target=1024m scope=both

System altered.

SQL >

The changes to these two parameters are in strict order, and it's okay if you don't follow them-- Oracle reports errors.

3. Manual shared memory management

This is a little more primitive. Because of the original, the new initialization parameters SGA_TARGET and MEMORY_TARGET should be set to 0. 0. Then set the sga parameters such as share_pool_size and db_cache_size manually. Note that the RESULT_CACHE_SIZE parameter is new to 11g and is used to cache SQL results.

4. Automatic PGA memory management

If you use AMM, don't worry about PGA. If you want to switch to automatic PGA memory management mode for fine control, you need to set WORKAREA_SIZE_POLICY = AUTO (the default is AUTO), and then you need to specify the value of PGA_AGGREGATE_TARGET. If precise control of PGA is required, then WORKAREA_SIZE_POLICY = MANUAL. (Thanks vongates)

5. Manual PGA management

The premise is that WORKAREA_SIZE_POLICY = manual, and then specify PGA-related parameters such as SORT_AREA_SIZE. It is estimated that no one does such a thankless thing now. This model can be ignored.

Limitations of AMM

If the initialization parameter LOCK_SGA = true, AMM is not available.

Related dynamic view

V$MEMORY_DYNAMIC_COMPONENTS

V$MEMORY_RESIZE_OPS

11g has done a lot to simplify the basic work of DBA. But this also masks some technical details. Oracle is gradually turning memory management into a black box, which is also based on a more stable algorithm. Generally speaking, the advantages outweigh the disadvantages. 11g DBA, are you ready?

At this point, the study of "what are the memory management methods of Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.