Oracle Active Data Guard 19c Features: an asterisk

Using Oracle Active Data Guard (ADG) gets easier to set up every release — you can even use dbca on a new standby for creating the initial standby in silent mode. Even though a physical active standby is read-only, you can create and use Global Temporary Tables (GTTs) to enhance the usability of the standby for applications that need to do a bit more than just run batch or ad-hoc reports. I ran into a curious glitch with GTTs on ADG that will end up with me filing a possible bug or definitely a feature enhancement request.

Scenario

On an 18c ADG standby, an application started generating an ORA-16000 shortly after a minor upgrade of the application code:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

As suspected, the change to the application involved how GTTs were manipulated and processed. The original code in the PL/SQL procedure looked like this:

insert into my_gtt(acct_id,ins_dt,src_cd)
select custno, sysdate, 2 from stage_table;

To enhance performance in a number of ways, the code now looks like this:

insert
   /*+ append parallel(4) enable_parallel_dml */
   into my_gtt(acct_id,ins_dt,src_cd)
select custno, sysdate, 2 from stage_table;
commit;

To accumulate and process intermediate results even more quickly, the INSERT now has a hint to use direct path operations at PARALLEL(4). The INSERT works fine. The COMMIT fails with an ORA-16000. The COMMIT was added so that we would not see an ORA-12838 when accessing the object later in the procedure after populating it in parallel.

Is It a Bug?

I was convinced that it was a bug, and there were a few related bug reports. However, I recreated the configuration under a similar 19c (19.8) environment, and got the same exact behavior. I started to think it was not really a bug, especially after looking at the rest of the error message:

00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).

The “catch” is what happens when doing direct path inserts into a GTT in parallel, then doing a COMMIT (ON COMMIT PRESERVE ROWS). Using direct path INSERTs into a table, and then a COMMIT, tries to update a data dictionary table, all of which are permanent objects, and that is not allowed on a read-only database like ADG.

Workaround?

What does work is using NOAPPEND instead of APPEND, and it appears that this does not involve any updates to data dictionary tables:

insert 
   /*+ noappend parallel(4) enable_parallel_dml */
into my_gtt(id,longtime,src_cd)
select rownum,sysdate,rownum from dual connect by level <= 10000000;

10,000,000 rows inserted.

commit;

Commit complete.

Initial testing shows that going back to conventional path loads, even in parallel, has much more overhead and elapsed time for the same operation:

It means that you can perform parallel DML on GTTs in ADG, which I suppose is better than not being able to at all… but as you can see, it’s going to involve impacts to the SGA’s buffer cache that would not have to happen on the read-write primary.

But Wait, There’s More…

I still don’t know exactly what data dictionary objects are involved with writing to GTTs on ADG in parallel using direct path operations, but I know that this works:

insert
   /*+ noappend parallel(4) enable_parallel_dml */
into my_gtt(id,longtime,src_cd)
select rownum,sysdate,rownum from dual connect by level <= 10;

10 rows inserted.

delete from my_gtt;

10 rows deleted.

commit;

Commit complete.

insert 
   /*+ append parallel(4) enable_parallel_dml */
into my_gtt(id,longtime,src_cd)
select rownum,sysdate,rownum from dual connect by level <= 10000000;

10,000,000 rows inserted.

commit;

Commit complete.

It’s not really a viable workaround, but it seems to indicate that if already allocated temporary segments are already there in a session, or the segment has already been written to once in the session with a conventional path INSERT, successive direct path INSERTs in parallel don’t hit the ORA-16000 by trying to write to the data dictionary. That is what tells me that this might still be a bug.

Other alternatives?

On Oracle 19c, you can now use the parameter ADG_REDIRECT_DML to (indirectly) perform DML on permanent objects in ADG: the DML is redirected to the primary site and via redo apply is applied to the ADG site. But that is supposed to be for occasional DML and involves many other considerations.

Conclusions

Using ADG is still a great way to leverage your failover servers for non-failover scenarios and get the best bang for your licensing buck, but note that populating and manipulating GTTs in a read-only environment might be a feature with an asterisk next to it if you are using direct path INSERTs in parallel.