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.

Oracle 23c FREE - Unrestricted Direct Path Loads glitches

Playing around with some new features added in 23c - here the removal of the restriction so far that Direct Path loads or Parallel DML prevented access to the same object within the same transaction - which meant that before you could access the same object again within the same session after manipulating it using Direct Path loads or Parallel DML the transaction had to be committed - please note that this applies only to the session performing the manipulation - other sessions outside the transaction won't notice any difference. 

So sometimes if you wanted to take advantage of these features you had to split a single transaction (from a business point of view) into multiple transactions due to this technical limitation. I assume this difference in behaviour was one of the main reasons why Oracle requires you to enable Parallel DML explicitly using ALTER SESSION ENABLE PARALLEL DML on session level or from 12c on using the ENABLE_PARALLEL_DML hint on statement level.

So generally speaking it's good to see that Oracle in 23c finally lifts this long lasting restriction - now users can make use of Direct Path and / or Parallel DML operations without having to worry about behaviour changes and splitting up transactions.

Tim Hall at Oracle-Base as usual has this new feature already covered - so you can check his notes here before continuing to get an idea what this is about.

Here I want to focus on what you get when you try this new feature with 23c FREE - Parallel Execution isn't supported in 23c FREE as far as I can see, and doesn't make a lot of sense given the limitations regarding maximum number of CPU threads allowed - but Direct Path loads are something you can certainly make use of in 23c FREE.

So to get an idea here is a very simple script (similar to what Tim used to demonstrate) using Session Statistics to confirm that Direct Path writes happened:

alter session set nls_language = american;

set echo on verify off

column value_start new_value value_start

column value_end new_value value_end

drop table /*if exists*/ t1 purge;

create table t1

-- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

--tablespace TEST_8K

tablespace TEST_8K_ASSM_AUTO

as

select *

from   all_objects;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

commit;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append NO_MULTI_APPEND */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

select /*+ NO_MULTI_APPEND */ count(*) from t1;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

commit;

select count(*) from t1;

And here is the 19c output:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 17:14:06 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Letzte erfolgreiche Anmeldezeit: Mi Aug 16 2023 17:08:15 +02:00

Verbunden mit:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

Session altered.

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL> drop table /*if exists*/ t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  --tablespace TEST_8K

  4  tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     67771

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

67771 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       1329

SQL> select count(*) from t1;

select count(*) from t1

                     *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

                          *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    135542

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

135542 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       2658

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

select /*+ NO_MULTI_APPEND */ count(*) from t1

                                            *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select count(*) from t1;

select count(*) from t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    271084

As expected after performing an insert in APPEND mode the same object can't be accessed within the same transaction - the error "ORA-12838: cannot read/modify an object after modifying it in parallel" is raised (which isn't very concise anyway - this is not a parallel but a direct path modification) for both query and DML attempts. Only after ending the transaction using COMMIT access is possible again.

Now when reading the 23c documentation available so far - follow this link - the lifted restriction regarding Direct Path loads / Parallel DML comes with some interesting limitations (quoting from above link):

"However, the restrictions on the above operations still apply when:

• The tables are IOT or clustered tables. (The table must be a heap table.)
• The tablespace is not under Automatic Segment Space Management (ASSM). Temporary tables are not under ASSM, so this includes them as well.
• The tablespace is of uniform extent.

The restrictions on multiple queries and DML/PDML operations as well as the restriction on multiple direct-path inserts in the same session can be reinstated when needed by including the NO_MULTI_STATEMENT hint in SQL statements."

There is no NO_MULTI_STATEMENT hint by the way - as you will see later. So when the tablespace used for the table (what about indexes on the table?) is not using ASSM and system managed extents the new feature should not be supported.

Here is what I get when I run the same script as above using 23c FREE and an ASSM AUTOALLOCATE tablespace - which should be a supported configuration:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 17:00:29 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 16 2023 16:59:53 +02:00

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL> drop table if exists t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  --tablespace TEST_8K

  4  tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

75677 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       1582

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

151354 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       3163

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

302708 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       6325

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

  COUNT(*)

----------

    302708

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    605416

Some observations: First it's really nice that now in the first part of the script several Direct Path loads are possible within the same transaction, and also accessing the manipulated object using SELECT within the transaction works as expected.

But check closely the second part of the script where I make use of the NO_MULTI_APPEND hint (which is apparently meant by the NO_MULTI_STATEMENT hint mentioned in the official documentation) that should revert to the previous behaviour.

While it is expected now that the second insert in APPEND mode fails again with the known "ORA-12838" error, the SELECT within the same transaction does not raise an error but what is even worse it returns a wrong result - it doesn't count the newly added rows but obviously only sees the rows that existed before the insert was executed.

It becomes even worse if a configuration gets used that isn't supported for the new feature - a tablespace using MSSM and / or uniform extent size - running the same script again only using a different tablespace:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 16:59:53 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 16 2023 16:57:27 +02:00

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL>

SQL> drop table if exists t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  tablespace TEST_8K

  4  --tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

75677 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       1580

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

151354 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                       3159

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

  COUNT(*)

----------

    151354

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

---------------------------

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

Now the same wrong results problem also occurs in the first part of the script - the SELECT within the same transaction doesn't raise an error but shows a wrong result.

Conclusion so far: It's good to see that this very long lasting restriction finally gets lifted in 23c, but at least in 23c FREE you need to be careful - the implementation doesn't seem to be complete yet. Hopefully this will all be sorted out when 23c final gets released.