Monitoring and Managing Memory

1. Monitoring and Managing Memory

2. Objectives After completing this lesson, you should be able to: • Describe the memory components in the SGA • Implement Automatic Shared Memory Management • Manually configure SGA parameters • Configure automatic PGA memory management

3. Memory Management: Overview DBAs must consider memory management to be a crucial part of their job because: • There is a finite amount of memory available • Allocating more memory to serve certain types of functions can improve overall performance • Automatically tuned memory allocation is often the appropriate configuration, but specific environments or even short-term conditions may require further attention

4. Keep buffer cache Large pool Streams pool Shared pool Recycle buffer cache Redo log buffer Database buffer cache Java pool nK block size buffer caches Oracle Memory Structures Server process 1 Server process 2 Back- ground process PGA PGA PGA SGA

5. LRU lists . . . . . . . . Buffer Cache SGA Server Checkpoint queue DB buffer cache DB_BLOCK_SIZE DB_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_KEEP_CACHE_SIZE DBWn Data files

6. Using Multiple Buffer Pools SGA DB buffer caches Recycle pool Keep pool Default pool

7. Using Multiple Buffer Pools CREATE INDEX cust_idx … STORAGE (BUFFER_POOL KEEP …); ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);

8. Shared Pool • Size is specified by using SHARED_POOL_SIZE. • Library cache contains statement text, parsed code, and execution plan. • Data dictionary cache contains definitions for tables, columns, and privileges from the data dictionary tables. • The User Global Area (UGA) contains session information if using Oracle shared server. Shared pool Shared pool Shared pool Librarycache Datadictionarycache UGA

9. Large Pool • Can be configured as a separate memory area in the SGA • Is sized by the LARGE_POOL_SIZE parameter • Is used to store data in memory for: • UGA • Backup and restore operations • Session data for the shared servers • Parallel query messaging Shared pool Large pool Redo log buffer Database buffer cache Library cache Dictionary cache UGA Backup Session Parallel

10. Java Pool • Can be configured as a separate memory area in the SGA • Is sized by the JAVA_POOL_SIZE parameter • Is used to store data in memory for all session-specific Java code and data within the JVM Shared pool Redo log buffer Database buffer cache Large pool Library cache Dictionary cache Java pool

11. Redo Log Buffer Shared pool Redo log buffer Database buffer cache Library cache Dictionary cache Serverprocess LGWR ARCn Control files SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736; Archivedlog files Data files Redo log files

12. Automatic Shared Memory Management: Overview • Automatically adapts to workload changes • Maximizes memory utilization • Helps eliminate out-of-memory errors Buffer cache Buffer cache Example: Large pool Large pool Shared pool Shared pool Java pool Java pool Streams pool Streams pool Online users Batch jobs

13. Benefits of Automatic Shared Memory Management DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE Total SGA size SGA_TARGET

14. How ASMM Works • ASSM is based on workload information that MMAN captures in the background. • MMAN uses memory advisors. • Memory is moved to where it is needed the most. • If an SPFILE is used (which is recommended): • Component sizes are saved across shutdowns. • Saved values are used to bootstrap component sizes. • There is no need to relearn optimal values.

15. Configuring ASMM by UsingDatabase Control

16. Manually Configuring ASMM • To have minimum sizes for some of the memory components, you should manually configure those component sizes: • If the automatically computed sizes do not serve your purpose for some reason • If there are short peaks or valleys in required memory that are not detected by ASMM • If you simply want a guaranteed minimum amount of memory for a specific component

17. STATISTICS_LEVEL = TYPICAL Manually Configuring ASMM Total SGA size = 8 GB Autotuned Autotuned Autotuned Streams pool Shared pool Database buffer cache Redo log buffer Keepbuffer pool Java pool Large pool Fixed SGA Autotuned Autotuned SGA_TARGET = 8G

