Academic journal article Issues in Informing Science & Information Technology

Oracle Database Workload Performance Measurement and Tuning Toolkit

Academic journal article Issues in Informing Science & Information Technology

Oracle Database Workload Performance Measurement and Tuning Toolkit

Article excerpt

Introduction

Regardless of IT environment-specific requirements for running mission-critical database-driven applications, the two basic requirements are always requested: firstly, high availability, in terms of database accessibility and resource availability; and secondly, a high performance, in terms of access and code efficiency (Silberschatz, Korth & Sudarshan, 2002). These database management systems, i.e. the most demanding systems with 24x7 availability (24 hours a day, 7 days a week), require regular or proactive maintenance practice in order to achieve and maintain the optimal database workload performance. Although Oracle, IBM, and Microsoft provide a solution for a high availability (or multi-node) computing environment, availability, scalability, and manageability among them differ significantly (Buch & Cheevers, 2002; Chandrasekaran, & Kehoe, 2003; MSDN, 2004).

Ultimately responsible for database tuning, the most inconvenient situation for a database administrator (DBA) is when users submit complaints about the database performance. Forced to act immediately (reactive tuning), for the DBA it is a critical task due to the fact that it often happens during the peak database activity. A common solution would be to optimize the SQL (Structured Query Language) statement's Execution Plan that improves the execution performance. However, performing the object optimization during the peak database activity shall decrease the overall performance, since the optimization process consumes resources (CPU and RAM), and may even bring some objects temporary unavailable. Identifying the cause of performance overhead is not an easy task. For example, knowing that excessive disk I/O operations may bring down the database performance up to 2500 times (Burleson, 2003), a DBA must have a real-time overview of major I/O users, as well as the most expensive user's SQL statements causing the slowdown. The root cause can be inaccurate SQL's Execution Plan, or unbalanced memory allocation due to inappropriate initialization of database, or because the database server has no more resources to handle the workload and requires the upgrade. In any case, the DBA must be able to assess the current database resource consumption and to proceed with investigation to discover the root of the problem and to solve the problem.

Based on conducted on-line survey and an interview with Oracle Support Manager (Zildzic 2005), we may conclude that database memory and index tuning practice needs an improvement. Since it is mainly conducted as a consequence of database performance overhead, i.e. when the user submits a complaint, a proactive monitoring of database performance should be implemented to anticipate performance leakage and ultimately avoid severe performance degradation.

The above considerations led to a custom developed monitoring and tuning tool which delivers a unique solution for the efficient and resource-effective Oracle database workload performance measurement and tuning. This tool, called Workload Performance Monitoring and Tuning (WPMT), is developed in Java. It can proactively and reactively be used during Oracle database tuning in order to measure the current workload performance of the main database components, anticipate resource shortage, and optimize database objects for the achievement of the optimal database performance (Zildzic, 2005).

Oracle's Monitoring and Tuning Tools

Effective data collection and analysis is essential for identifying and correcting system performance problems. Oracle provides tools, such as Statspack and Oracle Enterprise Manager (OEM) packs allowing a DBA to gather information on database performance (Metalink A87503-02, n.d.). These tuning tools, recommended by Oracle corporation and currently the most often used, use data dictionary views to capture database activity metrics. In this section, a part from Oracle Company designed tools, we shall assess the third-party tuning tool, i. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.