In my case replicat was running slow and below query was getting executed from long time
INST_ID| SID| Status| Oracle User| O/S User| O/S PID|Session Program |Current SQL |Logon_time |SQL_ID |EVENT
-------|------|---------|--------------|------------|----------|--------------------------|-------------------------------------------|--------------------|-------------|----------
2| 141| ACTIVE| GGADMIN| ggadmin| 11699 |replicat@trsodc0101 (TNS V|select aet.msg_id, e.local_transaction_id, |05/02/2021 08:06 |6mhp0u8686fds|db file sc
| | | | | | |e.source_commit_scn, e.external_source_pos,| | |attered re
| | | | | | | e.aq_transaction_id, e.source_transaction_| | |ad
| | | | | | |id, NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL| | |
| | | | | | |(e.xidsqn, 0), NVL(e.flags, 0) from sys.app| | |
| | | | | | |ly$_error e, sys.apply$_error_txn aet where| | |
| | | | | | | e.local_transaction_id=aet.local_transacti| | |
| | | | | | |on_id(+) and e.local_transaction_id=NVL(:1,| | |
| | | | | | |e.local_transaction_id) and bitand(NVL(aet.| | |
| | | | | | |flags, 0), 1) != :2 order by aet.txn_messag| | |
| | | | | | |e_number | | |
replicat showing lag of 26 min
GGSCI (trsodc0101 as ggadmin@TROY_1) 136> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R1_ICJ01 00:00:00 00:00:08
REPLICAT RUNNING R_ICJ01 00:26:57 00:00:49ran tunning advisor for the mentioned sql_id and first suestion was to gather stats but it didn’t helped
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>'APPLY$_ERROR', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO'); second suggestion was to create profile which will provide 9 parallelism and it worked great and solved the issue.
execute dbms_sqltune.accept_sql_profile(task_name =>'SQL_TUNNING_TASK_SQL_ID3', task_owner => 'SYS', replace => TRUE,profile_type => DBMS_SQLTUNE.PX_PROFILE);GGSCI (trsodc0101 as ggadmin@TROY_1) 149> info all
Program Status Group Lag at Chkpt Time Since Chkpt
REPLICAT RUNNING R1_ICJ01 00:00:00 00:00:00
REPLICAT RUNNING R_ICJ01 00:00:00 00:00:08explain plan before profile
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('6mhp0u8686fds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6mhp0u8686fds, child number 0
-------------------------------------
select aet.msg_id, e.local_transaction_id, e.source_commit_scn,
e.external_source_pos, e.aq_transaction_id, e.source_transaction_id,
NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.flags, 0)
from sys.apply$_error e, sys.apply$_error_txn aet where
e.local_transaction_id=aet.local_transaction_id(+) and
e.local_transaction_id=NVL(:1,e.local_transaction_id) and
bitand(NVL(aet.flags, 0), 1) != :2 order by aet.txn_message_number
Plan hash value: 1277036928
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 569K(100)| |
| 1 | SORT ORDER BY | | 2 | 588 | 569K (1)| 00:00:23 |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 588 | 569K (1)| 00:00:23 |
| 4 | VIEW | VW_JF_SET$10E0EC90 | 2 | 514 | 0 (0)| |
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| APPLY$_ERROR | 1 | 244 | 0 (0)| |
|* 8 | INDEX FULL SCAN | STREAMS$_APPLY_ERROR_UNQ | 1 | | 0 (0)| |
|* 9 | FILTER | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | APPLY$_ERROR | 1 | 244 | 0 (0)| |
|* 11 | INDEX UNIQUE SCAN | STREAMS$_APPLY_ERROR_UNQ | 1 | | 0 (0)| |
| 12 | TABLE ACCESS FULL | APPLY$_ERROR_TXN | 46M| 1625M| 569K (1)| 00:00:23 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(BITAND(NVL("AET"."FLAGS",0),1)<>:2)
3 - access("ITEM_1"="AET"."LOCAL_TRANSACTION_ID")
6 - filter(:1 IS NULL)
8 - filter("E"."LOCAL_TRANSACTION_ID" IS NOT NULL)
9 - filter(:1 IS NOT NULL)
11 - access("E"."LOCAL_TRANSACTION_ID"=:1)
40 rows selected.
explain plan after profile
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('6mhp0u8686fds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6mhp0u8686fds, child number 0
-------------------------------------
select aet.msg_id, e.local_transaction_id, e.source_commit_scn,
e.external_source_pos, e.aq_transaction_id, e.source_transaction_id,
NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.flags, 0)
from sys.apply$_error e, sys.apply$_error_txn aet where
e.local_transaction_id=aet.local_transaction_id(+) and
e.local_transaction_id=NVL(:1,e.local_transaction_id) and
bitand(NVL(aet.flags, 0), 1) != :2 order by aet.txn_message_number
Plan hash value: 1285636881
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 90417 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 5760K| 1593M| | 90417 (1)| 00:00:04 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 5760K| 1593M| 1730M| 90417 (1)| 00:00:04 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 5760K| 1593M| | 40702 (1)| 00:00:02 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 5760K| 1593M| | 40702 (1)| 00:00:02 | Q1,01 | P->P | RANGE |
| 6 | BUFFER SORT | | 5760K| 1593M| | | | Q1,01 | PCWP | |
|* 7 | FILTER | | | | | | | Q1,01 | PCWC | |
| 8 | NESTED LOOPS OUTER | | 5760K| 1593M| | 40702 (1)| 00:00:02 | Q1,01 | PCWP | |
| 9 | VIEW | VW_JF_SET$10E0EC90 | 2 | 506 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | UNION-ALL | | | | | | | Q1,01 | PCWP | |
|* 11 | FILTER | | | | | | | Q1,01 | PCWC | |
| 12 | PX BLOCK ITERATOR | | 1 | 78 | | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | APPLY$_ERROR | 1 | 78 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 14 | FILTER | | | | | | | Q1,01 | PCWC | |
| 15 | TABLE ACCESS BY INDEX ROWID | APPLY$_ERROR | 1 | 78 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 16 | BUFFER SORT | | | | | | | Q1,01 | PCWC | |
| 17 | PX RECEIVE | | 1 | | | 0 (0)| | Q1,01 | PCWP | |
| 18 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | | 0 (0)| | | S->P | HASH (BLOCK|
|* 19 | INDEX UNIQUE SCAN | STREAMS$_APPLY_ERROR_UNQ | 1 | | | 0 (0)| | | | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED| APPLY$_ERROR_TXN | 2880K| 101M| | 20350 (1)| 00:00:01 | Q1,01 | PCWP | |
|* 21 | INDEX RANGE SCAN | STREAMS$_APPLY_ERROR_TXN_UNQ | 2880K| | | 2385 (1)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(BITAND(NVL("AET"."FLAGS",0),1)<>:2)
11 - filter(:1 IS NULL)
13 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."LOCAL_TRANSACTION_ID" IS NOT NULL)
14 - filter(:1 IS NOT NULL)
19 - access("E"."LOCAL_TRANSACTION_ID"=:1)
21 - access("ITEM_1"="AET"."LOCAL_TRANSACTION_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - PARALLEL
Note
-----
- automatic DOP: Computed Degree of Parallelism is 8
- SQL profile SYS_SQLPROF_02792c652c9d0000 used for this statement
62 rows selected.
DBATracker 