ADB Migration: Why does my SPA report has so many errors?

I’m dealing lately with several migration to Oracle Autonomous Database, mostly to ADB Serverless (called “shared” a while ago, in short: ADB-S). With one of the customers we run SQL Performance Analyzer reports with about 1500 statements. But one topic came up in this ADB Migration: Why does my SPA report has so many errors? Let’s see why that is …

Photo by Weichao Deng on Unsplash

A little bit of background on SPA

The SQL Performance Analyzer (SPA) is one of the two pieces of Real Application Testing. This is a feature in the database I keep using since Oracle 11.1.0.7. I actually played with it in my presales days two decades ago – but since then it has nicely evolved. And while the 2nd component, Database Capture/Replay is also very promising, it is much harder to use it in real world projects. SPA is easier. And it is a simulation which actually reads data. But it is non-destructive as a Replay run will be since the Replay will change your data.

We use SPA reports in our Hands-On Lab environment, the Hitchhiker’s Guide for Upgrading to Oracle Database 23ai. You can play with it there. And it is an integral part of our Performance Stability Prescription. You can also get a quick overview at a glance in our Virtual Classroom Seminar #3.

The great thing with SPA is generally that you can trigger another simulation quickly and easily. As I wrote before, it will read actual data, and it will execute each statement 10x typically, the first time to warm up the buffer cache, and then 9x to build an average from it. Once you adjusted something in the setup or you fixed statements with SQL Plan Management or a SQL Tuning Advisor’s Profile, you can just run the simulation again and see the result.

The only caveat often is the fact that Real Application Testing requires an extra license on-prem. Which not everybody has included. But the good thing with a migration to Oracle Autonomous Database is: It is included, no need to pay extra for it. Just use it.

Where do you start with SPA?

Most of the SPA documentation is strictly Oracle Enterprise Manager driven. But I am an old-fashioned command line guy, and often the command line gets me in this case faster to the results I want to see. Hence, you will find some SPA scripts on this blog – feel free to copy/modify/extend them for your own purpose. They all start with spa_* in the name.

In short, for a SPA run as we use it in such projects you will collect statements on your source, either from AWR or – in case you have OLTP workloads – directly from the cursor cache. In the scripts section of the blog you will find two scripts, capture_awr.sql and capture_cc.sql to do this for you.

Once you have your statements collected on the source, you need to put them into a staging table, then export and import the table and unpack it. Now you moved the “information from before“, and you can simulate it and compare it to the results of the simulation. Sounds simple? It is actually that simple.

Luckily, my German colleague Ulrike Schwinn has written a wonderful blog post showing you the entire path with very useful examples. This blog post is an excellent starting point. And I shared it with several customers already. All the scripts Ulrike used are in her GIT.

What may be your result?

We typically are interested in the Regressed Statements report. Those are the misbehaving statements, the ones which took longer to execute. And I show you the overview header here from a customer project:

SQL Performance Analyzer – Regressed Statements – report header

So, you see that 56 statements are regressed. We know how to deal with those. Hence, this isn’t what I want to cover her. I was more wondering about the 311 statements with Errors. That’s quite a lot of statements giving errors.

I felt a bit clueless and asked the customer to generate a report on the tuning task but now showing the statements with errors. Since the normal report covered only the regressed statements, the statements with errors weren’t included.

Fetching the SPA Report with statements with Errors

You can do this simply by using this procedure:

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

set echo on
column filename new_val filename
select 'connection_low_errors_' || to_char(sysdate, 'yyyymmddhh24miss' ) || '.html' filename from dual;

spool &filename

set echo off
set feedback off

SELECT DBMS_SQLPA.report_analysis_task(
  task_name => 'AWR_TASK_1',
  type      => 'HTML',
  level     => 'ERRORS',
  section   => 'ALL',
  execution_name => 'EXEC_1978'
  )
FROM   dual;

SPOOL OFF

The tuning task (here with task name AWR_TASK_1 and the execution name EXEC_1978 – both can be found in the SPA report’s header as well) was still in the system since we didn’t delete it. Perfect.

Here is the overview on errors:

SPA Error Report – Overview

Then you are getting also an overview on the individual statements in the same report:

SPA Error Report – Top 100 SQL statements

I started checking some of the statements. And then it dawned me …

It was so obvious, and I didn’t see it

Why did we see so many errors? It was so obvious, but I didn’t see it – until I checked the with Errors report.

With a migration to Oracle Autonomous Database Serverless you usually have to make changes and adjustments. This is credited to the locked down architecture in ADB-S but also to the fact that some things require change, and may it be only the change into a different user schema because you don’t have access to SYS or SYSTEM.

Now, in this project the customer had to do plenty of changes. Those weren’t just a user defined password-verify-function or an external library but also layout changes. So, when we collect now the statements on the source side, which in this case is an 11.2.0.4 database, and then move these 1500 statements in a SQL Tuning Set over to the ADB-S instance to simulate them there, you can guess what happens.

Since the layout is different, some of the statements have no chance to succeed – they simply don’t work anymore as before. This is obvious when I see it in hindsight. I should have known that. But it didn’t come to my mind when I saw the high error count.

So, in this case, we hit the limits of a simulation. And this isn’t just applying to ADB migrations. It could be any migration project where you apply structural and layout changes.

You just should be aware of it. And I will in the future 🙂

Further Links and Information

• Hitchhiker’s Guide for Upgrading to Oracle Database 23ai
• [1:14:23] VC#3: Performance Stability Perscription: SQL Performance Analyzer
• SQL Performance Analyzer scripts
• Smooth Transition to Oracle Autonomous Database using SPA
• SPA scripts written by Ulrike Schwinn are a good starting point

–Mike