• 統計資訊

SQL> compute sum of bytes on pool

SQL> break on pool skip 1

SQL> select pool, name, bytes

from v$sgastat

order by pool, name

POOL NAME BYTES

---------------------- ---------------------------------------------------- ----------

java pool free memory 25165824

********************** ----------

sum 25165824

large pool free memory 50331648

********************** ----------

sum 50331648

shared pool 1M buffer 2098176

FileIdentificatonBlock 323292

FileOpenBlock 479952

POOL NAME BYTES

---------------------- ---------------------------------------------------- ----------

shared pool KGK heap 3756

KGLS heap 1894872

KQR M PO 548404

KQR S PO 116224

KQR S SO 1536

KSXR pending messages que 841036

KSXR receive buffers 1033000

MTTR advisory 38108

PL/SQL DIANA 627212

PL/SQL MPCODE 268136

PL/SQL PPCODE 40328

POOL NAME BYTES

---------------------- ---------------------------------------------------- ----------

shared pool PL/SQL SOURCE 12800

PLS non-lib hp 2068

PX subheap 19176

VIRTUAL CIRCUITS 481320

character set object 318524

dictionary cache 1610880

errors 126580

event statistics per sess 3133480

fixed allocation callback 180

free memory 54519460

joxs heap init 4220

POOL NAME BYTES

---------------------- ---------------------------------------------------- ----------

shared pool kgl simulator 695468

library cache 5138456

message pool freequeue 834752

miscellaneous 7861760

parameters 25404

sessions 748960

sim memory hea 141480

sql area 16316252

table definiti 2128

transaction 351516

trigger defini 2324

POOL NAME BYTES

---------------------- ---------------------------------------------------- ----------

shared pool trigger inform 1108

trigger source 968

********************** ----------

sum 100663296

buffer_cache 243269632

fixed_sga 453852

log_buffer 656384

********************** ----------

sum 244379868

看到SGA統計資訊裡一共有以下幾種型別

Java pool—為JVM分配的固定大小的MEM,10G後可以線上修改.

Large pool-- shared server connections for session memory,RMAN 等,9I開始就可以修改了.

Shared pool-- shared cursors, stored procedures, state objects, dictionary caches等,9I可以開始修改.

另外

SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';

SUM(BYTES)

----------

100663296

這個值始終比show parameter 裡的shared_pool_size 83886080大，也就是說v$sysstat裡的'shared pool'和shared_pool_size 不是一個概念，shared_pool_size 是'shared pool'的一部分，也就是shared_pool_size 是’shared pool’的重要組成部分但不是唯一的部分.比如control的個數，一個control_file會佔用256 bytes，你如果有3個的話就是768 bytes在這裡.但到了10G後這兩個值就是一致的了（一直都認為ORACLE的概念或命名規則方面有很大問題，不嚴謹，不統一）。

variable pool 是什麼呢？ variable pool = shared_pool + large_pool + java_pool，所以shared_pool=variable pool- large_pool - java_pool ,所以shared_pool= 176160768-25165824-50331648=100663296，和v$sgastat裡統計出來的一樣。

1* select * from v$sga_dynamic_components

SQL> /

COMPONENT

----------------------------------------------------------------------------------------------------

CURRENT_SIZE MIN_SIZE MAX_SIZE OPER_COUNT LAST_OPER_TY LAST_OPER_MO LAST_OPER_TIME GRANULE_SIZE

------------ ---------- ---------- ---------- ------------ ------------ -------------- ------------

shared pool

83886080 83886080 83886080 0 8388608

large pool

50331648 50331648 50331648 0 8388608

buffer cache

243269632 243269632 243269632 0 8388608

有些時候你設定的值和結果不一致是因為GRANULE_SIZE的關係，比如我如果設定large pool為50331000bytes，但實際上應該是50331648，因為最小分配單元是GRANULE_SIZE=8k，所以要分配6144個塊，是50331648，如上所示。GRANULE_SIZE的值就是分配記憶體的最小單元.版本的不同,OS的不同,SGA大小的不同, GRANULE_SIZE都有可能不同.