Re: FW: Disk capacity planning
Mladen, I agree you can measure how many IOs are being done and how many a disk sub- system, such as those provided by EMC, can perform and still give good performance. What I meant is that it is hard and some would say impossible to estimate how many IOs per sec a new application will do. A combination of paper calculations, testing, experience and looking at comparable systems will help to provide a good estimate. Cheers, Chris Quoting Mladen Gogala [EMAIL PROTECTED]: Oh, but it is done, you only need to ask. EMC routinely measures how many I/Os per second can they perform and they even have tools to measure it. Speaking of monitoring I/O, there used to be an old OS, which is mostly dead today and it used to have command monitor io/item=queue which would show length of the I/O queues per device, which was extremely useful, because you could quickly find out which devices are hot and which are not. On 2004.01.20 04:19, [EMAIL PROTECTED] wrote: Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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
Re: FW: Disk capacity planning
See the Ratio Modeling paper at Orapub.com It is a quick and dirty method for capacity planning. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 01:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: FW: Disk capacity planning Mladen, I agree you can measure how many IOs are being done and how many a disk sub- system, such as those provided by EMC, can perform and still give good performance. What I meant is that it is hard and some would say impossible to estimate how many IOs per sec a new application will do. A combination of paper calculations, testing, experience and looking at comparable systems will help to provide a good estimate. Cheers, Chris Quoting Mladen Gogala [EMAIL PROTECTED]: Oh, but it is done, you only need to ask. EMC routinely measures how many I/Os per second can they perform and they even have tools to measure it. Speaking of monitoring I/O, there used to be an old OS, which is mostly dead today and it used to have command monitor io/item=queue which would show length of the I/O queues per device, which was extremely useful, because you could quickly find out which devices are hot and which are not. On 2004.01.20 04:19, [EMAIL PROTECTED] wrote: Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
Re: FW: Disk capacity planning
When I worked for Oxford, there was a way to force the application to either perform or die. The OLTP database was enforcing profiles and there was limit of 1500 logical reads per call, because it was estimated that our typical OLTP application never performs more then that. If application was unable to achieve that performance, it wasn't allowed to the production OLTP system. That way, the performance could be guaranteed. On 01/21/2004 01:19:26 PM, [EMAIL PROTECTED] wrote: See the Ratio Modeling paper at Orapub.com It is a quick and dirty method for capacity planning. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 01:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: FW: Disk capacity planning Mladen, I agree you can measure how many IOs are being done and how many a disk sub- system, such as those provided by EMC, can perform and still give good performance. What I meant is that it is hard and some would say impossible to estimate how many IOs per sec a new application will do. A combination of paper calculations, testing, experience and looking at comparable systems will help to provide a good estimate. Cheers, Chris Quoting Mladen Gogala [EMAIL PROTECTED]: Oh, but it is done, you only need to ask. EMC routinely measures how many I/Os per second can they perform and they even have tools to measure it. Speaking of monitoring I/O, there used to be an old OS, which is mostly dead today and it used to have command monitor io/item=queue which would show length of the I/O queues per device, which was extremely useful, because you could quickly find out which devices are hot and which are not. On 2004.01.20 04:19, [EMAIL PROTECTED] wrote: Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle
Re: FW: Disk capacity planning
Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: FW: Disk capacity planning
Hi The bad news is that I don't believe that calculating IO/Sec *can* be done for a *new* system. At least I'd like to see how it is done. I'm willing to bet that any formula for doing it will include (x%) for 'overhead', which actually means 'stuff I don't know about'. Of course if the *new* system is a replacement for an old system with known IO requirements and the workload is similar (or predictably different) then obviously a calculation/lower bound could be set. (of course if one has the exact data set that you will use, and the IO required by each and every sql statement in use, and the exact number of clients and the exact machine and software configuration that will be used for always then one can measure your IO requirement. I have never seen such a situation.) Actually however I think that this bad news is rather mitigated by the fact that I don't believe that capacity can be calculated ahead of time for a *new* system either. It will entirely depend on the take up of the application and any changes to the design/usage post go-live. I think that that leaves us in a relatively good position, namely that we can estimate values for B,P etc based on our skill, judgement (and budget :( ), and that because none of the figures are *hard* figures it ought to be possible to negotiate *sensible* disk purchases. They key is to take into account all the demands on the system (as Cary says). I'm afraid that for *new* systems though getting into formulae for *calculating* requirements is likely to give false assurances. Time to brush up on negotiating skills (and to find how how to effectivey bribe your sys admin and/or budget holders). Yours unscientifically Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 20 January 2004 09:19 To: Multiple recipients of list ORACLE-L Subject: Re: FW: Disk capacity planning Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining
Re: FW: Disk capacity planning
Oh, but it is done, you only need to ask. EMC routinely measures how many I/Os per second can they perform and they even have tools to measure it. Speaking of monitoring I/O, there used to be an old OS, which is mostly dead today and it used to have command monitor io/item=queue which would show length of the I/O queues per device, which was extremely useful, because you could quickly find out which devices are hot and which are not. On 2004.01.20 04:19, [EMAIL PROTECTED] wrote: Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
RE: FW: Disk capacity planning
I agree wholeheartedly. This is why I think that anyone who attempts to size a system with formulas alone (that is, without testing) is almost 100% certain to either overspend miserably or downright fail. There are two things that are really important about testing. One is that it shows you how much hardware you'll need, because you can use real operational measurements to count things like IOps, instead of counting on smart people to infer operational statistics accurately while looking at paper. Just as importantly, it shows you how much wasted work your db/app/users are doing. This gives you the chance to eliminate that waste and go forward on a less expensive infrastructure than you might have imagined if you had studied it only on paper. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Niall Litchfield Sent: Tuesday, January 20, 2004 4:15 AM To: Multiple recipients of list ORACLE-L Hi The bad news is that I don't believe that calculating IO/Sec *can* be done for a *new* system. At least I'd like to see how it is done. I'm willing to bet that any formula for doing it will include (x%) for 'overhead', which actually means 'stuff I don't know about'. Of course if the *new* system is a replacement for an old system with known IO requirements and the workload is similar (or predictably different) then obviously a calculation/lower bound could be set. (of course if one has the exact data set that you will use, and the IO required by each and every sql statement in use, and the exact number of clients and the exact machine and software configuration that will be used for always then one can measure your IO requirement. I have never seen such a situation.) Actually however I think that this bad news is rather mitigated by the fact that I don't believe that capacity can be calculated ahead of time for a *new* system either. It will entirely depend on the take up of the application and any changes to the design/usage post go-live. I think that that leaves us in a relatively good position, namely that we can estimate values for B,P etc based on our skill, judgement (and budget :( ), and that because none of the figures are *hard* figures it ought to be possible to negotiate *sensible* disk purchases. They key is to take into account all the demands on the system (as Cary says). I'm afraid that for *new* systems though getting into formulae for *calculating* requirements is likely to give false assurances. Time to brush up on negotiating skills (and to find how how to effectivey bribe your sys admin and/or budget holders). Yours unscientifically Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 20 January 2004 09:19 To: Multiple recipients of list ORACLE-L Subject: Re: FW: Disk capacity planning Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because
RE: FW: Disk capacity planning
Chris, Thanks. When people do what you say, it's kind of like what would have happened if NASA had used the following assumption throughout the Apollo project: Assume adequate quantities of breathable air... It would have made the planning phase much simpler, but it would have been a touch more difficult on the users once the journey began. :) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 3:19 AM To: Multiple recipients of list ORACLE-L Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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
RE: FW: Disk capacity planning
I found myself working with some larger databases in the 500-800 GB range that also spawn into multiple test databases. I take a df -k or bdf and bring that into excel. Then I take a query on all autoextend and break that out by disk. then I put that all together and tell what's left on disks. This allows me to quickly match test to prod by using autoextend and then run this to find out just which disks are WAY over kill and which ones still have room left. I may find a disk that has been promised 40 gig, but only had 5 gig on it, but then find 2-5 volumes that have 5-30 gig on them, and I can then add datafiles where they are needed and pull the extra autoextend from the full disk. The advantage is I'm usually quite okay while they are working and come back around and make it more perfect before they get me into trouble. -- Michael Kline Midlothian, VA 23112 804-744-1545 Chris, Thanks. When people do what you say, it's kind of like what would have happened if NASA had used the following assumption throughout the Apollo project: Assume adequate quantities of breathable air... It would have made the planning phase much simpler, but it would have been a touch more difficult on the users once the journey began. :) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 3:19 AM To: Multiple recipients of list ORACLE-L Cary, Good answer. The problem is most people concentrate on bytes because it's relatively easy and everyone understands it. IOs per sec is much harder to calculate for a new system and hence it's not normally done. Cheers, Chris Dunscombe Quoting Cary Millsap [EMAIL PROTECTED]: I don't think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your system's peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than you'll really, truly need. The way I'd recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements.) Roughly speaking, the number of disks you'll need to buy is max(B, P, A, .). It's more complicated than that because you'll need to segment your total drive set into sensibly-sized arrays, you'll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance http://www.hotsos.com/training/PD101.html Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 : March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Chris Dunscombe [EMAIL PROTECTED] - Everyone should
RE: FW: Disk capacity planning
--- Niall Litchfield [EMAIL PROTECTED] wrote: Hi The bad news is that I don't believe that calculating IO/Sec *can* be done for a *new* system. At least I'd like to see how it is done. I'm willing to bet that any formula for doing it will include (x%) for 'overhead', which actually means 'stuff I don't know about'. Of course if the *new* system is a replacement for an old system with known IO requirements and the workload is similar (or predictably different) then obviously a calculation/lower bound could be set. (of course if one has the exact data set that you will use, and the IO required by each and every sql statement in use, and the exact number of clients and the exact machine and software configuration that will be used for always then one can measure your IO requirement. I have never seen such a situation.) Actually however I think that this bad news is rather mitigated by the fact that I don't believe that capacity can be calculated ahead of time for a *new* system either. It will entirely depend on the take up of the application and any changes to the design/usage post go-live. I think that that leaves us in a relatively good position, namely that we can estimate values for B,P etc based on our skill, judgement (and budget :( ), and that because none of the figures are *hard* figures it ought to be possible to negotiate *sensible* disk purchases. They key is to take into account all the demands on the system (as Cary says). I'm afraid that for *new* systems though getting into formulae for *calculating* requirements is likely to give false assurances. Time to brush up on negotiating skills (and to find how how to effectivey bribe your sys admin and/or budget holders). Yours unscientifically Niall this is where the z-space kicks in (discreteness). if you're using direct attached storage, the cabinets typically hold 14 drives spread across a split backplane per enclosure. 10 = PCI-X slots open 0 4 Server PCI-X buses n dual channel external SCSI RAID controllers n dual channel external SCSI RAID enclosures n*14 = drives 0 so we will attempt to spread the IO across the PCI-X bus channels, across RAID controller channels, across drives. so pick the following: 1 cabinet, 1 dual channel SCSI RAID controller, 14 drives 2 cabinets, 2 dual channel SCSI RAID controllers, 28 drives 3 cabinets, 3 dual channel SCSI RAID controllers, 42 drives (we have a winner :) ). 4 cabinets, 4 dual channel SCSI RAID controllers, 56 drives
FW: Disk capacity planning
I dont think this one made it through on my first attempt. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance Diagnosis101: 1/27 Atlanta - SQL Optimization101: 2/16 Dallas - Hotsos Symposium 2004: March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 5:54 PM To: '[EMAIL PROTECTED]' Subject: RE: Disk capacity planning Counting bytes is far, far, FAR less important than counting I/O-per-second (IOps) requirements and making sure that you have enough total capacity to handle your systems peak I/O loads. Counting bytes is important too, but what many people find is that the byte-counting exercise will result in the sub-verdict of needing far fewer disk drives than youll really, truly need. The way Id recommend structuring your project is to evaluate the following: - How many bytes will you need to store your data? How many disks is that? Call the answer B. - How many disks will you need to meet your IOps requirements? Call the answer P. - How many disks will you need to meet your availability requirements? Call the answer A. - (Consider other attributes as necessary, like perhaps I/O throughput requirements) Roughly speaking, the number of disks youll need to buy is max(B, P, A, ). Its more complicated than that because youll need to segment your total drive set into sensibly-sized arrays, youll be able to buy some disks now then some later, and so on, but this is the general gist. The important thing is to have enough hardware to meet *all* of the constraints your business will place upon your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance Diagnosis101: 1/27 Atlanta - SQL Optimization101: 2/16 Dallas - Hotsos Symposium 2004: March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:29 AM To: Multiple recipients of list ORACLE-L Subject: Disk capacity planning Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel
Re: Disk capacity planning
Hi! I don't remember any documents other than Oracle documentation by heart, but would like to make one point here: Disk capacity planning is not only predicting how many Mega/Giga/Terabytes you'll need in certain point of time, planning also includes requires IOPS prediction, also IO throughput requirements for your SAN or storagearray and so on. Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 8:29 AM Subject: Disk capacity planning Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel
resend: Re: Disk capacity planning
is the list smart enough to block my posts made after 2 am? Pd --- Paul Drake [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Hi Rhojel, http://www.baarf.com not organized, just a brain dump. use a UPS and configure it to properly shutdown the instance should it be running out of power. use battery backed-up cache (NVRAM) on your host-based RAID controllers. replace or recharge the battery as perscribed in the manual (or you'll be testing media recovery in an unscheduled fashion). enable write-back caching on your host-based RAID controller (if you have satisfied the above conditions). Base the number of disks on the number of IOPS (independent operations per second) the storage subsystem must handle in steady state, and in peak usage, and on the response time that exists in the service level agreement with your users. It will be very likely that you will not be constrained on space, but on controller channels and physical hard drives (e.g. having 24 x 36 GB hard drives, but only having an 18 GB database - I am not kidding). Spread the disks out across controllers and controller channels such that controller bandwidth is not the limiting factor. Spread controllers across PCI-X bus channels. use dedicated drives for online redo logs. At least have 2 redo log members per group on separate controllers. Provided that one member per group is still accessible, you can take a hit in terms of losing a volume and your instance(s) will still be up. Don't use more than 50% of the space of your RAID volumes for live files. Don't even create filesystems for live files on the remaining 50%, or use them for staging backup sets or for trial recoveries. This will keep your mean seek time down, reducing latency. better yet, don't format them at all. Beware of Network Admins chanting iSCSI that want to store their files on your unused space. If need be, create bogus tablespaces with say 1 GB datafiles to consume the excess space. drop them. when its time to add a datafile, re-use the bogus datafiles. in the meantime, the storage reports from the OS won't turn up loads of free space. If your SysAdmins are crafty and will see through this ploy, create raw volumes on the unused space and label them in a very cryptic fashion, or say that they are for when you are going to partition the large fact tables. use dedicated drives for your archived redo logs. use a stripe size that is a multiple of your operating system max IO size. set your db file multiblock read count accordingly. don't set it too high, as the CBO will think its time to chow down and grab blocks by the 32 pack instead of using that index that the developer intended. have space for cloned databases for testing new application updates, new oracle server software releases, on disk backup sets, uncompressed archived redo logs since your last full backup, logical dumps, copies of binaries and patch sets. All of these can help reduce mean time to recovery in the event that you need to perform media recovery or re-install the database server software. if you intend to use dbms_flashback or select AS OF ... use dedicated drives for your UNDO tablespace. use tape for getting backup sets offsite. recover from local storage. use dedicated drives for your TEMPORARY tablespace's temp files, unless you have so much memory that you don't sort to disk. Remember that global temporary tables and hash joins can write out to TEMP also. segregate files on RAID volumes based upon access methods. If you want an agressive read-ahead algorithm to be used for full table scans, don't store index data files there that are only accessed a block at a time. stripe and mirror everything if you can. even numbers are best, multiples of 2 work well with Oracle block sizes (4 or 8 disk RAID 10 volume). by now, 24 drives looks about halfway there, doesn't it? My point is, that if you have enough drives to keep your CPUs well fed, you likely won't have any space concerns. it is customary to include your server OS and Oracle version. as this is a storage question, you might want to include the storage system manufacturer if you are not going with direct attached storage. Based upon someone's posting about his monday morning having 20 database instances crashed due to a sysadmin pulling a cable, use redundant data paths if possible. If you are connecting via Fibrechannel
Re: Disk capacity planning
Hi Rhojel, You've had some responses that go beyond your forecasting question. I'll go the simple-minded route here. Maybe there is some canned software out there that will do it but it seems vendors stay away from forecasting future growth. Maybe this is to avoid being held accountable for faulty results (probably shouldn't trust my answer either...). Anyway, I was taught that the first rule of forecasting is that the forecast will be wrong. This doesn't mean you shouldn't forecast the growth of your database. It just means that you need to follow your forecast on a regular basis and note variances (ie: significant changes to growth rates) and ask questions. A spike in growth can be a development boo-boo, new functionality, growth in business, or something else. A simple approach is to track (at least monthly...depending on your comfort zone) the physical size of tablespaces and actual data and calculate net changes in size between your forecasting periods (eg: Month). Summarize this and calculate the average rate over time (eg: Year). Given the average rate and the last rate, you can forecast your growth using both rates over some horizon (1 year? 2 years?) and compare it to your available diskspace. You should be able to identify when you'll run out...but don't assume this will be correct. Also track significant events that may have driven growth. And if there is a major difference between the average growth rate and the last growth rate...it's analysis time. There are fancier forecasting methods like exponential smoothing and so on but the simple approach might get you started. But you need to keep testing results to see if your situation has changed. The above would be a fairly simple spreadsheet. Kip Bryant |Hi everyone! |Can anybody point me to any good documentation regarding disk capacity |planning? Sharing your experience or approach will also give me so much |help. I'd like to know other people's approach on forecasting the growth |of their databases particularly on determining the (growth) rate of disk |space usage and on deciding when to add and how many disk to add on an |Oracle server. |Thanks in advance. |Best Regards, |Rhojel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Disk capacity planning
Thanks for the time answering my question guys... :-) Tanel, I've just inherited the database that I'm handling right now and I was not part of the real capacity planning. I really lack experience on this part of the job and I would appreciate your thoughts on any good documentation regarding IOPS prediction and determining IO throughput requirements. Thanks again guys and best regards, Rhojel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2004 01:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Rhojel Echano/Manila/PH/SGS) Subject:Re: Disk capacity planning Hi Rhojel, You've had some responses that go beyond your forecasting question. I'll go the simple-minded route here. Maybe there is some canned software out there that will do it but it seems vendors stay away from forecasting future growth. Maybe this is to avoid being held accountable for faulty results (probably shouldn't trust my answer either...). Anyway, I was taught that the first rule of forecasting is that the forecast will be wrong. This doesn't mean you shouldn't forecast the growth of your database. It just means that you need to follow your forecast on a regular basis and note variances (ie: significant changes to growth rates) and ask questions. A spike in growth can be a development boo-boo, new functionality, growth in business, or something else. A simple approach is to track (at least monthly...depending on your comfort zone) the physical size of tablespaces and actual data and calculate net changes in size between your forecasting periods (eg: Month). Summarize this and calculate the average rate over time (eg: Year). Given the average rate and the last rate, you can forecast your growth using both rates over some horizon (1 year? 2 years?) and compare it to your available diskspace. You should be able to identify when you'll run out...but don't assume this will be correct. Also track significant events that may have driven growth. And if there is a major difference between the average growth rate and the last growth rate...it's analysis time. There are fancier forecasting methods like exponential smoothing and so on but the simple approach might get you started. But you need to keep testing results to see if your situation has changed. The above would be a fairly simple spreadsheet. Kip Bryant |Hi everyone! |Can anybody point me to any good documentation regarding disk capacity |planning? Sharing your experience or approach will also give me so much |help. I'd like to know other people's approach on forecasting the growth |of their databases particularly on determining the (growth) rate of disk |space usage and on deciding when to add and how many disk to add on an |Oracle server. |Thanks in advance. |Best Regards, |Rhojel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
Disk capacity planning
Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel
RE: CPU Capacity Planning
Thanks a lot, Cary. Yes, this sentence on p.248: As long the execution of each business function can be expressed in terms of an LIO count, you can translate the queueing model's output in terms of business function response time and throughput was the one I marked as something to go back to, as I didn't really understand it. Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: Boris, I think I covered this in my response to Ryan. It was the two stages part. Note that you can avoid even using queueing theory at all if you just make sure that utilization stays to the left of the knee in the performance curve for each resource on the system. You can learn the location of the knee for a given number of parallel service channels (for example, CPUs in your case) on Table 9-3 on p260 of Optimizing Oracle Performance. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 12:39 PM To: Multiple recipients of list ORACLE-L Thanks for the clarifications, Cary. With regards to a hardware sizing - how do LIOs fit into queueing theory? Let's say I can come up with something like: #CPUs required = Sum( LIOs(Bus.Tx i)) / (10,000*clock rate/100) where i={Bus.Tx 1..n} [on a projected box that haven't been bought yet, it might be a little difficult to estimate the denominator, ... and on the existing one I guess I have to get hold of Jonathan's paper to learn how this can be done] ..but in any event for forecasting purposes, how queueing effect might be taken into account here? Let's say I measured Sum( LIOs(...)) for a 50 users in a unit testing environment and I am told that production would be 10 times more than that, what do I do? Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, you'll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but it's really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] You'll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. It's because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesn't trigger any new serialization issues that weren't visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1
RE: CPU Capacity Planning
Boris, If you mean that some people on your system execute Bus.Tx1, some others execute Bus.Tx2, and some others (maybe with some overlap) execute Bus.Tx3, then my answer to your question is: No, I would strongly encourage you *not* to do this! It was exercises like this that first led me to discover the fact that there's no such thing as a system in the sense that most people use the term (that is, as a big mishmash of different transactions, in which averages have any real meaning). Combining your three CDFs will hurt you in the way described in Why understanding distribution is important on pp238-239 of the Optimizing Oracle Performance book. Here's another example: Imagine the following system... avg. avg. runs/day sec/run who uses it Tx1 10,0001 Group A Tx21,000 10 Group B Tx3 100 100 Group C So, what's this system's average response time? A naïve mathematician might think it's the weighted average of all the response times: (1*1 + 1000*10 + 100*100) / (1+1000+100) = 2.7 sec. But what use is this figure? Nobody's response time is ever really 2.7 sec. footnoteI say ever here because it's of course possible that a program whose avg. sec/run is 1 (or even 10) will occasionally have a true response time of 2.7./footnote If you're *anybody* actually using the system, the number 2.7 sec/run is just stupid! The 2.7s figure is especially ludicrous if you're a member of Group B or C, because your average response time is either really 3.7x that number (B) or 37x that number (C)! The mathematical explanation for the stupid-looking-ness is that, no matter what you're doing, this 2.7 number is an average influenced by stuff that you're *not* doing. There is no such thing as an average user (any more than there's an American family with 2.3 children); in this example, there are only members of Groups A, B, and C. What if you're a member of two groups simultaneously (e.g., you run different transaction types in the same day)? It's the same problem, because your expectation of, for example, Tx1 response time is completely different from your expectation of Tx3 response time. Clumping response times from Tx1 and Tx2 into one average makes no sense even then. Expecting *anything* you do to take 2.7 sec/run is going to leave you unfulfilled. The number 2.7 has no useful meaning here. Certainly, if you have some kind of service level agreement (SLA) wired to the number 2.7 sec/run in this case, then I would say you have a SLA that's worse than having no SLA at all. Maybe an easier analogy is this... How would you respond to the question, Using the global air transportation system, how long does it take to fly someplace? One way to answer would be to compute a weighted average of all flight durations recorded by IAPA for the past 12 months. Imagine that the worldwide average is 2.7 hours. How much good does this do someone who really wants to know how long it takes to get from Chicago to Sydney? How 'bout Chicago to Detroit? No, it's fundamentally the wrong way to respond. The right way to respond to How long does it take to fly someplace? begins with asking the question From where to where? The problem with averages comes when the statistics you're trying to average don't come from a single well-behaved distribution. See pp236-254 of Optimizing Oracle Performance for a more complete explanation of what I mean by this. Virtually every computer system used by participants on this list has two or more transaction types whose performance characteristics do not come from a single well-behaved statistical distribution. On these systems, it is impossible to come up with a single number (an average) that will present a useful description of your system. And I mean impossible in the strictest, most carefully considered sense. Bottom line: Do not attempt to combine your Bus.Tx# data in the way you describe. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Saturday, December 06, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Let's say I have 3 business transactions (consisting of numerous Oracle transactions each) and I know total service time for each (from c readings off sql traces for the length of the bus.tx). Doing queuing theory exercise I can also get CDF(r max) for each. Let's say Bus.Tx1 - CPU time=5s CDF(r)=97% Bus.Tx2 - CPU time=8s CDF(r)=95% Bus.Tx3 - CPU time=10s CDF(r)=90% How can I combine these three together and make any conclusions as to what the overall CDF(r) would be for the whole system consisting of the above 3 business transactions? Is this doable? Thanks, Boris Dali.
RE: CPU Capacity Planning
Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other concurrent activities on the system. Is there a way to account for this if capacity planning is done at the individual bus.tx level? Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: Boris, If you mean that some people on your system execute Bus.Tx1, some others execute Bus.Tx2, and some others (maybe with some overlap) execute Bus.Tx3, then my answer to your question is: No, I would strongly encourage you *not* to do this! It was exercises like this that first led me to discover the fact that there's no such thing as a system in the sense that most people use the term (that is, as a big mishmash of different transactions, in which averages have any real meaning). Combining your three CDFs will hurt you in the way described in Why understanding distribution is important on pp238-239 of the Optimizing Oracle Performance book. Here's another example: Imagine the following system... avg. avg. runs/day sec/run who uses it Tx1 10,0001 Group A Tx21,000 10 Group B Tx3 100 100 Group C So, what's this system's average response time? A naïve mathematician might think it's the weighted average of all the response times: (1*1 + 1000*10 + 100*100) / (1+1000+100) = 2.7 sec. But what use is this figure? Nobody's response time is ever really 2.7 sec. footnoteI say ever here because it's of course possible that a program whose avg. sec/run is 1 (or even 10) will occasionally have a true response time of 2.7./footnote If you're *anybody* actually using the system, the number 2.7 sec/run is just stupid! The 2.7s figure is especially ludicrous if you're a member of Group B or C, because your average response time is either really 3.7x that number (B) or 37x that number (C)! The mathematical explanation for the stupid-looking-ness is that, no matter what you're doing, this 2.7 number is an average influenced by stuff that you're *not* doing. There is no such thing as an average user (any more than there's an American family with 2.3 children); in this example, there are only members of Groups A, B, and C. What if you're a member of two groups simultaneously (e.g., you run different transaction types in the same day)? It's the same problem, because your expectation of, for example, Tx1 response time is completely different from your expectation of Tx3 response time. Clumping response times from Tx1 and Tx2 into one average makes no sense even then. Expecting *anything* you do to take 2.7 sec/run is going to leave you unfulfilled. The number 2.7 has no useful meaning here. Certainly, if you have some kind of service level agreement (SLA) wired to the number 2.7 sec/run in this case, then I would say you have a SLA that's worse than having no SLA at all. Maybe an easier analogy is this... How would you respond to the question, Using the global air transportation system, how long does it take to fly someplace
RE: CPU Capacity Planning
My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, youll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but its really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] Youll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. Its because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesnt trigger any new serialization issues that werent visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other concurrent activities on the system. Is there a way to account for this if capacity planning is done at the individual bus.tx level? [Cary Millsap] The hardest part about capacity planning is that theres no useful industry-wide standard unit of CPU work to use. You cant use MHz, you cant use MIPS, and you cant use SPECints, or anything else like that. But you can use Oracle LIOs. Its not hard to test a system to see how many LIOs/sec it can handle; this is your supply (capacity). Its also not hard to see how many LIOs/sec an application needs; this is your demand (workload). With this realization, capacity planning is much simpler. The game is to ensure that supply exceeds demand at all times, and by a sufficient amount so that you dont have unstable response times. [Cary Millsap] ...And, of course, as I mentioned previously, you have to keep your peripheral vision open for the possibility that some new scalability threat will manifest and surprise you. Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: Boris, If you mean that some people on your system execute Bus.Tx1, some others execute Bus.Tx2, and some others (maybe with some overlap) execute Bus.Tx3, then my answer to your question is: No, I would strongly encourage you *not* to do this! It was exercises like this that first led me to discover the fact that there's no such thing as a system in the sense that most people use the term (that is, as a big mishmash of different transactions, in which averages have any real meaning). Combining your three CDFs will hurt you in the way described in Why understanding distribution is important on pp238-239 of the Optimizing Oracle Performance book. Here's
Re: CPU Capacity Planning
how would you test how many LIO's your system can handle? How do you choose which queries to test? how do you choose the number of concurrent users to test while doing this? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 07, 2003 11:24 AM My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, you'll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but it's really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] You'll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. It's because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesn't trigger any new serialization issues that weren't visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other concurrent activities on the system. Is there a way to account for this if capacity planning is done at the individual bus.tx level? [Cary Millsap] The hardest part about capacity planning is that there's no useful industry-wide standard unit of CPU work to use. You can't use MHz, you can't use MIPS, and you can't use SPECints, or anything else like that. But you can use Oracle LIOs. It's not hard to test a system to see how many LIOs/sec it can handle; this is your supply (capacity). It's also not hard to see how many LIOs/sec an application needs; this is your demand (workload). With this realization, capacity planning is much simpler. The game is to ensure that supply exceeds demand at all times, and by a sufficient amount so that you don't have unstable response times. [Cary Millsap] ...And, of course, as I mentioned previously, you have to keep your peripheral vision open for the possibility that some new scalability threat will manifest and surprise you. Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: Boris, If you mean that some people on your system execute Bus.Tx1, some others execute Bus.Tx2, and some others (maybe with some overlap) execute Bus.Tx3, then my answer to your question is: No, I would strongly encourage you *not* to do this! It was exercises like this that first led me
RE: CPU Capacity Planning
Thanks for the clarifications, Cary. With regards to a hardware sizing - how do LIOs fit into queueing theory? Let's say I can come up with something like: #CPUs required = Sum( LIOs(Bus.Tx i)) / (10,000*clock rate/100) where i={Bus.Tx 1..n} [on a projected box that haven't been bought yet, it might be a little difficult to estimate the denominator, ... and on the existing one I guess I have to get hold of Jonathan's paper to learn how this can be done] ..but in any event for forecasting purposes, how queueing effect might be taken into account here? Let's say I measured Sum( LIOs(...)) for a 50 users in a unit testing environment and I am told that production would be 10 times more than that, what do I do? Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, youll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but its really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] Youll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. Its because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesnt trigger any new serialization issues that werent visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other concurrent activities on the system. Is there a way to account for this if capacity planning is done at the individual bus.tx level? [Cary Millsap] The hardest part about capacity planning is that theres no useful industry-wide standard unit of CPU work to use. You cant use MHz, you cant use MIPS, and you cant use SPECints, or anything else like that. But you can use Oracle LIOs. Its not hard to test a system to see how many LIOs/sec it can handle; this is your supply (capacity). Its also not hard to see how many LIOs/sec an application needs; this is your demand (workload). With this realization, capacity planning is much simpler. The game is to ensure that supply exceeds demand at all times, and by a sufficient amount so that you dont have unstable response times. [Cary Millsap
RE: CPU Capacity Planning
You can do this even with a completely quiesced single-user system if you want. Just run something that will perform LIO calls non-stop for several seconds. Jonathan Lewis has a great CPU killer that does this very nicely. From observing this run, you can learn easily how many LIO calls one CPU on your system can handle in a given unit of time. There are probably hundreds of caveats, beginning with Not all LIOs are created equal (i.e., take the same amount of time). Many such caveats are listed in Optimizing Oracle Performance. ...But this test is an excellent start, and even this simple of a test does actually take into account things like some OS code path required to keep your system running during the test. As I alluded earlier, there are at least two distinct stages of performance degradation that will occur as you add users. One is the effect of queueing. Even if your system scaled perfectly (no surprises with latches, buffer busy waits, etc.), as you keep adding load, you'll experience the hockey-stick-shaped deterioration of response time simply because of queueing. The second stage is caused by the scalability threats (not modeled by M/M/m in the book) that I mentioned previously in this thread. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Ryan Sent: Sunday, December 07, 2003 12:09 PM To: Multiple recipients of list ORACLE-L how would you test how many LIO's your system can handle? How do you choose which queries to test? how do you choose the number of concurrent users to test while doing this? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 07, 2003 11:24 AM My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, you'll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but it's really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] You'll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. It's because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesn't trigger any new serialization issues that weren't visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements
RE: CPU Capacity Planning
Boris, I think I covered this in my response to Ryan. It was the two stages part. Note that you can avoid even using queueing theory at all if you just make sure that utilization stays to the left of the knee in the performance curve for each resource on the system. You can learn the location of the knee for a given number of parallel service channels (for example, CPUs in your case) on Table 9-3 on p260 of Optimizing Oracle Performance. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 12:39 PM To: Multiple recipients of list ORACLE-L Thanks for the clarifications, Cary. With regards to a hardware sizing - how do LIOs fit into queueing theory? Let's say I can come up with something like: #CPUs required = Sum( LIOs(Bus.Tx i)) / (10,000*clock rate/100) where i={Bus.Tx 1..n} [on a projected box that haven't been bought yet, it might be a little difficult to estimate the denominator, ... and on the existing one I guess I have to get hold of Jonathan's paper to learn how this can be done] ..but in any event for forecasting purposes, how queueing effect might be taken into account here? Let's say I measured Sum( LIOs(...)) for a 50 users in a unit testing environment and I am told that production would be 10 times more than that, what do I do? Thanks, Boris Dali. --- Cary Millsap [EMAIL PROTECTED] wrote: My answers are in-line, preceded with [Cary Millsap]... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Sunday, December 07, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? [Cary Millsap] Sort of, but not exactly. There are lots of scalability threats that begin to manifest in reality when you crank up the load. For example, you'll see latch free waiting on applications that parse too much, but only at higher user volumes (never in unit test). You can consider the new appearance of latch free events to be a type of queueing if you want, but it's really not queueing in the sense of a simple CPU queueing model. If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? [Cary Millsap] You'll put yourself at risk if you simply try to use a queueing model to extrapolate big-system performance from data collected in a unit testing environment. It's because of the potentially out-of-model scalability threats. In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? [Cary Millsap] ...Only if you your production environment doesn't trigger any new serialization issues that weren't visible on your unit test env. And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall system required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this system on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other
CPU Capacity Planning
Let's say I have 3 business transactions (consisting of numerous Oracle transactions each) and I know total service time for each (from c readings off sql traces for the length of the bus.tx). Doing queuing theory exercise I can also get CDF(r max) for each. Let's say Bus.Tx1 - CPU time=5s CDF(r)=97% Bus.Tx2 - CPU time=8s CDF(r)=95% Bus.Tx3 - CPU time=10s CDF(r)=90% How can I combine these three together and make any conclusions as to what the overall CDF(r) would be for the whole system consisting of the above 3 business transactions? Is this doable? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Capacity Planning Methods?
For anyone interested. I took one of those old scripts, modified it into a data warehouse sort of implementation then publish it every day at month end as an internal web page. There's a copy of today's output at http://vdac2.vicr.com/space_new.html if your interested. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, August 12, 2003 10:44 AM To: Multiple recipients of list ORACLE-L blush Let's just say it's the same as Jared's...Yeah, that's it! The very same! And my wife, Morgan Fairchild, whom I've seen naked. sigh When you don't update those old scripts with newer versions of Oracle they really look old and convoluted. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, August 11, 2003 5:29 PM To: Multiple recipients of list ORACLE-L Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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.net -- Author: Goulet, Dick 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: Capacity Planning Methods?
Jared, David, Listers, Another very useful method that I have used in the past is the Ratio Modeling Technique. It is a low-precision method, but it is better than no-precision. Craig Shallahamer et al., wrote a paper on it within the Oracle context. I am sure there are many more out there, but I wanted to mention this, just in case you have not seen it. If you do a Google search on the keyword Ratio Modeling Technique, you should get it. Cheers, Gaja --- [EMAIL PROTECTED] wrote: In a nutshell: Collect stats from dbms_free_space once a week, save them in a table. Once a month, load the stats into another database for calculation. Run them through a Perl filter to create a summary table by tablespace. Use Oracle's Linear Regression functions to make a prediction of future usage at the end of the year, middle of next year, and end of next year. LR is also used to predict the current usage based on past space. The difference between the actual and the prediction is used to adjust future predictions. It ain't perfect, but it's fairly accurate. Jared David Wagoner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/11/2003 02:24 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Capacity Planning Methods? How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator = __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha 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: Capacity Planning Methods?
In a nutshell: Collect stats from dbms_free_space once a week, save them in a table. Once a month, load the stats into another database for calculation. Run them through a Perl filter to create a summary table by tablespace. Use Oracle's Linear Regression functions to make a prediction of future usage at the end of the year, middle of next year, and end of next year. LR is also used to predict the current usage based on past space. The difference between the actual and the prediction is used to adjust future predictions. It ain't perfect, but it's fairly accurate. Jared David Wagoner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/11/2003 02:24 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Capacity Planning Methods? How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator
RE: Capacity Planning Methods?
blush Let's just say it's the same as Jared's...Yeah, that's it! The very same! And my wife, Morgan Fairchild, whom I've seen naked. sigh When you don't update those old scripts with newer versions of Oracle they really look old and convoluted. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, August 11, 2003 5:29 PM To: Multiple recipients of list ORACLE-L Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Capacity Planning Methods?
Hey, beauty is in the eye of the beholder. And if that were not true I sure could not explain some of my friends wives. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, August 12, 2003 11:14 AM To: Multiple recipients of list ORACLE-L EWWAH! Morgan FairchildYUK! -Original Message- Sent: Tuesday, August 12, 2003 9:44 AM To: Multiple recipients of list ORACLE-L blush Let's just say it's the same as Jared's...Yeah, that's it! The very same! And my wife, Morgan Fairchild, whom I've seen naked. sigh When you don't update those old scripts with newer versions of Oracle they really look old and convoluted. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, August 11, 2003 5:29 PM To: Multiple recipients of list ORACLE-L Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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.net -- Author: Odland, Brad 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.net -- Author: Goulet, Dick 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).
Capacity Planning Methods?
Title: Capacity Planning Methods? How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator
RE: Capacity Planning Methods?
Took it a few years ago. Highly recommended. Capacity planning is about a lot more than disk space, which is probably the easiest part of it. Jared On Mon, 2003-08-11 at 17:24, Johnson, Michael wrote: Just lurking as usual, but Craig Schallahammer (sp) has a fairly extensive technical seminar on methods DBAs can use for capacity planning. Here is the link to his web-site. http://www.orapub.com/cgi/genesis.cgi http://www.orapub.com/cgi/genesis.cgi -Original Message- Sent: Monday, August 11, 2003 2:24 PM To: Multiple recipients of list ORACLE-L How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Capacity Planning Methods?
David I analyze the tables each week primarily to populate NUM_ROWS in USER_TABLES. I use ANALYZE TABLE because that works better in 8.1.6. Then I save the data to a separate table along with the date I did it. The primary rows are schema_name (if there is more than one schema of interest), date, num_rows, chained_rows, blocks. Whatever suits your purposes. Then I can go back and see the pattern of growth over time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 4:24 PM To: Multiple recipients of list ORACLE-L How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Capacity Planning Methods?
Title: Capacity Planning Methods? Just lurking as usual, but Craig Schallahammer (sp) has a fairly extensive technical seminar on methods DBAs can use for capacity planning. Here is the link to his web-site. http://www.orapub.com/cgi/genesis.cgi -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]Sent: Monday, August 11, 2003 2:24 PMTo: Multiple recipients of list ORACLE-LSubject: Capacity Planning Methods? How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator
RE: Capacity Planning Methods?
Title: RE: Capacity Planning Methods? Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions
RE: Capacity Planning Methods?
Come on Brad, this is the guy's wife you're talking about !! =) Mike -Original Message- Sent: 12 August 2003 15:14 To: Multiple recipients of list ORACLE-L EWWAH! Morgan FairchildYUK! -Original Message- Sent: Tuesday, August 12, 2003 9:44 AM To: Multiple recipients of list ORACLE-L blush Let's just say it's the same as Jared's...Yeah, that's it! The very same! And my wife, Morgan Fairchild, whom I've seen naked. sigh When you don't update those old scripts with newer versions of Oracle they really look old and convoluted. Rich E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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: Capacity Planning Methods?
EWWAH! Morgan FairchildYUK! -Original Message- Sent: Tuesday, August 12, 2003 9:44 AM To: Multiple recipients of list ORACLE-L blush Let's just say it's the same as Jared's...Yeah, that's it! The very same! And my wife, Morgan Fairchild, whom I've seen naked. sigh When you don't update those old scripts with newer versions of Oracle they really look old and convoluted. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, August 11, 2003 5:29 PM To: Multiple recipients of list ORACLE-L Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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.net -- Author: Odland, Brad 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: Capacity Planning Methods?
OK, I'll post it. Salt to taste or toss it in the garbage. Note that this is just collecting data and doesn't make any recommendations or such. Comments and critiques welcome, except from Mladen... (running for cover) ;) And, of course, standard disclaimers apply! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- CREATE TABLE TS_ACTIVITY ( TABLESPACE_NAME VARCHAR2(30), FREE_SPACE NUMBER, USED_SPACE NUMBER, MAX_FREE_SPACE NUMBER, TIMESTAMPDATE ) TABLESPACE USERS / COMMENT ON TABLE TS_ACTIVITY IS 'Tablespace Activity: Records changes in physical attributes of all permanent, dictionary-managed tablespaces.' / CREATE INDEX TS_ACTIVITY_TS_NAME ON TS_ACTIVITY (TABLESPACE_NAME) TABLESPACE USERS / CREATE OR REPLACE PROCEDURE TS_CHECK IS -- -- Explicit SELECT access must be granted to this schema on the following SYS views: -- -- DBA_TABLESPACES -- DBA_DATA_FILES -- DBA_FREE_SPACE -- -- Modification History -- - -- 12/19/2001 REJ Created. v_tablespace_name ts_activity.tablespace_name%TYPE; v_free_spacets_activity.free_space%TYPE; v_used_spacets_activity.used_space%TYPE; v_max_free_spacets_activity.max_free_space%TYPE; v_rowcount NUMBER; CURSOR C_TS IS SELECT d.tablespace_name, f.bytes FREE_SPACE, NVL(a.bytes - NVL(f.bytes, 0), 0) USED_SPACE, f.max_free_space FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) max_free_space FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.contents = 'PERMANENT'; BEGIN FOR tsrec IN C_TS LOOP SELECT COUNT(*) INTO v_rowcount FROM ts_activity WHERE tablespace_name = tsrec.tablespace_name; IF v_rowcount 0 THEN SELECT q.tablespace_name, q.free_space, q.used_space, q.max_free_space INTO v_tablespace_name, v_free_space, v_used_space, v_max_free_space FROM ts_activity q, (SELECT MAX(timestamp) timestamp FROM ts_activity WHERE tablespace_name = tsrec.tablespace_name) ts WHERE q.tablespace_name = tsrec.tablespace_name AND q.timestamp = ts.timestamp; END IF; IF tsrec.free_space != v_free_space OR tsrec.used_space != v_used_space OR tsrec.max_free_space != v_max_free_space OR v_rowcount = 0 THEN INSERT INTO ts_activity (tablespace_name, free_space, used_space, max_free_space, timestamp) VALUES (tsrec.tablespace_name, tsrec.free_space, tsrec.used_space, tsrec.max_free_space, SYSDATE); END IF; END LOOP; END TS_CHECK; / -Original Message- Sent: Monday, August 11, 2003 5:29 PM To: Multiple recipients of list ORACLE-L Rich, I'd love to see the procedure and table that you use. Thanks for offering. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Capacity Planning Methods?
I also grab freespace from dba_free_space, dump it into a table, and watch the deltas there, too. It helps for watching less predictable object growth, like perhaps with an index. Although my procedure is tablespace-oriented, so it won't give detail as to exactly what's doing all the growing. If you'd like, I could post the smallish 68-line procedure and the table create that I use to do this. There are perhaps better ways of doin g this, but it works for me in 8.1.7 on dictionary managed tablespaces. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Monday, August 11, 2003 4:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: Capacity Planning Methods? David I analyze the tables each week primarily to populate NUM_ROWS in USER_TABLES. I use ANALYZE TABLE because that works better in 8.1.6. Then I save the data to a separate table along with the date I did it. The primary rows are schema_name (if there is more than one schema of interest), date, num_rows, chained_rows, blocks. Whatever suits your purposes. Then I can go back and see the pattern of growth over time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 4:24 PM To: Multiple recipients of list ORACLE-L How do you guys collect capacity planning metrics, such as DB size? Do you use StatsPack, Oracle OEM, others? I'm interested in an efficient method to track DB growth in GB month over month. Best regards, David B. Wagoner Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Capacity Planning -- Expecting the DB growth !!!
One thing I don't think I've seen mentioned is the issue of storage configuration and uptime requirements impact on capacity planning. Is the planned system 7x24 or will there be maintenance windows to add storage? Can storage be added hot or is downtime required. These issues will require the fudge factor to be adjusted accordingly. Mike Hand Polaroid Corp. -Original Message- Sent: Tuesday, December 17, 2002 5:01 PM To: Multiple recipients of list ORACLE-L One problem I've run into when doing some rudimentary cap planning is that much of it is directly related to how much business our Sales force predicts we will generate next year. I've looked at the growth of our general ledger tables and our sales order tables and it's mostly driven by the amount of business we do (we manufacture printing controls). So, some insight into the business by Sales or other company execs may help, too. That, and a fudge factor. One of our larger audit tables had 300% more rows in 2000 than in 1999 because of planned inventory moves used in implementing lean manufacturing. No way I could have predicted that one, but a fudge factor softened the blow a bit. Just my $.02 (before taxes), Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA This transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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: Capacity Planning -- Expecting the DB growth !!!
Jared, Can you send me the script you use. I can't even figure out the syntax for DBMS_SPACE from the docs. I would be very grateful Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 3:58 PM I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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
Re: Capacity Planning -- Expecting the DB growth !!!
Thanks, Dave! I just started looking into using DBMS_SPACE. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 11:14 AM Ruth, have you seen this doc on metastink? It might be helpful. Note:116923.1 Dave -Original Message- Sent: Wednesday, December 18, 2002 9:04 AM To: Multiple recipients of list ORACLE-L Jared, Can you send me the script you use. I can't even figure out the syntax for DBMS_SPACE from the docs. I would be very grateful Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 3:58 PM I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: Capacity Planning -- Expecting the DB growth !!!
Ruth, have you seen this doc on metastink? It might be helpful. Note:116923.1 Dave -Original Message- Sent: Wednesday, December 18, 2002 9:04 AM To: Multiple recipients of list ORACLE-L Jared, Can you send me the script you use. I can't even figure out the syntax for DBMS_SPACE from the docs. I would be very grateful Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 3:58 PM I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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
Re: Capacity Planning -- Expecting the DB growth !!!
I can forward a script that shows how to use DBMS_SPACE. The other stuff I mentioned is not exactly in packagable form. Just use: exec show_space('SCOTT','EMP') Jared create or replace procedure show_space ( p_segname in varchar2 , p_owner in varchar2 default user , p_type in varchar2 default 'TABLE' , p_partition in varchar2 default null ) as l_free_blocks number; l_total_blocks number; l_total_bytes number; l_unused_bytes number; l_unused_blocks number; l_LastusedExtFileId number; l_LastUsedExtBlockIdnumber; l_last_used_block number; l_segname varchar2(30); l_owner varchar2(30); l_type varchar2(20); l_partition varchar2(30); procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line(rpad(p_label,40,'.')||p_num); end; begin l_segname := upper(p_segname); l_owner := upper(p_owner); l_type := upper(p_type); l_partition := upper(p_partition); dbms_space.free_blocks ( segment_owner = l_owner , segment_name = l_segname , segment_type = l_type , partition_name= l_partition , freelist_group_id = 0 , free_blks = l_free_blocks ); dbms_space.unused_space ( segment_owner = l_owner , segment_name = l_segname , segment_type = l_type , partition_name= l_partition , total_blocks = l_total_blocks , total_bytes = l_total_bytes , unused_blocks = l_unused_blocks , unused_bytes = l_unused_bytes , last_used_block = l_last_used_block , last_used_extent_file_id = l_LastusedExtFileId , last_used_extent_block_id = l_LastUsedExtBlockId ); p( 'Free Blocks', l_free_blocks); p( 'Total Blocks', l_total_blocks); p( 'Total Bytes', l_total_bytes); p( 'Unused Blocks', l_unused_blocks); p( 'Unused Bytes', l_unused_bytes); p( 'Last Used Ext FileId', l_LastusedExtFileId); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId); p( 'Last Used Block', l_last_used_block); end; / show error procedure show_space Ruth Gramolini [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/18/2002 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Capacity Planning -- Expecting the DB growth !!! Jared, Can you send me the script you use. I can't even figure out the syntax for DBMS_SPACE from the docs. I would be very grateful Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 3:58 PM I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly
Re: Capacity Planning -- Expecting the DB growth !!!
Thanks a million! Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 12:50 PM I can forward a script that shows how to use DBMS_SPACE. The other stuff I mentioned is not exactly in packagable form. Just use: exec show_space('SCOTT','EMP') Jared create or replace procedure show_space ( p_segname in varchar2 , p_owner in varchar2 default user , p_type in varchar2 default 'TABLE' , p_partition in varchar2 default null ) as l_free_blocks number; l_total_blocks number; l_total_bytes number; l_unused_bytes number; l_unused_blocks number; l_LastusedExtFileId number; l_LastUsedExtBlockIdnumber; l_last_used_block number; l_segname varchar2(30); l_owner varchar2(30); l_type varchar2(20); l_partition varchar2(30); procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line(rpad(p_label,40,'.')||p_num); end; begin l_segname := upper(p_segname); l_owner := upper(p_owner); l_type := upper(p_type); l_partition := upper(p_partition); dbms_space.free_blocks ( segment_owner = l_owner , segment_name = l_segname , segment_type = l_type , partition_name= l_partition , freelist_group_id = 0 , free_blks = l_free_blocks ); dbms_space.unused_space ( segment_owner = l_owner , segment_name = l_segname , segment_type = l_type , partition_name= l_partition , total_blocks = l_total_blocks , total_bytes = l_total_bytes , unused_blocks = l_unused_blocks , unused_bytes = l_unused_bytes , last_used_block = l_last_used_block , last_used_extent_file_id = l_LastusedExtFileId , last_used_extent_block_id = l_LastUsedExtBlockId ); p( 'Free Blocks', l_free_blocks); p( 'Total Blocks', l_total_blocks); p( 'Total Bytes', l_total_bytes); p( 'Unused Blocks', l_unused_blocks); p( 'Unused Bytes', l_unused_bytes); p( 'Last Used Ext FileId', l_LastusedExtFileId); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId); p( 'Last Used Block', l_last_used_block); end; / show error procedure show_space Ruth Gramolini [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/18/2002 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Capacity Planning -- Expecting the DB growth !!! Jared, Can you send me the script you use. I can't even figure out the syntax for DBMS_SPACE from the docs. I would be very grateful Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 3:58 PM I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly
RE: Capacity Planning -- Expecting the DB growth !!!
Reddy - Step number one is to immediately begin collecting size data. For most sites, storing a weekly count of the number of rows in each table is sufficient. Usually combine it with regular ANALYZE TABLE or DBMS_STATS executions. This will be your basis for making projections. Then get to know the factors that influence the growth. You mention events such as holidays as an example. But some sites are affected more by the holidays than others. If your developers are adding features to the database, that will influence growth. I have one application whose busy season is the springtime. As far as automation, just having the information in a database table allows you to perform forecasts just using simple SQL commands. In my experience the problem with total automation is that the questions keep changing. Periodically you will be asked a question related to capacity, but the question is usually one you haven't thought of before, but with a base of history you can figure out the answer. Inevitably Pareto's Rule (a.k.a. 80/20 principle) plays heavily. Just a handful of tables will contain most of your growth. If you can predict those correctly, the rest falls into place. That table that holds 10 rows and just might grow to 12 rows in 2 years isn't going to be your challenge. Many packages that monitor performance over the long term also perform capacity planning. I think OEM has this capability. Just go to www.google.com http://www.google.com and enter Oracle Capacity Planning, and you will see many interesting sites. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, December 16, 2002 8:19 PM To: Multiple recipients of list ORACLE-L Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Capacity Planning -- Expecting the DB growth !!!
Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Capacity Planning -- Expecting the DB growth !!!
Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Reddy, Madhusudana 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: Capacity Planning -- Expecting the DB growth !!!
I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week. Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators. Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it. Earlier this year my projection for SAP running out of space was 6/28/02. There were some delays in getting the new storage hardware installed. Guess which day we ran out of space? ;) That was just dumb luck that it happened the same day, but now people believe my projections. :) I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were. There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG. Jared Reddy, Madhusudana [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Capacity Planning -- Expecting the DB growth !!! Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Capacity Planning -- Expecting the DB growth !!!
There is not really capacity planning here. I'm starting a new job as a permanent employee and since my first project is quite small, I've started writing a capacity planning guide hoping the other groups will collaborate. One thing we do is to run statspack each hour on all production databases and we generate nice graphics from statspack data so we can explain pas behavior and extrapolate for the futur. When planning size, try to accomodate for 1 year. --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, It should include all as you said , but since I do not belong to a capacity planning group in my shop , I mostly concentrate on the DB growth . I have taken Sales as business object to correlate with the DB size . I think in our case both of them are directly proportional. ( Business objects may vary from shop to shop ) first step I have done is calculating the : Growth factor = avg. History Db growth / avg Hist Sales growth Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.) 3rd step: Now I have sales projection and growth Factor , and can the projected DB growth . Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process . I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us. or somebody can better help me in identifying the Q? from managers Thank YOU all for your replies Madhu -Original Message- Sent: Tuesday, December 17, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Check the link Kirti has posted (orapub). Is capacity planning only on the database size ? In my mind it also includes : Transaction description (online and batch) Transaction frequency (online and batch) Transaction window Networking requirements Number of users (all and concurrent) Overall disk space (inside and outside the database) Availability --- Reddy, Madhusudana [EMAIL PROTECTED] a écrit : Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size , even by considering events like thanks giving , holiday season and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would you help me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Reddy, Madhusudana 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED
Capacity Planning -- Expecting the DB growth !!!
Title: Blank Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size ,even by considering events like "thanks giving" , "holiday season" and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would youhelp me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu Blank Bkgrd.gif
RE: Capacity Planning -- Expecting the DB growth !!!
Title: Blank so how r u gonna do it? 1 business object = n number of rows in m different tables + indexes if regular day then x number of business objects are created if holiday then y number of business objects are created and are u going to considering the historical growth ofdatabase objects during regular and holidays? do u hv tablespace space consumption for the last couple of yrs to get a good graph and extrapolate (?) it. -Mandar -Original Message-From: Reddy, Madhusudana [mailto:[EMAIL PROTECTED]]Sent: Monday, December 16, 2002 6:19 PMTo: Multiple recipients of list ORACLE-LSubject: Capacity Planning -- Expecting the DB growth !!! Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size ,even by considering events like "thanks giving" , "holiday season" and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would youhelp me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu
RE: Capacity Planning -- Expecting the DB growth !!!
Title: Blank Craig Shallahamer has a good training class on this topic : http://www.orapub.com/cgi/genesis.cgi?p1=subp2=cp_course - Kirti -Original Message-From: Reddy, Madhusudana [mailto:[EMAIL PROTECTED]]Sent: Monday, December 16, 2002 8:19 PMTo: Multiple recipients of list ORACLE-LSubject: Capacity Planning -- Expecting the DB growth !!! Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size ,even by considering events like "thanks giving" , "holiday season" and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would youhelp me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu
Re: Capacity Planning -- Expecting the DB growth !!!
Title: Blank Load 5-10% of the stuff and see how much it takes . From this you can calculate. No formula will give you accurate results, mainly because actual field length vary so much that approximation is useless. (based on a lot of years checking this) Yechiel AdarMehish - Original Message - From: Reddy, Madhusudana To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 17, 2002 4:18 AM Subject: Capacity Planning -- Expecting the DB growth !!! Hello All, I am currently working on capacity planning of the database , expecting the database size based on the business object ,sales ( Historical data). I am not sure about the approach I am following . I believe there might be some better approach followed in some shop to estimate the DB size ,even by considering events like "thanks giving" , "holiday season" and all. Also I have to automate this process. Would like to know some best suggestions you always have in this forum. Would youhelp me in identifying some formulae. Any kind of documentation will be a great help !!! Thanks in advance, Madhu
Capacity planning courses
Thanks to everyone who made suggestions on training courses. Looking at my skill set and the requirements of my current position, I am thinking about taking a course in capacity planning. Does anyone have any recommendations? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity planning courses
Check out the courses offered by Craig Shallahammar (sp?) at www.orapub.com --- Schauss, Peter [EMAIL PROTECTED] wrote: Thanks to everyone who made suggestions on training courses. Looking at my skill set and the requirements of my current position, I am thinking about taking a course in capacity planning. Does anyone have any recommendations? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity planning courses
Check out www.orapub.com. Craig Shallahamer conducts Capacity Planning course. I have not yet attended one but I have heard from others that the course is very good. - Kirti -Original Message- Sent: Monday, June 24, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Thanks to everyone who made suggestions on training courses. Looking at my skill set and the requirements of my current position, I am thinking about taking a course in capacity planning. Does anyone have any recommendations? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity planning courses
Craig Shallahamer, www.orapub.com. I've taken the class, it's excellent. Two things you will learn: * There's more to capacity planning than you thought * It's not that hard to do, given the tools and techniques. Jared On Monday 24 June 2002 06:43, Schauss, Peter wrote: Thanks to everyone who made suggestions on training courses. Looking at my skill set and the requirements of my current position, I am thinking about taking a course in capacity planning. Does anyone have any recommendations? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Capacity Planning
Good morning, I am trying determine whether a proposed 8-way Intel (Unisys ES7000) system running Windows 2000 will support our anticipated work load. I know this question has a LOT of variables but I don't even have a feel for some of the numbers. Two things: One - just to get an idea of the scale of things, is 300 concurrent users a 'lot' on such a system? Like I said, I KNOW there are many other variables - specifically, what the users are doing at the time, tunning, ... but I am wondering (assuming well written queries, apps, database design, ...) if this number is in the range of 'THIS SYSTEM IS OVERKILL' 'PROBABLY OK' 'HMM BETTER LOOK OUT' 'YOU ARE INSANE AND WILL BE JOB HUNTING SOON' Does anyone have a similar system running with say ... 1000 concurrent users? 100? 5000??? Two - Can anyone point me to resources (other than the vendors) that might help. I don't know why I have such distrust for someone who wants my money. Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bond Mike A Contr OC-ALC/TILC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning
Mike You have not specified how much memory is on the machine, neither have you specified which application you will be running against the box. also, are you going to be using an intelligent RAID controller device (EMC, Shark, similar) with this ? Are you running this across a LAN / WAN ? Are all 300 users distributed across a few physical sites ? for OLTP, I think your system is way overkill, but always better to have more rather than less. for a DW situation, you may be underpowered, though not many sites have 300 marketing type / OLAP users connected, so I am going to assume your are running an OLTP type DB, in which case, you should be happy with the performance and scalability. HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Please note 17 hour time difference between Melbourne and CA Only Robinson Crusoe had all his work done by Friday -Original Message- Sent: Friday, 14 June 2002 6:23 AM To: Multiple recipients of list ORACLE-L Good morning, I am trying determine whether a proposed 8-way Intel (Unisys ES7000) system running Windows 2000 will support our anticipated work load. I know this question has a LOT of variables but I don't even have a feel for some of the numbers. Two things: One - just to get an idea of the scale of things, is 300 concurrent users a 'lot' on such a system? Like I said, I KNOW there are many other variables - specifically, what the users are doing at the time, tunning, ... but I am wondering (assuming well written queries, apps, database design, ...) if this number is in the range of 'THIS SYSTEM IS OVERKILL' 'PROBABLY OK' 'HMM BETTER LOOK OUT' 'YOU ARE INSANE AND WILL BE JOB HUNTING SOON' Does anyone have a similar system running with say ... 1000 concurrent users? 100? 5000??? Two - Can anyone point me to resources (other than the vendors) that might help. I don't know why I have such distrust for someone who wants my money. Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bond Mike A Contr OC-ALC/TILC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Ferenc Mantfeld INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
CAPACITY planning
Hi Is any additional benefit in terms of performance in Oracle9i? Does any one have documents on capacity planning? Thx -Seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CAPACITY planning
Check Craig Shallahamer's web site (http://www.orapub.com) for Capacity Planning documents. He also conducts a training class on this topic. I have yet to attend it, but I have heard that it is good and worth attending. - Kirti -Original Message- Sent: Thursday, May 30, 2002 12:57 PM To: Multiple recipients of list ORACLE-L Hi Is any additional benefit in terms of performance in Oracle9i? Does any one have documents on capacity planning? Thx -Seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: capacity planning??
Shibu, Go to http://www.orapub.com Craig Shallahamer is THE Guru on the subject. I took his course a couple years ago - excellent! Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of ShibuSent: Friday, February 08, 2002 12:13 AMTo: Multiple recipients of list ORACLE-LSubject: capacity planning?? Hi all Can anyone send me a doc or white paper on capcity planning?? regards, shibu
RE: capacity planning??
I will chime in with my recommendation for this as well. Jack and I attended the same class. Jared Jack C. Applewhite [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/08/02 07:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: capacity planning?? Shibu, Go to http://www.orapub.com Craig Shallahamer is THE Guru on the subject. I took his course a couple years ago - excellent! Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, February 08, 2002 12:13 AM To: Multiple recipients of list ORACLE-L Hi all Can anyone send me a doc or white paper on capcity planning?? regards, shibu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
capacity planning??
Hi all Can anyone send me a doc or white paper on capcity planning?? regards, shibu
Capacity Planning
Could anyone direct me to some excellent documentation on capacity planning or give any insights that they might have? Thanks Don Bricker Database Administrator / System Administrator Illinois Environmental Protection Agency 1021 North Grand Avenue East Mail Code #32 Springfield, IL 62794-9276 [EMAIL PROTECTED] (217) 558-2290 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Donald Bricker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning
for sun: Configuration and capacity planning for Solaris servers by Brian L. Wong. -Original Message- Sent: Tuesday, February 05, 2002 10:46 AM To: Multiple recipients of list ORACLE-L Could anyone direct me to some excellent documentation on capacity planning or give any insights that they might have? Thanks Don Bricker Database Administrator / System Administrator Illinois Environmental Protection Agency 1021 North Grand Avenue East Mail Code #32 Springfield, IL 62794-9276 [EMAIL PROTECTED] (217) 558-2290 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Donald Bricker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Ferguson II, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
capacity planning
Hi What are the basic tips for capacity planning for hardware on oracle8.1.7? Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Capacity Planning.
Hi List , Any one has any links to white papaers ,ideas on Capacity Planning for the database . Has anyone been through it all. Could u please guide . How do u estimate and Project : Space required , Cpu Required , Memory required . I have a bit of idea about Space estimation . But the other two are completely new ones. Any additions to the above parameters shreepad . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shreepad Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning.
Hi Shreepad, Check out http://www.orapub.com//cgi//genesis.cgi?p1=subp2=train_main. Craig Shallahamer offers a training class on all the things you are looking for. Worth attending. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Shreepad Vaidya [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 8:22 AM To: Multiple recipients of list ORACLE-L Subject: Capacity Planning. Hi List , Any one has any links to white papaers ,ideas on Capacity Planning for the database . Has anyone been through it all. Could u please guide . How do u estimate and Project : Space required , Cpu Required , Memory required . I have a bit of idea about Space estimation . But the other two are completely new ones. Any additions to the above parameters shreepad . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shreepad Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning.
Adrian's Sun performance tuning book has some great comments on this. Highly recommend looking at it, even though not Oracle specific entirely, VERY good start. -Original Message- Sent: Tuesday, May 22, 2001 9:22 AM To: Multiple recipients of list ORACLE-L Hi List , Any one has any links to white papaers ,ideas on Capacity Planning for the database . Has anyone been through it all. Could u please guide . How do u estimate and Project : Space required , Cpu Required , Memory required . I have a bit of idea about Space estimation . But the other two are completely new ones. Any additions to the above parameters shreepad . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shreepad Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning.
For starters, you are going to require some information from the project manager/developers/users or whoever is driving the project. Avg # of users simultaneously connected Avg # of transactions Avg transaction size Desired average response time. This should really be part of a Service Level Agreement. You will need this information to make any kind of meaningful estimate of the CPU power and memory requirements. A good site to visit for a CPU capacity planning tool is www.orapub.com. There you will find an MS Excel workbook that uses queuing theory to help determine the proper number of CPU's, and the percentage of utilization . Practical capacity planning dictates that your system should utilize have a 70% maximum CPU utilization under normal load, though I prefer the margin of error that 60% provides. Determining memory requirements is much easier. On top of your estimated SGA size, add 15 meg of RAM for each user session, this has been my observation for Oracle 8i. Though a session may only use 8-10 meg, I would rather have more memory than immediately necessary than not enough. The 16 meg was observed on a DW with a SORT_AREA_SIZE of 5 meg. Memory utilization per user can be affected by a few other things: With parallel query, multiply the 15 meg by degree of parallelization. SORT_AREA_SIZE will affect this as well. Once again, you will need those transaction numbers to do any of this. If it is a 3rd party app, they should already have some kind of worksheet to aid in all this. Jared On Tuesday 22 May 2001 06:21, Shreepad Vaidya wrote: Hi List , Any one has any links to white papaers ,ideas on Capacity Planning for the database . Has anyone been through it all. Could u please guide . How do u estimate and Project : Space required , Cpu Required , Memory required . I have a bit of idea about Space estimation . But the other two are completely new ones. Any additions to the above parameters shreepad . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Capacity Planning.
Excellent resource I might add. -Original Message- Sent: Tuesday, May 22, 2001 10:16 AM To: Multiple recipients of list ORACLE-L Hi Shreepad, Check out http://www.orapub.com//cgi//genesis.cgi?p1=subp2=train_main. Craig Shallahamer offers a training class on all the things you are looking for. Worth attending. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Shreepad Vaidya [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 8:22 AM To: Multiple recipients of list ORACLE-L Subject: Capacity Planning. Hi List , Any one has any links to white papaers ,ideas on Capacity Planning for the database . Has anyone been through it all. Could u please guide . How do u estimate and Project : Space required , Cpu Required , Memory required . I have a bit of idea about Space estimation . But the other two are completely new ones. Any additions to the above parameters shreepad . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shreepad Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).