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.
Cool, thanks for mentionning it. I was waiting for it.
Christophe.
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?
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:
Best,
Chris
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