Oracle Database Tuning Plan - Report Example

Oracle Database Tuning Plan Name Instructor Institution Oracle Database Tuning Plan The efficient performance of any database requires continuous monitoring and regular maintenance. The database administrator is tasked with ensuring that the database management system (DBMS) serves up data in a most efficient manner especially in complex application environments. Database tuning involves the group of activities aimed at optimizing and homogenizing the performance of a database in terms of execution time and throughput. Performance tuning is aimed at identification and reacting to performance problems, preventing this problems from occurring in future, meeting service levels and requirements, optimizing usage of available and avoiding upgrades, and ensuring the application meets organizational and customer needs (Powell, 2007, p.27). Performance tuning for Oracle DBMS’s requires adequate planning and development of a strategy for optimizing various aspects of the database. The Reason and Scope of Tuning Database performance tuning is necessitated by existing bottlenecks that require making appropriate changes in the configuration of the database to reduce or eliminate. Performance bottlenecks can occur suddenly requiring a reactive real-time approach to tuning, or they can develop over time thus requiring a methodical and analytic al approach utilizing historical information in finding the problem and its source, and resolving the issue. Tuning is mostly done as a reaction response to a problem either before or after the database system has been deployed. In tuning the database, a database administrator must come up with a strategy that outlines the performance tuning methodology that is supported by the software making up the DBMS. The methodology for tuning an Oracle database system involves detecting, diagnosing and resolving issues in the DBMS. The major and most common problem encountered with the DBMS when serving up stored data is poorly tuned application code for SQL and PL/SQL. Other problems include contention for available internal Oracle database resources including locks, latches and buffers, I/O bottlenecks at the logical, data file and disk levels, and inadequate hardware resources in terms of disk space, CPU and RAM. Tuning of the database encompasses management of complexities in the software applications, computer systems, disk storage, I/O, users and jobs, and network factors as they affect the Oracle database engine. Recommended Tuning Steps to Achieve the Best Database System Performance The steps in planning for the tuning process are profiling each database instance, setting a baseline for each instance and setting objective goals for the tuning effort (Schneider, 2005, p. 25). Detecting the problem involves reacting to immediate performance problems and referencing historical data on performance trends. The diagnosis step involves drilling into historical and real-time data to find the root cause of the problem, and listing and evaluating tuning options, and choosing the most appropriate based on the particular benefits of each. Resolving the problem involves implementation of the tuning option selected, measurement of performance after applying the option, and repeating the diagnosis and resolving steps to find any new issues. 1. Planning for the Tuning Process The planning step begins with profiling each database instance to understand what type of applications that each instance supports. Different applications for databases will have different goals depending on the application environment in terms of on-line transaction processing (OLTP) versus online application processing (OLAP) environment requirements. This results in differences in the tuning requirements, therefore necessitating different approaches to performing the tuning process (Consens, Ioannidou, LeFevre, Polyzotis, 2012, p. 21). Planning for database tuning requires setting a performance baseline for each instance to determine the current performance of the database in relation to the required performance level. The set baseline determines whether the current performance is acceptable or unacceptable in terms of the set goals and requirements for the database. The baseline provides a starting point for the tuning process and a reference point for evaluating how tuning efforts affect performance. Planning also requires setting of goals for the tuning effort depending on the requirements for tuning, either reactively solving existing immediate problems, proactive prevention of future problems, improving database availability and service levels, or reducing user response time. 2. Problem Detection In reactively dealing with immediate performance problems, one needs a mechanism for determining where in the database environment the problems are occurring. Detection looks for and tracks performance metrics that may have been exceeded, and seeks to understand why and when a particular threshold was exceeded along with tracking the trend, interval and frequency of these occurrences. These may be issues with the operating system (OS), the Oracle database itself, or some SQL statements that require tuning. Issues that may arise with the OS affecting the DBMS are I/O problems including disk time, disk reads and writes per second, disk queuing and file space usage, and CPU issues with percentage of the processor busy time and process interrupts (Shasha, Bonnet, 2007, p. 50). Other issues in the OS include memory availability, page swaps per second, swap time and memory queues, and network utilizations and collisions. Metrics for the SQL collection and transactions are SGA information at sub-second intervals on SQL text, OS and Oracle user, program of origin, logical and reads, and CPU usage. Database metrics to be considered are obtained from the V$ tables and Oracle alert logs on the SQL*Net statistics about active users and sessions, and average response time. Other database metrics are background processes for the redo log and DBMS writers, and archive, SGA data on utilization, hit ratio for the buffer cache, usage of the keep and recycle pool, usage of the redo buffer and shared pool, and sorts. In addition, database metrics on I/O information on redo log statistics, I/O and wait events, locks, latches, file and object growth, and alert log messages need to be analyzed. For problem detection purpose, monitoring software is available alongside handwritten scripts and OS commands that scan the database and computer system for errors and exceeded thresholds that might be causing the problem. In proactive tuning, historical information from different timeframes is referenced and performance trends evaluated to ensure there is no progressive decline in performance that may eventually reach unacceptable levels. This involves manual or automatic creation of a historical repository where performance logs and monitoring script results may be stored. 3. Problem Diagnosis In diagnosing existing or potential problems with the database, the database administrator uses historical and real-time information. This provides additional metrics other than the observable problem in determining the root cause of the performance problem. For instance, a problem with the buffer cache may be the result of one of the users running a process that takes long to complete, consuming a large portion of the buffer cache. Once the problem has been diagnosed, a list of available options for dealing with the problem is considered to determine the tuning procedure that would have the biggest benefit for the database. Along with this, other minor tuning procedures that would benefit other aspects of the database not related to the problem may be outlined and considered. The three main diagnostics categories are SQL issues, contention and I/O problems. Diagnosis of SQL issues may take any of several ways, but should begin with examination of the V$SQL view showing all cached SQL statements (Powell, 2007, p.117). SQL statements consuming a large portion of buffer gets and those with a high buffer gets and execution ratio should be identified. Explain Plan and analyze statistics are used for diagnosing SQL statement syntax and for determining whether the optimizer chose the most efficient path for the SQL statements. TKPROF is used to identify operations with a high “rowcount”. In diagnosing contention, the V$System Event table is examined for Events, Total waits, Total timeouts, Time waited, Average wait, and idle events. Diagnosing I/O issues involves identifying the disks with disproportionally heavy activity in terms of reads and writes. Within these disks, files and tables experiencing the most activity are moved to less active disks. Data collection utilities including utlbstat and utlestat by Oracle and UNIX iostat are used to collect information pinpointing the root cause of excessive I/O. 4. Resolving the Problem After selecting the tuning option, it is then implemented beginning with solving the immediate issues that may be affecting users for reactive tuning. Proactive tuning would implement the tuning option that would have the highest benefits in achievement of the goals defined in the planning phase. Oracle tuning encompasses tuning at the OS level, database parameter level, user, space and object levels, and individual SQL statements’ tuning focusing on resolving specific issues detected and thoroughly diagnosed. Operating system tuning focuses on CPU cycles in relation to run-queues, available RAM memory especially for data buffers and in-memory sorts, network monitoring and management, monitoring and managing disk I/O for object access and RAID configuration (Shasha, Bonnet, 2007, p. 57). Database tuning focuses on initialization parameter settings for specific use cases depending on the version of Oracle being run, and space management for reorganization of tables to minimize or remove extent fragments, rebuilding the freelist chain, changing object parameters and re-sequencing table rows. Database management would also involve I/O optimization for the size of the buffer cache and spreading the I/O evenly across available disks. SQL tuning focuses on either rewriting the SQL statements and creating an index that changes the pattern for that statement, index tuning reducing full table scans and utilizing unused indexes, and user and object usage patterns and settings (Niemiec, 2007, p. 37). Measurement of performance after implementing the tuning option is necessary in assessing the impact of the tuning option on overall database performance. Measurement validates the tuning solution applied in terms of successful improvement of the Oracle database instance. If the solution is determined to be successful, measurement should be conducted with the aim of setting a new baseline that would enable evaluation of subsequent performance issues. Conclusion The steps of detecting, diagnosing and resolving should be repeated and scheduled for periodic evaluation of performance and emerging problems since tuning is not a none-time exercise but a constant requirement for keeping the DBMS at optimum performance. The Oracle database has many advantages over other database management systems (DBMS) in terms of adaptability, level of configurability of the relational database engine, scalability and customer support. However, tuning an Oracle database requires adequate prior planning and specialized skill since it can be quite a daunting task. Quest Software package by Oracle contains Quest Central, a database management tool that simplifies and facilitates execution of an effective performance tuning methodology for Oracle DBMS (Niemiec, 2012, p. 15). The application enables database administration and improves performance and availability through real-time and historical performance diagnostics, automating some common database administration tasks, SQL tuning and Space management. References Powell, G. (2007). Oracle Performance Tuning for 10gR2, 2nd ed. Burlington, MA: Elsevier Digital Press Shasha, D., Bonnet, P., (2007). Database Tuning – Principles, Experiments and Troubleshooting Techniques. San Francisco, CA: Morgan Kaupmann Publishers Chan, I. (2008). Oracle Database Performance Tuning Guide, 10g, Release 2, No.10.2. Redwood, CA: Oracle Corporation Schneider, R., (2005). MySQL Database Design and Tuning. Upper Saddle River, New Jersey: Pearson Education Zhang, M., (2008). Using Economic Models to Tune Resource Allocations in Database Management Systems. Ontario, Canada: Queen’s University Stephens, R. Plew, R., (2001). Database Design. Indianapolis, Indiana: Sams Publishing Consens, M., Ioannidou, K., LeFevre, J., Polyzotis, N., (2012). “Divergent Physical Design Tuning for Replicated Databases”, Annual ACM SIGMOD Conference 2012, 20-24. Scottsdale, Arizona: Association for Computing Machinery’s Special Interest Group on management of Data Niemiec, R., (2007). Oracle Database 10g Performance Tuning Tips and Techniques. New York, NY: McGraw-Hill Companies, Inc. Duan, S., Thummala, V., Babu, S., (2009). “Tuning Database Configuration Parameters with iTuned”, The VLDB Endowment Journal, 2009, 24-28. Lyon, France: VLDB, Springer-Verlag Niemiec, R., (2012). Oracle Database 11g Release 2 Performance Tuning Tips and Techniques. New York, NY: McGraw-Hill Companies, Inc. Read More