1、Monitoring and Managing MemoryObjectivesAfter completing this lesson, you should be able to:Describe the memory components in the SGAImplement Automatic Shared Memory ManagementManually configure SGA parametersUse automatic PGA memory managementOracle Memory StructuresJava poolDatabasebuffer cacheSh

2、ared poolRedo log bufferLarge poolSGAStreams poolServerprocess1PGAServerprocess2PGABack-groundprocessPGAKeep buffer cacheRecycle buffer cachenK Block Size buffer cachesOracle Memory StructuresFull Notes PageBuffer CacheData filesSGACheckpoint queue.DBWnServerDB_BLOCK_SIZEDB_CACHE_SIZEDB_RECYCLE_CACH

3、E_SIZEDB_KEEP_CACHE_SIZELRU lists.DB buffer cacheUsing Multiple Buffer PoolsSGADB buffer cachesKeep poolRecycle poolDefault poolUsing Multiple Buffer PoolsCREATE INDEX cust_idx STORAGE (BUFFER_POOL KEEP );ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE);ALTER INDEX oe.cust_lname_ix STORAGE (BU

4、FFER_POOL KEEP);Shared poolLibrarycacheDatadictionarycacheUGAShared poolShared pool Defined by SHARED_POOL_SIZELibrary 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 con

5、tains session information for Oracle Shared Server users when a large pool is not configured.Shared PoolShared poolDatabase buffer cacheRedo logbuffer Large pool Large PoolLibrary cacheDictionary cacheUser global areaCan be configured as a separate memory area in the SGAIs sized by the LARGE_POOL_SI

6、ZE parameterIs used to store data in memory for:Backup and restore operationsSession data for the shared serversParallel query messagingUGA and Oracle Shared ServerShared poolPGAShared poolorlarge poolStackspacePGAStackspaceCursor stateUser session dataUGADedicated server configurationShared server

7、configurationUser session dataCursor stateUGAJava PoolCan be configured as a separate memory area in the SGAIs 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 poolDatabase buffer cacheRedo logbuffer Large pool Li

8、brary cacheDictionary cacheUser global areaThe Redo Log BufferSQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736;Serverprocess LGWRControl filesARCnArchivedlog filesRedo log filesData filesShared poolRedo logbuffer Library cacheDictionary cacheUser global areaDatabase buffer cacheA

9、utomatic Shared Memory Management: OverviewAutomatically adapts to workload changesMaximizes memory utilizationHelps eliminate out-of-memory errorsBuffer cacheLarge poolShared poolJava poolBuffer cacheLarge poolShared poolJava poolOnline usersBatch jobsBenefits of Automatic Shared Memory ManagementD

10、B_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZETotal SGA sizeSGA_TARGETSGA Tuning PrinciplesBased 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 mended:

11、Component sizes saved across shutdownsSaved values used to bootstrap component sizesAvoids having to relearn optimal valuesDatabase Control andAutomatic Shared Memory ManagementManual ConfigurationSGA_TARGET = 8G Total SGA size = 8 GBJava poolDatabasebuffer cacheRedo log bufferShared poolLarge poolF

12、ixed SGASTATISTICS_LEVEL = TYPICAL Auto-tunedAuto-tunedAuto-tunedAuto-tunedStreams poolKeepbuffer poolBehavior of Auto-TunedSGA ParametersWhen 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 da

13、tabase 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|MFROM v$ pone

14、nts;Behavior of Manually Tuned SGA ParametersSome components are not auto-tuned.KEEP and RECYCLE buffer cachesMultiple block size cachesLog bufferStreams poolThese components must be manually configured using database parameters.The memory used by these components reduces the amount of memory availa

15、ble for auto-tuning the SGA.Using the V$PARAMETER ViewSGA_TARGET = 8GSELECT name, value, isdefault FROM v$parameterWHERE name LIKE %size;DB_CACHE_SIZE = 0JAVA_POOL_SIZE = 0LARGE_POOL_SIZE = 0SHARED_POOL_SIZE = 0 Resizing SGA_TARGETThe SGA_TARGET initialization parameter:Is dynamicCan be increased up

16、 to SGA_MAX_SIZECan be reduced until all components reach their minimum sizeA change in the value of SGA_TARGET affects only automatically sized components.SGA size = 8 GBDisabling Automatic Shared Memory Management Setting SGA_TARGET to zero disables auto-tuning.Auto parameters are set to their cur

17、rent sizes.SGA size as a whole is unaffected.Parameters:sga_target = 8Gshared_pool_size=1GParameters:sga_target = 0db_cache_size = 4Gshared_pool_size = 2Glarge_pool_size = 512Mjava_pool_size = 512MSGA size = 8 GBOriginal valuesManually Resizing Dynamic SGA ParametersFor auto-tuned parameters, manual

18、 resizing:Results in immediate component resize if the new value is greater than the current sizeChanges the minimum size if the new value is smaller than the current sizeManually tuned parameter resizing affects only the tunable portion of the SGA.Program Global Area (PGA)PGAServerprocessPGAShared

19、poolorlarge poolPGADedicatedconnectionsSharedserverPGAShared serverconnectionsPrivateSQLareasCursorand SQLareaSessionmemoryWorkareaAutomatic PGA Memory ManagementDynamically adjusts the amount of PGA memory dedicated to work areasMemory allocated to work areas is derived from the PGA_AGGREGATE_TARGET parameterHelps to maximize the performance of all the memory-intensive SQL operationsEnabled by default PGA Management ResourcesStatistics to manage the PGA_AGGREGATE_TARGET initialization