Transcript shared pool
Monitoring and Managing Memory Copyright © 2004, Oracle. All rights reserved. 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 15-2 Copyright © 2004, Oracle. All rights reserved. Oracle Memory Structures Server process 1 PGA Server process 2 PGA Background process PGA SGA Shared pool Streams pool Large pool Keep buffer cache Recycle buffer cache Java pool 15-3 Database buffer cache Redo log buffer Copyright © 2004, Oracle. All rights reserved. nK Block Size buffer caches Buffer Cache SGA Server LRU Checkpoint queue lists . . . . DB buffer cache . . . . DBWn DB_BLOCK_SIZE DB_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_KEEP_CACHE_SIZE Data files 15-5 Copyright © 2004, Oracle. All rights reserved. Using Multiple Buffer Pools SGA DB buffer caches Recycle pool Keep pool Default pool 15-7 Copyright © 2004, Oracle. All rights reserved. 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); 15-8 Copyright © 2004, Oracle. All rights reserved. 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 Library cache Shared Datapool dictionary cache UGA 15-9 Copyright © 2004, Oracle. All rights reserved. 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 Redo log Database buffer buffer cache Shared pool Library cache Dictionary cache User global area 15-10 Copyright © 2004, Oracle. All rights reserved. Large pool UGA and Oracle Shared Server Dedicated server configuration PGA Stack space Shared pool UGA User Cursor session state data Shared server configuration Shared pool or large pool 15-11 UGA User Cursor session state data Copyright © 2004, Oracle. All rights reserved. PGA Stack space 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 sessionspecific Java code and data within the JVM Redo log Database buffer buffer cache Shared pool Library cache Dictionary cache User global area 15-12 Copyright © 2004, Oracle. All rights reserved. Large pool The Redo Log Buffer Redo log Database buffer buffer cache Shared pool Library cache Dictionary cache User global area Server process LGWR ARCn Control files SQL> UPDATE employees 15-13 2 SET salary=salary*1.1 3 WHERE employee_id=736; Data files Redo log files Copyright © 2004, Oracle. All rights reserved. Archived log files 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 15-14 Shared pool Shared pool Java pool Java pool Online users Batch jobs Copyright © 2004, Oracle. All rights reserved. Benefits of Automatic Shared Memory Management DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE Total SGA size SGA_TARGET 15-15 Copyright © 2004, Oracle. All rights reserved. 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-16 Copyright © 2004, Oracle. All rights reserved. Database Control and Automatic Shared Memory Management 15-17 Copyright © 2004, Oracle. All rights reserved. Manual Configuration Auto-tuned Auto-tuned Shared pool Database buffer cache Redo log buffer Streams pool Fixed SGA Large pool Keep buffer pool Java pool Auto-tuned Auto-tuned Total SGA size = 8 GB SGA_TARGET = 8G STATISTICS_LEVEL = TYPICAL 15-18 Copyright © 2004, Oracle. All rights reserved. Behavior of Auto-Tuned SGA 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. SELECT SUM(bytes)/1024/1024 FROM v$sgastat WHERE pool = 'shared pool'; • 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 component,current_size/1024/1024||'M' FROM v$sga_dynamic_components; 15-19 Copyright © 2004, Oracle. All rights reserved. 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 • • 15-20 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. Copyright © 2004, Oracle. All rights reserved. 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 15-21 Copyright © 2004, Oracle. All rights reserved. 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 • 15-22 A change in the value of SGA_TARGET affects only automatically sized components. Copyright © 2004, Oracle. All rights reserved. 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 = 8G shared_pool_size=1G Parameters: sga_target = 0 db_cache_size = 4G shared_pool_size = 2G large_pool_size = 512M java_pool_size = 512M Original values 15-23 Copyright © 2004, Oracle. All rights reserved. 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 • 15-24 Manually tuned parameter resizing affects only the tunable portion of the SGA. Copyright © 2004, Oracle. All rights reserved. Program Global Area (PGA) PGA Server process PGA Private Cursor Session SQL and SQL memory areas area Dedicated connections Shared server PGA Shared server connections 15-25 Shared pool or large pool Copyright © 2004, Oracle. All rights reserved. PGA Work area Automatic PGA Memory Management • • • • 15-27 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 Copyright © 2004, Oracle. All rights reserved. PGA Management Resources • Statistics to manage the PGA_AGGREGATE_TARGET initialization parameter, such as PGA cache hit percentage • 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 15-28 Copyright © 2004, Oracle. All rights reserved. Using the Memory Advisor 15-29 Copyright © 2004, Oracle. All rights reserved. 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 15-30 Copyright © 2004, Oracle. All rights reserved. Practice 15 Overview: Automatic Shared Memory Management This practice covers using Automatic Shared Memory Management to avoid long running query issues. 15-31 Copyright © 2004, Oracle. All rights reserved.