Production versus Development Systems

Tuning is an iterative and often time-consuming process. It's also a major inconvenience to users of a production system. Many of the tuning changes you will be making will require a database shutdown and restart. Some may require reconfiguration of the operating system or a kernel relink on UNIX systems. Some of the changes may make the situation worse than before.

Your tuning strategy must first consider the environment in which you'll be working. I'm making an assumption that you've never seen the system before, that you're just walking in the front door for the first time. You can either tune on a development or a production system. There are tradeoffs for each one. If you're tuning on a development system, you should be able to demand full control of the system. This means that there won't be someone else changing up the operating system or trying to reconfigure the database. You'll probably be able to start and stop the system with minimum hassle and inconvenience. You probably will have to contend with other developers on the system at the same time. Try to understand what they're doing and whether there will be someone on the production system doing the same thing. Developers and coders habitually put different types of loads on a system than users do. If your production users are running a fairly "canned" application, with little or no ad hoc querying and all querying done through your "canned" application, the loads will be consistent and will have consistent problems. On a development system, there's a lot more ad hoc querying going on as well as a lot of bad SQL that is in the process of being discarded. A major challenge is providing a production-level user load on a development system. A lot of times this is complicated by the fact that the development system is seldom an exact hardware duplicate of the production system. The development system often a less powerful sibling of the production system. Sometimes it is last year's production system,

Tuning on a production system is another matter entirely. Depending on the exposure of the system within the organization, one production database can affect many people within an organization. Random database restarts or operating system reboots will not endear you to the users. If you're lucky, you'll have a window after normal working hours that will allow you to tune. If you're in a 24 x 7 environment, tuning can be difficult or even impossible. You'll have a lot less leeway in tuning a production system, and your technique will be much more restrained and conservative. Aggressive experiments you may be able to try on a development system are often best forgotten in the production system.