BUFFER CACHE之四:Cache Hit Ratio和缓冲池的问题

 

全表扫描,应用程序设计,随意查询大表,查询对象过于集中。

 

二、        命中率不是buffer cache性能优化的重中之重:

1.    设计糟糕的数据库可能有99%的命中率

2.    只是性能优化的一部分

3.    等待事件和SQL优化占主导。

有良好命中率的应用程序仍可能有很多不必要的物理读!

A程序:1000000逻辑读,10000物理读,命中率是99%

B程序:100逻辑读,仅有40次物理读,命中率是60%

AB,谁更优?

 

三、        增大Cache Size要考虑的条件顺序,切忌命中率一低就开始增加:

1.    等待事件是否调优?

2.    SQL语句是否调优?(SQL语句去看Sataspack >报告)

3.    是否有过多的分页错误(undue page faulting)?

4.    先前增加buffer cache的操作是否有效?

(无效就需慎重操作了,分类和并行读不需要缓冲池)

5.    还是低命中率(Low cable hit ratio)。

6.    决定增大缓冲池,且sga_max_size不大,用alter system增加keepdefaultrecycle

NOTE:命中率过高就需减少buffer cache

 

按理论buffer cache hit ratio不应该超过100……

09-16

按理论buffer cache hit ratio不应该超过100,通过性能监视器发现我的buffer cache hit ratio每天11:45左右的一两分钟时间到达1360000rn09/14/2005 10:38:47.694 99.2153257rn09/14/2005 10:38:49.694 99.2150159rn09/14/2005 10:38:51.694 5747901.741rn09/14/2005 10:38:53.694 515597.7801rn09/14/2005 10:38:55.694 311252.2189rn09/14/2005 10:38:57.694 249727.5434rn09/14/2005 10:38:59.694 182096.1847rn09/14/2005 10:39:01.694 118111.2583rn09/14/2005 10:39:03.694 90559.26688rn09/14/2005 10:39:05.694 75203.45049rn09/14/2005 10:39:07.694 66035.59667rn09/14/2005 10:39:09.694 59326.06661rn09/14/2005 10:39:11.694 53221.80572rn09/14/2005 10:39:13.694 51192.13933rn09/14/2005 10:39:15.694 47585.82575rn09/14/2005 10:39:17.694 45193.37542rn09/14/2005 10:39:19.694 39051.55997rn09/14/2005 10:39:21.694 37811.36261rn09/14/2005 10:39:23.694 36109.42874rn09/14/2005 10:39:25.694 34086.26887rn09/14/2005 10:39:27.694 32604.7433rn09/14/2005 10:39:29.694 30867.90043rn09/14/2005 10:39:31.694 29747.68425rn09/14/2005 10:39:33.694 28644.62525rn09/14/2005 10:39:35.694 27788.29587rn09/14/2005 10:39:37.693 26650.864rn09/14/2005 10:39:39.693 26212.9629rn09/14/2005 10:39:41.693 25877.45002rn09/14/2005 10:39:43.693 25619.27298rn09/14/2005 10:39:45.693 24749.53133rn09/14/2005 10:39:47.693 24110.92358rn09/14/2005 10:39:49.693 23638.06354rn09/14/2005 10:39:51.693 23400.58606rn09/14/2005 10:39:53.693 22330.81192rn09/14/2005 10:39:55.693 21644.28423rn09/14/2005 10:39:57.693 21410.37797rn09/14/2005 10:39:59.693 20594.77421rn09/14/2005 10:40:01.693 20531.39872rn09/14/2005 10:40:03.693 20168.54912rn09/14/2005 10:40:05.693 20106.32549rn09/14/2005 10:40:07.693 19997.6852rn09/14/2005 10:40:09.693 19419.91134rn09/14/2005 10:40:11.693 18797.68374rn09/14/2005 10:40:13.693 18457.64324rn09/14/2005 10:40:15.693 18169.09876rn09/14/2005 10:40:17.693 17972.65426rn09/14/2005 10:40:19.693 17725.18116rn09/14/2005 10:40:21.693 17220.22352rn09/14/2005 10:40:23.693 17191.05881rn09/14/2005 10:40:25.693 16980.10212rn09/14/2005 10:40:27.693 16810.22065rn09/14/2005 10:40:29.692 16471.5515rn09/14/2005 10:40:31.692 16297.74257rn09/14/2005 10:40:33.692 16084.71468rn09/14/2005 10:40:35.692 15786.16544rn09/14/2005 10:40:37.692 15439.49296rn09/14/2005 10:40:39.692 14923.06631rn09/14/2005 10:40:41.692 14526.9854rn09/14/2005 10:40:43.692 14216.32529rn09/14/2005 10:40:45.692 14200.49195rn09/14/2005 10:40:47.692 14187.14916rn09/14/2005 10:40:49.692 14068.51409rn09/14/2005 10:40:51.692 13807.74264rn09/14/2005 10:40:53.692 13406.62437rn09/14/2005 10:40:55.692 13389.61879rn09/14/2005 10:40:57.692 13219.30331rn09/14/2005 10:40:59.692 13208.90846rn09/14/2005 10:41:01.692 13081.88064rn09/14/2005 10:41:03.692 12954.88938rn09/14/2005 10:41:05.692 12825.85494rn09/14/2005 10:41:07.692 1.668141662rn09/14/2005 10:41:09.692 1.75548695rn09/14/2005 10:41:11.692 2.180512861rn09/14/2005 10:41:13.692 3.800690178rn09/14/2005 10:41:15.692 3.845417622rn09/14/2005 10:41:17.692 4.409128446rn09/14/2005 10:41:19.692 5.222575894rn09/14/2005 10:41:21.691 5.585918829rn09/14/2005 10:41:23.691 6.187959048rn09/14/2005 10:41:25.691 7.343187515rn09/14/2005 10:41:27.691 7.756865186rnrnrn09/15/2005 11:42:37.128 99.17437919rn09/15/2005 11:42:39.128 99.17595215rn09/15/2005 11:42:41.128 99.17791277rn09/15/2005 11:42:43.128 99.17850346rn09/15/2005 11:42:45.128 1361522.713rn09/15/2005 11:42:47.128 210211.1769rn09/15/2005 11:42:49.128 150173.2575rn09/15/2005 11:42:51.128 132079.4064rn09/15/2005 11:42:53.127 111020.5858rn09/15/2005 11:42:55.127 102861.9233rn09/15/2005 11:42:57.127 93084.38193rn09/15/2005 11:42:59.127 82634.99431rn09/15/2005 11:43:01.127 78131.61591rn09/15/2005 11:43:03.127 70037.08478rn09/15/2005 11:43:05.127 69057.58034rn09/15/2005 11:43:07.127 63472.2991rn09/15/2005 11:43:09.127 62542.70394rn09/15/2005 11:43:11.127 59373.97958rn09/15/2005 11:43:13.127 57906.17567rn09/15/2005 11:43:15.127 56364.44136rn09/15/2005 11:43:17.127 55307.03637rn09/15/2005 11:43:19.127 53054.5952rn09/15/2005 11:43:21.127 51194.55405rn09/15/2005 11:43:23.127 50432.2361rn09/15/2005 11:43:25.127 49469.51483rn09/15/2005 11:43:27.127 46055.77017rn09/15/2005 11:43:29.127 45331.17394rn09/15/2005 11:43:31.127 44698.38639rn09/15/2005 11:43:33.127 44594.77318rn09/15/2005 11:43:35.127 44176.1207rn09/15/2005 11:43:37.127 43013.87504rn09/15/2005 11:43:39.127 40170.98565rn09/15/2005 11:43:41.127 39497.38279rn09/15/2005 11:43:43.127 39118.69163rn09/15/2005 11:43:45.126 35697.61728rn09/15/2005 11:43:47.126 34661.58201rn09/15/2005 11:43:49.126 33918.62731rn09/15/2005 11:43:51.126 32156.42691rn09/15/2005 11:43:53.126 30746.14868rn09/15/2005 11:43:55.126 29004.14271rn09/15/2005 11:43:57.126 28922.56091rn09/15/2005 11:43:59.126 28805.29126rn09/15/2005 11:44:01.126 27634.21739rn09/15/2005 11:44:03.126 26152.7046rn09/15/2005 11:44:05.126 24513.33575rn09/15/2005 11:44:07.126 24418.87782rn09/15/2005 11:44:09.126 24286.01535rn09/15/2005 11:44:11.126 24176.99846rn09/15/2005 11:44:13.126 23611.12525rn09/15/2005 11:44:15.126 22995.91103rn09/15/2005 11:44:17.126 21728.97044rn09/15/2005 11:44:19.126 20809.61672rn09/15/2005 11:44:21.126 19742.91914rn09/15/2005 11:44:23.126 19358.0486rn09/15/2005 11:44:25.126 19232.14371rn09/15/2005 11:44:27.126 18948.37086rn09/15/2005 11:44:29.126 18558.45949rn09/15/2005 11:44:31.126 18345.17817rn09/15/2005 11:44:33.126 18121.02833rn09/15/2005 11:44:35.126 17093.2994rn09/15/2005 11:44:37.125 16912.72888rn09/15/2005 11:44:39.125 16550.34556rn09/15/2005 11:44:41.125 16413.36909rn09/15/2005 11:44:43.125 16004.57667rn09/15/2005 11:44:45.125 15886.24067rn09/15/2005 11:44:47.125 15756.38966rn09/15/2005 11:44:49.125 15705.79763rn09/15/2005 11:44:51.125 15631.80079rn09/15/2005 11:44:53.125 15170.42017rn09/15/2005 11:44:55.125 15134.3493rn09/15/2005 11:44:57.125 15098.39524rn09/15/2005 11:44:59.125 14990.64693rn09/15/2005 11:45:01.125 14918.68894rn09/15/2005 11:45:03.125 14421.35834rn09/15/2005 11:45:05.125 14284.99757rn09/15/2005 11:45:07.125 14223.73613rn09/15/2005 11:45:09.125 14137.29749rn09/15/2005 11:45:11.125 13960.44533rn09/15/2005 11:45:13.125 13837.58137rn09/15/2005 11:45:15.125 13692.60556rn09/15/2005 11:45:17.125 13480.92671rn09/15/2005 11:45:19.125 13302.34602rn09/15/2005 11:45:21.125 13172.99287rn09/15/2005 11:45:23.125 13125.29885rn09/15/2005 11:45:25.125 13040.45375rn09/15/2005 11:45:27.125 13009.12363rn09/15/2005 11:45:29.124 12649.91479rn09/15/2005 11:45:31.124 12636.39158rn09/15/2005 11:45:33.124 12575.29167rn09/15/2005 11:45:35.124 12445.80665rn09/15/2005 11:45:37.124 12263.35518rn09/15/2005 11:45:39.124 12134.90305rn09/15/2005 11:45:41.124 0.683436588rn09/15/2005 11:45:43.124 2.735947223rn09/15/2005 11:45:45.124 4.54275996rn09/15/2005 11:45:47.124 4.70901971rn09/15/2005 11:45:49.124 7.660117784rn09/15/2005 11:45:51.124 8.667222077rn09/15/2005 11:45:53.124 8.837809812rnrn请专家答疑! 论坛

没有更多推荐了,返回首页