Real Application Testing – SPA

Lately I took part in Oracle Real Application Testing Workshop managed by Oracle product management team.
I’d like to tell you a little bit about the feature beyond the introduction I posted before here.
In the previous post I introduced the SPA and DB Replay options of the feature called Real Application Testing (RAT). I’d like to dive dipper into these options and introduce other parts of the feature: SPA Quick Check and DB Replay Concurrent. In this post I’ll start with SPA.
As I mentioned in the previous post, the SPA is something like SQL statement “unit testing”. Its goal is to answer the question: “How does a change I plan to do will affect the SQL statements in my database?”
Note that only certain changes can be relevant to this question, and the SPA will only identify some of the consequences made by these changes. This is a very important thing to remember.
The SPA concept is to choose a set of SQL statements and run them one by one on the environment before and after the relevant change. This way we can identify specific statements that got better or worse following the change. After identifying these statements, we can review and fix them (or the change) and eventually have a better and much more stable change in the production than we ever had before.
What changes can be relevant? Quite a few, such as:

• Upgrades and patch installation
• Gathering optimizer statistics
• Schema changes (adding or removing indexes, columns, etc.)
• Changing initialization parameter
• Using tuning features such as SQL Profiles
• Hardware and OS changes

How does it work?

As I explained, the idea is to choose a set of SQL statements and run them one by one. In order to do so, this is what we are going to do:

1.      Creating STS

STS, or SQL Tuning Set, is a database object containing:

• SQL Statements (queries, DML, etc.) including the full text of the operation.
• Session information such as schema, module and action.
• Bind variables values
• Execution statistics: execution time, CPU time, buffer gets, I/O, affected rows, execute count and more.
• Execution plan and relevant statistics.

In order the run SPA we need to create an STS that contains all the SQL statements we would like to check. We can create such STS in several ways:

• From the AWR we can create STS that contains the top statements. Note that using this method we will probably miss statements on one hand, but on the other hand, we will have the most important, heaviest and most used queries easily.
• From the cache – using this method we sample the Shared Pool periodically (the default is 5 minutes) in order to gather all the SQL statements. This is a recommended and efficient method. The chance to have all or most SQL statements is quite high and the overhead caused by this process is low (1%-2%).
• SQL trace – there is also a way to create STS from statements in SQL trace files. The reason to do this is cases where we want to have STS from old environments (9i or before) or Oracle Standard Edition, both don’t have the STS feature. The down side is, of course, the massive overhead (might be even 30%) and the disk space the trace files use.

Another important thing to remember is special processes, such as nightly jobs, end of week/month/quarter/year processes, etc. We would like to add them to our STS as well, but wouldn’t want to add a cache sampling that will run every 5 minutes for an entire month. What we can do is to create several different STSs. One for a regular day, one for night jobs, one for weekend processes, etc. At the end we can consolidate the different STSs to one large STS and use it for the SPA.

2.      Copying to the Test Environment

Eventually, we will run the SPA in a test environment, so we need to copy the STS to it. Copying the STS can be done by exporting it to a staging table using DBMS_SQLTUNE, then move the table to the test database (using Data Pump for example) and import the STS from the staging table to the database.

3.      First Run on the Test Environment

The first run of the SPA on the test environment should be identical as possible to the production environment and will be performed before making any change we plan to do. The idea is to create a baseline to which we will be able to compare all subsequent runs (after the changes).

4.      Perform the Change

At this stage we will perform the desired change, patch installation, index creation, parameter change or anything else.

5.      Additional Run on the Test

After the change we will run the STS again in order to see the difference between the runs.

6.      Comparison

Now we can use the SPA to compare the runs before and after the change. The SPA report contains a lot of information about the differences between the runs, including SQL changes in execution time, CPU time, I/O access, memory access, number of rows returned etc.
Performance degradation is defined in percentage of execution time. We have the ability to control the percentage itself, while the default is 1% (meaning, if a query took 100 seconds, it will be considered as degradation if after the change it will take more than 101 seconds, but not 100.5).

7.      Additional Iterations

After the comparison and finding problems, we can investigate them, fix them and then try again as many times as we would like until we achieve a good level of stability. Only then we can be confident enough to make the change in the production environment.

Limitations and Comments

• For SPA to be effective, the test environment should be identical to the production, hardware, software versions, database structure and database data. If the environments are not identical, we can still compare the SQL statements before and after the changes, but in the real production things might be different.
• If the application uses bind variables, the SPA will run only one set of variables, not all the bind variable that were executed in the production.
• Procedures, functions, packages and other PL/SQL code are not running as part of the SPA. The SPA will run all the standard SQL statements that were executed as part of the code, but not the PL/SQL code itself.
• If the application changes session parameters when connecting, these changes will not run as part of the SPA, we will need to perform the changes manually before running the SPA.
• The SPA does not execute DML and DDL operations. Queries that are part of the DML and DDL will run (delete from tab1 where x=1 will run the relevant select where x=1 but will not delete any data). There is an option to allow DML operations, in this case the DMLs will be executed but the SPA will rollback the transaction after it completes. We cannot run DDLs as part of SPA and there is no way to commit the changes.
• The SPA is available from Oracle 11g. Creating an STS from 9i and below can only be done by using SQL trace as I explained above. SPA on Oracle 10g can be done by creating an SPA environment on Oracle 11g or 12c and running all the processes remotely on the 10g (the actual SPA logic is done in the 11g or 12c database, and the SQL statements are executed on the 10g using remote execution).

SPA Quick Check

A new feature in Oracle 12c is the SPA Quick Check. The concept of this feature is to test a change we are going to do on the production locally. The difference between SPA and SPA Quick Check is that the SPA is running on a test environment and tests every change we are planning to do. We use the Quick Check only before “local” changes on the production itself. Such changes can be creating an index, changing a parameter, etc. But not patch installation or any other “big” changes.
SPA Quick Check uses existing features such as optimizer pending statistics, invisible indexes, session parameters, etc. With these features, we are performing a change the production environment, while SPA Quick Check is the only one that can see the change (for example, by creating an invisible index, while only the SPA Quick Check can use it). Then the SPA Quick Check runs the STS on this session. After the run is completed we can compare the “before” and “after” runs just like in the regular SPA. Once we verify that the SQL statements look OK we just apply the change to the entire system.
There are, of course, implications on the production with this process, but it’s a change we are going to do anyway. We just take a little bit more precaution and do it more responsibly.
Advantages:

• Designed to run on production
• The entire process runs in the SPA Quick Check scope, other users are not influenced
• Compares only execution plan and executes only SQL that their plan have changed
• Optionally compares only execution plans without executing at all
• Uses resource manager to limit the load it performs on the production
• Does not execute DML and DDL operations

Summary

In this post I explained about the SPA which is only half of RAT. As you can see, this tool is very powerful and can be helpful in many cases.
Hope you enjoyed, next time I’ll cover the other half of RAT, Database Replay.
Liron