As more and more Oracle users upgrade their databases to Oracle 19c and beyond, posts on the Oracle community mailing lists about changes in query performance are increasing. In most cases, users are seeing improved performance; but, as Scotty said in Star Trek, “The more they tinker with the plumbing, the easier it is to stop up the drain”.
When problems do occur, they’re often obscure “edge cases” that are difficult to diagnose, and even more difficult to correct; especially if you’re using Oracle Standard Edition 2, where tools like Real Application Testing and other tuning aids are not available.
Fortunately, Quest has several tools that can be used to mitigate the risks of upgrading, and minimize the downtime required to perform the upgrade. Here’s a short summary those tools, and how they can be combined to make sure your database “plumbing” flows smoothly.
Get a baseline with Foglight
The first step in any journey is to establish your starting point. Quest’s Foglight for Databases makes it easy to establish and visualize your baseline and see any current performance issues. The graph below shows the baseline as a shaded background and current performance as the solid blue line. This is a great way to see how your database is performing before you begin any upgrade process. Like all of the tools we’ll be discussing, Foglight will run against any recent version of Oracle (12c or later) and any Edition (including SE2) without requiring additional tools or licenses from Oracle.
Capture your workload with Benchmark Factory
As a next step, you can use Benchmark Factory to capture the workload in your existing environment and then replay that workload in a test environment before you upgrade your production environment. Benchmark Factory also works with SE2 and all recent versions of Oracle, so there’s no need to purchase additional licenses from Oracle. You can easily visualize what happens as your user loads scale.
Compare old and new with Foglight
Once you have your test environment up and running, you can use the compare feature in Foglight to see the differences in performance.
If you see a decrease in performance, Foglight can also help you zero in on the SQL statements that are causing the bottlenecks
Once you’ve identified possible candidates for tuning, you can use Foglight to review the execution plan and see exactly what operations are consuming the most resources.
Tune your SQL with SQL Optimizer
Once you’ve identified a statement or statements that need tuning, you can use SQL Optimizer to examine different alternatives, and come up with a better plan. You can even test you SQL statements with different bind values to isolate possible optimizer issues.
Migrate your database with low risk and near zero downtime
Now that you’ve tested and tuned your database, for the migration itself, you can use Quest SharePlex to reduce risk and downtime.
Reduce downtime
Without SharePlex, you’d need to take your database down to copy it to your new environment. With a terabyte or petabyte database, that could mean hours or days of downtime for your critical applications. With SharePlex, your users can continue to use your old database while SharePlex captures transactions that occur after you make a consistent copy. SharePlex can then apply those transactions to your new database, and keep it synchronized with the old, so that when you’re ready to use the new database, it’s available and your users can just disconnect from the old system and connect to the new.
Reduce Risk
Using SharePlex also reduces risk in case something isn’t right after the migration and you need to “fall back” to your old environment. Transactions applied to the new database can be captured and retained. If you need to move back to your old environment, you can apply those transactions and avoid data loss.
Next steps
I hope you’ve found this information useful and that your Oracle migrations will all be smooth and painless. Please click the links below if you’d like more information or a free trial of the Quest products I’ve mentioned here.
SQL Optimizer (Part of Toad for Oracle)