还得请各位帮下忙!
现在数据库的Data buffer 命中率太低了,我想调整一下db_cache_size和shared_pool_size。但我不能确定当前他们的大小,请大家帮忙看一下,当前的db_cache_size和shared_pool_size的设置是多少呢!
现在数据库是动态SGA管理,未做过调整:
SQL> --Data buffer 命中率检查 (不应该低于90%,如果该值过低,应该调高初始化参数。8
i调整db_block_buffer,9i调整db_cache_size)
SQL> select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
2 from v$sysstat cur, v$sysstat con, v$sysstat phy
3 where cur.name = 'db block gets'
4 and con.name = 'consistent gets'
5 and phy.name = 'physical reads';
HIT RATIO
----------
.876198106
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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> show sga
Total System Global Area 671088640 bytes
Fixed Size 1373692 bytes
Variable Size 145361412 bytes
Database Buffers 520093696 bytes
Redo Buffers 4259840 bytes
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 6M
shared_pool_size big integer 0
SQL> select * from v$sgainfo;
NAME BYTES RES
------------------------- ---------- ---
Fixed SGA Size 1373692 No
Redo Buffers 4259840 No
Buffer Cache Size 507510784 Yes
Shared Pool Size 146800640 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 671088640 No
Startup overhead in Share 62914560 No
d Pool
Startup NUMA Shared Pool 25165824 No
memory
Free SGA Memory Available 0
已选择12行。
SQL> @getpar.txt
输入 par 的值: cache_size
原值 6: AND x.ksppinm Like '%&par%'
新值 6: AND x.ksppinm Like '%cache_size%'
NAME VALUE DESCRIB
------------------------- ------------- ----------------------------------------
__db_cache_size 511705088 Actual size of DEFAULT buffer pool for s
tandard block size buffers
db_cache_size 0 Size of DEFAULT buffer pool for standard
block size buffers
db_2k_cache_size 0 Size of cache for 2K buffers
db_4k_cache_size 0 Size of cache for 4K buffers
db_8k_cache_size 0 Size of cache for 8K buffers
db_16k_cache_size 0 Size of cache for 16K buffers
db_32k_cache_size 0 Size of cache for 32K buffers
db_keep_cache_size 0 Size of KEEP buffer pool for standard bl
ock size buffers
db_recycle_cache_size 0 Size of RECYCLE buffer pool for standard
block size buffers
_xsolapi_sql_prepare_stmt 16 OLAP API prepare statement cache size
_cache_size
_xsolapi_sql_result_set_c 32 OLAP API result set cache size
ache_size
已选择11行。
SQL> @getpar.txt
输入 par 的值: shared_pool
原值 6: AND x.ksppinm Like '%&par%'
新值 6: AND x.ksppinm Like '%shared_pool%'
NAME VALUE DESCRIB
------------------------- ------------- ----------------------------------------
__shared_pool_size 142606336 Actual size in bytes of shared pool
shared_pool_size 0 size in bytes of shared pool
shared_pool_reserved_size 6291456 size in bytes of reserved area of shared
pool
_shared_pool_reserved_pct 5 percentage memory of the shared pool all
ocated for the reserved area
_shared_pool_reserved_min 4400 minimum allocation size in bytes for res
_alloc erved area of shared pool
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_shared_pool_max_size 0 shared pool maximum size when auto SGA e
nabled
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use
for a mining model
已选择8行。
SQL>