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:

Creating a backup table (DBMS_SPD.CREATE_STGTAB_DIRECTIVE)
Exporting the SQL plan directives you are interested in from the data dictionary to the backup table (DBMS_SPD.PACK_STGTAB_DIRECTIVE)
Moving the backup table and/or its content between databases by means of a generic data movement utility (e.g. Data Pump)
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.

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

4 Comments

JC Dauchy

24 July 2015

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

Christophe.

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?

- 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
  - 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

Bugs Related to SQL Plan Directives Pack and Unpack

4 Comments

Leave a Reply Cancel reply