ORACLE ACTIVE DATA GUARD 19C FEATURES: AN Add-On

In the previous post ORACLE ACTIVE DATA GUARD 19C FEATURES: AN ASTERISK, using Global Temporary Tables (GTTs) on an Oracle 18c or 19c Active Data Guard (ADG) standby database worked for the most part — except when trying to speed things up by doing a direct path INSERT in parallel. More evidence has come to light — it still seems more like a bug, or at least a feature deficiency!

Behavior and Error Messages

To summarize, a change in our released code started using INSERT /*+ APPEND PARALLEL(4) ENABLE_PARALLEL_DML */ when populating GTTs, and worked fine on primary Data Guard instances and of course on standalone instances. But when trying to do the INSERT on the standby instance, it would fail on the COMMIT:

Of course, an ADG standby is a read-only database, except for creating and using GTTs. The additional clue was that a direct path INSERT would not fail if there was a previous conventional INSERT/DELETE of 1 row from the GTT.

That reminded me of a post from a few years ago, and a feature introduced way back in 12c — automatic basic statistics gathering for bulk loads. This feature was only the beginning of automatic online statistics operations that culminated in the current 19c high frequency statistics collection features. But this default basic statistics collection generates an ORA-16000, and the code makes an explicit call to DBMS_STATS afterwards anyway, and that always works fine. Therefore I don’t need the basic statistics collection to happen. To fix this, set this hidden parameter only on the ADG standby:

alter system set "_optimizer_gather_stats_on_load"=FALSE;

That fixed the issue in all scenarios. This parameter could also be set at the session level, or only at the PDB level, but I want to avoid the behavior everywhere in the CDB. Gathering statistics on the GTT in the session works with no errors:

begin
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'RJB', 
      tabname => 'MY_SIMPLE_GTT', 
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
      DEGREE=>DBMS_STATS.AUTO_DEGREE, 
      force => true);
end;
/

The statistics are session-private by default:

Session-private statistics are also discarded after the session has terminated, so my assumption is that they must also be stored in a temporary segment like the table rows themselves, marked as temporary session-specific rows in a permanent data dictionary table, or a V$ view. Otherwise, this would also be an issue on an ADG standby instance.

The Alternatives

I could also use a hint to avoid the undesirable behavior:

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

But even adding or modifying a hint in the existing stored procedure is a code change that would require involvement from R&D and QA. The documentation on basic statistics collection during bulk loads was the last clue:

Bulk loads only gather online statistics automatically when the object is empty.

That explained the behavior of the bulk load INSERT not causing an error on COMMIT if there was even one existing empty segment previously allocated for the GTT in the session.

Conclusions and Next Steps

Using any underscore (hidden) parameter always has some risk, but its behavior is well documented and solves my immediate issue without a code change and has no impact on the Data Guard primary since the parameter is not set there! On the standby, the only code that would benefit from session statistics had full statistics gathered later in the procedure anyway. But this still appears to be a minor bug and I’ll continue to push for a bug fix — every other feature that works with GTTs on ADG standby works as advertised, but basic statistics collection on a direct path load tries to update permanent objects, which is should not!