• 统计信息

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都有可能不同.