ANBOB™

网上有一种说法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

仅供参考

打赏

微信扫一扫，打赏作者吧～