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

Bugs Related to SQL Plan Directives Pack and Unpack

13 July 2015 4 Comments Written by Christian Antognini

SQL plan directives are a new concept introduced in version 12.1. Their purpose is to help the query optimizer cope with misestimates. To do so, they store in the data dictionary information about the predicates that cause misestimates. Simply put, the purpose of SQL plan directives is to instruct the database engine to either use dynamic sampling or automatically create extended statistics (specifically, column groups).

Since the database engine automatically maintains (e.g. creates and purges) SQL plan directives, in some situations it is necessary to copy the SQL plan directives created in one database to another one. This can be done with the help of the DBMS_SPD package.

Here are the key steps for such a copy:

  1. Creating a backup table (DBMS_SPD.CREATE_STGTAB_DIRECTIVE)
  2. Exporting the SQL plan directives you are interested in from the data dictionary to the backup table (DBMS_SPD.PACK_STGTAB_DIRECTIVE)
  3. Moving the backup table and/or its content between databases by means of a generic data movement utility (e.g. Data Pump)
  4. Importing the SQL plan directives from the backup table to the data dictionary (DBMS_SPD.UNPACK_STGTAB_DIRECTIVE)

The problem is that if you test such operations in either Oracle Database 12.1.0.1 or 12.1.0.2, you will discover that not everything works as expected!

While testing the functionality for one of my customers I discovered three bugs:

  • A pack/unpack of a SQL plan directive does not always lead to the creation of a corresponding column group
  • The OBJ_LIST parameter of the DBMS_SPD.PACK_STGTAB_DIRECTIVE function does not work as expected
  • To pack cached SQL plan directives DBMS_SPD.CREATE_STGTAB_DIRECTIVE requires a flush through DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE

Hopefully Oracle fixed these issues. The patch, which is available for 12.1.0.2 only, is number 19731829. For Linux x86-64, it can be directly downloaded from My Oracle Support. For other platforms, you have to request it.

12cR1, Bug, Query Optimizer, SQL Plan Directives
Ad: Trivadis DK Opening Event
Foreign Keys and Library Cache Locks

4 Comments

  1. JC Dauchy JC Dauchy
    24 July 2015    

    Cool, thanks for mentionning it. I was waiting for it.

    Christophe.

    Reply
  2. David Christian David Christian
    31 July 2015    

    I was wondering for the solution to these bugs. Here I get to know how to fix the issues. Really helpful post, I do have one question regarding same, if you can help me out: Can I have two different SQL Plan Directives for the same table and column?

    Reply
    • Christian Antognini Christian Antognini
      3 August 2015    

      Hi David

      > Can I have two different SQL Plan Directives for the same table and column?

      Yes, it’s possible. In fact, a column can be used in several predicates or used to join several tables…
      With the following SQL statement you should be able to find such a case in your database:

      select owner, object_name, subobject_name, count(distinct directive_id)
      from dba_sql_plan_dir_objects
      where subobject_name is not null
      group by owner, object_name, subobject_name
      having count(distinct directive_id) > 1

      Best,
      Chris

      Reply
      • David Christian David Christian
        3 August 2015    

        Hi Chris

        Thanks for your support. I might have some more issues regarding plan directives. As I find more, I need you to help me with that if you don’t mind.

        Regards
        David

        Reply

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.