When to Stop Tuning in Production

 < Day Day Up > 



When do you stop tuning? This is always debatable. You could stop tuning when performance targets are met, depending on what needs to be tuned. The resolution of obvious bottlenecks is a very clear indicator that no more tuning is required. It is often the case that physical tuning, that is, configuration, physical database structure, networking and hardware bottleneck issues can only amount to as little as 10% of total effective tuning activity, both in development and production.

The simple approach to when to stop tuning is to teach your developers to build properly tuned SQL code from the word go and make sure that the data model is sound before you do even that. This is probably impossible but the more SQL code tuning that is done during the development process then the fewer problems you will have later on. Many software projects are discarded because they take too long to develop. However, many other software projects are thrown out or rewritten because they do not meet acceptable performance standards, rendering them useless. Tuning data models and SQL code after completion of development can sometimes simply be too expensive.

When to stop tuning depends on your situation and the skills you have. If the company and your database size and activity grow you will have performance problems anyway, but you can be better prepared.

Let us examine the steps in tuning production databases in order to decide when to stop tuning the different areas.

Bottlenecks

Solving performance bottlenecks is usually reactive rather than proactive. The term bottleneck is technical computer jargon and usually deals with a particular facet of your environment, which is overloaded, be it within or outside your database.

Stop tuning when the bottleneck is removed.

Configuration

If there are immense configuration and physical problems some downtime may be required. Configuration issues are easier to resolve than physical problems and both are easier than tuning data models and SQL code.

Configuration can be as simple as setting parameters correctly in the Oracle Database configuration parameters file and Oracle networking software configuration files. Make absolutely sure configuration parameters are completely understood before changing. Firstly, incorrectly formed configuration can prevent the database from starting and perhaps cause a crash. Secondly, some parameters have very specific functions; incorrect settings can cause a totally different and probably undesired effect.

Stop tuning when configuration parameters are correct. Experimenting with changing configuration parameters on a production database is risky; test, test, test!

Physical Space Usage

Physical space usage and growth tuning for performance includes tuning of datafiles, redo logs, archive logs, and rollback segments.

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated. Use automatic undo.

Resolving physical problems with space usage and growth can cause a lot of downtime but may be the only solution. Small databases have small initial configuration parameter settings. If those small databases suddenly start to get very busy then immense problems can occur. The sooner the physical issues are resolved for a rapidly growing database the better. If the database continues growing the temptation is often to spend enormous amounts of money on hardware. Organizing a database physically can essentially save a lot of disk space.

Note 

 Oracle Database 10 Grid   The current trend in Oracle Database is veering towards automated management of physical space. As a result physical space management to save disk space is becoming less important.

The smaller a database is the less disk space to search through when finding data, thus the faster your data searches will be. Additionally highly fragmented data can cause a lot of "bouncing around" when retrieving data.

Stop tuning when performance is acceptable as long as requirements for uptime are not compromised. Only extreme situations of database growth cause problems with use of physical space.

SQL Code Tuning

Poorly constructed SQL code usually causes most database performance problems. When SQL code is poorly tuned there is little that database administrators can do to improve performance using physical and configuration tuning alone. Database administrators can tune SQL code contained in PL/SQL stored procedures. The ideal approach to SQL code tuning is to teach your developers to tune SQL code as they build applications.

Sometimes developers will build applications rapidly without much consideration to building efficient SQL code. Developers do not really have extra time to make sure the SQL code is as efficient as possible. Most production database administrators tend to have roots as either operating system or network administrators. These skills are essential for production database administration. There's the rub! The administrators sometimes do not know how to tune SQL code and they cannot change the application code since the code belongs to the developers. The developers do not have time or the skills to produce efficient SQL code. Most Oracle Database tuning experts perform physical tuning on the database and the operating system, not SQL code tuning. The result is often only a 10% performance improvement. I have seen SQL code tuned for an application in its entirety and performance increases of 30 to 500 times. That is 500 times faster. One hundred percent is twice as fast. This is an extreme from a consulting job I worked on a few years ago. There were other projects in the past with similar performance issues.

Stop tuning SQL code when development stops if you can. Teach and encourage your developers to write efficient SQL code during development. It is much more difficult and expensive to tune SQL in production, especially when SQL is embedded in applications.

Data Model Tuning

SQL code performance depends largely on the data model, especially if the data model is poorly structured. Beware of copying older, previously "invented" data models or using a data model because it is the accepted standard for a particular application. Relational databases, Normalization, and Denormalization have been in existence for many years. However, the structure of the applications overlaying those relational databases has changed recently and is still changing. What in the past was COBOL and C is now C++, Java, Perl, and even object Perl.

Object-oriented design application development languages such as Java tend to throw sand in the eyes of what used to be accepted relational database design. Object applications tend to impose an unwilling structure on top of a relational database. The result is a data model which is a hybrid between relational and object database methodologies. This hybrid structure can be an advantage for OLTP and client-server transactional applications but a performance hog for any type of reporting or data warehouse applications.

I have seen many OLTP applications with Java object structures imposed onto relational databases. Sometimes this top-down application to data model approach works extremely well, sometimes very poorly. Object methodology promotes breaking things down into their smallest manageable parts. This approach can be a complete disaster for efficiency in a relational database. Imposing an object structure onto a relational database in its purest form is relational database Normalization in its extreme. In these situations 3rd, 4th, and 5th Normal Forms are common. A partial solution is often two databases, one OLTP and the other a data warehouse, doubling costs. The second database is a Denormalized data warehouse database; Denormalizing a database can enhance performance from a reporting perspective.

Tuning the data model is the most difficult and most expensive option because SQL code depends on the structure of the data model; extensive application code changes can result. Tuning the data model is more effective than physical and configuration tuning but can easily escalate into a full rewrite.

If further data model tuning is required after production release you may want to look at data warehouse type options. Tuning the data model for an OLTP database after production release will generally involve a partial or complete rewrite. A data warehouse type approach generally involves duplicating a database and restructuring that duplicate for output performance in terms of processing many rows at once. Quite often transactional databases in OLTP and client- server environments are required to be tuned for small response reaction time to keep your customers happy; very few rows retrieved at a time. If a web page takes more than 7 s to load then your customers may lose interest and go elsewhere, straight to the competition. Data warehouse databases, on the other hand, are designed for rapid throughput of large amounts of information for analytical and reporting purposes.

Stop tuning your data model preferably before development starts or before the first production release. Do not build your relational database to mimic the object structure of a Java application because relational and object methodologies are completely opposed to each other with respect to methodology. Object structures break down for simplicity and relational structures are efficient when summarizing information into groupings. These structural types are completely contrary to each other.

So when should you stop tuning in general? As long as there are problems or if users are not satisfied, never! Tune whenever something changes if there are problems or when you have not reached a performance target. There is no harm in going past a target level of performance. Then again time might be better utilized doing other tasks. You should stop tuning the data model before SQL code development starts. You should stop tuning SQL code when development finishes and the code goes into production.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net