: SGA Tirthankar Lahiri, Arvind Nithrakasyhap, Brian Hirano, Kant Patel, Poojan Kumar, Sushil Kumar Oracle Corporation Oracle Database 10g 1 SGA Oracle Oracle Oracle SGA SQL PL/SQL Java Java Java SGA shared_pool_size db_cache_size java_pool_size large_pool_size SGA Oracle9i DBA ORA-4031 Recovery Manager OLTP Oracle Database 10g Oracle 10g DBA SGA_TARGET SGA : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 1
SGA_TARGET SGA_TARGET SGA 1. Oracle SGA 2. 3. 4. Java 5. 6. / 7. 8. Oracle Database 10g SGA SGA_TARGET SGA SGA Oracle SGA_TARGET SGA SGA_TARGET 1. SQL PL/SQL 2. Java Java 3. Recovery Manager 4. Oracle : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 2
1a: SGA_TARGET = 256MB SHARED_POOL_SIZE = 32MB DB_CACHE_SIZE = 100MB 32MB 100MB 124MB 4 SGA = 64MB = 128MB Java = 60MB = 4MB : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 3
SGA V$SGA_DYNAMIC_COMPONENTS DB_CACHE_SIZE SHARED_POOL_SIZE SGA Enterprise Manager 1b: SGA EM SGA SGA / DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_<N>K_CACHE_SIZE N = {2 4 8 16 32} : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 4
STREAMS_POOL_SIZE Enterprise Manager ALTER SYSTEM SGA_TARGET = 256MB DB_8K_CACHE_SIZE = 32MB STREAMS_POOL_SIZE = 24MB 200MB 256 32 24 SGA SGA 1GB SGA SHARED_POOL_SIZE=128MG DB_CACHE_SIZE=896MB 128MB ORA-4031 DBA SGA_TARGET = 1G ORA-4031 SQL : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 5
1 DBA SGA EM SGA_TARGET SGA SGA 16MB SHARED_POOL_SIZE = 256MB DB_CACHE_SIZE = 512MB LARGE_POOL_SIZE = 256MB LOG_BUFFER = 16MB SGA_TARGET = 256MB + 512MB + 256MB + 16MB + 16MB SGA = 1056MB Enterprise Manager SGA : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 6
2: Enterprise Manager EM SGA_TARGET SGA_TARGET SGA SGA V$SGA select sum(value) from v$sga; : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 7
alter system set sga_target=512m; alter system set db_cache_size = 0; 536870912 512MB SGA alter system set shared_pool_size = 0; alter system set large_pool_size = 0; alter system set java_pool_size = 0; SGA SGA_TARGET SGA_TARGET SGA_MAX_SIZE 1 SGA_TARGET 1 SGA_TARGET OS Unix SGA SGA_MAX_SIZE SGA_TARGET SGA_MAX_SIZE SGA_MAX_SIZE Solaris Windows SGA SGA_TARGET SGA_TARGET SGA_MAX_SIZE = 1024MB SGA_TARGET = 512MB DB_8K_CACHE_SIZE = 128MB SGA_TARGET 1024MB Java 1 CPU DB_8K_CACHE_SIZE 128MB SGA_TARGET SGA_MAX_SIZE = 1024MB SGA_TARGET = 512MB DB_CACHE_SIZE = 96MB DB_8K_CACHE_SIZE = 128MB : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 8
DB_8K_CACHE_SIZE 128MB 96MB SGA_TARGET SGA_TARGET SGA_TARGET SGA_TARGET SHARED_POOL_SIZE SGA SGA_TARGET = 512MB SHARED_POOL_SIZE = 256MB = 284MB SHARED_POOL_SIZE 128MB SGA_TARGET SHARED_POOL_SIZE 300MB 300MB 1 1 1 : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 9
SGA_TARGET = 512MB DB_8K_CACHE_SIZE = 128MB DB_8K_CACHE_SIZE 144MB 16MB 16MB DB_8K_CACHE_SIZE 112MB 16MB 16MB SPFILE SPFILE Oracle Database 10g : SGA Oracle Corporation THE SELF-MANAGING DATABASE: AUTOMATIC SGA MEMORY MANAGEMENT 10