Active Data Guard Performance in the Context of Global Temp Tables

Data is the lifeblood of a company, and Oracle Data Guard and Oracle Active Data Guard have long been the answer for the real-time protection, availability, and usability of your Oracle data. In recent times, Oracle has brought new capabilities of the Oracle database to benefit the customers and deliver the best performance, scalability, reliability, and security for all their transactional and analytical workloads and application development requirements.

This article demonstrates the performance benefits in the field of the Oracle Active Data Guard database using global temporary tables.

Introduction

In today’s digital world, the challenge revolves around not just understanding how much data you’ve created; but how to protect, process efficiently, and use it as well. In the Oracle world of databases and applications, data can be protected using data guard database, which is an exact transactional consistent copy of a primary (production) database [1].

Until the 11g release 1, traditional availability technology required idle spares. These are uneconomical and unreliable. You can not tell if the backup system will work when you need it. Oracle has come up with a great idea and design, making use of the standby database as read-only called Active Data Guard. Oracle Active Data Guard has been integrated with Oracle Database In-Memory and Oracle E-Business Suite and offers a faster application response after a role transition. DML can now be redirected from an Oracle Active Data Guard standby to its primary for a more flexible data protection in today’s data centers.

Problem Statement

In Oracle E-Business Suite, there are two seeded reports, active users and responsibilities, and we want to combine these two reports and roles into one report for SOX purpose. A custom program is the solution to get the desired results. If you look at the explain plan of the SQL query, these tables are heavily indexed and the runtime to extract 45,004 rows takes more than 10 hours.

I tried different options to optimize the query, and one solution is to drop indexes that are not needed for this query. Dropping these indexes have cascaded impact to other modules, hence I dropped the idea.

Secondly, I created new tables as select * from source tables. A report against these new tables really helped runtimes, however, there is unwanted redo, undo, and storage space. This additional redo has cascaded impact to standby databases and its RMAN backups.

Technology Solutions

In the 11.2.0.4 version, when we used global temporary tables in the primary database, the redo generation 1GB and temp was 168MB. The runtime of the query was less than 1 minute, which is a huge performance gain.

Scalability of this solution is a challenge, especially during month- and quarter-end financial closures. To overcome the contention issues with temp tablespace, we have created additional temp storage.

create global temporary table Fnd_User_gt on commit preserve rows as select * from apps.Fnd_User;

create global temporary table Fnd_User_Resp_Groups_All_gt on commit preserve rows as select * from Apps.Fnd_User_Resp_Groups_All;

create global temporary table Fnd_Responsibility_Tl_gt on commit preserve rows as select * from Apps.Fnd_Responsibility_Tl;

create global temporary table Fnd_Application_Tl_gt on commit preserve rows as select * from Apps.Fnd_Application_Tl;

create global temporary table Per_People_f_gt on commit preserve rows as select * from Apps.Per_People_f;

create global temporary table Per_Assignments_f_gt on commit preserve rows as select * from Apps.Per_Assignments_f;

create global temporary table Per_People_x_gt on commit preserve rows as select * from apps.Per_People_x ;

create global temporary table Hr_Organization_Units_gt on commit preserve rows as select ORGANIZATION_ID,

Despite that Oracle has come up with Active Data Guard feature in 11g Release 1, not all of the reports can be offloaded to ADG due to the reason that a session requires a write permission to certain tables. We would not be able to use all the reports in 11g ADG.

For experimental purposes, I have copied the data to 18c to utilize the new feature called Active Data Guard DML Redirect, which enables you to do transactions against the standby database. With adg_redirect_dml settings, we could achieve the desired results.

Conclusion

Global Temporary tables can be very useful, but they're very commonly used incorrectly. Whenever you're writing (or reviewing code) that's using a temporary table, consider the following:

• Is this temporary table helping to achieve desired performance?
• With temporary tables, can we scale up the solution, especially during peak load?