In Oracle 12c, Real application testing uses to Oracle utilities Database Replay and SQL Performance Analyzer (SPA).   These two utilities allow an administrator to capture application traffic into a database then replay the activities while taking analysis of the SQL acting upon the incoming traffic.    Though an iterative processes of SQL analysis we are able to optimize both the PL/SQL and other database structures for optimal performance.  This article gives a brief overview of these utilities.

 

 

Database replay captures all external database calls made to system during capture period. This capture can be replayed against test system for the purpose of testing new software, evaluating bug fixes, or optimization of current code.   The capture includes all relevant information about the client request, such as SQL text bind values, transaction information.  Background activities and scheduler jobs are not captured.   Additional client requests that are not captured are:

 

1. Direct path load SQL*Loader.
2. Shared Server requests (MTS)
3. Oracle Streams.
4. Advance Replication streams.
5. NON-PL/SQL based advance queuing(AQ)
6. Oracle Call Interface (OCI) based object navigation.
7. NON SQL-based object access.
8. Distributed transactions.

 

 

 

 

SQL Performance Analyzer (SPA) allows you to evaluate a set of SQL statements against known workloads.  This set of SQL statements with execution statistics and execution context is known as an SQL Tuning set.    A single SQL Tuning set can be adjusted with updated SQL statements to test various evolutions of the same process.   Additionally, you can use cursor cache and AWR snapshots to track performance.  Using SQL TUNING SET enables you to:

 

1. Store SQL text and auxiliary info in single persistent database objects.
2. Capture Insert, update, delete, and select SQL statements in the SQL TUNING SET.
3. Load and merge content from different data sources like AWR and cursor cache.
4. Export SQL TUNING SET where SQL workload is captured and import into other systems.
5. Reuse SQL workload as input source for other advisors like SQL Tuning Advisor and SQL Access Advisor.

 

The following Views relate to SPA.

DBA_ADVISOR_TASKS – Info about SPA tasks created.

DBA_ADVISOR_EXECUTIONS – info about SPA tasks executed.

DBA_ADVISOR_FINDINGS – info about SPA finding, generate the following reports:

• Performance regressions problems.
• Changing structure of execution plan.
• Errors like non-existence of objects.
• Info messages.

DBA_ADVISOR_SQLPLANS – list of execution plans.

DBA_ADVISOR_SQLSTATS – list of SQWL compilations and execution statistics.

V$ADVISOR_PROGRESS – displays operation progress of SPA.

 

 

 

Larry Catt

OCP