SGA的各种参数

SQL> show parameter xxxx

db_block_size integer 8192

sga_max_size big integer 420551900

java_pool_size big integer 25165824

large_pool_size big integer 50331648

shared_pool_reserved_size big integer 4194304

shared_pool_size big integer 83886080

db_cache_size big integer 243269632

log_buffer integer 524288

workarea_size_policy string AUTO

SQL> show sga

Total System Global Area 420551900 bytes

Fixed Size 453852 bytes

Variable Size 176160768 bytes

Database Buffers 243269632 bytes

Redo Buffers 667648 bytes

SQL> select * from v$sga;

NAME VALUE

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

Fixed Size 453852

Variable Size 176160768

Database Buffers 243269632

Redo Buffers 667648

• 基本组成

Fixed Size 一般不大，而且我们无法控制，是SGA内部用来控制其它内部信息的,比如控制其他SGA组件。比如它应该和java_pool_size + large_pool_size + shared_pool_size的大小有关系，而且还会和其他参数有关.

Redo Buffer 存放那些需要写到online redo logs的东西.因为MEM TO MEM要比MEM TO DISK快很多, ORACLE这么设置就是为了提高速度,所以数据不会在这里存放很长时间就会被LGWR 写到REDO LOG(three seconds,commit,one third full,1M).一般设置成128*cpu

Database Buffers (BUFFER CACHE)自8以后就分成三个部分.

Default Pool

Keep Pool :

Recycle Pool

这三个部分的管理方法与淘汰方法都一样,只是为了将’热的’(Default),’普通的’(Keep),’不需要关心是否要保持在CACHE中’(Recycle)这三部分隔离开来, Recycle Pool 不需要很大,以便来去都快.这种设计增加了DBA的工作强度,实际上很多时候不需要考虑这么细分buffer cache,尽量通过其他途径比如修改SQL来调整性能.

Database Buffers 里面有两种数据,1,dirty ,会被DBWn写入到数据文件中,

2,非dirty,用来读.

8.0以前用LRU淘汰法,现在用’touch count’的算法,这个算法更符合实际.具体信息可以在x$bh里得到.

1 select tch||' '|| owner||' '|| object_name||' '|| object_type

2 from dba_objects,

3 (select *

4 from ( select tch, obj

5 from x$bh

6 order by tch desc

7 )

8 where rownum <= 5

9 ) hottest_blocks

10 where dba_objects.data_object_id = hottest_blocks.obj

11* order by tch desc

SQL> /

TCH||''||OWNER||''||OBJECT_NAME||''||OBJECT_TYPE

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

417 SBPOPT DE_RECEIVELOG TABLE

412 SBPOPT DE_RECEIVELOG TABLE

192 SBPOPT TI_REPAIR_DEED TABLE PARTITION

165 SYS C_USER# CLUSTER

165 SYS TSQ$ TABLE

165 SYS USER$ TABLE

X$BH里就是在BUFFER CACHE里的块的信息,TCH就是’touch count’,随着频繁的使用,TCH也会增加的.

或

select tch, file#, dbablk

from x$bh

where (dbablk,file#) in

(select dbms_rowid.rowid_block_number(rowid),

dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','YOUR TABLE NAME')

from dual)

and state = 1