Managing Memory in Administrator & #39; s Guide, managingdire

Managing Memory in Administrator's Guide, managingdire

Automatic Memory Management

1. If you want to enable Automatic Memory Management, how can you determine the value of MEMORY_TARGET?

1> show parameter target under SQL * Plus. The result is as follows:

SQL> show parameter targetNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------archive_lag_target                   integer     0db_flashback_retention_target        integer     1440fast_start_io_target                 integer     0fast_start_mttr_target               integer     0memory_max_target                    big integer 0memory_target                        big integer 0parallel_servers_target              integer     256pga_aggregate_target                 big integer 4779Msga_target                           big integer 14368M

2> determine the value of maximum PGA allocated

SQL> select value/1024/1024 from v$pgastat where name='maximum PGA allocated';VALUE/1024/1024---------------     4331.59961

3> memory_target = sga_target + max (pga_aggregate_target, maximum PGA allocated)

In this example, memory_target must be at least 14368 + 4779 = 19147 M

2. How to optimize Memory Target Size, SGA Target Size, and PGA Target Size.

You can query the V $ MEMORY_TARGET_ADVICE, V $ SGA_TARGET_ADVICE, and V $ PGA_TARGET_ADVICE tables respectively. The following uses V $ SGA_TARGET_ADVICE as an example.

SQL> select * from v$sga_target_advice order by sga_size;   SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS---------- --------------- ------------ ------------------- -------------------       290              .5       448176              1.6578             1636103       435             .75       339336              1.2552             1636103       580               1       270344                   1             1201780       725            1.25       239038               .8842              907584       870             1.5       211517               .7824              513881      1015            1.75       201866               .7467              513881      1160               2       200703               .7424              513881

The row where SGA_SIZE_FACTOR and ESTD_DB_TIME_FACTOR are 1 corresponds to the current SGA value. In this example, the SIZE of the current SGA is 580 M. You can adjust the SGA value based on the expected values of ESTD_DB_TIME and ESTD_PHYSICAL_READS.

3. Relationship between MEMORY_TARGET and MEMORY_MAX_TARGET

MEMORY_TARGET is a dynamic parameter that can be dynamically modified during database operation. MEMORY_MAX_TARGET is a static parameter and takes effect only when the database is restarted. The value of MEMORY_TARGET cannot exceed MEMORY_MAX_TARGET. Setting MEMORY_MAX_TARGET has two advantages: 1. prevent accidental setting of MEMORY_TARGET too high. 2. reserve space for increasing the value of MEMORY_TARGET in the future.

In pfile, if you ignore the value of MEMORY_MAX_TARGET and only set the value of MEMORY_TARGET, MEMORY_MAX_TARGET is set to MEMORY_TARGET by default. If only the value of MEMORY_MAX_TARGET is set, but the value of MEMORY_TARGET is not set, the default value of MEMORY_TARGET is 0.

4. You can use v $ memory_dynamic_components to view the information of each memory component.

SQL> select component,current_size,min_size,max_size,user_specified_size from v$memory_dynamic_components;COMPONENT                      CURRENT_SIZE   MIN_SIZE     MAX_SIZE USER_SPECIFIED_SIZE------------------------------ ------------ ----------   ---------- -------------------shared pool                        92274688   41943040     92274688                   0large pool                          8388608    8388608     71303168                   0java pool                           4194304    4194304      4194304                   0streams pool                              0          0            0                   0SGA Target                        205520896  205520896    205520896                   0DEFAULT buffer cache               92274688   37748736    100663296                   0KEEP buffer cache                         0         0             0                   0RECYCLE buffer cache                      0         0             0                   0DEFAULT 2K buffer cache                   0         0             0                   0DEFAULT 4K buffer cache                   0         0             0                   0DEFAULT 8K buffer cache                   0         0             0                   0COMPONENT                      CURRENT_SIZE   MIN_SIZE     MAX_SIZE  USER_SPECIFIED_SIZE------------------------------ ------------ ----------   ----------  -------------------DEFAULT 16K buffer cache                  0          0            0                   0DEFAULT 32K buffer cache                  0          0            0                   0Shared IO Pool                            0          0            0                   0PGA Target                        109051904  109051904    109051904                   0ASM Buffer Cache                          0          0            0                   016 rows selected.

5. How to enable Automatic Memory Management? The settings are as follows:

ALTER SYSTEM SET MEMORY_TARGET = nM;ALTER SYSTEM SET SGA_TARGET = 0;ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

NOTE: If SGA_TARGET and PGA_AGGREGATE_TARGET are not set to 0, the value is the minimum value.

SGA

6. Each component of SGA is allocated in granule units, and the size of granule is measured by SGA_MAX_SIZE, as follows:

You can use the following statement to query the size of granule:

SQL> select * from v$sgainfo where name='Granule Size';NAME                                  BYTES RES-------------------------------- ---------- ---Granule Size                        4194304 No

Note: The space allocated by SGA to each component is an integer multiple of granule. For example, if your granule size is 4 MB, you can set the value of DB_CACHE_SIZE to 10 MB, the memory size allocated by SGA for DB_CACHE_SIZE is 12 Mb.

7. About SGA_MAX_SIZE

SGA_MAX_SIZE is a static parameter. The specific description and default value are as follows:

8. ASMM

Automatic shared memory management is enabled by setting the SGA_TARGET value. In SGA components, some components can automatically allocate memory through ASMM, and some components must explicitly specify the value. The details are as follows:

Automatically Sized SGA Components and Corresponding Parameters

The following parameter SGA can be automatically adjusted. If ASMM is enabled, the following value must be set to 0. If it is not 0, it indicates its minimum value SELECT (select sum (value) from v $ SGA) -(SELECT CURRENT_SIZE from v $ SGA_DYNAMIC_FREE_MEMORY) "SGA_TARGET" from dual;

11. Buffer Cache

The size of the Buffer cache affects the database performance. Although the large buffer cache can reduce the number of physical reads and writes, because it occupies a large amount of memory, it may lead to paging and switching.

Oracle supports block size of different sizes. When creating a tablespace, if a non-standard block size is specified, the corresponding DB_nK_CACHE_SIZE value must be set. Note that the system tablespace uses standard blocks. You can view the size of standard blocks using the following statement:

SQL> show parameter db_block_sizeNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_size                        integer     8192

Oracle supports five block sizes and corresponding cache values. You can set these values using the following parameters:

DB_2K_CACHE_SIZEDB_4K_CACHE_SIZEDB_8K_CACHE_SIZEDB_16K_CACHE_SIZEDB_32K_CACHE_SIZE

Note: DB_nK_CACHE_SIZE cannot be used to set the cache value of the standard block. The cache size of the standard block is determined by db_cache_size. In this example, the size of the standard block is 8 k, so the value of DB_8K_CACHE_SIZE will report the following error:

SQL> alter system set db_8k_cache_size=10m;alter system set db_8k_cache_size=10m*ERROR at line 1:ORA-32017: failure in updating SPFILEORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size

The block size of 32 KB is only valid in 64-bit operating systems.

12. Buffer Pools

Buffer cache contains three pools: KEEP pool, RECYCLE pool, and DEFAULT pool. The KEEP pool permanently stores the object data in the memory, and the RECYCLE pool removes the data from the memory once the data is not needed. The DEFAULT pool is the DEFAULT one. The size of the KEEP and RECYCLE pools is set based on DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. The three pools only support standard blocks. For non-standard blocks, only the DEFAULT pool is supported.

13. To manually manage PGA, set the following values explicitly.

SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE.