Oracle 19c RU Release Update 19.19 nice little enhancement: DBA_HIST_SQL_PLAN ACCESS_PREDICATES and FILTER_PREDICATES columns populated

I've recently found out by coincidence a nice little enhancement that apparently was introduced with the 19.19 Release Update - a backport of the fix that was originally introduced with Oracle 20c / 21c populating the ACCESS_PREDICATES and FILTER_PREDICATES columns in DBA_HIST_SQL_PLAN.

This fix is truly a long awaited one - in fact the problem originally came from an ORA-600 error / bug in Oracle 9i (!) when populating execution plans in STATSPACK if I remember correctly, the workaround back then was setting the parameter "_cursor_plan_unparse_enabled" to FALSE which resulted among other things in those columns not being populated. This had been carried forward to AWR when it was  originally implemented in Oracle 10g.

The original bug / root cause was fixed a very long time ago (although there is for example bug ORA-600 [qksxaCompactToXml:2] When Generating An Execution Plan (Doc ID 1626499.1) which applies to Oracle 11.2 fixed in 12.1) but in all that time Oracle never managed to enhance the AWR code to include those columns again when populating DBA_HIST_SQL_PLAN.

When Oracle 20c / 21c came out Oracle finally included the enhancement populating those columns in DBA_HIST_SQL_PLAN.

And now - although it is not part of the official documentation listing the new features added to 19c Release Updates (which also misses at least the backport of Automatic SQL Tuning Sets to Oracle 19.7 RU) - according to MyOracleSupport document Missing FILTER_PREDICATES And ACCESS_PREDICATES In DBA-HIST_SQL_PLAN (Doc ID 2900189.1) this has been backported to the 19.19 RU.

I currently only have access to the 19.20 RU, so I can't confirm that it's already there in 19.19 but in 19.20 definitely those columns in DBA_HIST_SQL_PLAN are populated (and in 19.16 they definitely don't get populated) - so this is very good news for those that regularly deal with execution plan details. 

Without the ACCESS_PREDICATES and FILTER_PREDICATES it's not possible to fully understand the meaning of an execution plan - for example you might miss the fact that there is a datatype issue that introduces an implicit datatype conversion (like a TO_NUMBER function - 23c adds the "SQL Analysis" part to the execution plan notes), you can't tell how efficient an index access path is (are there FILTER predicates on index access operation level in addition to the ACCESS predicates indicating a suboptimal usage of the index), you might not be able to tell if a HASH JOIN includes additional FILTER predicates (potentially eating up a lot of CPU), you might not be able to fully understand the shape of an execution plan because you don't see what actually happens as part of a FILTER operation to name just a few cases where those columns are crucial for understanding / assessing properly an execution plan.

Addendum / clarification: Not only these columns are now populated in DBA_HIST_SQL_PLAN, but DBMS_XPLAN.DISPLAY_AWR (now deprecated  - you should use DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY instead in a CDB/PDB configuration) shows now happily a "Predicate Information" section when retrieving execution plans from AWR.

Oracle 21c / 23c New Features - move a partitioned table in a single command

Just a quick note about a nice little enhancement that might have gone unnoticed so far - since 21c it's now possible to move all partitions and subpartitions of a partitioned heap table using a single ALTER TABLE MOVE command.

So before 21c - if you had a (sub)partitioned heap table and tried to do:

ALTER TABLE <PART_TABLE> MOVE ...

then you got the error message "ORA-14511: cannot perform operation on a partitioned object" and you had to move each physical segment separately - so for a composite partitioned table this was only allowed on subpartition level, for partitioned tables on partition level.

Now from 21c on this is possible and also officially documented here and here for example.

Note that it looks like that it is still not supported to move a partition in case the table is subpartitioned - this still throws the error message "ORA-14257: cannot move a partition which is a composite partition" - so the feature at present is limited to moving the entire table on global level.

However, what is still not possible - even in 23c (FREE) - rebuilding a (sub)partitioned index using a single command.

So running:

ALTER INDEX <PART_INDEX> REBUILD ...

on a (sub)partitioned index still throws the error message: "ORA-14086: a partitioned index may not be rebuilt as a whole", which is a pity - and leaves you again only with the option to perform the rebuilds on the lowest level.

Which might also explain the limitation of above new feature to heap tables only - index organized tables are not supported (and I haven't checked clustered tables).

And the very handy package DBMS_INDEX_UTL which allows very easy handling of (sub)partitioned index rebuilds is still not documented yet, which is even more a pity.