11 GB Automatic Memory settings

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. A simple way to manage memory is to allow oracle to automatically manage and optimize the memory for you! To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET ). on most platforms, you only need to set The initialization parameter MEMORY_TARGET, and then set the initialization parameter MEMORY_MAX_TARGET The total memory that the instance uses remains relatively constant at Will (equal to MEMORY_TARGET, based on the value of MEMORY_TARGET, and the instance automatica Lly distributes memory between the system global area (SGA) and the instance program global area (instance PGA ). as memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA. the total memory of the instance is subject to the value set by MEMORY_TARGET (oracle allocates memory to the instance based on the value of MEMORY_TARGET during startup! If the business needs to allocate more memory, oracle will automatically expand, but cannot exceed the value of MEMORY_MAX_TARGET), the instance will automatically allocate memory to SGA and PGA When automatic memory management is not enabled as needed, you must size both the SGA and instance PGA manually. when automatic memory management is not enabled, you must specify the memory size of SGA and PGA Because the MEMORY_TARGET initialization parameter is dynamic, you can change MEMORY_TARGET at any time without restarting the database. MEMORY_TARGET is a dynamic parameter and can be adjusted as needed without restarting the instance! MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally set MEMORY_TARGET too high, and so that enough memory is set aside for the database instance in case you do want to increase total instance memory in the future. because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting MEMO RY_TARGET too low. MEMORY_MAX_TARGET is a static parameter. As an upper limit (the MEMORY_TARGET parameter is restricted), you cannot set MEMORY_TARGET too high and reserve enough memory to meet the instance expansion requirements in the future. However, you cannot set the MEMORY_TARGET parameter too low for the instance because the size of the SGA component is limited by the minimum value, and it is difficult to narrow down some components! If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. if you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management. if you use DBCA to create a database and select the Basic installation option, the automatic memory is enabled by default! For advanced installation, you can also enable automatic memory management! Step 1: Start SQL * Plus and connect to the database as SYSDBA. log On as A sys user 2: Calculate the minimum value for MEMORY_TARGET as follows: Calculate the minimum value of MEMORY_TARGET! A: Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET by entering the following SQL * Plus command: Determine the size of the SGA_TARGET and PGA_AGGREGATE_TARGET parameters: show parameter target name type value ------------------------------ ----------- ---------------- archive_lag_target integer 1_integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target Big integer 0memory_target big integer 0pga_aggregate_target big integer 90Msga_target big integer 272 Mb: Run the following query to determine the maximum instance PGA allocated since the database was started: run the following query to determine the maximum instance PGA allocated since the database was started: determines the maximum value allocated to PGAde by the instance since the database is started: select value/1024/1024 | 'M' from v $ pgastat where name = 'maximu M PGA allocated '; c: Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. add SGA_TARGET to this value. calculate the maximum memory_target value memory_target = sga_target + max (pga_aggregate_target, maximum PGA allocated) For example. if SGA_TARGET is 272 M and PGA_AGGREGATE_TARGET is 90 M as shown above, and if the maximum PGA allocated is determined to be 120 M, then MEMORY_TARGET sh Ocould be at least 392 M (272 M + 120 M ). for example, if SGA_TARGET is 272 M, PGA_AGGREGATE_TARGET is 90 M, and maximum PGA allocated is 120 M, the minimum value of MEMORY_TARGET is 392 M (272 M + 120 M ). This is only his minimum value! You can also set a larger value on the premise that there is enough physical memory! 3: For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you wowould want to allocate to the database for the foreseeable future. that is, determine the maximum value for the sum of the SGA and instance PGA sizes. this number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step. set MEMORY_MAX_TARGET to be greater than or equal to MEMORY_TAR. The value of the GET parameter. According to some experiences on the Internet, there is no fixed formula for the value of MEMORY_MAX_TARGET = OS _MEMORY * 80%! Determine based on the actual situation !! Script for modifying parameters: alter system set MEMORY_MAX_TARGET = nM SCOPE = SPFILE; if you modify this parameter MEMORY_MAX_TARGET, restart the instance! The dynamic performance view V $ MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA. the dynamic view V $ MEMORY_DYNAMIC_COMPONENTS shows the size of the current dynamic memory component. Including the total size of SGA and PGA! The view V $ MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter. SQL> select * from v $ memory_target_advice order by memory_size; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ---------------- -------------- 180. 5 458 1.344 0 270. 75 367 1.0761 0 360 1 341 1 0 450 1.25 335. 9817 0 540 1.5 335. 9817 0 630 1.75 335. 9817 0 720 2 335. 9817 0The row with the MEMORY_SIZE_FACTOR of 1 shows the current size of memory, as set by the MEMORY_TARGET initialization parameter, and the amount of DB time required to complete the current workload. in previous and subsequent rows, the results show several alternative MEMORY_TARGET sizes. for each alternative size, the database shows the size factor (the multiple of the current size ), and the estimated DB time to complete the current workload if the MEMORY_TARGET parameter were changed to the alternative size. notice that for a total memory size smaller than the current MEMORY_TARGET size, estimated DB time increases. notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450 MB. however, this situation might change if a complete workload has not yet been run.