Automatic Oracle SGA management feature (sga_target parameter)

Oracle SGA automatic management feature (sga_target parameter) 1. related parameter description a. Parameter SHARED_POOL_SIZE = integer [K | M | G] Default value If SGA_TARGET is set: if the parameter is not specified, then the default is 0 (internally determined by the Oracle Database ). if the parameter is specified, then the user-specified value indicates a minimum value for the memory pool. if SGA_TARGET is not set (32-bit platforms): 32 M, rou Nded up to the nearest granule size. if SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the nearest granule size. range of values Minimum: the granule size Maximum: operating system-dependent B, parameter SGA_TARGETSGA_TARGET = integer [K | M | G] Default value 0 (SGA autotuning is disabled) SGA_TARGET specifies the total size of all SGA components. if SGA_TARGET is specified, then the followin G memory pools are automatically sized: Buffer cache (DB_CACHE_SIZE) Shared pool (SHARED_POOL_SIZE) Large pool (LARGE_POOL_SIZE) Java pool (JAVA_POOL_SIZE) Streams pool (STREAMS_POOL_SIZE) if these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by AutomaticShared Memory Management. you wocould set minimum values if an application component ne Eds a minimum amount of memory to function properly. the following pools are manually sized components and are not affected by automation Shared Memory Management: Log buffer Other buffer caches, such as KEEP, RECYCLE, and other block sizes Fixed SGA and other internal allocationsThe memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Mana Gementcomputes the values of the automatically tuned memory pools. 2. When the sga_target parameter is zero [SQL] -- # edit a temporary parameter file, set sga_target = 0, and set the size of several pool pools, db_cache_size, the following robin @ SZDB:/u02/database/SYBO2SZ> grep size SYBO2SZ. ora. tmp *. db_block_size = 8192 *. db_cache_size = 285212672 *. db_recovery_file_dest_size = 1G *. java_pool_size = 4194304 *. large_pool_size = 4194304 *. shared_pool_size = 293601280 *. stream s_pool_size = 419 4304 robin @ SZDB:/u02/database/SYBO2SZ> grep target SYBO2SZ. ora. tmp *. pga_aggregate_target = 199229440 *. sga_target = 0 -- # use a temporary parameter file to start the database robin @ SZDB: /u02/database/SYBO2SZ> sqlplus/as sysdba idle> startup pfile =/u02/database/SYBO2SZ/SYBO2SZ. ora. tmp ORACLE instance started. --- we can see that sga_target is 0 idle> show parameter sga_tar name type value ------------------------------------------------------------- ---------------- Sga_target big integer 0 --> View memory allocation status idle> SELECT name, VALUE 2 FROM v $ parameter 3 WHERE name IN 4 ('shared _ pool_size ', 5 'java _ pool_size ', 6 'streams _ pool_size', 7 'Log _ buffer', 8 'db _ cache_size ', 9 'db _ 2k_cache_size ', 10 'db _ 4k_cache_size ', 11 'db _ 8k_cache_size', 12 'db _ 16k_cache_size ', 13 'db _ 32k_cache_size', 14 'db _ keep_cache_size ', 15 'db _ recycle_cache_size ', 16 'large _ poo L_size '); name value implements shared_pool_size 293601280 bytes 4194304 java_pool_size 4194304 streams_pool_size 4194304 db_cache_size 285212672 db_2k_cache_size 0 bytes 0 bytes 0 bytes 0 bytes 0 bytes 0 log_buffer 6120448 13 rows selected. -- use temporary pfile to create spfile id Le> create spfile from pfile = '/u02/database/SYBO2SZ/SYBO2SZ. ora. tmp '; File created. 3. When the sga_target parameter has a non-zero value, [SQL] -- restart db idle> startup force; ORACLE instance started. idle> select distinct isspecified from v $ spparameter; ISSPEC ------ TRUE --> If the table NAME is true, spfile is used to start the database FALSE -- The sga_max VALUE is 572 m idle> show parameter sga_max name type value ----------------------------------------------------------------- ------------ Sga_max_size big integer 572 M -- modify sga_target to a non-zero value idle> alter system set sga_target = 572 m; System altered. -- at this time, sga_target is changed to 576 m, which is caused by the allocation granularity and the sum of the currently allocated memory, automatically rounded up idle> show parameter sga_target name type value ------------- ------------------------------ sga_target big integer 576 M -- the total size allocated by the VALUE specified in the parameter file is 564 m idle> select (285212672 + 4194304) + 4194304 + 293601280 + 41 94304)/1024/1024 from dual; (285212672 + 4194304 + 4194304 + 293601280 + 4194304)/1024/1024 ----------------------------------------------------- 564 -- let's take a look at the memory allocation granularity, in this case, the parameter VALUE is 4 mb idle> @ mem_granule name value descbtion before running -------------------------- _ ksmg_granule_size 4194304 granule size in bytes _ ksmg_granule_locking_status 1 granu Tus -- the value of 572m is also an integer multiple of 4. This should be caused by some fixed or other memory consumption. Currently, 576 MB is allocated. If the total size of the parameter file is reduced, we can specify sga_target <= sga_max_size idle> select 576/4 from dual; 576/4 ---------- 144 -- generate pfile from spfile to observe the changes to the non-zero value of sga_target idle> create pfile = '/tmp. ora 'from spfile; File created. -- we can see that the entry idle> ho grep size/tmp related to sga_target memory allocation is added starting with dbname and underlined. ora SYBO2SZ. _ db_cache_size = 285212672 SYBO2SZ. _ java_pool_size = 4194304 SYBO 2SZ. _ large_pool_size = 4194304 SYBO2SZ. _ shared_pool_size = 297795584 SYBO2SZ. _ streams_pool_size = 4194304 *. db_block_size = 8192 *. db_cache_size = 285212672 *. db_recovery_file_dest_size = 1G *. java_pool_size = 4194304 *. large_pool_size = 4194304 *. shared_pool_size = 293601280 *. streams_pool_size = 4194304 --/tmp. the sga_target in the ora file is changed to 603979776 = 576 m idle> ho grep sga_target/tmp. ora *. sga_target = 603979776 -- bottom Edit/tmp. ora, remove the original memory configuration value, and use the underlined value to start the database idle> vi/tmp. ora idle> ho grep size/tmp. ora SYBO2SZ. _ db_cache_size = 285212672 SYBO2SZ. _ java_pool_size = 4194304 SYBO2SZ. _ large_pool_size = 4194304 SYBO2SZ. _ shared_pool_size = 297795584 SYBO2SZ. _ streams_pool_size = 4194304 *. db_block_size = 8192 #*. db_cache_size = 285212672 *. db_recovery_file_dest_size = 1G #*. java_pool_size = 4194304 #*. large_pool_size = 4194 304 #*. shared_pool_size = 293601280 #*. streams_pool_size = 4194304 -- first shut down the Database idle> shutdown immediate; Database closed. database dismounted. ORACLE instance shut down. -- use a temporary pfile to start idle> startup pfile =/tmp. ora ORACLE instance started. -- The following query shows that all memory allocations related to sga_target are changed to 0 idle> SELECT name, VALUE 2 FROM v $ parameter 3 WHERE name IN 4 ('shared _ pool_size ', 5 'java _ pool_size ', 6 'streams _ pool_size ', 7 'Log _ buffer', 8 'db _ cache_size ', 9 'db _ 2k_cache_size', 10 'db _ 4k_cache_size ', 11 'db _ 8k_cache_size ', 12 'db _ 16k_cache_size ', 13 'db _ 32k_cache_size', 14 'db _ keep_cache_size ', 15 'db _ recycle_cache_size', 16 'large _ pool_size '); name value ------------------------------------------------------------ shared_pool_size 0 large_pool_size 0 java_pool_size 0 streams_pool_size 0 db_cache_size 0 db2. K_cache_size 0 db_4k_cache_size 0 db_8k_cache_size 0 db_16k_cache_size 0 db_32k_cache_size 0 db_keep_cache_size 0 defaults 0 log_buffer 6120448 13 rows selected. -- in this case, sga_max_size is also changed to 576 m, because sga_target must be smaller than or equal to sga_max_size idle> show parameter sga_max name type value ------------- ------------------------------ sga_max_size big integer 576 M 4. When _ target is a non-zero value, the following parameters are controlled by sga_targe, but the total value cannot exceed sga_target. Buffer cache (DB_CACHE_SIZE) Shared pool (SHARED_POOL_SIZE) Large pool (LARGE_POOL_SIZE) Java pool (JAVA_POOL_SIZE) Streams pool (STREAMS_POOL_SIZE) B, non-zero value sga_target, the size of several parameters can be dynamically allocated, and the size of each part can be dynamically adjusted as needed. C. If DB_CACHE_SIZE and other parameters are set when sga_target is a non-zero value, the sga_targe allocation is not less than the set value as the initial value. D. When sga_target is set to zero, the memory allocation of SGA-related parameters is controlled by the parameters and cannot be dynamically adjusted according to the system conditions. F. We recommend that you set the SGA memory management to sga_target for dynamic management after 10 Gb of Oracle.