oracle sga構成

SGA

1、Followed components can be managed auto.

1)shared pool(sql pl/sql execute, dictionary cache)

2)buffer cache(data buffer read from /write to disk；使用兩個list管理：write list/LRU list.)

The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list.

3)redo buffer pool( redo buffer write to disk，log_buffer一般設定1M到3M,見注1)

4)large pool(rman backup buffer)

5)java pool(execute java)

6)stream pool()

2、Followed components can be managed mannually only.

■ Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)
■ Additional buffer caches for non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

注1：

The initialization parameter LOG_BUFFER determines the size (in bytes) of the redo log
buffer. In general, larger values reduce log file I/O, particularly if transactions are long
or numerous. The default setting is either 512 kilobytes (KB) or 128 KB times the
setting of the CPU_COUNT parameter, whichever is greater.

重要引數說明：

sga_target 動態引數，可以透過alter system set sga_target=400M;

sga_max_size 靜態初始化引數。sga_target

DB_BLOCK_SIZE=4096 (資料塊大小)
DB_CACHE_SIZE=1024M （預設資料塊大小4k對應的buffer cache）
DB_2K_CACHE_SIZE=256M（2k資料塊對應的buffer cache）
DB_8K_CACHE_SIZE=512M（8k資料塊對應的buffer cache）

USE_INDIRECT_DATA_BUFFERS（擴充套件記憶體引數）

The USE_INDIRECT_DATA_BUFFERS parameter enables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory, this parameter is ignored.

SGA相關檢視

select * from V$SGA_RESIZE_OPS;
select * from V$SGA_CURRENT_RESIZE_OPS;
select * from V$SGA_TARGET_ADVICE;
select * from V$SGA;
select * from V$SGAINFO;
select * from V$SGASTAT;
select * from V$SGA_DYNAMIC_COMPONENTS;
select * from V$SGA_DYNAMIC_FREE_MEMORY;

也可以透過下面的sql查詢：

SQL> select name,object_id,type from v$fixed_table where name like 'V%SGA%' ;

NAME OBJECT_ID TYPE
------------------------------ ---------- -----
V$SGA 4294950938 VIEW
V$SGASTAT 4294950939 VIEW
V$SGAINFO 4294952085 VIEW
V$SGA_CURRENT_RESIZE_OPS 4294951935 VIEW
V$SGA_RESIZE_OPS 4294951937 VIEW
V$SGA_DYNAMIC_COMPONENTS 4294951939 VIEW
V$SGA_DYNAMIC_FREE_MEMORY 4294951941 VIEW
V$SGA_TARGET_ADVICE 4294952616 VIEW

8 rows selected

SQL>

[@more@]