Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

17 December 2015 6 Comments Written by Christian Antognini

UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0.

UPDATE 2016-11-08: The patch related to bug 19450314 fixes the issue in case of ADD column. What it doesn’t fix are similar issues in case of MODIFY, RENAME and DROP column. Have a look to bug 21418655 (MORE INVALIDATIONS WORK IN 12C) in MOS for additional information.


The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

What is new in 12c is that a SQL plan directive can instruct DBMS_STATS to create an extension without the intervention of a developer or DBA. In other words, automatically and almost at any time (depending on how you gather the object statistics). Since such an operation can invalidate packages that depend on the altered table, in some situations that might cause problems to systems that must be available 24×7. Hence, you have to be aware of it and, if necessary, make sure that you are not impacted by it.

Independently on how an extension is created, someone might ask why invalidations can take place. In fact, because of fine-grained dependencies, adding a hidden virtual column to a table should not require an invalidation of the objects that depend on the table. However, in practice, this is not always the case. (Honestly, I do not know whether is an expected behavior or a bug.) An example is provided by the following package:

CREATE OR REPLACE PACKAGE p1 AS
 PROCEDURE p;
END p1;
/
 
CREATE OR REPLACE PACKAGE BODY p1 AS
 PROCEDURE p IS
   c NUMBER;
 BEGIN
   SELECT count(1) INTO c 
   FROM t;
 END p;
END p1;
/

CREATE OR REPLACE PACKAGE p1 AS PROCEDURE p; END p1; / CREATE OR REPLACE PACKAGE BODY p1 AS PROCEDURE p IS c NUMBER; BEGIN SELECT count(1) INTO c FROM t; END p; END p1; /

Since the package uses a “count(1)”, when an extension is created on the table T, the package body is invalidated. Note that using a “count(1)” instead of a “count(*)” is an old trick that is presently useless for improving the performance! In fact, the optimizer has a transformation that rewrites the “count(1)” into a “count(*)”. And, in this particular case, using the “count(1)” makes things worse from an availability point-of-view.

If you are interested in experimenting with such a package with either a user-defined extension or an extension created through a SQL plan directive, here are two scripts:

  • extension_invalidate_pkg.sql
  • spd_invalidate_pkg.sql
11gR1, 11gR2, 12cR1, Bug, Object Statistics, SQL Plan Directives
Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT
Oracle Database In-Memory Demos

6 Comments

3 Pings/Trackbacks

  1. Mikhail Velikikh Mikhail Velikikh
    18 December 2015    

    Hi Christian,
    I have also faced this issue after upgrading one of our database to 12c.
    I would like to provide a link to a relevant bug: Bug 19450314 – Unnecessary compiled PL/SQL invalidations in 12c (Doc ID 19450314.8)

    Reply
  2. Adaptive mayhem | Oracle Scratchpad Adaptive mayhem | Oracle Scratchpad
    22 September 2016    

    […] Chris Antognini’s note on bug 19450314 […]

  3. Group Column Statistics (Part 2) | Oracle Tuning Group Column Statistics (Part 2) | Oracle Tuning
    3 January 2017    

    […] statistics causing unnecessary invalidation, one I found is this (Doc ID 2110955.1) , another is a blog by Christian Antognini  . I do believe that the problem with extended stats is also contributing […]

  4. Greg Greg
    11 April 2017    

    hi
    Strange behavior … We had some issue time few weeks ago on production DB where some packages changed last_ddl
    Chris, Do you know how to find when virtual column is created ?
    is there is any track record for that ?

    Reply
    • Christian Antognini Christian Antognini
      11 April 2017    

      Hi Greg

      IIRC there’s no simple way to get this information. I.e. it’s not directly available in the data dictionary.

      If you really need the exact point-in-time, what can be done is the following:
      – Find out when the statistics for the involved table where gathered
      – Check if the archived redo logs of those periods are still available
      – If yes, use the log miner to get detailed information about the DDL that modified the involved table

      HTH
      Chris

      Reply
  5. Best of OBI 2016 – OBIEE News Best of OBI 2016 – OBIEE News
    22 June 2018    

    […] and it gives you a plan with a note that says “This is an adaptive plan”.  He refers to this blog and this blog […]

  1. Adaptive mayhem | Oracle Scratchpad on 22 September 2016 at 06:27
  2. Group Column Statistics (Part 2) | Oracle Tuning on 3 January 2017 at 07:41
  3. Best of OBI 2016 – OBIEE News on 22 June 2018 at 10:27

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.