Session id 40091 The SelfManaging Database Automatic SGA

Session id: 40091 The Self-Managing Database: Automatic SGA Memory Management Tirthankar Lahiri Senior Manager, Distributed Cache & Memory Management Oracle Corporation

Outline Ÿ Overview of Oracle Shared Memory (SGA) Ÿ Introducing Automatic SGA Management Ÿ Benefits Ÿ Using the feature Ÿ How Does it Work? Ÿ Summary

SGA Overview Ÿ SGA: Oracle’s “Shared Global Area” Ÿ Comprises multiple memory components Shared Pool Database Buffer Cache Redo Log Buffer Java Pool Fixed SGA Large Pool SGA

SGA Overview Ÿ In past releases: Separate parameters for SGA components DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE

SGA Overview Ÿ Difficult to optimally set parameters Ÿ Undersized component: – – Poor performance (excess IO, parses) Out-of-memory errors (ORA-4031) Ÿ Oversized component: – – Wastes memory Configuring for the worst-case

SGA Overview Ÿ Oracle 9 i introduced SGA memory advisories: – – Buffer Cache Advice (v$db_cache_advice) Shared Pool Advice (v$shared_pool_advice) Ÿ Predicts performance for different sizes Buffer Cache Advice Physical IOs Buffer cache size

SGA Overview Ÿ Advisories allow better sizing for components – – Available out-of-the-box Reduces trial and error Recommendations based on actual workload With Dynamic SGA: Ÿ Recommendations can be implemented online Ÿ Task of adjusting sizes still left to the DBA

Introducing Automatic SGA Memory Management Ÿ The Oracle Database 10 g – – – Single parameter for total SGA size Automatically sizes SGA components Memory is transferred to where most needed Uses workload information Uses internal advisory predictions

Introducing Automatic SGA Memory Management Ÿ Single parameter for SGA size DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE Enable Automatic Shared Memory Management SGA_TARGET

Introducing Automatic SGA Memory Management Ÿ Set SGA_TARGET to the total SGA size Shared Pool Database Buffer Cache Redo Log Buffer Java Pool Fixed SGA Large Pool SGA_TARGET = 8 G

Introducing Automatic SGA Memory Management Ÿ Four most commonly configured components are automatically sized: – – Shared Pool Large Pool Java Pool Buffer Cache (DEFAULT buffer pool) Ÿ STATISTICS_LEVEL must be set to TYPICAL

Benefits of Automatic SGA Management Online Users Buffer Cache Large Batch Jobs Buffer Cache Large Pool Shared Pool Java Pool Ÿ Automatically adapts to workload changes Ÿ Maximizes memory utilization Ÿ Single Parameter makes it easier to use Ÿ Helps eliminate out of memory errors Ÿ Can help improve performance

Using Automatic SGA Management: SGA_TARGET Parameter Ÿ Includes everything in the SGA: – – Fixed SGA and other internal allocations Automatically sized SGA components Ÿ Default buffer cache Ÿ Shared pool Ÿ Large pool Ÿ Java pool – Manual SGA components Ÿ Log buffer Ÿ Other caches (KEEP/RECYCLE, other blocksizes) Ÿ Streams pool (new in 10 g)

Using Automatic SGA Management: Automatically Tuned Parameters Ÿ When SGA_TARGET is not set (or zero): – – Auto-tuned parameters behave as in Oracle 9 i Exception: SHARED_POOL_SIZE: Ÿ In Oracle 10 g internal startup overhead is included Ÿ May need to increase value from Oracle 9 i Ÿ Query to determine actual shared pool size in 9 i Ÿ Shared Pool Size automatically adjusted during upgrade SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared pool';

Using Automatic SGA Management: Automatically Tuned Parameters Sga size = 8 G Parameters: SGA_TARGET = 8 G LARGE_POOL_SIZE = 1 G SHARED_POOL_SIZE = 1 G Ÿ When SGA_TARGET is set: – Buffer Cache (3 G) – Large Pool (1 G) – Shared Pool (3 G) (Min size = 1 G) Java Pool (1 G) Default values of auto-tuned parameters is zero A non-zero value is a lower bound on the size of the component Actual component size may be higher

Using Automatic SGA Management: Determining the current component sizes

Using Automatic SGA Management: Manually Tuned Parameters Ÿ Some components not yet auto-tuned – – – Other buffer caches (Keep/Recycle) Multiple blocksize caches Streams Pool Ÿ Their parameters are user-specified Ÿ Precisely control the sizes of their components

