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 18.104.22.168 or 22.214.171.124, 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 126.96.36.199 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.