Responsibly Forecasting Oracle System Performance Oracle World 2003

Responsibly Forecasting Oracle System Performance Oracle. World 2003 Session 36691 Craig A. Shallahamer Ora. Pub, Inc. Portland, Oregon USA craig@orapub. com © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Presentation Objectives • What is Oracle performance management. • Know an industry standard methodology. • Know about many proven models. • How to begin using forecasting in your daily routine. • Where to go from here. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

What is performance management? • What is management? • For a DBA, PM is both reactive and proactive. • Reactive: – It’s were most DBAs live. – Is what we have been taught to do. • Proactive: – It’s where DBAs must learn and live. – Forecasting is a significant part of PPM. – It’s not as difficult as you might think. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

We must forecast methodically. • We desire reproducible consistency. – Without method there is chaos. – With too much method, creativity is halted. • Forecasting is a scientific thing, therefore we should be able to develop a method. • There are many different methods. • The project plan, if there is one, should embrace and enhance our method. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Methodically forecasting performance • Determine the “study question. ” • Characterize the workload. • Develop the appropriate model. • Validate the forecast. • Forecast! © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Determine the “study question” • Each project has a fundamental question that needs answering. • For example: – Can the system handle the load six months from now? – Will response time significantly change if we add five more CPUs? – How many CPUs and IO devices do we need to keep people happy? • Write it down. • Get everyone to agree to it…over and over again. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Characterize the workload • Objective: To capture the most relevant characteristics of the real or proposed workload. • Workload characterisation is simply segmenting and linking processes by common characteristics. (e. g. , time of operation, CPU requirements, calls, hits, etc. ) • It is important because before any analysis or forecasts can be made, the workload must be understood. – You can’t ask about something if you don’t know anything about it. • © 2003 Ora. Pub, Inc. A “capacity planning” tool must allow for flexible characterisation of the workload. www. orapub. com Responsibly Forecasting

Forecast model development. • Every forecast uses a some type of model. • Models are a reality abstraction. – Something simple representing something complex. • We use models every day of our lives. • Models: – Are not real – May seem overly simplified – May seem overly complex – Can be tunable or optimized © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Some benefits of using mathematical forecast models. • • Greater chance of dealing appropriately with reality Reduced complexity The same WC must be performed. Lower cost (labor, equipment, traveling, etc. ) Unlimited scenario testing (“what if”) Very flexible when slight changes are needed Very portable from one project to the next Non-technical people can many times use the model. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

The costs of using mathematical models. • Dealing with unbelieving clients and colleagues • Mathematical modeling skills • Must have the ability to think both abstractly, high level, and very detailed. • Modeling doesn’t deal with algorithmic, scaling, and code issues very well. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Forecast model characteristics. • Precision possibility. Each model has a limit on it’s possible precision. You want to match required precision with precision possibility. • Project duration. Some models typically are involved with projects of specific duration. • System status. Some models can only be applied to an existing production system, while some can work well with a proposed system. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Forecast model characteristics. • Required Data. Some models require detailed transaction level detail while others only require general WC and O/S data. • Scope. Some models only deal with one system component (e. g. , CPU) and do not account for O/S subsystem interaction. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Your unique situation will lead you to the best model. © 2003 Ora. Pub,

Some models are extremely simple. • Simple Math. 2 MB * 100 users = 200 MB – Fast and simple – We use this all the time! • Ratio Modeling relates a WC category to a specific system resource. – 150 OLTP transactions = 1 CPU – We use it all the time – Relatively fast – Amazingly precise given its simplicity – No associated statistics © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Linear Regression is simple. • LR relates one or more WC category to a specific system resource. – It’s relatively fast – Very precise – Easy to learn – Statistically sound – Dangerous © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Simple Queuing is very versatile. • Simple Queuing deals with the reality of waiting and non-uniform stuff. – Very precise – Statistically sound. – Very versatile – low precision or – high precision © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Simulation is common in the research community. • Simulation places transactions into a controlled system and records what happens. – Very powerful and very versatile – Statistically sound – Different types © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

How many cycles does it take? © 2003 Ora. Pub, Inc. www. orapub. com

Comparing different forecast models. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

What model validation is used for. • Validating a model helps us understand its precision capabilities. • If a model is not validated, we can not responsibly use it. • Validation usually provides valuable statistics used to quantify error. • Basic statistics is required to understand the model validation results. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

How a model is validated. • Validation applies unseen historical data to a model, forecasts, and then compares the forecasted values to the actual historical values. • The smaller the error, the more precise the forecast model. • Validation can focus on different “forecast areas” of interest. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Forecasting…finally! • Forecasting is very satisfying, especially after all you have gone through to get there. • Forecasting takes “never before seen data” and applies it to the forecast model. • When following our methodology, the forecast output combined with the statistics, enables for very responsible forecasts. • Forecasting is very model specific. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

An example simulation forecast summary © 2003 Ora. Pub, Inc. www. orapub. com Responsibly

Thoughts about integrating forecasting into your daily routine. © 2003 Ora. Pub, Inc. www.

Start forecasting right away. • Don’t wait and don’t wait for the perfect project. • Look for natural forecasting opportunities in you existing work. • Add additional value to your existing work. • Don’t wait for the perfect opportunity. • There is no perfect forecast project. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Listen for opportunities • You may notice this yet, but people frequently ask forecast type questions. • They may not expect you to be able to answer them. • Start listening to people around you. • As you listen be thinking about how they could be helped using forecasting. • Don’t wait for them, listen and ask them if they could benefit from a forecast. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Answer very simple questions. • Don’t wait for the big perfect project. • Start with small, very well defined, and very relevant forecasts. • Don’t try to impress people, just get it done. • If you do a good job, people will begin asking for more forecasts. • Don’t be talked into doing something larger than you, or for that matter anyone, could do. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Build a historical database. • Forecasts need lots of data. • Starting collecting lots of data now. • Don’t wait until you need the data…it will be do late. • Gather Oracle, application, and O/S data. • Store it in a DB so you can get to it whenever you need to. • The gathering frequency should be between 30 minutes to 2 hours. One hour is a good place to start. • The OSM tools are a good way to start. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Begin with existing tools and services. • Use the tools and services you received from this course. • Don’t try and create your own mathematical tools right away. • Use what you have and enhance as appropriate. • If you have a general modification, please share. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Don’t get caught up in the math. • Don’t let the math scare you away. • Don’t let the math or the tool distract you from answering the study question. • More complex math does mean more precise forecasts. © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Final Exam! • What are the five methodology steps? • Name four forecasting models?

Resources • www. orapub. com • Simulation with Arena • Practical Performance Analyst •

Thank You! © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting

Responsibly Forecasting Oracle System Performance Oracle. World 2003 Craig A. Shallahamer Ora. Pub, Inc. Portland, Oregon USA craig@orapub. com © 2003 Ora. Pub, Inc. www. orapub. com Responsibly Forecasting