Using Automatic SGA Management: Manually Tuned Parameters Ÿ When SGA_TARGET is set: – – Total size of manual parameters is subtracted Balance is given to the auto-tuned components Parameters: SGA_TARGET = 8 G DB_KEEP_CACHE_SIZE = 1 G Auto Tuned Components (7 G) Shared Pool Large Pool Default Cache Java Pool Keep Cache (1 G)

Using Automatic SGA Management Using v$parameter Ÿ Initialization parameter values: SGA_TARGET = 8 G DB_CACHE_SIZE = 0 JAVA_POOL_SIZE = 0 LARGE_POOL_SIZE = 0 SHARED_POOL_SIZE = 0 Querying V$PARAMETER SELECT name, value, isdefault FROM v$parameter WHERE name like '%size';

Enabling Automatic SGA Management

Using Automatic SGA Management : Resizing SGA_TARGET Ÿ SGA_TARGET is dynamic Ÿ Can be increased till SGA_MAX_SIZE Ÿ Can be reduced till some component reaches minimum size Ÿ Change in value of SGA_TARGET affects only automatically sized components

Using Automatic SGA Management : Resizing SGA_TARGET Shared Pool Database Buffer Cache Java Pool Fixed SGA Redo Log Buffer Large Pool SGA_TARGET = 8 G SGA_TARGET=9 G SGA_MAX_SIZE=10 G

Disabling Automatic SGA Management Ÿ SGA_TARGET = 0 disables auto-tuning – – Auto parameters set to current component sizes SGA size as a whole is unaffected sga size = 8 G Parameters: sga_target = 8 G shared_pool_size=1 G SGA_TARGET=0 Parameters: sga_target = 0 db_cache_size = 4 G shared_pool_size = 2 G large_pool_size = 512 M java_pool_size = 512 M

Using Automatic SGA Management Resizing Auto Tuned Parameters • Results in component resize only if new value > current size • Otherwise silently changes the minimum size Database Shared Pool SGA_TARGET = 8 G SHARED_POOL_SIZE=1 G Buffer Cache Redo Log Buffer actual size = 2 G Java Pool Fixed SGA Large Pool

Using Automatic SGA Management Resizing Manually Set Parameters Ÿ Manual parameter resize affects the tunable portion of the SGA Parameters: SGA_TARGET = 8 G DB_KEEP_CACHE_SIZE = 1 G Parameters: SGA_TARGET = 8 G DB_KEEP_CACHE_SIZE = 2 G Auto Tuned Components (7 G) Shared Pool Large Pool Default Cache Auto Tuned Components (6 G) Java Pool Keep Cache (1 G) Keep Cache (2 G)

How Does It Work? SGA Background Process Coordinates sizing of SGA components Background SGA Memory Broker Tracks component size and pending resize

How Does It Work? Basic SGA Tuning Principles Ÿ Based on workload information Ÿ Captures statistics periodically in background Ÿ Uses the different memory advisories Ÿ Memory is moved to where most needed Ÿ No need to configure parameters for the worst-case

How Does It Work? Persistence of auto-tuned values Ÿ If server parameter file (spfile) is used: – – – Component sizes saved across shutdowns Saved values used to bootstrap component sizes Avoids having to relearn optimal values Ÿ For this reason use of spfile is recommended with Automatic SGA Management

Conclusions Ÿ Relieves the administrator from manually configuring the SGA Ÿ Internal algorithm adjusts based on workload Ÿ No need to configure for the worst-case Ÿ Simple usage model

Next Steps…. Ÿ Recommended sessions – – – The Self-Managing Database : Proactive Space and Schema Object Management (Thurs, Sept 11, 8 AM) The Self-Managing Database : Automatic Health Monitoring (Thurs, Sept. 11, 11 AM) The Invisible Oracle : Deploying Oracle Database in Embedded Environment (Wed, Sept. 10, 4: 30 PM) Ÿ Recommended demos and/or hands-on labs – – Oracle Database 10 g : Manage the Oracle Environment Hands. On Lab Campground Demo : “The Self-Managing Database: Memory Management” Ÿ Relevant web sites to visit for more information – http: //otn. oracle. com/products/manageability/database/content. ht ml

Reminder – please complete the Oracle. World online session survey Thank you.

Q& A QUESTIONS ANSWERS