More than ever, today's computing environment is forcing IT professionals to operate in a complex business environment where success is no longer determined by a single factor, such as having the best computer system or having the right technical skills. But rather by complexity, as every aspect of the computing environment is becoming more complex --- in terms of dealing with multiple technologies and protocols; getting multiple vendor products to work together with mission critical reliability; taking into consideration the skills, experience, and demands of a wide range of varied users; managing computing resources physically located in multiple areas and also managing its varied users; and dealing with the rapid change in technology advancement and routinely greater business demand for extra new features and capabilities. Each of these facets presents a unique deployment, management, reliability, and availability challenges, (Piedad & Hawkins, 2001).

This paper defines database tuning and highlights some of the main concerns of the database tuning aspect of today's computing environment. By definition, database tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed. That is, “it’s an on going process of optimizing the operation of the database configuration based on attainable goals that determine when to tune, what to tune, how to tune it, and when tuning is done. Therefore, database-tuning focuses on optimizing database operations to maintain operation integrity and preventing performance problems that may increase frustration, decrease end-user satisfaction, and ultimately affect productivity through costly production slowdowns,” (Afyouni, 2004). Database tuning encompasses application tuning; database instance tuning (memory allocation, CPU usage, disk I/O); hardware tuning (storage usage); and the underlying database processes. It also involves the management and manipulation of the database structure itself, such as the design and layout of tables and indexes. Furthermore, it often involves the modification of the database architecture to optimize the use of hardware resources available (Stephens, Plew, & Sams, 2003).

In addition, database tuning is in part art and to some extent science with no quick fix formulas. That is, its involves an artistic display of creativity, skills, intuition, and experience with systematic scientific investigation to find and eliminate database performance problems or bottlenecks in accordance to realistic tuning goals set forth by business requirements. In a nutshell, this involves the initial problem assessment, problem investigation, result analysis, hypothesis creation, solution proposal and implementation, and system monitoring (Afyouni, 2004; Whalen, 2005; Beaulieu, 2005).

Some of the main concerns of database tuning include the sheer complexity of the modern day extensive database systems, and the need to be equipped with the knowledge of the various simple or complex database tuning metrics and tools. With the introduction of very expansive, powerful, and robust database systems like Oracle database 10g and Oracle database 11g that are incorporated with the principles of artificial and business intelligence, database tuning has become increasingly complex. The demand for business data to be highly available has advanced the need for database applications to have increasingly greater uptime requirement and are expected to handle larger numbers of transactions and users (Afyouni, 2004). Nonetheless, many other things need to be considered when tuning a database, and it’s normally the job of the database administrator (DBA) in conjunction with the system administrator to carryout these tasks. Their main objective is to ensure that the database has been designed in a way that best accommodates the expected activity within the database (Stephens, Plew, & Sams, 2003). This involves having a sound database tuning methodology in place and also being aware of how a well-optimized operating system, network, and hardware can greatly impact the availability, scalability, reliability, and performance of database systems (Afyouni, 2004).

Furthermore, a DBA might be concerned about resource-intensive applications and processes (programs that perform specific tasks) that interfere with database availability and response time. Applications that access the database include SQL and PL/SQL statements, OLTP transactions, among others. This concern usually stem from poorly written SQL statements (or queries that require extensive sorting), improper implementation of business rules, or poorly designed user interface and database schemas (with missing indexes) that consume a lot of system shared resources and thus, degrade its performance (Afyouni, 2004; Lee & Zildžić, 2006). Application tuning should involve working with developers to tune SQL statements and indexes in order to achieve different execution plans and data access patterns; and taking advantage of databases features, such as partitioning and parallel query (Whalen, 2005). Applications and processes that utilize a lot of CPU resources or other shared resources should be monitored and eventually eliminated.

Another concern is whether resources have been properly allocated to objects and the database instance, because improper object or memory sizing and capacity planning could cause serious I/O contentions and excessive I/O (read/writes) of disks that could be costly to the database operation and impact performance negatively. The correct memory size allocation depends on the nature of your application, the nature of users, and the size of transactions. If there isn’t enough memory, the application will have to perform time-consuming disk I/Os (Alapati, 2008; Lee & Zildžić, 2006).). For example, too much sizing leads to resource wasting while too little resource allocation causes contentions and performance degradation as users and sessions compete for shared resources. In addition, poor database instance configuration can cause disk thrashing or paging, which results in database performance degradation (Afyouni, 2004). Since the database instance is made up of memory structure (System Global Area (SGA)) and background processes, instance tuning should involve either adjusting the initialization parameters in the client-side configuration file (pfile) or the server-side parameter file (spfile), as well as proper database block sizing and capacity planning (Afyouni, 2004; Whalen, 2005).

