
Automatic SGA Management
- You specify the total amount of SGA availabe using
SGA_TARGET
- Oracle automatically distributes the memory among the various SGA components.
- SGA Components: Pools of memory used to satisfy different classes of memory allocation requests.
- Shared Pool (Library cache, Data Dictionary Cache, Server Result cache, reserved pool, Private sql areas, etc)
- Large Pool
- Database Buffer Cache
- Java Pool
- Streams Pool
- Fixed SGA
- If you are using an SPFILE, the database remember the sizes of the various components across instance shutdowns (no need to learn workload characteristics at each startup).
- Oracle allocates/deallocates memory in chuncks of allocation units (granules)
- If total sga size < 1G ==> granule = 4Mb.
- If total_sga_size > 1G ==> granule = 16Mb
1. Automatic Instance tuning
|
2. Automatic SGA and PGA tuning
|
- You can query the
V$SSGAINFO
view to check the size of the various SGA components and see the allocation unit size (called granule size) that is being used by the instance. - granule size is platform-specific and is determined by the total SGA size
SQL> select name, bytes, resizeable from v$sgainfo; NAME BYTES RES ------------------------------- ---------- --- Fixed SGA Size 1336260 No Redo Buffers 12582912 No Buffer Cache Size 318767104 Yes Shared Pool Size 385875968 Yes Large Pool Size 16777216 Yes Java Pool Size 16777216 Yes Streams Pool Size 33554432 Yes Shared IO Pool Size 0 Yes Granule Size 16777216 No Maximum SGA Size 1272213504 No Startup overhead in Shared Pool 67108864 No Free SGA Memory Available 486539264 12 rows selected.
3. Automatic SGA and Manual PGA tuning
|
4. Manual SGA and Automatic PGA tuning
|
5. Fully manual ( SGA and PGA ) tuning
|
No comments:
Post a Comment