tag:blogger.com,1999:blog-2170637149389736039.post4832784346601359508..comments2023-10-30T06:20:46.020-07:00Comments on Simple Oracle Dba: Oracle Performance Tuning with hit ratios - Risky.PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-2170637149389736039.post-11158497207418755012019-09-17T02:59:51.320-07:002019-09-17T02:59:51.320-07:00This comment has been removed by a blog administrator.ramyahttps://www.blogger.com/profile/04210819277937060366noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-48309703099212702862019-08-27T22:04:42.704-07:002019-08-27T22:04:42.704-07:00This comment has been removed by a blog administrator.Ram Niwashttps://www.blogger.com/profile/01920750600501448726noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-65289663162353406942018-12-16T21:15:01.115-08:002018-12-16T21:15:01.115-08:00Thank you for sharing wonderful information with u...Thank you for sharing wonderful information with us to get some idea about that content. check it once through <br /><a href="https://www.anexas.net/machine-learning-with-tensorflow-training-in-tel-aviv" rel="nofollow">Machine Learning With TensorFlow Training and Course in Tel Aviv</a><br />| <a href="http://www.sixsigmaindubai.org/cphq-training/cphq-online-training-in-beirut-get-certified-online/" rel="nofollow">CPHQ Online Training in Beirut. Get Certified Online</a><br />Anonymoushttps://www.blogger.com/profile/01379802277255394663noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-48731172224666273352016-05-17T05:30:55.890-07:002016-05-17T05:30:55.890-07:00Such a wonderful blog post..I really liked it..Tha...Such a wonderful blog post..I really liked it..Thank for giving this one nice article..and<a href="http://www.aimitacademy.com/android-training-in-chennai/" rel="nofollow">see here </a>Ranjithhttp://www.aimitacademy.com/android-training-in-chennai/noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-13045035945644514232014-03-26T23:19:27.740-07:002014-03-26T23:19:27.740-07:00This comment has been removed by a blog administrator.Anonymoushttps://www.blogger.com/profile/01479459458221259663noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-21953698092160317402014-03-18T00:08:11.316-07:002014-03-18T00:08:11.316-07:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/01479459458221259663noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-13845347823795310242009-11-09T15:46:07.176-08:002009-11-09T15:46:07.176-08:00hm... me rambling on. This thing started on impuls...hm... me rambling on. This thing started on impulse a week ago. Typed almost a complete post again. Long sentences too. <br /><br />But then, the best (presentation-)topics are those with a grain of passion in them.<br /> <br />I hope I simplified or clarified something. Zzzz now.PdVhttps://www.blogger.com/profile/11518325134965208858noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-19815052796908667062009-11-09T15:42:33.917-08:002009-11-09T15:42:33.917-08:00Welcome, and grinning in agreement at you Jonathan...Welcome, and grinning in agreement at you Jonathan. I got _an_ idea.<br /><br />And Thanks for pointing out how granularity (and understanding!) is important.<br /><br />I hope I have conveyed the "limitations" of ratios as well.<br />"Do we have a problem?" or "did we pinpoint a problem?" depends on more then just a good ratio.<br /><br />Uniformity of measurement: good idea. <br />I had not given that any thought yet, as I only use ratios rather casual and tend to improvise. The V$SQL (diff over time) and WRH$SQL_STAT (deltas) probably are good (granular?) places to look.<br /><br />Also, data-collection may be interesting for comparison. Sys.aux_stat$ comes to mind. But I dont want to return to a situation where we re-publish all sort of high-tech ratio-cripts with blind-to-follow advice attached. I'd rather get ppl to think and understand things better (myself included).<br /><br /><br />Let me distinguish two situations for my "ratio usage": Acceptance-Testing versus troubleshooting.<br /><br />When in Acceptance-testing and capacity-planning, I am rather insistant on "good" ratios for gets/exe and gets/row (and plan-stability and LIO-predictability). If gets/exe are high (100s, 1000s, higher?) and the statements are expected to be fired at 1000s/hr, I know I have a potential CPU-problem, possibly a storage-bottleneck, and possibly longer-lasting transaction-locks, possibly a looming concurrency problem as well. <br />All bad news. Hence I might insist on good ratios, but I am never religious on it (hey, everyone is allowed their mistakes, I know I make many myself).<br /><br />When in troubleshooting-mode, the ratios I mention are no more then possible indicators of trouble. Just showing the "excellent hit ratio" from a system in trouble to a colleague will make him think and will demonstrate the danger of "aggregates".<br /><br />In troubleshooting mode, a lot depends on the situation. I would try to look in detail, for example for skews you mention, and for SQL_ids with multiple plans. <br />But I would also want to have an overview of the system both inside and outside of the database using OEM, Statspack and sar. And I would try to ascertain that the suspect-SQL was indeed related to (business) problem.<br /><br /><br />Nowadays, the CPU-capacity of some systems is large enough to ignore or hide a problem for quite some time. For example when data doesnt get purged. <br />But when a system then does reach (CPU)saturation it tends to hit "the knee" fairly hard.<br />Hence, my attempts to force ppl to write (and test) code for efficiency of individual actions. <br />Those detailed ratios are helpful there.<br />Concurrency, notably locking, is next on the list to check, but different topic.PdVhttps://www.blogger.com/profile/11518325134965208858noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-82980593335869153172009-11-09T13:22:13.465-08:002009-11-09T13:22:13.465-08:00... and then I got my row counts out by a factor o...... and then I got my row counts out by a factor of 10 - but I'm sure you get the idea.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-79046896744009272682009-11-09T13:19:41.885-08:002009-11-09T13:19:41.885-08:00Piet,
There is an important difference between yo...Piet,<br /><br />There is an important difference between your "buffer gets per row" and the historical use of hit ratios which I can best demonstrate with a simple question:<br /><br />Looking at the instance statistics from a one-hour snapshot, I see:<br /><i><br />table fetch by rowid: 2.6 billion<br />session logical reads: 1.99 billion<br /></i><br />According to your figures, does this indicate a problem or not ?<br /><br />And the answer should be that you can't tell. You can even go to a finer granularity - assume a sprepsql report shows 3 versions and 1,000 executions for a query: the number of buffer gets is 90,000 and the number of rows is 1,000 - do you have a problem ? <br /><br />Maybe, maybe not - perhaps 900 of your executions are doing 10 buffer gets and 100 are doing 810. (The awrsqrpt shows the work done by each version separately - which improves visibility, of course).<br /><br />At a fine enough granularity, an observation of the type "for each thing of type X we do N things of type Y" is, indeed, an example of using a ratio - but you need a degree of uniformity in how you measure before the ratio can have a useful meaning.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-56215893374747867452009-10-31T16:17:48.913-07:002009-10-31T16:17:48.913-07:00Thanks Martin.
Indeed. The good old cache-hit rat...Thanks Martin.<br /><br />Indeed. The good old cache-hit ratio can easily be "shown" to new(bie) and old(-fashioned) dba-groupies. Just point out logical and physical reads from statspack. Then ask them: What would you say the hit-ratios is, and is that good or bad?. Makes them think.<br /><br />Most modern system (OLTP) have very good cache-hit ratios, 99.9+ percent is not unusual. But they can still experience bad performance (or I wouldnt be looking into them).<br /><br />I'm looking forward to your blog on the topic.PdVhttps://www.blogger.com/profile/11518325134965208858noreply@blogger.comtag:blogger.com,1999:blog-2170637149389736039.post-3022874503797297602009-10-31T11:16:29.614-07:002009-10-31T11:16:29.614-07:00Nice one Piet - I can't disagree with your rat...Nice one Piet - I can't disagree with your ratios there. I'm looking forward to your next thoughts on the topic.<br /><br />I have a worse confession. I still use the Cache Hit Ratio and I am of the opinion that it is very useful. I think I'll use my own blog to expand on my dirty confession, I would not want to taint your blog with such things...Anonymousnoreply@blogger.com