DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Active Data Guard Performance in the Context of Global Temp Tables

Active Data Guard Performance in the Context of Global Temp Tables

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

suryanarayana nekkanti user avatar by
suryanarayana nekkanti
·
Apr. 22, 19 · Opinion
Like (2)
Save
Tweet
Share
6.05K Views

Join the DZone community and get the full member experience.

Join For Free

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?
Database Data (computing) Guard (information security)

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How to Secure Your CI/CD Pipeline
  • Real-Time Stream Processing With Hazelcast and StreamNative
  • Top Three Docker Alternatives To Consider
  • Upgrade Guide To Spring Data Elasticsearch 5.0

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: