Hi Friends :) Hope you are good ! Today I want to discuss about performance of replicat running on Oracle goldengate home
Scenario: Replicat is running very slow
Initial Investigation:
Replicat works on target database, where it applies the records from remote trail directory to target table. when it reads records from remote trail directory, using the information on the trail, it creates the consistent sql to apply to the target table. If the sql made by the replicat is not using Indexes or optimizer is generating different execution plans by excluding index's or may be blocking sessions or wait events running on database. These are the different things where we need to look into
There is no blocking sessions
Immediately took AWR report and found DB TIME is quiet higher than usual and checked for " Top foreground events by total wait time " found db file sequential read. Hint, may be replicat is using bad index.
later on checked for SQL's ordered by elapsed time, then found my replicat on top of it
I got SQL ID and tried to run SQL tuning advisor on it. Results as follows
-----------
Plan hash value: 748041570
---------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 39 | 184K (2)| 00:36:49 |
| 1 | UPDATE | | | | | |
|* 2 | TABLE ACCESS FULL| | 1 | 39 | 184K (2)| 00:36:49 |
---------------------------------------------------------------------------------------------
Root cause: I found replicat is performing full table-scan to update the record and creating lag in the environment.
Potential Fix: created index on predicate columns as suggestions from SQL tuning advisor improved performance of replicat by 100%
-----when we add supplemental logging, primary keys will be logged into the online redo, here we have to understand keys should be the same for both source table and target table. If the keys are different where we can see these kind of issues in environment -----
---Happy weekend ---
--Oracle in memory --
--Nikhil Tatineni ---
Scenario: Replicat is running very slow
Initial Investigation:
Replicat works on target database, where it applies the records from remote trail directory to target table. when it reads records from remote trail directory, using the information on the trail, it creates the consistent sql to apply to the target table. If the sql made by the replicat is not using Indexes or optimizer is generating different execution plans by excluding index's or may be blocking sessions or wait events running on database. These are the different things where we need to look into
There is no blocking sessions
Immediately took AWR report and found DB TIME is quiet higher than usual and checked for " Top foreground events by total wait time " found db file sequential read. Hint, may be replicat is using bad index.
later on checked for SQL's ordered by elapsed time, then found my replicat on top of it
I got SQL ID and tried to run SQL tuning advisor on it. Results as follows
-----------
Plan hash value: 748041570
---------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 39 | 184K (2)| 00:36:49 |
| 1 | UPDATE | | | | | |
|* 2 | TABLE ACCESS FULL| | 1 | 39 | 184K (2)| 00:36:49 |
---------------------------------------------------------------------------------------------
Root cause: I found replicat is performing full table-scan to update the record and creating lag in the environment.
Potential Fix: created index on predicate columns as suggestions from SQL tuning advisor improved performance of replicat by 100%
-----when we add supplemental logging, primary keys will be logged into the online redo, here we have to understand keys should be the same for both source table and target table. If the keys are different where we can see these kind of issues in environment -----
---Happy weekend ---
--Oracle in memory --
--Nikhil Tatineni ---
