Select Page

12c Upgrade Bug with SQL Tuning Advisor

Author: Megan Elphingstone | | March 22, 2017

Recently, after an upgrade from 11.2 to 12.1, we ran across some performance problems. Eventually we found that the SQL Tuning Advisor was missing. The cause was BUG 20540751.

Oracle offers both a patch and a work around. If you find this as part of your upgrade testing, before your production upgrade, the patch number is 20540751 and it can be applied as part of your upgrade outage window. If you are seeing performance problems after an upgrade, check for this bug by running the SQL below and apply the work around.

There is nothing in the upgrade log to let you know you have hit this bug, and it’s not clear what causes it to appear. It wouldn’t hurt to add a step to your post upgrade checklist to make sure the SQL Tuning Advisor job still exists.

Per ORACLE:

Bug  20540751 – AUTO TASK SQL TUNING ADVISOR ENABLED BUT PROGRAM NOT RUNNING AFTER 12C UPGRADE. This affects versions 12.1.0.1 and 12.1.0.2.

If you have recently upgraded and are experiencing performance problems check to see if your SQL Tuning Advisor job is missing by running this:

SQL > select program_name, program_action from dba_scheduler_programs where program_name = 'AUTO_SQL_TUNING_PROG';

no rows selected

If you see “no rows selected” then you have hit this bug.

The workaround from Oracle is to run:

@?/rdbms/admin/execsqlt.sql

to create the scheduler program that was dropped.

See Oracle Support Note: Doc ID 2171154.1 for more info, go to: https://support.oracle.com/

For additional resources please see our white paper: Upgrading to Oracle 12c.

Datavail Script Terms & Conditions:

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

  1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.
  2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
  3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.
  4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT. You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.
  5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

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.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS