Memory Management of Oracle Database

Large page Memory NotesNote 1:cannot use Oracle Automatic memory management; NOTE 2:The goal is to reduce the use of swap; Note 3:recommended SGA greater than 8G; Note 4:limited to Linux systems only; Note 5:do not use/DEV/SHM space; Note 6:need to set memory lock；if large page memory is configured but not used by Oracle1.sga_max_size over large pages of memory; 2.No memory lock set; 3.Automatic memory management is not canceled；View the usage of the system's large page memoryCat/proc/meminfo | Grep-i hugepages Note 1: The default size of general large page memory is 2M; NOTE 2: The size of a single memory page is determined by the OS kernel and cannot be modified;set the number of large pages of memoryView Sga_max_sizeShow Parameter Sga_max_sizecalculates the total memory size (in units of M) for large pages of memorySga_max_size (MB)/size of each large page of memory (default 2M) +200 (reserved)Configuring large page memory ParametersVi/etc/sysctl.conf added or modified to the following:vm.nr_hugepages =make configuration effectiveSysctl-pView the usage of large pages of memoryCat/proc/meminfo | Grep-i hugepages Note 1: the constant "sysctl-p"Until the setpoint is reached, or the system is restarted;Set Memory LockConfiguring the Limits.conf fileVi/etc/security/limits.conf added or modified to the following:Oracle Soft Memlock-1Oracle Hard Memlock-1Note:-1 is not limited, if set, the Memlock data is larger than the number of large pages of memoryView Changes(Oracle User)Ulimit-lConfigure swap SpaceEmpty swap spaceSwapoff-a && swapon-aModify the sysctl.conf fileVi/etc/sysctl.conf added or modified to the following:vm.swappiness=0Make it effectiveSysctl-pLock_sga and Pre_page_sga NotesNote 1:cannot use Oracle Automatic memory management; NOTE 2:The goal is to reduce the use of swap; Note 3:recommended SGA less than 8G; Note 4:do not use/DEV/SHM space; Note 5:with large page memory two select one; Note 6:need to set memory lock； DescriptionLOCK_SGA: The SGA is locked in physical memory, which prevents the SGA from being swapped out into swap PRE_PAGE_SGA: The entire SGA is read to physical memory when the database is started, but not the swap space, effectively improving the database efficiency But it will increase the startup time of the database.Set Memory LockConfiguring the Limits.conf fileVi/etc/security/limits.conf added or modified to the following:Oracle Soft Memlock-1Oracle Hard Memlock-1Note:-1 is not limited, if set, the Memlock data is larger than the number of large pages of memoryView Changes(Oracle User)Ulimit-lModifying ParametersSqlplus/as sysdbasql>alter System Set LOCK_SGA = True Scope=spfile; Sql>alter system Set Pre_page_sga = True Scope=spfile; Sql>shutdown immediate; Sql>startup Open;ErrorError 1sql>startup;Ora-27102:out of Memorylinux-x86_64 error:12:cannot Allocate memoryWorkaround:Set Memory Lock；Automatic memory management (best database memory configuration for system-on-line) Memory Setup RecommendationsYou can set the server80% of total physical memoryassigning to a database (SGA+PGA)PGA Memory management parametersAutomatic PGA Memory management via two parameters (V$parameter) workarea_size_policy--The default is auto, which is automatically allocating memory, PGA_AGGREGATE_TARGET--PGA manually allocating memory size, Generally set to 30% of the size of the SGA;View PGA Actual sizeSet pagesize 100set linesize 100col name for A22col used_size for A9select name, round (value/1024/1024/1024, 2) | | GB ' as used_size from v$pgastatwhere name like '%PGA allocated ';SGA Memory Management parametersview the actual size of the SGASet pagesize 100set linesize 100col pool_name for A15col used_size for A9select NVL (Pool, Name) as Pool_name, round ( Sum (bytes)/1024/1024/1024, 2) | | ' GB ' as used_size from V$sgastat Group BY NVL (pool, name) Order BY NVL (pool, name);Db_keep_cache_size and Db_recycle_cache_size1. Set the keep|recycle pool size 2. Use keep|recycle pool and do not use alter table|index .... Storage (Buffer_pool Keep|recycle|default); remark: Db_ keep_cache_size--can make the table reside in this region for a long time 3. Cache | Do not cache keep in the pool ALTER TABLE table_name Cache|nocache; 4. Flush cache alter SYSTEM flush Shared_pool 5. See if the table has been cached in the Keep pool select Table_name,cache,buffer_pool from User_tables;Automatic memory Managementsetting the PGA parameteralter system set Workarea_size_policy=auto;Setting the PGA sizealter system set pga_aggregate_target=0;set the SGA sizealter system set sga_target=0;set the shared pool sizealter system set shared_pool_size=0;Setting the database buffer sizealter system set db_cache_size=0;set Large pool sizealter system set large_pool_size=0;set the flow pool sizealter system set streams_pool_size=0;Setting the Java pool sizealter system set java_pool_size=0;set the maximum total memory value for a databaseAlter system set MEMORY_MAX_TARGET=20G scope = SPFile;set total database memory parametersalter system set memory_target=20g; remarks 1:memory_target=sga_target+pga_aggregate_target notes 2:memory_target<=memory_ Max_targetcanceling automatic memory managementalter system reset Memory_target scope=spfile;alter system reset Memory_max_target scope=spfile;OLTP Systemsalter system Set Sga_target =(Total physical Memory * 0.8) * 0.7Scope=spfile;alter system Set Sga_max_size =(Total physical Memory * 0.8) * 0.7Scope=spfile;alter System Set pga_aggregate_target=(Total physical Memory * 0.8) * 0.3Scope=spfile;OLAP Systemsalter system Set Sga_target =(Total physical Memory * 0.8) * 0.5Scope=spfile;alter system Set Sga_max_size =(Total physical Memory * 0.8) * 0.5Scope=spfile;alter system pga_aggregate_target=(Total physical Memory * 0.8) * 0.5Scope=spfile;Memory Advisor (memory configuration reference after the system has been running for a period of time) Set Memory Advisor parametersEnable Memory Advisor to set the parameter statistics_level to all or typicalPGA Memory AdvisorSet pagesize 100set linesize SELECT round (pga_target_for_estimate/1024/1024, 0) as ESTIMATE_PGA_MB,--predicted PGA memory value Pga_target_factor as Pga_rate,--if 1, pga_target_for_estimate indicates the current PGA memory value Estd_pga_cache_hit_percentage, the percentage of cache hit rates obtained by--PGA estimates Estd_overalloc_count--If 0 means that the PGA's overload can be eliminated, that is, the PGA memory value can be set to Pga_target_for_estimate From V$pga_target_advice; Note 1:pga_target_for_estimate is the predicted PGA memory value note 2:pga_target_factor to predict the ratio of the PGA value to the current actual PGA value, which, if 1, represents pga_target_for_ Estimate is the current actual PGA value Note 3: If Estd_overalloc_count is 0 or estd_pga_cache_hit_percentage is 100, then the PGA Memory value (pga_aggregate _target) set to the corresponding Pga_target_for_estimate valueSGA Memory AdvisorSet pagesize 100set linesize sga_size as ESTIMATE_SGA_MB,--predicted SGA memory value Sga_size_factor as sga_rate,--if 1 , the sga_size represents the current SGA memory value Estd_physical_reads--Percentage of physical reads From V$sga_target_advice; Note 1:sga_size is the predicted SGA memory value Note 2:sga_size_factor is the ratio of the predicted SGA value to the current actual SGA value, or 1, indicating that Sga_size is the current actual SGA value Note 3: When Estd_physical_ When the reads is not reduced, the SGA memory value (sga_target) can be set to the corresponding sga_sizeTotal Memory AdvisorSet pagesize 100set linesize memory_size as ESTIMATE_MEMORY_MB,--predicted total memory value memory_size_factor,--if 1, mem Ory_size indicates the current total memory value Estd_db_time, Estd_db_time_factor--the scale factor from V$memory_target_advice that consumes database time; Note 1:memory_size is the predicted total memory value Note 2:memory_size_factor is the ratio of the total memory value to the current actual total memory value, or 1, indicating that Memory_size is the current actual total memory Value Note 3: When estd_db_ When the time_factor is not reduced, the total memory value (Memory_target) can be set to the corresponding memory_sizeto view the size of each memory componentV$memory_dynamic_components

Memory Management of Oracle Database