Re: Performance monitoring
Jorma, Performance tuning is a complex subject. There really isn't a list of 10 things to watch for. Every system is different. I would (attempt to) summarize tuning by these five steps: 1.) Have a capacity/performance target in mind. If you don't know where you're going, how will you know if you have gotten there? 2.) Monitor your response times as load increases. Can you achieve your response time target at the specified load? If so, you're done, successful test, congratulations. If not, continue to next step. 3.) Actively monitor what's going on in the database, while it's happening. It's always easier to see it in real time than just looking at random StatsPack snapshots taken at 5 or 10 or 15 minute intervals. (Not that I'm saying StatsPack shouldn't be collected. I'm just saying don't rely on StatsPack as your only source of info about the database.) The V$ Wait Interface is your friend. If you're not familiar with it, go to http://www.hotsos.com/ and get Mogens Norgaard's paper, Introducing the V$ Wait Interface. Where is the database spending it's time? What's the bottleneck? If you identify a few trouble sessions, you may want to dive deeper w/ some 10046 traces at level 8 on specific sessions. You almost certainly do NOT want to do this instance wide. 4.) Once you have some indication as to what's going on in the database, you need to see how the system is doing overall. On most flavors of *nix, where I'm comfortable, sar (System Activity Reporter) is an excellent tool. Use it to determine if you have any systemwide CPU, memory, or I/O contention. (Other OSes almost certainly have similar utilities.) 5.) Address the biggest bottleneck. This is where it can't be summarized in a simple step. You need to understand the bottleneck, so that you can understand how to tune it. If may be latch contention. Depending on the latch, it could be poorly tuned SQL, or lack of bind variables, or simple CPU capacity limits, or a whole host of things. I/O contention? Could be anything from poorly designed and/or configured RAID array to poorly tuned SQL, or who knows what. Determine the cause of the biggest bottleneck and minimize or eliminate it. There you have it, Mark's Simplified Performance Tuning, in five easy steps! ;-) -Mark On Wed, 2002-10-02 at 02:08, [EMAIL PROTECTED] wrote: > Ave ! > > I like to hear Your opinion about the most importat > issues, what should be monitored from the database (8.1.7, SUN) during > perfomance testing. The purpose in this case, is limit the > monitoring to concern only about 10 most important ones. > > I have difficulties to make my mind to pick up the right ones, so > if You had to have made similar kind of decisions or have opinions, > please let me know. > > TIA > Jorma > - > Name: Jorma Vuorio Phone: +358-9-7180 67759 > Company: Nokia Business Infrastucture Fax:+358-9-7180 67465 > Address: P.O.Box 321, FIN-00045 NOKIA GROUP, FINLAND > Internet: [EMAIL PROTECTED]Mobile: +358-50-486 8043 > - -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring
Thak's Mark I agreed, but they have gotten an idea to get only couple "most important" measurements from db, because they don't want to have a huge reports with all possible statistics. Very understandable, but as You wrote, there isn't any absolutely top ten. In any case, I have to do this (stupid) list, so give Your best shot, please. t.Jorma Ps. I heard, that Dave Ensor from BMC, has once presented that kind of list? -Original Message- Sent: 02 October, 2002 12:23 To: Multiple recipients of list ORACLE-L Jorma, Performance tuning is a complex subject. There really isn't a list of 10 things to watch for. Every system is different. I would (attempt to) summarize tuning by these five steps: 1.) Have a capacity/performance target in mind. If you don't know where you're going, how will you know if you have gotten there? 2.) Monitor your response times as load increases. Can you achieve your response time target at the specified load? If so, you're done, successful test, congratulations. If not, continue to next step. 3.) Actively monitor what's going on in the database, while it's happening. It's always easier to see it in real time than just looking at random StatsPack snapshots taken at 5 or 10 or 15 minute intervals. (Not that I'm saying StatsPack shouldn't be collected. I'm just saying don't rely on StatsPack as your only source of info about the database.) The V$ Wait Interface is your friend. If you're not familiar with it, go to http://www.hotsos.com/ and get Mogens Norgaard's paper, Introducing the V$ Wait Interface. Where is the database spending it's time? What's the bottleneck? If you identify a few trouble sessions, you may want to dive deeper w/ some 10046 traces at level 8 on specific sessions. You almost certainly do NOT want to do this instance wide. 4.) Once you have some indication as to what's going on in the database, you need to see how the system is doing overall. On most flavors of *nix, where I'm comfortable, sar (System Activity Reporter) is an excellent tool. Use it to determine if you have any systemwide CPU, memory, or I/O contention. (Other OSes almost certainly have similar utilities.) 5.) Address the biggest bottleneck. This is where it can't be summarized in a simple step. You need to understand the bottleneck, so that you can understand how to tune it. If may be latch contention. Depending on the latch, it could be poorly tuned SQL, or lack of bind variables, or simple CPU capacity limits, or a whole host of things. I/O contention? Could be anything from poorly designed and/or configured RAID array to poorly tuned SQL, or who knows what. Determine the cause of the biggest bottleneck and minimize or eliminate it. There you have it, Mark's Simplified Performance Tuning, in five easy steps! ;-) -Mark On Wed, 2002-10-02 at 02:08, [EMAIL PROTECTED] wrote: > Ave ! > > I like to hear Your opinion about the most importat > issues, what should be monitored from the database (8.1.7, SUN) during > perfomance testing. The purpose in this case, is limit the > monitoring to concern only about 10 most important ones. > > I have difficulties to make my mind to pick up the right ones, so > if You had to have made similar kind of decisions or have opinions, > please let me know. > > TIA > Jorma > - > Name: Jorma Vuorio Phone: +358-9-7180 67759 > Company: Nokia Business Infrastucture Fax:+358-9-7180 67465 > Address: P.O.Box 321, FIN-00045 NOKIA GROUP, FINLAND > Internet: [EMAIL PROTECTED]Mobile: +358-50-486 8043 > - -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGu
RE: Performance monitoring
Well ... if you need short reports, look for: 1. waits 2. buffer cache hit ratio 3. dictionary hit ratio 4. library hit ratio 5. latches 6. parsing/execution ratio 7. data file i/o 8. shared pool memory distribution 9. session contention 10. session memory usage inka -Original Message- Sent: Wednesday, October 02, 2002 7:08 AM To: Multiple recipients of list ORACLE-L Thak's Mark I agreed, but they have gotten an idea to get only couple "most important" measurements from db, because they don't want to have a huge reports with all possible statistics. Very understandable, but as You wrote, there isn't any absolutely top ten. In any case, I have to do this (stupid) list, so give Your best shot, please. t.Jorma Ps. I heard, that Dave Ensor from BMC, has once presented that kind of list? -Original Message- Sent: 02 October, 2002 12:23 To: Multiple recipients of list ORACLE-L Jorma, Performance tuning is a complex subject. There really isn't a list of 10 things to watch for. Every system is different. I would (attempt to) summarize tuning by these five steps: 1.) Have a capacity/performance target in mind. If you don't know where you're going, how will you know if you have gotten there? 2.) Monitor your response times as load increases. Can you achieve your response time target at the specified load? If so, you're done, successful test, congratulations. If not, continue to next step. 3.) Actively monitor what's going on in the database, while it's happening. It's always easier to see it in real time than just looking at random StatsPack snapshots taken at 5 or 10 or 15 minute intervals. (Not that I'm saying StatsPack shouldn't be collected. I'm just saying don't rely on StatsPack as your only source of info about the database.) The V$ Wait Interface is your friend. If you're not familiar with it, go to http://www.hotsos.com/ and get Mogens Norgaard's paper, Introducing the V$ Wait Interface. Where is the database spending it's time? What's the bottleneck? If you identify a few trouble sessions, you may want to dive deeper w/ some 10046 traces at level 8 on specific sessions. You almost certainly do NOT want to do this instance wide. 4.) Once you have some indication as to what's going on in the database, you need to see how the system is doing overall. On most flavors of *nix, where I'm comfortable, sar (System Activity Reporter) is an excellent tool. Use it to determine if you have any systemwide CPU, memory, or I/O contention. (Other OSes almost certainly have similar utilities.) 5.) Address the biggest bottleneck. This is where it can't be summarized in a simple step. You need to understand the bottleneck, so that you can understand how to tune it. If may be latch contention. Depending on the latch, it could be poorly tuned SQL, or lack of bind variables, or simple CPU capacity limits, or a whole host of things. I/O contention? Could be anything from poorly designed and/or configured RAID array to poorly tuned SQL, or who knows what. Determine the cause of the biggest bottleneck and minimize or eliminate it. There you have it, Mark's Simplified Performance Tuning, in five easy steps! ;-) -Mark On Wed, 2002-10-02 at 02:08, [EMAIL PROTECTED] wrote: > Ave ! > > I like to hear Your opinion about the most importat > issues, what should be monitored from the database (8.1.7, SUN) during > perfomance testing. The purpose in this case, is limit the > monitoring to concern only about 10 most important ones. > > I have difficulties to make my mind to pick up the right ones, so > if You had to have made similar kind of decisions or have opinions, > please let me know. > > TIA > Jorma > - > Name: Jorma Vuorio Phone: +358-9-7180 67759 > Company: Nokia Business Infrastucture Fax:+358-9-7180 67465 > Address: P.O.Box 321, FIN-00045 NOKIA GROUP, FINLAND > Internet: [EMAIL PROTECTED]Mobile: +358-50-486 8043 > - -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: h
Re: Performance monitoring
The ten most important performance items to monitor are your ten most important business transactions. Period, end of story. If you want to provide an intelligent response, monitor how long they take and provide a weekly report, noting any changes. Ratios and wait events are just diagnostic tools -- when business transactions become slow you use them to find where the problem is. Anyone who asks you to monitor ten internal things, such as ratios or wait events, and no more than just ten because they only want the ten most important, is simply uneducated and unexperienced, since these things are of no importantance if the system is running fast enough for the users, and besides there are way more than ten important things like this. However, they may also be very smart, since a good manager may be wise to start by getting a handle on the ten most important things. Probably, you want to be taking statspack snapshots every hour just to have a baseline, so just DO THAT and give the statspack report to the manager once a week -- and make them happy by picking out ten items you consider important and running a yellow highlighter over them. Have a cover page that compares the current value of the ten items with the same items four weeks ago -- so you are monitoring them. That's fast, easy and smart, it makes your manager happy and you'll have your eye on the ball too. But don't forget that the most important thing to monitor is reality. Your coolest move is to have the manager pull the users into a meeting and get them to identify the ten business transactions that are most important to them. Find out if any are too slow. Find out if anything is too slow. Monitor that. Tune that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring
Right On! -Original Message- Sent: Wednesday, October 02, 2002 4:18 PM To: Multiple recipients of list ORACLE-L The ten most important performance items to monitor are your ten most important business transactions. Period, end of story. If you want to provide an intelligent response, monitor how long they take and provide a weekly report, noting any changes. Ratios and wait events are just diagnostic tools -- when business transactions become slow you use them to find where the problem is. Anyone who asks you to monitor ten internal things, such as ratios or wait events, and no more than just ten because they only want the ten most important, is simply uneducated and unexperienced, since these things are of no importantance if the system is running fast enough for the users, and besides there are way more than ten important things like this. However, they may also be very smart, since a good manager may be wise to start by getting a handle on the ten most important things. Probably, you want to be taking statspack snapshots every hour just to have a baseline, so just DO THAT and give the statspack report to the manager once a week -- and make them happy by picking out ten items you consider important and running a yellow highlighter over them. Have a cover page that compares the current value of the ten items with the same items four weeks ago -- so you are monitoring them. That's fast, easy and smart, it makes your manager happy and you'll have your eye on the ball too. But don't forget that the most important thing to monitor is reality. Your coolest move is to have the manager pull the users into a meeting and get them to identify the ten business transactions that are most important to them. Find out if any are too slow. Find out if anything is too slow. Monitor that. Tune that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring
Very well said. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Sent: Wednesday, October 02, 2002 4:18 PM To: Multiple recipients of list ORACLE-L The ten most important performance items to monitor are your ten most important business transactions. Period, end of story. If you want to provide an intelligent response, monitor how long they take and provide a weekly report, noting any changes. Ratios and wait events are just diagnostic tools -- when business transactions become slow you use them to find where the problem is. Anyone who asks you to monitor ten internal things, such as ratios or wait events, and no more than just ten because they only want the ten most important, is simply uneducated and unexperienced, since these things are of no importantance if the system is running fast enough for the users, and besides there are way more than ten important things like this. However, they may also be very smart, since a good manager may be wise to start by getting a handle on the ten most important things. Probably, you want to be taking statspack snapshots every hour just to have a baseline, so just DO THAT and give the statspack report to the manager once a week -- and make them happy by picking out ten items you consider important and running a yellow highlighter over them. Have a cover page that compares the current value of the ten items with the same items four weeks ago -- so you are monitoring them. That's fast, easy and smart, it makes your manager happy and you'll have your eye on the ball too. But don't forget that the most important thing to monitor is reality. Your coolest move is to have the manager pull the users into a meeting and get them to identify the ten business transactions that are most important to them. Find out if any are too slow. Find out if anything is too slow. Monitor that. Tune that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring
Buffer Cache Hit Ratio? What's that? "Inka Bezdziecka" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/02/2002 08:03 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Performance monitoring Well ... if you need short reports, look for: 1. waits 2. buffer cache hit ratio 3. dictionary hit ratio 4. library hit ratio 5. latches 6. parsing/execution ratio 7. data file i/o 8. shared pool memory distribution 9. session contention 10. session memory usage inka -Original Message- Sent: Wednesday, October 02, 2002 7:08 AM To: Multiple recipients of list ORACLE-L Thak's Mark I agreed, but they have gotten an idea to get only couple "most important" measurements from db, because they don't want to have a huge reports with all possible statistics. Very understandable, but as You wrote, there isn't any absolutely top ten. In any case, I have to do this (stupid) list, so give Your best shot, please. t.Jorma Ps. I heard, that Dave Ensor from BMC, has once presented that kind of list? -Original Message- Sent: 02 October, 2002 12:23 To: Multiple recipients of list ORACLE-L Jorma, Performance tuning is a complex subject. There really isn't a list of 10 things to watch for. Every system is different. I would (attempt to) summarize tuning by these five steps: 1.) Have a capacity/performance target in mind. If you don't know where you're going, how will you know if you have gotten there? 2.) Monitor your response times as load increases. Can you achieve your response time target at the specified load? If so, you're done, successful test, congratulations. If not, continue to next step. 3.) Actively monitor what's going on in the database, while it's happening. It's always easier to see it in real time than just looking at random StatsPack snapshots taken at 5 or 10 or 15 minute intervals. (Not that I'm saying StatsPack shouldn't be collected. I'm just saying don't rely on StatsPack as your only source of info about the database.) The V$ Wait Interface is your friend. If you're not familiar with it, go to http://www.hotsos.com/ and get Mogens Norgaard's paper, Introducing the V$ Wait Interface. Where is the database spending it's time? What's the bottleneck? If you identify a few trouble sessions, you may want to dive deeper w/ some 10046 traces at level 8 on specific sessions. You almost certainly do NOT want to do this instance wide. 4.) Once you have some indication as to what's going on in the database, you need to see how the system is doing overall. On most flavors of *nix, where I'm comfortable, sar (System Activity Reporter) is an excellent tool. Use it to determine if you have any systemwide CPU, memory, or I/O contention. (Other OSes almost certainly have similar utilities.) 5.) Address the biggest bottleneck. This is where it can't be summarized in a simple step. You need to understand the bottleneck, so that you can understand how to tune it. If may be latch contention. Depending on the latch, it could be poorly tuned SQL, or lack of bind variables, or simple CPU capacity limits, or a whole host of things. I/O contention? Could be anything from poorly designed and/or configured RAID array to poorly tuned SQL, or who knows what. Determine the cause of the biggest bottleneck and minimize or eliminate it. There you have it, Mark's Simplified Performance Tuning, in five easy steps! ;-) -Mark On Wed, 2002-10-02 at 02:08, [EMAIL PROTECTED] wrote: > Ave ! > > I like to hear Your opinion about the most importat > issues, what should be monitored from the database (8.1.7, SUN) during > perfomance testing. The purpose in this case, is limit the > monitoring to concern only about 10 most important ones. > > I have difficulties to make my mind to pick up the right ones, so > if You had to have made similar kind of decisions or have opinions, > please let me know. > > TIA > Jorma > - > Name: Jorma Vuorio Phone: +358-9-7180 67759 > Company: Nokia Business Infrastucture Fax:+358-9-7180 67465 > Address: P.O.Box 321, FIN-00045 NOKIA GROUP, FINLAND > Internet: [EMAIL PROTECTED]Mobile: +358-50-486 8043 > - -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5
RE: Performance monitoring
we're hiring a hosting company to manage and monitor our production apps... they handed me their spreadsheet of Oracle "things" to monitor... I finally found "wait events" on that list. Buffer cache hit ratios were high on the list and flagged as "critical" nuh uh, didn't have time to gently explain (with the two by four) that that was going to be unacceptable. But I will have loads of time tomorrow. What scares me is that this list was compiled by "experienced" DBAs. --- [EMAIL PROTECTED] wrote: > Buffer Cache Hit Ratio? > > What's that? > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/02/2002 08:03 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Performance monitoring > > > Well ... > if you need short reports, look for: > > 1. waits > 2. buffer cache hit ratio > 3. dictionary hit ratio > 4. library hit ratio > 5. latches > 6. parsing/execution ratio > 7. data file i/o > 8. shared pool memory distribution > 9. session contention > 10. session memory usage > > inka > > -Original Message- > Sent: Wednesday, October 02, 2002 7:08 AM > To: Multiple recipients of list ORACLE-L > > > Thak's Mark > > I agreed, but they have gotten an idea to get only couple > "most important" measurements from db, because they don't want > to have a huge reports with all possible statistics. Very > understandable, but as You wrote, there isn't any absolutely top ten. > > In any case, I have to do this (stupid) list, so give Your best shot, > please. > > t.Jorma > Ps. I heard, that Dave Ensor from BMC, has once presented that > kind of list? > > -Original Message- > Sent: 02 October, 2002 12:23 > To: Multiple recipients of list ORACLE-L > > > Jorma, > > Performance tuning is a complex subject. There really isn't a list > of > 10 things to watch for. Every system is different. > > I would (attempt to) summarize tuning by these five steps: > > 1.) Have a capacity/performance target in mind. If you don't know > where you're going, how will you know if you have gotten there? > > 2.) Monitor your response times as load increases. Can you achieve > your response time target at the specified load? If so, you're done, > successful test, congratulations. If not, continue to next step. > > 3.) Actively monitor what's going on in the database, while it's > happening. It's always easier to see it in real time than just > looking > at random StatsPack snapshots taken at 5 or 10 or 15 minute > intervals. > (Not that I'm saying StatsPack shouldn't be collected. I'm just > saying > don't rely on StatsPack as your only source of info about the > database.) The V$ Wait Interface is your friend. If you're not > familiar with it, go to http://www.hotsos.com/ and get Mogens > Norgaard's > paper, Introducing the V$ Wait Interface. Where is the database > spending it's time? What's the bottleneck? If you identify a few > trouble sessions, you may want to dive deeper w/ some 10046 traces at > level 8 on specific sessions. You almost certainly do NOT want to do > this instance wide. > > 4.) Once you have some indication as to what's going on in the > database, you need to see how the system is doing overall. On most > flavors of *nix, where I'm comfortable, sar (System Activity > Reporter) > is an excellent tool. Use it to determine if you have any systemwide > CPU, memory, or I/O contention. (Other OSes almost certainly have > similar utilities.) > > 5.) Address the biggest bottleneck. This is where it can't be > summarized in a simple step. You need to understand the bottleneck, > so > that you can understand how to tune it. If may be latch contention. > Depending on the latch, it could be poorly tuned SQL, or lack of bind > variables, or simple CPU capacity limits, or a whole host of things. > I/O contention? Could be anything from poorly designed and/or > configured RAID array to poorly tuned SQL, or who knows what. > Determine > the cause of the biggest bottleneck and minimize or eliminate it. > > > There you have it, Mark's Simplified Performance Tuning, in five easy > steps! ;-) > > -Mark > > > > On Wed, 2002-10-02 at 02:08, [EMAIL PROTECTED] wrote: > > Ave ! > > > > I like to hear Your opinion about the most importat > > issues, wh
RE: Performance monitoring
Hi Greg You can count on, that I have tried to tell them, what You wrote in two first lines. I have to got a hammer...:) -Original Message- Sent: 03 October, 2002 00:18 To: Multiple recipients of list ORACLE-L The ten most important performance items to monitor are your ten most important business transactions. Period, end of story. If you want to provide an intelligent response, monitor how long they take and provide a weekly report, noting any changes. Ratios and wait events are just diagnostic tools -- when business transactions become slow you use them to find where the problem is. Anyone who asks you to monitor ten internal things, such as ratios or wait events, and no more than just ten because they only want the ten most important, is simply uneducated and unexperienced, since these things are of no importantance if the system is running fast enough for the users, and besides there are way more than ten important things like this. However, they may also be very smart, since a good manager may be wise to start by getting a handle on the ten most important things. Probably, you want to be taking statspack snapshots every hour just to have a baseline, so just DO THAT and give the statspack report to the manager once a week -- and make them happy by picking out ten items you consider important and running a yellow highlighter over them. Have a cover page that compares the current value of the ten items with the same items four weeks ago -- so you are monitoring them. That's fast, easy and smart, it makes your manager happy and you'll have your eye on the ball too. But don't forget that the most important thing to monitor is reality. Your coolest move is to have the manager pull the users into a meeting and get them to identify the ten business transactions that are most important to them. Find out if any are too slow. Find out if anything is too slow. Monitor that. Tune that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring
Sort of putting on my devil's advocate hat... - "perhaps" the document is old and just hasn't been updated. A lot of the documentation that we have lying around is marked as 7.3, we just haven't had the time to update them, since were overwhelmed with real work, and can't hire additional DBAs. - some Oracle sites still believe in the myths and ratio based tuning. It can be difficult to convince a client that their long practiced tuning methodology is "obsolete". So for your specific case, perhaps they have dealt with these types of clients in the past so they "tread lightly". It will be interesting to see how the hosting company responds to your explanations. -Original Message- Sent: Wednesday, October 02, 2002 9:39 PM To: Multiple recipients of list ORACLE-L we're hiring a hosting company to manage and monitor our production apps... they handed me their spreadsheet of Oracle "things" to monitor... I finally found "wait events" on that list. Buffer cache hit ratios were high on the list and flagged as "critical" nuh uh, didn't have time to gently explain (with the two by four) that that was going to be unacceptable. But I will have loads of time tomorrow. What scares me is that this list was compiled by "experienced" DBAs. --- [EMAIL PROTECTED] wrote: > Buffer Cache Hit Ratio? > > What's that? > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/02/2002 08:03 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Performance monitoring > > > Well ... > if you need short reports, look for: > > 1. waits > 2. buffer cache hit ratio > 3. dictionary hit ratio > 4. library hit ratio > 5. latches > 6. parsing/execution ratio > 7. data file i/o > 8. shared pool memory distribution > 9. session contention > 10. session memory usage > > inka > > -Original Message- > Sent: Wednesday, October 02, 2002 7:08 AM > To: Multiple recipients of list ORACLE-L > > > Thak's Mark > > I agreed, but they have gotten an idea to get only couple > "most important" measurements from db, because they don't want > to have a huge reports with all possible statistics. Very > understandable, but as You wrote, there isn't any absolutely top ten. > > In any case, I have to do this (stupid) list, so give Your best shot, > please. > > t.Jorma > Ps. I heard, that Dave Ensor from BMC, has once presented that > kind of list? > > -Original Message- > Sent: 02 October, 2002 12:23 > To: Multiple recipients of list ORACLE-L > > > Jorma, > > Performance tuning is a complex subject. There really isn't a list > of > 10 things to watch for. Every system is different. > > I would (attempt to) summarize tuning by these five steps: > > 1.) Have a capacity/performance target in mind. If you don't know > where you're going, how will you know if you have gotten there? > > 2.) Monitor your response times as load increases. Can you achieve > your response time target at the specified load? If so, you're done, > successful test, congratulations. If not, continue to next step. > > 3.) Actively monitor what's going on in the database, while it's > happening. It's always easier to see it in real time than just > looking > at random StatsPack snapshots taken at 5 or 10 or 15 minute > intervals. > (Not that I'm saying StatsPack shouldn't be collected. I'm just > saying > don't rely on StatsPack as your only source of info about the > database.) The V$ Wait Interface is your friend. If you're not > familiar with it, go to http://www.hotsos.com/ and get Mogens > Norgaard's > paper, Introducing the V$ Wait Interface. Where is the database > spending it's time? What's the bottleneck? If you identify a few > trouble sessions, you may want to dive deeper w/ some 10046 traces at > level 8 on specific sessions. You almost certainly do NOT want to do > this instance wide. > > 4.) Once you have some indication as to what's going on in the > database, you need to see how the system is doing overall. On most > flavors of *nix, where I'm comfortable, sar (System Activity > Reporter) > is an excellent tool. Use it to determine if you have any systemwide > CPU, memory, or I/O contention. (Other OSes almost certainly have > similar utilities.) > > 5.) Address the biggest bottleneck. This is where it can't b
RE: Performance monitoring
they haven't been around as a company all that long so I doubt the doc is from 7.3 as for the methodology, I've talked to their DBAs and they are forward thinking, which is why the doc suprised me --- "Grabowy, Chris" <[EMAIL PROTECTED]> wrote: > Sort of putting on my devil's advocate hat... > > - "perhaps" the document is old and just hasn't been updated. A lot > of the documentation that we have lying around is marked as 7.3, we > just haven't had the time to update them, since were overwhelmed with > real work, and can't hire additional DBAs. > - some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". So for your specific > case, perhaps they have dealt with these types of clients in the past > so they "tread lightly". > > It will be interesting to see how the hosting company responds to > your explanations. > > -Original Message- > Sent: Wednesday, October 02, 2002 9:39 PM > To: Multiple recipients of list ORACLE-L > > > we're hiring a hosting company to manage and monitor our production > apps... they handed me their spreadsheet of Oracle "things" to > monitor... I finally found "wait events" on that list. Buffer cache > hit > ratios were high on the list and flagged as "critical" > > nuh uh, didn't have time to gently explain (with the two by four) > that > that was going to be unacceptable. But I will have loads of time > tomorrow. What scares me is that this list was compiled by > "experienced" DBAs. > > --- [EMAIL PROTECTED] wrote: > > Buffer Cache Hit Ratio? > > > > What's that? > > > > > > > > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 10/02/2002 08:03 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject:RE: Performance monitoring > > > > > > Well ... > > if you need short reports, look for: > > > > 1. waits > > 2. buffer cache hit ratio > > 3. dictionary hit ratio > > 4. library hit ratio > > 5. latches > > 6. parsing/execution ratio > > 7. data file i/o > > 8. shared pool memory distribution > > 9. session contention > > 10. session memory usage > > > > inka > > > > -Original Message- > > Sent: Wednesday, October 02, 2002 7:08 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Thak's Mark > > > > I agreed, but they have gotten an idea to get only couple > > "most important" measurements from db, because they don't want > > to have a huge reports with all possible statistics. Very > > understandable, but as You wrote, there isn't any absolutely top > ten. > > > > In any case, I have to do this (stupid) list, so give Your best > shot, > > please. > > > > t.Jorma > > Ps. I heard, that Dave Ensor from BMC, has once presented that > > kind of list? > > > > -Original Message- > > Sent: 02 October, 2002 12:23 > > To: Multiple recipients of list ORACLE-L > > > > > > Jorma, > > > > Performance tuning is a complex subject. There really isn't a list > > of > > 10 things to watch for. Every system is different. > > > > I would (attempt to) summarize tuning by these five steps: > > > > 1.) Have a capacity/performance target in mind. If you don't know > > where you're going, how will you know if you have gotten there? > > > > 2.) Monitor your response times as load increases. Can you > achieve > > your response time target at the specified load? If so, you're > done, > > successful test, congratulations. If not, continue to next step. > > > > 3.) Actively monitor what's going on in the database, while it's > > happening. It's always easier to see it in real time than just > > looking > > at random StatsPack snapshots taken at 5 or 10 or 15 minute > > intervals. > > (Not that I'm saying StatsPack shouldn't be collected. I'm just > > saying > > don't rely on StatsPack as your only source of info about the > > database.) The V$ Wait Interface is your friend. If you're not > > familiar with i
RE: Performance monitoring
> some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". In such cases, Connor's wonderful script comes very handy ;) http://www.oracledba.co.uk/tips/choose.htm I have used it to convince some old dogs - Kirti -Original Message- Sent: Thursday, October 03, 2002 12:44 PM To: Multiple recipients of list ORACLE-L they haven't been around as a company all that long so I doubt the doc is from 7.3 as for the methodology, I've talked to their DBAs and they are forward thinking, which is why the doc suprised me --- "Grabowy, Chris" <[EMAIL PROTECTED]> wrote: > Sort of putting on my devil's advocate hat... > > - "perhaps" the document is old and just hasn't been updated. A lot > of the documentation that we have lying around is marked as 7.3, we > just haven't had the time to update them, since were overwhelmed with > real work, and can't hire additional DBAs. > - some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". So for your specific > case, perhaps they have dealt with these types of clients in the past > so they "tread lightly". > > It will be interesting to see how the hosting company responds to > your explanations. > > -Original Message- > Sent: Wednesday, October 02, 2002 9:39 PM > To: Multiple recipients of list ORACLE-L > > > we're hiring a hosting company to manage and monitor our production > apps... they handed me their spreadsheet of Oracle "things" to > monitor... I finally found "wait events" on that list. Buffer cache > hit > ratios were high on the list and flagged as "critical" > > nuh uh, didn't have time to gently explain (with the two by four) > that > that was going to be unacceptable. But I will have loads of time > tomorrow. What scares me is that this list was compiled by > "experienced" DBAs. > > --- [EMAIL PROTECTED] wrote: > > Buffer Cache Hit Ratio? > > > > What's that? > > > > > > > > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 10/02/2002 08:03 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject:RE: Performance monitoring > > > > > > Well ... > > if you need short reports, look for: > > > > 1. waits > > 2. buffer cache hit ratio > > 3. dictionary hit ratio > > 4. library hit ratio > > 5. latches > > 6. parsing/execution ratio > > 7. data file i/o > > 8. shared pool memory distribution > > 9. session contention > > 10. session memory usage > > > > inka > > > > -Original Message- > > Sent: Wednesday, October 02, 2002 7:08 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Thak's Mark > > > > I agreed, but they have gotten an idea to get only couple > > "most important" measurements from db, because they don't want > > to have a huge reports with all possible statistics. Very > > understandable, but as You wrote, there isn't any absolutely top > ten. > > > > In any case, I have to do this (stupid) list, so give Your best > shot, > > please. > > > > t.Jorma > > Ps. I heard, that Dave Ensor from BMC, has once presented that > > kind of list? > > > > -Original Message- > > Sent: 02 October, 2002 12:23 > > To: Multiple recipients of list ORACLE-L > > > > > > Jorma, > > > > Performance tuning is a complex subject. There really isn't a list > > of > > 10 things to watch for. Every system is different. > > > > I would (attempt to) summarize tuning by these five steps: > > > > 1.) Have a capacity/performance target in mind. If you don't know > > where you're going, how will you know if you have gotten there? > > > > 2.) Monitor your response times as load increases. Can you > achieve > > your response time target at the specified load? If so, you're > done, > > successful test, congratulations. If not, continue to next step. > > > > 3.) Actively monitor what's going on in the database, while it's
RE: Performance monitoring
Of course, BCHR was already obsolete in 7.3. ;) "Grabowy, Chris" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/03/2002 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Performance monitoring Sort of putting on my devil's advocate hat... - "perhaps" the document is old and just hasn't been updated. A lot of the documentation that we have lying around is marked as 7.3, we just haven't had the time to update them, since were overwhelmed with real work, and can't hire additional DBAs. - some Oracle sites still believe in the myths and ratio based tuning. It can be difficult to convince a client that their long practiced tuning methodology is "obsolete". So for your specific case, perhaps they have dealt with these types of clients in the past so they "tread lightly". It will be interesting to see how the hosting company responds to your explanations. -Original Message- Sent: Wednesday, October 02, 2002 9:39 PM To: Multiple recipients of list ORACLE-L we're hiring a hosting company to manage and monitor our production apps... they handed me their spreadsheet of Oracle "things" to monitor... I finally found "wait events" on that list. Buffer cache hit ratios were high on the list and flagged as "critical" nuh uh, didn't have time to gently explain (with the two by four) that that was going to be unacceptable. But I will have loads of time tomorrow. What scares me is that this list was compiled by "experienced" DBAs. --- [EMAIL PROTECTED] wrote: > Buffer Cache Hit Ratio? > > What's that? > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/02/2002 08:03 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Performance monitoring > > > Well ... > if you need short reports, look for: > > 1. waits > 2. buffer cache hit ratio > 3. dictionary hit ratio > 4. library hit ratio > 5. latches > 6. parsing/execution ratio > 7. data file i/o > 8. shared pool memory distribution > 9. session contention > 10. session memory usage > > inka > > -Original Message- > Sent: Wednesday, October 02, 2002 7:08 AM > To: Multiple recipients of list ORACLE-L > > > Thak's Mark > > I agreed, but they have gotten an idea to get only couple > "most important" measurements from db, because they don't want > to have a huge reports with all possible statistics. Very > understandable, but as You wrote, there isn't any absolutely top ten. > > In any case, I have to do this (stupid) list, so give Your best shot, > please. > > t.Jorma > Ps. I heard, that Dave Ensor from BMC, has once presented that > kind of list? > > -Original Message- > Sent: 02 October, 2002 12:23 > To: Multiple recipients of list ORACLE-L > > > Jorma, > > Performance tuning is a complex subject. There really isn't a list > of > 10 things to watch for. Every system is different. > > I would (attempt to) summarize tuning by these five steps: > > 1.) Have a capacity/performance target in mind. If you don't know > where you're going, how will you know if you have gotten there? > > 2.) Monitor your response times as load increases. Can you achieve > your response time target at the specified load? If so, you're done, > successful test, congratulations. If not, continue to next step. > > 3.) Actively monitor what's going on in the database, while it's > happening. It's always easier to see it in real time than just > looking > at random StatsPack snapshots taken at 5 or 10 or 15 minute > intervals. > (Not that I'm saying StatsPack shouldn't be collected. I'm just > saying > don't rely on StatsPack as your only source of info about the > database.) The V$ Wait Interface is your friend. If you're not > familiar with it, go to http://www.hotsos.com/ and get Mogens > Norgaard's > paper, Introducing the V$ Wait Interface. Where is the database > spending it's time? What's the bottleneck? If you identify a few > trouble sessions, you may want to dive deeper w/ some 10046 traces at > level 8 on specific sessions. You almost certainly do NOT want to do > this instance wide. > > 4.) Once you have some indication as to what's going on in the > database, you need to see how the system is doing overall. On most >
RE: Performance monitoring
Of course!! I just laughed when he published that script, and didn't think anything more of it. Now I can use it to "drive home" my point. Excellent. Thanks Kirti. -Original Message- Sent: Thursday, October 03, 2002 4:03 PM To: Multiple recipients of list ORACLE-L > some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". In such cases, Connor's wonderful script comes very handy ;) http://www.oracledba.co.uk/tips/choose.htm I have used it to convince some old dogs - Kirti -Original Message- Sent: Thursday, October 03, 2002 12:44 PM To: Multiple recipients of list ORACLE-L they haven't been around as a company all that long so I doubt the doc is from 7.3 as for the methodology, I've talked to their DBAs and they are forward thinking, which is why the doc suprised me --- "Grabowy, Chris" <[EMAIL PROTECTED]> wrote: > Sort of putting on my devil's advocate hat... > > - "perhaps" the document is old and just hasn't been updated. A lot > of the documentation that we have lying around is marked as 7.3, we > just haven't had the time to update them, since were overwhelmed with > real work, and can't hire additional DBAs. > - some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". So for your specific > case, perhaps they have dealt with these types of clients in the past > so they "tread lightly". > > It will be interesting to see how the hosting company responds to > your explanations. > > -Original Message- > Sent: Wednesday, October 02, 2002 9:39 PM > To: Multiple recipients of list ORACLE-L > > > we're hiring a hosting company to manage and monitor our production > apps... they handed me their spreadsheet of Oracle "things" to > monitor... I finally found "wait events" on that list. Buffer cache > hit > ratios were high on the list and flagged as "critical" > > nuh uh, didn't have time to gently explain (with the two by four) > that > that was going to be unacceptable. But I will have loads of time > tomorrow. What scares me is that this list was compiled by > "experienced" DBAs. > > --- [EMAIL PROTECTED] wrote: > > Buffer Cache Hit Ratio? > > > > What's that? > > > > > > > > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 10/02/2002 08:03 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject:RE: Performance monitoring > > > > > > Well ... > > if you need short reports, look for: > > > > 1. waits > > 2. buffer cache hit ratio > > 3. dictionary hit ratio > > 4. library hit ratio > > 5. latches > > 6. parsing/execution ratio > > 7. data file i/o > > 8. shared pool memory distribution > > 9. session contention > > 10. session memory usage > > > > inka > > > > -Original Message- > > Sent: Wednesday, October 02, 2002 7:08 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Thak's Mark > > > > I agreed, but they have gotten an idea to get only couple > > "most important" measurements from db, because they don't want > > to have a huge reports with all possible statistics. Very > > understandable, but as You wrote, there isn't any absolutely top > ten. > > > > In any case, I have to do this (stupid) list, so give Your best > shot, > > please. > > > > t.Jorma > > Ps. I heard, that Dave Ensor from BMC, has once presented that > > kind of list? > > > > -Original Message- > > Sent: 02 October, 2002 12:23 > > To: Multiple recipients of list ORACLE-L > > > > > > Jorma, > > > > Performance tuning is a complex subject. There really isn't a list > > of > > 10 things to watch for. Every system is different. > > > > I would (attempt to) summarize tuning by these five steps: > > > > 1.) Have a capacity/performance target in mind. If you don't know > > where you're going, how will you know if you have gotten there? > > > > 2.) Monitor your response times as load
RE: Performance monitoring
BHR has taken quite a beating. I am sure the point everyone's trying to make is that a 99% BHR does not indicate a well tuned database, and a 50% BHR does not indicate a poorly peforming database. A low BHR does not mean I need to increase db_buffers as was believed and practiced earlier. Point well taken and understood. But, it "CAN" be an indicator that something out of the ordinary is happening in my well tuned database. I emphasize on "CAN". Why have the PIO's increased so suddenly? Say, if the BHR is consistently around 80, and suddenly, it drops to 70, or rises to 95, I want to know why? What you infer from the BHR is what counts. There would definitely be other ways of finding this out. Or are there? Regards Raj "Grabowy, Chris" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Performance monitoring Sent by: root@fatcity. com October 03, 2002 04:53 PM Please respond to ORACLE-L Of course!! I just laughed when he published that script, and didn't think anything more of it. Now I can use it to "drive home" my point. Excellent. Thanks Kirti. -Original Message- Sent: Thursday, October 03, 2002 4:03 PM To: Multiple recipients of list ORACLE-L > some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". In such cases, Connor's wonderful script comes very handy ;) http://www.oracledba.co.uk/tips/choose.htm I have used it to convince some old dogs - Kirti -Original Message- Sent: Thursday, October 03, 2002 12:44 PM To: Multiple recipients of list ORACLE-L they haven't been around as a company all that long so I doubt the doc is from 7.3 as for the methodology, I've talked to their DBAs and they are forward thinking, which is why the doc suprised me --- "Grabowy, Chris" <[EMAIL PROTECTED]> wrote: > Sort of putting on my devil's advocate hat... > > - "perhaps" the document is old and just hasn't been updated. A lot > of the documentation that we have lying around is marked as 7.3, we > just haven't had the time to update them, since were overwhelmed with > real work, and can't hire additional DBAs. > - some Oracle sites still believe in the myths and ratio based > tuning. It can be difficult to convince a client that their long > practiced tuning methodology is "obsolete". So for your specific > case, perhaps they have dealt with these types of clients in the past > so they "tread lightly". > > It will be interesting to see how the hosting company responds to > your explanations. > > -Original Message- > Sent: Wednesday, October 02, 2002 9:39 PM > To: Multiple recipients of list ORACLE-L > > > we're hiring a hosting company to manage and monitor our production > apps... they handed me their spreadsheet of Oracle "things" to > monitor... I finally found "wait events" on that list. Buffer cache > hit > ratios were high on the list and flagged as "critical" > > nuh uh, didn't have t
Re: Performance monitoring
On Thursday 03 October 2002 14:49, [EMAIL PROTECTED] wrote: > "CAN". Why have the PIO's increased so suddenly? Say, if the BHR is > consistently around 80, and suddenly, it drops to 70, or rises to 95, I > want to know why? What you infer from the BHR is what counts. There would > definitely be other ways of finding this out. Or are there? Track them with statspack. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance monitoring - mostly: further derailment
Thank you Rachel. Jared, here is what I meant: select 100 * (a.value + b.value - c.value) / (a.value + b.value) from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d where a.statistic# = 37 and /* db block gets */ b.statistic# = 38 and /* consistent gets */ c.statistic# = 39 and /* physical gets */ d.statistic# = 40 /* physical writes */ ; It had been for years "the most important thing to measure" - if the number was over 80%, everyone was happy. If I remember correctly, Oracle Education Services taught it at the advanced level of dba courses. If anyone remembers the beginning of this tread, the issue at hand is not how to measure performance, but what are the 10 metrics one can measure. After the first note or so, there was still a plea for "the list". To add to a discussion, which derailed from the original plea for "the list": Good or bad performance has very little to do with ratios, numbers, importance of transactions (by the way: from whose point of view?) Well performing system is such, that allows ALL supported by it business functions to achieve their objectives for the less possible amount of money. If a program, which produces monthly report for shareholders is poorly written from the art of programming point of view, creates a report formatted as per specifications, including colours, ready to be delivered on time - the system is performing well. Even if that program runs 5 hours except 5 minutes, as long as it is not in a way of anything else. Computers are expensive, useless gadgets when they are not working. In addition, managers are neither educated in technical nuances nor need to be. Somebody, maybe another dba, or an editor of an in-flight magazine has written somewhere that there are ten Oracle database parameters that should be measured. So, to keep all parties happy, one should produce "the list", meet with users to find out what is not performing up to their expectations. When this is known, use both, common sense and "the list" to find out what and how can be changed. If anything, because many times the only acceptable to the business solution is: "learn to like it". Amen. grandma inka -Original Message- Sent: Wednesday, October 02, 2002 9:39 PM To: Multiple recipients of list ORACLE-L we're hiring a hosting company to manage and monitor our production apps... they handed me their spreadsheet of Oracle "things" to monitor... I finally found "wait events" on that list. Buffer cache hit ratios were high on the list and flagged as "critical" nuh uh, didn't have time to gently explain (with the two by four) that that was going to be unacceptable. But I will have loads of time tomorrow. What scares me is that this list was compiled by "experienced" DBAs. --- [EMAIL PROTECTED] wrote: > Buffer Cache Hit Ratio? > > What's that? > > > > > > > "Inka Bezdziecka" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/02/2002 08:03 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Performance monitoring > > > Well ... > if you need short reports, look for: > > 1. waits > 2. buffer cache hit ratio > 3. dictionary hit ratio > 4. library hit ratio > 5. latches > 6. parsing/execution ratio > 7. data file i/o > 8. shared pool memory distribution > 9. session contention > 10. session memory usage > > inka > > -Original Message- > Sent: Wednesday, October 02, 2002 7:08 AM > To: Multiple recipients of list ORACLE-L > > > Thak's Mark > > I agreed, but they have gotten an idea to get only couple > "most important" measurements from db, because they don't want > to have a huge reports with all possible statistics. Very > understandable, but as You wrote, there isn't any absolutely top ten. > > In any case, I have to do this (stupid) list, so give Your best shot, > please. > > t.Jorma > Ps. I heard, that Dave Ensor from BMC, has once presented that > kind of list? > > -Original Message- > Sent: 02 October, 2002 12:23 > To: Multiple recipients of list ORACLE-L > > > Jorma, > > Performance tuning is a complex subject. There really isn't a list > of > 10 things to watch for. Every system is different. > > I would (attempt to) summarize tuning by these five steps: > > 1.) Have a capacity/performance target in mind. If you don't know > where you're going, how will you know if you have gotten there? > > 2.) Monitor your response times as load increases. Can y