18. Behavior of AutotunedSGA Parameters • When SGA_TARGET is not set or is set to zero: • Autotuned parameters behave as normal • SHARED_POOL_SIZE may need to be increased from settings used in earlier database versions • When SGA_TARGET is set to a nonzero value: • Default value of autotuned parameters is zero • The specified value is used as a minimum size SELECT SUM(bytes)/1024/1024 size_mb FROM v$sgastat WHERE pool = 'shared pool'; SELECT component, current_size/1024/1024 size_mb FROM v$sga_dynamic_components;

19. Behavior of Manually Tuned SGA Parameters • Some components are not autotuned. • KEEP and RECYCLE buffer caches • Multiple block size caches • Log buffer • These components must be manually configured using database parameters. • The memory used by these components reduces the amount of memory available for autotuning the SGA.

20. Using the V$PARAMETER View SGA_TARGET = 8G DB_CACHE_SIZE = 0 JAVA_POOL_SIZE = 0 LARGE_POOL_SIZE = 0 SHARED_POOL_SIZE = 0 STREAMS_POOL_SIZE = 0 SELECT name, value, isdefault FROM v$parameter WHERE name LIKE '%size';

21. Modifying the SGA_TARGET Parameter • The SGA_TARGET initialization parameter: • Is dynamic • Can be increased up to SGA_MAX_SIZE • Can be reduced until all components reach their minimum size • A change in the value of SGA_TARGET affects only automatically sized components.

22. Disabling ASMM • Setting SGA_TARGET to zero disables autotuning. • Autotuned parameters are set to their current sizes. • The SGA size as a whole is unaffected. SGA size = 8 GB SGA size = 8 GB Parameters: sga_target = 0 db_cache_size = 5G shared_pool_size = 2G large_pool_size = 512M java_pool_size = 256M streams_pool_size = 256M Parameters: sga_target = 8G shared_pool_size = 1G Original values

23. Manually Resizing Dynamic SGA Parameters • For autotuned parameters, manual resizing: • Results in immediate component resize if the new value is greater than the current size • Changes the minimum size if the new value is smaller than the current size • Resizing manually tuned parameters affects only the tunable portion of the SGA.

24. Program Global Area (PGA) • PGA Server process PGA • PrivateSQLareas • Cursor • and SQL • area • Sessionmemory • Workarea Dedicatedconnections Shared server PGA • Shared poolorlarge pool PGA Shared serverconnections

25. Automatic PGA Memory Management • Dynamically adjusts the amount of PGA memory dedicated to work areas, on the basis of the PGA_AGGREGATE_TARGET parameter • Helps maximize the performance of all the memory-intensive SQL operations • Is enabled by default

26. PGA Management Resources • Statistics to manage the PGA_AGGREGATE_TARGET initialization parameter, such as PGAcachehitpercentage • Views for monitoring the PGA work area include: • v$sql_workarea_histogram • v$pgastat • v$sql_workarea_active • v$sql_workarea • v$tempseg_usage • Views to assist in sizing the PGA work area are: • v$pga_target_advice • v$pga_target_advice_histogram

27. Using the Memory Advisor toSize the SGA

28. Using the Memory Advisorto Size the PGA

29. Efficient Memory Usage: Guidelines • Try to fit the SGA into physical memory. • Tune for a high buffer cache hit ratio, with the following caveats: • Even valid and necessary full table scans lower it. • It is possible that unnecessary repeated reads of the same blocks are artificially raising it. • Use the Memory Advisor.

30. Memory Tuning Guidelinesfor the Library Cache • Establish formatting conventions for developers so that SQL statements match in the cache. • Use bind variables. • Eliminate unnecessary duplicate SQL. • Consider using CURSOR_SHARING. • Use PL/SQL when possible. • Cache sequence numbers. • Pin objects in the library cache.

31. Summary In this lesson, you should have learned how to: • Describe the memory components in the SGA • Implement Automatic Shared Memory Management • Manually configure SGA parameters • Use automatic PGA memory management

32. Practice Overview: Using ASMM to Correct a Memory Allocation Problem This practice covers the following topics: • Diagnosing a memory allocation problem • Enabling Automatic Shared Memory Management