Likewise, a DBA could be concerned about making sure that the database query response time and application response time are optimal (most favorable) because users are concerned about service efficiency. Different users often require different response time standard. For example, business intelligence (BI) users and financial analysts may require faster database query processing and transaction processing response time than ordinary users because they need to make business decision on demand. Therefore, in this case response time and database availability can be used to measure the effectiveness of a system in terms of user satisfaction, which can be measured in quantifiable response time for query processing or transaction processing. However, if the goal of the DBA is to ensure the effectiveness of the database operation for all users, then he/she may use resource utilization and throughput to measure that effectiveness (Donar, 2002). But sometimes a “DBA may focus too much of his or her time and effort towards improving response time to improve important system metrics such as hit ratios, average latencies, and wait times, only to find users are unable to perceive the difference. And an expensive hardware upgrade may not help either,” (Millsap & Holt, 2003). Therefore, the ultimate goal of the DBA in terms of managing end-user needs it to strike a proper balance between giving the user access to data that they need and still maintain the integrity of the overall data within the database system (Stephens, Plew, & Sams, 2003).

With corporations that have large global business networks, as a DBA, I would be concerned about computer network traffic generated by the system of insufficient bandwidth that is not capable of handing normal traffic demands (Afyouni, 2004). Networks propagate data to and from database servers. Therefore, database server configuration, logging, connection management, as well as operating system configuration (of computers that host the server) can greatly impact performance and network availability. This also includes media storage technologies, such as RAID configured disks, storage area networks (SAN), and network attached storage (NAS) that can significantly impact the availability, scalability, and performance. Network tuning means making sure that the network can handle the expected traffic as well as keeping a pool of connections open to handle future client requests (Afyouni, 2004).

Network tuning may also involve hardware tuning, which includes reconfiguring or adding new hardware to the existing database system to improve performance. This is necessary when the hardware resources, such as I/O subsystem and system RAM have been exhausted, and when efforts to tune the application and database instance to reduce resource use have not improved performance enough. However, this should not be the first step, but rather the last resort of tuning your database system (Whalen, 2005). Performance experts generally agree that performance problems are mainly caused by poorly conceived SQL statements and not by lack of server resources. However, it’s a common practice to lay the blame on the server when performance problem arises and to initiate costly and unnecessary hardware upgrades (Beaulieu, 2005).

DBAs must be mainly proactive instead of reactive to diagnose and resolve problems ranging from I/O contentions, scalability issues, problematic queries, sorting or parsing issues to resource management problems. This must begin from the inception of the database project especially from the design phase to the maintenance phase. Prior to the production environment, the DBA(s) must establish clear benchmarks to understand the load and throughput the database system can handle. Performing load test will not only determine the type of tests (CPU utilization, memory, I/O subsystem, locking) and how valuable the results are, but can also be used to validate several aspects of the application that a functional testing cannot validate (Whalen, 2005). Real-time and historical (collected metrics) analysis should be documented to provide the basis for determining and developing proper action plans and troubleshooting task list.

Depending on the different data structures and different transaction loads, database tuning can maximize the return on investment (ROI) on system hardware, software, and other application programs. As a result, minimizing the overall total cost of ownership (TCO) and providing users the maximum performance a database system can deliver, (Page, 1999).




References:

Afyouni, A. H. (2004). Oracle 9i Performance Tuning: Optimizing Database Productivity. Thompson Course Technolgy

Beaulieu, Alan. (2005). Learning SQL. O’Reilly.

Donar, T. (2002). Tru64 UNIX-Oracle9i cluster quick reference. HP Technologies

Lee, S. P. & Zildžić, D. (2006). Oracle Database Workload Performance Measurement and Tuning Toolkit: Issues in Informing Science and Information Technology Volume 3, 2006. Retrieved on September 29, 2009 from http://informingscience.org/proceedings/InSITE2006/IISITLee172.pdf

Millsap, C. V. & Holt, J. (2003). Optimizing Oracle performance. O’Reilly.

Page, W. G. (1999). Using Oracle8/8i, Special Edition. Chapter 20: Que Books.
Retrieved on September 28, 2009 from http://docs.rinet.ru/O8/index.htm

Piedad, F. & Hawkins, M. (2001). High Availability: Design, Techniques, and Processes. Prentice Hall PTR, New Jersey.

Powell, G. & McCullough-Dieter, C. (2007). Oracle 10g Database Administrator: Implementation & Administration. Thomson Course Tech.

Stephens, R., Plew, R., & Sams, A. J. (2003). Teach Yourself SQL in 24 Hours, 3rd Edition. Sams Publishing.

Alapati, S. R. (2008). Expert Oracle Database 11g Administration. Apress.

Whalen, Edward. (2005). Oracle Database 10g Linux administration. McGraw-Hill-Osborne/Oracle Press.