Karl Dias

Automatic SQL tuning in oracle 10g

SQL tuning is a very critical aspect of database performance tuning. It is an inherently complex activity requiring a high level of expertise in several domains: query optimization, to improve the execution plan selected by the query optimizer; access design, to identify missing access structures; and SQL design, to restructure and simplify the text of a badly written SQL statement. Furthermore, SQL tuning is a time consuming task due to the large volume and evolving nature of the SQL workload and its underlying data. n nIn this paper we present the new Automatic SQL Tuning feature of Oracle 10g. This technology is implemented as a core enhancement of the Oracle query optimizer and offers a comprehensive solution to the SQL tuning challenges mentioned above. Automatic SQL Tuning introduces the concept of SQL profiling to transparently improve execution plans. It also generates SQL tuning recommendations by performing cost-based access path and SQL structure what-if analyses. n nThis feature is exposed to the user through both graphical and command line interfaces. The Automatic SQL Tuning is an integral part of the Oracles framework for self-managing databases. The superiority of this new technology is demonstrated by comparing the results of Automatic SQL Tuning to manual tuning using a real customer workload.

Self-Tuning for SQL Performance in Oracle Database 11g

Commercial database customers across the board list SQL performance tuning as one of the most time-consuming tasks for database administrators (DBAs). The 10g Oracle Database provides a feature called the SQL Tuning Advisor to simplify the task. The 11g release adds a new database feature, called Automatic SQL Tuning, that closes the feedback loop for the first time, fully automating the SQL tuning workflow and solving some SQL performance problems without any DBA intervention.

Oracle database replay

This paper presents Oracle Database Replay, a novel approach to testing changes to the relational database management system component of an information system (software upgrades, hardware changes etc). Database Replay makes it possible to subject a test system to a real production system workload, which helps identify all potential problems before implementing the planned changes on the production system. Any interesting workload period of a production database system can be captured with minimal overhead. The captured workload can be used to drive a test system while maintaining the concurrency and load characteristics of the real production workload. Therefore, the test results using database replay can provide very high assurance in determining the impact of changes to a production system before applying these changes. This paper presents the architecture of Database Replay as well as experimental results that demonstrate its usefulness as testing methodology.

Real application testing with database replay

Oracle Database Replay provides a new way to test changes to a database system by reproducing the real user workload in a test environment. It helps to identify potential problems after software or hardware upgrades, patches, or changes to database parameters, schema or data. Any interesting workload period of a production database system can be captured with minimal overhead. The captured workload can be used to drive a test system while maintaining the concurrency and load characteristics of the real production workload. The replay does not depend on any other software including the application itself. It reliably reproduces the captured workload to support early diagnosis and troubleshooting. In this paper we discuss the analysis of replay results and introduce a new compare-period report, which assists with a detailed performance comparison of the capture and its replays with respect to changes. We demonstrate its usefulness in a case study involving an upgrade for a Siebel financial application, where Database Replay identifies performance problems after the upgrade and helps correcting them.