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 • Use automatic PGA memory management

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

4. 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

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

6. 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);

7. Shared Pool • Defined by 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. • UGA contains session information for Oracle Shared Server users when a large pool is not configured. Shared pool Shared pool Shared pool Librarycache Datadictionarycache UGA

8. 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: • Backup and restore operations • Session data for the shared servers • Parallel query messaging Shared pool Redo log buffer Database buffer cache Large pool Library cache Dictionary cache User global area

9. UGA and Oracle Shared Server Dedicated server configuration • PGA • UGA • Stack • space • Shared pool • Cursor state • User session data Shared server configuration • PGA • UGA • Shared poolorlarge pool • Cursor state • Stack • space • User session data

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 User global area

11. The Redo Log Buffer Shared pool Redo log buffer Database buffer cache Library cache Dictionary cache User global area 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 Large pool Large pool Shared pool Shared pool Java pool Java pool Online users Batch jobs

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

14. SGA Tuning Principles • Based on workload information, MMAN captures statistics periodically in the background. • MMAN uses the different memory advisories. • Memory is moved to where it is most needed. • Using an SPFILE is recommended: • Component sizes saved across shutdowns • Saved values used to bootstrap component sizes • Avoids having to relearn optimal values

15. Database Control andAutomatic Shared Memory Management

16. STATISTICS_LEVEL = TYPICAL Manual Configuration Total SGA size = 8 GB Auto-tuned Auto-tuned Streams pool Shared pool Database buffer cache Redo log buffer Keepbuffer pool Java pool Large pool Fixed SGA Auto-tuned Auto-tuned SGA_TARGET = 8G

17. Behavior of Auto-TunedSGA Parameters • When SGA_TARGET is not set or is set to zero: • Auto-tuned parameters behave as previously. • SHARED_POOL_SIZE may need to be increased from settings used on earlier database versions. • When SGA_TARGET is set to a non-zero value: • Default value of auto-tuned parameters is zero. • If set to a non-zero value, the specified value is used as a minimum size. SELECT SUM(bytes)/1024/1024 FROM v$sgastatWHERE pool = 'shared pool'; SELECT component,current_size/1024/1024||'M' FROM v$sga_dynamic_components;

18. Behavior of Manually Tuned SGA Parameters • Some components are not auto-tuned. • KEEP and RECYCLE buffer caches • Multiple block size caches • Log buffer • Streams pool • These components must be manually configured using database parameters. • The memory used by these components reduces the amount of memory available for auto-tuning the SGA.

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

20. Resizing SGA_TARGET • 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.

21. Disabling Automatic Shared Memory Management • Setting SGA_TARGET to zero disables auto-tuning. • Auto parameters are set to their current sizes. • SGA size as a whole is unaffected. SGA size = 8 GB SGA size = 8 GB Parameters: sga_target = 0 db_cache_size = 4G shared_pool_size = 2G large_pool_size = 512M java_pool_size = 512M Parameters: sga_target = 8G shared_pool_size=1G Original values

22. Manually Resizing Dynamic SGA Parameters • For auto-tuned 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 • Manually tuned parameter resizing affects only the tunable portion of the SGA.

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

24. Automatic PGA Memory Management • Dynamically adjusts the amount of PGA memory dedicated to work areas • Memory allocated to work areas is derived from the PGA_AGGREGATE_TARGET parameter • Helps to maximize the performance of all the memory-intensive SQL operations • Enabled by default

25. 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

26. Using the Memory Advisor

27. 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

28. Practice 15 Overview:Automatic Shared Memory Management • This practice covers using Automatic Shared Memory Management to avoid long running query issues.