Oracle sga和pga的分配

今天我们来说下Oracle 中sga和pga的分配：
modify sga
alter system set sga_target=1024m scope=spfile;
alter system set sga_max_size=1024m scope=spfile;(SGA分配为可用物理内存的40%)
shut immediate
startup

SHOW PARAMETERS SHARED_POOL_SIZE;
ALTER SYSTEM SET SHARED_POOL_SIZE=800M SCOPE=both; memory 10%

shared_pool_size+db_cache_size=SGA_MAX_SIZE*70%左右
alter system set db_cache_size= 大小M scope=spfile sid=‘数据库SID’;
alter system set db_cache_size=2048m scope=spfile;
show parameter db_cache_size

RAC环境，需要这样增加sid=’’：
alter system set sga_max_size=1024m scope=spfile sid=’’;
alter system set sga_target=1024m scope=spfile sid=’*’;
shut immediate
startup

modify pga
alter system set workarea_size_policy=auto scope=both;
alter system set pga_aggregate_target=1024m scope=both;
show parameter workarea
show parameter pga
show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;

查看命中率
SELECT a.VALUE “命中率” FROM V$PGASTAT a where a.NAME = ‘cache hit percentage’;