At times there are batch jobs run on source database that can cause backlogging of queues, slowness of Shareplex processes, delay in posting to the target database resulting in latency, etc. All this is due to the way the transaction is done on the source. The management may want to identify the user or application that ran the transaction on the source. The question arises as to whether this can be inferred with the help of shareplex.
It is possible to pinpoint the application or user that executed the transaction on the source database which resulted in slowness or other issues, subject to certain conditions. If the queue data contains those transactions at present time (which is possible if the queue data is backlogged), then it may be possible to find out the log sequence #, offset, of the problem transaction. A simpler approach is to issue "show read internal" on source sp_ctrl or "show post detail" on target sp_ctrl depending on whether the queues are currently backlogged on source or target Shareplex and have the transaction embedded in them. These outputs provide some details of the transaction currently processed by Shareplex process (owner.tablename, type of DML, log sequence #, offset and time the transaction occurred on source). The following is a sample output from "show read internal":
sp_ctrl > show read internal
Source Status Forwarded Since Total
---------- --------------- ---------- ------------------ ----------
o.source_SID Running 467015372 11-Feb-08 21:54:56 2829510
Last operation forwarded:
Redo log: 45814 Log offset: 795506996
UPDATE of "OWNER"."TABLE_NAME" at 03/05/08
It should be possible for the management to logmine the data and arrive at the conclusion as to what application or user ran that batch job. The following is an excerpt from Oracle documentation about logminer that delves on such audit:
"Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them. (However, to use LogMiner for such a purpose, you need to have an idea when the event occurred so that you can specify the appropriate logs for analysis; otherwise you might have to mine a large number of redo log files, which can take a long time. Consider using LogMiner as a complementary activity to auditing database use. See the Oracle Database Administrator's Guide for information about database auditing.)"
If the data has already posted to the target database and its remnants are not available in Shareplex queues, then the whole matter is after the fact and it is not possible to pinpoint by looking at Shareplex queues or processes as to what caused the slowness.
The following is the link to the Oracle documentation:
Oracle AWR/statspack reports and OEM may also provide the answer to the above question. Please refer to Oracle documentation.