The key to a successful upgrade is to test, tune, test and repeat. Once you have read the release notes, planned changes to the optimizer, verified your application is certified on your new Oracle release, and tested thoroughly you’ll be ready to upgrade.
But there are also many precautions that can be put in place ahead of time to ensure a successful upgrade, and put you in a good place for troubleshooting problems if they arise. The biggest fear with an upgrade is that SQL that was working before will become slower. With any upgrade, some plans will be faster and some will be slower, and the key to avoiding decreased speed is to test thoroughly before upgrading. However, be prepared by installing the following five Oracle programs and familiarizing yourself with how they work, so you can do proactive tuning, and quickly troubleshoot problems.
OSWatcher – Operating system monitoring
SQLHC – SQL Health Check Script
SQLT – SQLTEXPLAIN – Focus on problem SQL
TFA – Trace File Analyzer
SPM – SQL Plan Management
Overview: OSWatcher collects and stores operating system performance metrics. It consists of two components, a Unix shell script called oswbb which collects and stores the data and oswbba which is a java utility that can be used to analyze the data and provide advice. Oracle recommends installing OSWatcher on all servers, and all nodes in a RAC environment.
OSWatcher is available from Oracle Support either as a standalone or as part of the Trace File Analyzer Package (TFA) which will be covered below.
Documentation and Installation instructions: Find the OSWatcher Users Guide (301137.1) on Oracle Support.
Proactive: Review the output to look for CPU spikes, memory usage, network issues and I/O issues.
Reactive: By default, 48 hours of data are stored by OSWatcher. During or after a performance problem, outage, or node eviction, use the information stored by OSWatcher to look for problems with CPU, memory, I/O or the network. OSWatcher files can be added to an Oracle SR for diagnoses as well.
Caveats: OSWatcher runs only on AIX, Solaris, HP-UX and Linux.
SQL Health Check (SQLHC)
Overview: SQLHC checks elements that may affect the performance of the SQL being analyzed, such as Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters. SQLHC is a subset of SQLT, which we will discuss next. This script is a quick way to diagnose problems without having SQLT installed.
Documentation and Install:SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
Proactive: Use this script to examine SQL that is not running up to standards while testing an upgrade.
Reactive: Use this script to get recommendations on problem SQL after an upgrade.
Caveats: SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems.
Overview: SQLT is used to diagnose the cause of poor performance of a specific SQL statement. and provides diagnostics to performance.
Documentation and Installation: All About the SQLT Diagnostic Tool (Doc ID 215187.1)
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
Proactive – Use the SQLT tool to proactively tune the queries that cause the biggest load on your system to prevent problems.
Reactive – Use SQLT to tune a query that is causing production problems.
Caveat – Unlike with SQLHC, this does have a database footprint. It should be installed in test, and tested, before installing in production. That’s why it is essential to have this in place before a problem arises so you can immediately start troubleshooting.
Trace File Analyzer (TFA)
Overview: Starting with version 18.104.22.168, TFA is included with the database installation. However, the initial install may not include all the tools, or be up to date. Before an upgrade, make sure you have the latest TFA version installed. TFA collects real-time data, and it allows you to analyze the data. TFA will also package logs and trace files to be added to an SR. TFA runs on all supported versions of Oracle Database and Clusterware and all supported operating systems.
Documentation and Installation: TFA with Database Support Tools Bundle (Doc ID 1513912.1)
Proactive: TFA includes ORAchk, which is a health check that can be used to check the database health before there is a performance problem.
Reactive: TFA can be configured to send email alerts. DBAs can resolve the issue if they are able, or upload the information to Oracle Support with an SR.
SQL Plan Manager (SPM)
Overview: SQL Plan Management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. DBAs can review the sets of plans and chose the best plan for their performance needs.
Documentation and Installation: Using SQL Plan Management (SPM) (Doc ID 1905305.1)
Master Note: Plan Stability Features (Including SQL Plan Management (SPM)) (Doc ID 1359841.1)
Proactive and Reactive scenarios for SPM usage are the same, the question is just whether you find the problem while testing before an upgrade, or afterwards. SPM can improve or preserve a plan in the case the plan has gotten worse due to a database upgrade, system or data changes, or an application upgrade.
Proactive: Use SPM to look at the top queries (most read/writes, highest executions, longest running, highest CPU usage) to choose a plan that works best for that query.
Reactive: After an upgrade if a query has slowed down, you can set the plan to a prior plan that worked well.
Caveats: SPM is included with Enterprise Edition
Contact Datavail for more help with upgrades and troubleshooting your Oracle database. Datavail is a specialized IT services company focused on Data Management with solutions in BI/DW, analytics, database administration, custom application development, and enterprise applications. We provide both professional and managed services delivered via our global delivery model, focused on Microsoft, Oracle and other leading technologies.
For additional resources please see our white papers.
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.