Monday, December 25, 2017

Erman Arslan is now an Oracle ACE!

I 'm an Oracle ACE now! Today is my birthday, and this is the best birthday gift ever!  :)
I have been writing this blog since 2013 and thanks to my passion on writing, I wrote the book (Practical Oracle E-business Suite) with my friend Zaheer Syed last year.
I aimed to share my knowledge with all my followers around the world and to keep up with the new developments in Oracle technologies.
I spent a significant time to give voluntarily support on my forum and did several Oracle projects in customer sites in parallel to that.
My primary focus was on EBS, but I was also researching and doing projects on Exadata, Oracle Linux, OVM, ODA, Weblogic and many other Oracle Technologies.
I 'm still  working with the same self-sacrifice as I started to work as an Oracle DBA in the year 2006 and I 'm still learning, implementing and explanining the Oracle Solutions with the same motiviation that I had in the first years of my career.

I want to send my special thanks to Mr. Hasan Tonguç Yılmaz, who's nominated me to become an Oracle ACE. I offer my respect to Mr. Alp Çakar, Mr. Murat Gökçe and Mr. Burak Görsev who have directly or indirectly supported me in this way.

Friday, December 22, 2017

Goldengate -- UROWID column performance

As you may guess, these blog posts will be the last blog posts of the year :)

This one is for Goldengate.

Recently, started working for a new company and nowadays, I deal with Exadata machines and Goldengate more often.

Yesterday, analyzed a customer environment, where Goldengate was not performing well.

That is, there are was a lag/gap reported in a target database, in which, 55-60 tables were populated by Goldengate 12.2.

When we analyzed the environment, we saw that, it was not the extract process or network which was causing the issue.

The REPLICAT process was also looking good in the first glance, as it  was performing well on its trail files.

However, when we check the db side, we saw that there was a lag around 80 hours.. So target db was behind the source db with a 80 hours difference.

We analyzed the target database, because we thought that it might be the cause.. I mean, there could be some PK, or FK missing on the target environment.. (if the keys are missing, this can be a throuble in goldengate replications). However, we concluded that, no keys were missing.

In addition to that, we analyzed the AWR reports, we analyzed the db structure using various client tools (like TOAD) and we check the db parameters, but -> all were fine..

Both source and target databases were on Exadata. AWR reports were clean. Load average was so low, the machine was sleeping and there were almost no active sessions in the database (when we analyzed it real time)

Then we checked the goldengate process reports and saw that REPLICAT was performing very slow.

It was doing 80 tps , but it should be around 10000 tps in this environment..

At that moment, we followed the note, and check the replicat accordingly.
(Excessive REPLICAT LAG Times (Doc ID 962592.1))

We considered the things in the following list, as well:
  • Preventing full table scan in the absence of keys KEYCOLS
  • Splitting large transactions
  • Improve update speed - redefine tables - stop and start replicat
  • Ensure effective execution plans by keeping fresh statistics
  • Set Replicat transaction timeout
Unfortuneatly, no matter what we did, the lag was increasing..

Then fortuneatly :), we saw that all these 55-60 tables in the target db had columns with the type of

These columns were recently added to the tables by the customer.

We also discovered that, this performance issue have started, after these columns were added.

We wanted to change the column type, because these UROWID columns have recently become supported with Goldengate..

ROWID/UROWID Support for GoldenGate (Doc ID 2172173.1)

So we thought that these columns may cause the REPLICAT to perform with this low performance.

The customer was using these columns to identify the PK changes and accepted to change the type of these columns to VARCHAR2.

As for the solution, we changed the type of those columns to varchar2 by creating empty tables and transferring the data using INSERT INTO APPEND statements.

Thanks to EXADATA , it didn't take lots of our time and thanks to Oracle Database 12C, we didn't need to gather statistics of these new tables, since in 12C it is done automatic during CTAS and Insert Into Append..

After changing the column type of those tables, we restarted the REPLICAT and the lag was dissapeared in 2 hours.

So, be careful when using UROWID columns in a Goldengate environment..