Oracle Database Memory Management Guide

1. Database Administration Managing Memory

2. Memory Management - Memory management involves maintaining optimal sizes for the Oracle Database instance memory structures as demands on the database change. The memory structures that must be managed are the system global area (SGA) and the instance program global area (instance PGA). أ.ندى الغامدي , أ.ندى الطوالة

3. Memory Management • -Oracle Database supports various memory management methods, which are chosen by initialization parameter settings: • 1-Automatic Memory Management • Oracle Database can manage the SGA memory and • instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. • 2- Manual Memory Management • If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. أ.ندى الغامدي , أ.ندى الطوالة

4. Memory Management • 2- Manual Memory Management: • The methods therefore vary in the amount of effort and knowledge required by the DBA. These methods are: • ■ Automatic shared memory management - for the SGA • ■ Manual shared memory management - for the SGA • ■ Automatic PGA memory management - for the instance PGA • ■ Manual PGA memory management - for the instance PGA أ.ندى الغامدي , أ.ندى الطوالة

5. 1-Automatic Memory Management -The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. -you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). -The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). -the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. أ.ندى الغامدي , أ.ندى الطوالة

6. Enabling Automatic Memory Management • 1-Calculate the minimum value for MEMORY_TARGET as follows: • A- Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET by entering the following SQL command: • SHOW PARAMETER TARGET • SQL*Plus displays the values of all initialization parameters with the string • TARGET in the parameter name. • 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 0 • memory_target big integer 0 • pga_aggregate_target big integer 90M • sga_target big integer 272M أ.ندى الغامدي , أ.ندى الطوالة

7. Enabling Automatic Memory Management B-Run the following query to determine the maximum instance PGA allocated since the database was started: select value from v$pgastat where name='maximum PGA allocated'; C-Compute the maximum value between the query result from step 1b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value. memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated) 2- Choose the value for MEMORY_TARGET that you want to use. “This can be the minimum value that you computed in step 1, or you can choose to use a larger value if you have enough physical memory available.” أ.ندى الغامدي , أ.ندى الطوالة

8. Enabling Automatic Memory Management 3-For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would 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. أ.ندى الغامدي , أ.ندى الطوالة

9. Enabling Automatic Memory Management • 4-Do one of the following: • -with spfile, enter the following command: • ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE; • “Where n is the value that you computed in step 3.” • -with text initialization parameter file: • memory_max_target = nM • memory_target = mM • “where n is the value that you determined in step3, and m is the value that you determined in step 2.” • 5-shut down and restart the database. أ.ندى الغامدي , أ.ندى الطوالة

10. Enabling Automatic Memory Management 6-Enter the following commands: ALTER SYSTEM SET MEMORY_TARGET = nM; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0; Note: set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA. أ.ندى الغامدي , أ.ندى الطوالة

11. Automatic Memory Management through the Enterprise Manager Oracle 11g

12. أ.ندى الغامدي , أ.ندى الطوالة

13. أ.ندى الغامدي , أ.ندى الطوالة

14. Enabling Automatic Memory Management 1- to modify total memory size (new value does not exceed maximum memory size) 2- to modify total memory size (new value exceed maximum memory size) أ.ندى الغامدي , أ.ندى الطوالة