With the sudden increase in data and its importance, database management systems have become an essential part of any organization, big or small. And in most cases, a DBA is deployed to set-up and maintain a database system. Post initial setup, a DBA spends most of the time monitoring the database. He also constantly works on optimizing the throughput.
But, running an analysis can be tough whenever there’s a change in action or execution. This could include any amendments in the SQL database such as upgrade or addition of indexes. Moreover, these processes can impact your SQL database performance. With the release of Oracle Database 11g, the Real Application Testing option was introduced.
A key feature of the Real Application testing is the SQL Performance Analyzer (SPA). It brought relief to the DBAs with its ability to cut down on the time and effort required to identify slow queries.
How does the SQL Performance Analyzer Work?
An SQL Performance Analyzer offers a granular view of how a change impacts the performance. An SQL performance Analyzer executes the SQL query in isolation and offers the before and after results. It then generates a report with a proper detailing of the effects of the statement on the workload.
In case of a regressive SQL statement, the Analyzer identifies it and repairs it before the execution of the changes to the database such as migration, upgradation or new index introduction. Moreover, running SPA prior to running the database replay feature is one of the best practices which save a lot of time and effort. The main aim is to identify and repair any regression. For this purpose, SPAs make use of the already present SQL Tuning Sets (STS). An STS contains predefined statements, execution information, and execution plans from the workload.
Where is a SQL Performance Analyzer used?
Some common scenarios which need an SQL Performance Analyzer are mentioned below. These are scenarios when changes are made to the database, potentially affecting its performance.
Database upgradation: Whenever a newer version of the database is installed, it leads to the installation of a new optimizer. This affects the system performance. The analyzer is then used to create the before and after view, pointing out to any regressions. This enables the DBA to re-write the required SQL queries prior to the change.
Hardware and Software change: At times, the system is upgraded, meaning additional CPUs or memory. Additionally, new operating systems can be installed. These changes to the system affect the SQL performance. In such scenarios, an SQL Performance Analyzer plays a key role in identifying any deterioration.
Patch Deployment: If a patch to fix issues with the performance is being deployed, it is advisable to run it through the SQL performance analyzer. It checks your production for any errors which might cause regression post-implementation to the workload.
Optimizer Statistics: The plans generated by the system rely heavily on the optimizers’ decisions. These decisions, in return, are affected by Optimizer statistics. Whenever new statistics are collected and the existing refreshed, it can cause an impact on the SQL performance. Hence, preventing any regressions the analyzer keeps a check on the statistics.
Changes to Schema: Whenever a database undergoes schema changes such as addition or change in the index, it will inevitably affect the SQL performance. As this can lead to deterioration of performance, it is important for the SQL Performance Analyzer to keep a check on the same.
The SQL Performance Analyzer process
The execution process of an SPA is very simple. After capturing the SQL workload, and storing it in an SQL test set, the DBA will set up the test target. This is where the analyzer comes into the picture.
An SQL Performance Analyzer task is then assigned to the test system. Using this, the DBA creates a pre-change task followed by the implementation of the change. Once the change has been implemented, a post-change task is created.
The two tasks are compared for any regressive queries or changes which might affect the performance of the SQL system. The analysis will guide the DBA on the optimization process. The process is repeated until the required performance statistics are obtained.
I have been working for almost 2 decades on Database optimizations. I can help you optimize your database to make your application perform better and save costs. Contact me to know more about how I can help you.
Eric Vanier
Database PerformanceTechnical Blog Writer - I love Data
