Bugs Related to SQL Plan Directives Pack and Unpack

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.

4 Comments

  1. JC Dauchy JC Dauchy
    July 24, 2015    

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

    Christophe.

  2. July 31, 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?

    • August 3, 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

      • August 3, 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

Leave a Reply

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


Time limit is exhausted. Please reload CAPTCHA.