17,377
社区成员
发帖
与我相关
我的任务
分享命中率低的原因？
db block gets 403326885
consistent gets 8335854806
physical reads 4260756381
命中率才有52%
服务器物理内存1G,SGA为四百六十M，数据库缓冲区高速缓存已为三百五十MB。
请问如何调整？
...全文
请发表友善的回复…
发表回复
ylg007 2004-02-03
- 打赏
- 举报
高手帮帮忙
black_snail 2004-02-02
- 打赏
- 举报
check you sql first
ylg007 2004-02-02
- 打赏
- 举报
有一个存储过程需要扫描这个大表。
ylg007 2004-02-02
- 打赏
- 举报
是有一个大表（五百万条记录）被频繁的全表扫描。
welyngj 2004-02-02
- 打赏
- 举报
From www.dba-village.com
I would not suggest you to increase your SGA size, as it may cause swapping of memory. But low hot ratio does not mean that your system is performing bad or high buffer hit ratio does not mean that your system is performing well. But to tune your system, you need to concentrate on LIO. Your main goal should be reducing Logical IO (LIO) which will reduce the physical IO. For this, you may install the statspack and take a snapshot during peak hours and analyze your report and find out the queries which are doing lots of LIO.
HTH
Goto: Reply - Top of page
--------------------------------------------------------------------------------
Subject: Re: How to tune the memory?
Author: Michel Cadot, France
Date: Feb 01, 2004, 19 hours ago
Score:
Message: Hi,
My 2 cents to add something to good answer from Shailandra.
Do you have any response time probem? If not, don't worry about hit ratio or others?
If you have some performance issue, before statspack verify that you are not swapping. And with statspack, before analyzing all SQL statements to reduce LIO, take a look at the wait section of statspack report. It shows you where Oracle waits. May be this is not LIO but switch log too often, dbwr too slow, file I/O too slow, access to same block, contention on header... the waiting events show where are the bottlenecks. Have also a look at hard parses ratio (-> not use of bind variables), recursive call ratio (-> dictionary cache too small)...
Remember to set timed_statistics to true before running statspack.
Regards
Michel
I would not suggest you to increase your SGA size, as it may cause swapping of memory. But low hot ratio does not mean that your system is performing bad or high buffer hit ratio does not mean that your system is performing well. But to tune your system, you need to concentrate on LIO. Your main goal should be reducing Logical IO (LIO) which will reduce the physical IO. For this, you may install the statspack and take a snapshot during peak hours and analyze your report and find out the queries which are doing lots of LIO.
HTH
Goto: Reply - Top of page
--------------------------------------------------------------------------------
Subject: Re: How to tune the memory?
Author: Michel Cadot, France
Date: Feb 01, 2004, 19 hours ago
Score:
Message: Hi,
My 2 cents to add something to good answer from Shailandra.
Do you have any response time probem? If not, don't worry about hit ratio or others?
If you have some performance issue, before statspack verify that you are not swapping. And with statspack, before analyzing all SQL statements to reduce LIO, take a look at the wait section of statspack report. It shows you where Oracle waits. May be this is not LIO but switch log too often, dbwr too slow, file I/O too slow, access to same block, contention on header... the waiting events show where are the bottlenecks. Have also a look at hard parses ratio (-> not use of bind variables), recursive call ratio (-> dictionary cache too small)...
Remember to set timed_statistics to true before running statspack.
Regards
Michel
ylg007 2004-02-02
- 打赏
- 举报
to ： black_snail(●男人要忍○) (
存储过程是一个日结处理过程,每天统计每种商品(两万种商品)的销售异动.
所以要频繁的扫描销售明细表(五百万条记录).
当销售明细表超过三百万条记录时,日结处理速度变慢.
有什么好的建议吗?
存储过程是一个日结处理过程,每天统计每种商品(两万种商品)的销售异动.
所以要频繁的扫描销售明细表(五百万条记录).
当销售明细表超过三百万条记录时,日结处理速度变慢.
有什么好的建议吗?
ylg007 2004-01-31
- 打赏
- 举报
to yupyjy() (
如何检查呢?
如何检查呢?
welyngj 2004-01-31
- 打赏
- 举报
小表加cache属性.
do like this:
ALTER TABLE little CACHE;
去掉cache属性：
ALTER TABLE little NOCACHE
do like this:
ALTER TABLE little CACHE;
去掉cache属性：
ALTER TABLE little NOCACHE
yfjyz 2004-01-31
- 打赏
- 举报
小表加cache属性，是否就是把表中的数据保存到数据高速缓存中，而不是保存到数据文件里？
yupyjy 2004-01-31
- 打赏
- 举报
检查是否有频繁的全表扫描，导致数据替换出数据高速缓存
将经常使用的小表，加cache属性，将其数据保存到缓存中，避免换出
将经常使用的小表，加cache属性，将其数据保存到缓存中，避免换出
