The Self-Managing Database: Automatic SGA Memory Management

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

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

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

5. SGA Overview • In past releases: Separate parameters for SGA components DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE

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

7. SGA Overview • Oracle9i 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

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

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

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

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

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

13. Buffer Cache Buffer Cache Large Pool Shared Pool Java Pool Benefits of Automatic SGA Management • 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 Online Users Large Batch Jobs Large Pool Shared Pool Java Pool

14. 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 10g)

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

16. Using Automatic SGA Management: Automatically Tuned Parameters Parameters: SGA_TARGET = 8G LARGE_POOL_SIZE = 1G SHARED_POOL_SIZE = 1G • When SGA_TARGET is set: • 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 Buffer Cache (3G) Large Pool (1G) Sga size = 8G Shared Pool (3G) (Min size = 1G) Java Pool (1G)

17. Using Automatic SGA Management: Determining the current component sizes

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

19. Parameters: SGA_TARGET = 8G DB_KEEP_CACHE_SIZE = 1G Keep Cache (1G) Auto Tuned Components (7G) Shared Pool Large Pool Default Cache Java Pool 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

20. Using Automatic SGA Management Using v$parameter • Initialization parameter values: SGA_TARGET = 8G 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';

21. Enabling Automatic SGA Management

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

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

24. 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 = 8G sga size = 8G 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 SGA_TARGET=0

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

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

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

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

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

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

31. 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 10g : 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.html

32. Reminder – please complete the OracleWorld online session surveyThank you.

33. Q & Q U E S T I O N S A N S W E R S A