网上有一种说法ASH buffers的大小按照以下算法分配:Min(shared_pool_size*5%,2M*cpu_count)
我验证了一下,使用asmm或msmm,找了三台机器,结果有点不同
########################server 1########################
SQL> run
1 select name,value,display_value from v$parameter where name in
2* ('shared_pool_size','cpu_count')
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 2 2
shared_pool_size 0 0
SQL> select * from v$sgastat where name like '%SHARED%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool SHARED SERVERS INFO 2696
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 4194304
shared pool UNDO INFO HASH 2992
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
################################server 2#################################
SQL> run
1 select name,value,display_value from v$parameter where name in
2 ('shared_pool_size','cpu_count')
3*
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 16 16
shared_pool_size 0 0
SQL> select * from v$sgastat where name like '%SHARED%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool SHARED SERVERS INFO 4000
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 30408704
shared pool UNDO INFO HASH 38880
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
##################################server 3############################
[oracle@icme-db ~]$ ora
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 26 17:07:20 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col name for a10
SQL> col value for a10
SQL> col name for a20
SQL> col display_value for a10
SQL> select name,value,display_value from v$parameter where name in
2 ('shared_pool_size','cpu_count');
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 8 8
shared_pool_size 838860800 800M
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 16252928
shared pool UNDO INFO HASH 77600
SQL> select 838860800*5/100 from dual;
838860800*5/100
---------------
41943040
SQL> select 2*1024*1024*8 from dual;
2*1024*1024*8
-------------
16777216
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
仅供参考
我验证了一下,使用asmm或msmm,找了三台机器,结果有点不同
########################server 1########################
SQL> run
1 select name,value,display_value from v$parameter where name in
2* ('shared_pool_size','cpu_count')
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 2 2
shared_pool_size 0 0
SQL> select * from v$sgastat where name like '%SHARED%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool SHARED SERVERS INFO 2696
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 4194304
shared pool UNDO INFO HASH 2992
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
################################server 2#################################
SQL> run
1 select name,value,display_value from v$parameter where name in
2 ('shared_pool_size','cpu_count')
3*
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 16 16
shared_pool_size 0 0
SQL> select * from v$sgastat where name like '%SHARED%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool SHARED SERVERS INFO 4000
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 30408704
shared pool UNDO INFO HASH 38880
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
##################################server 3############################
[oracle@icme-db ~]$ ora
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 26 17:07:20 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col name for a10
SQL> col value for a10
SQL> col name for a20
SQL> col display_value for a10
SQL> select name,value,display_value from v$parameter where name in
2 ('shared_pool_size','cpu_count');
NAME VALUE DISPLAY_VA
-------------------- ---------- ----------
cpu_count 8 8
shared_pool_size 838860800 800M
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ -------------------- ----------
shared pool ASH buffers 16252928
shared pool UNDO INFO HASH 77600
SQL> select 838860800*5/100 from dual;
838860800*5/100
---------------
41943040
SQL> select 2*1024*1024*8 from dual;
2*1024*1024*8
-------------
16777216
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
仅供参考
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




