-
Title
Investigate slow post performance. -
Description
How to investigate slow post performance.
-
Resolution
Rename all opo.log files to opo.log.old in the VARDIR/LOG directory of the target server and set the performance debugger:
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_DEBUG_FLAG 0x0f000800
sp_ctrl> start post
Let this run for 5-10 minutes or until you get around 40 meg of opo files. Then turn it off:
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_DEBUG_FLAG 0x00000000
sp_ctrl> start post
Once complete, send the NEW opo log(s), event_log and paramdb from the target system to Quest Support at support@quest.com. The opo.log(s) and the event_log are located in the VARDIR/log directory of the target. The paramdb file is located in the VARDIR/data directory.-------------------------------------------------------------------------------------------
Current SharePlex versions have the feature of running “trace” on a specific Post queue (if using named Post queue) or the general Post queue (if no named Post queues are used). This comes in handy as the command is very simple with no debug to be turned on or off. The command offers an inherent debugging capability with a default debug running time of 15 minutes or as specified by user.
For example, I can trace the actions of Post for 10 minutes as:
sp_ctrl>trace post queue [queue_name] 10
Once the trace finishes, the *trace* file is generated in target's /vardir/log directory
Here is how a typical trace file looks like (which has the same look and feel of the *opo* log generated with the debug setting of 0x0f000800. The debug still works but the trace is much more convenient. There is no separate execute.out generated and the trace file contains the output as given in the erstwhile execute.out file. Here is a sample content of the trace file:
POST TRACE: Started: Tue Aug 16 21:14:20 2016
Completed: Tue Aug 16 21:29:21 2016
Summary
-------
Rate (operations/sec) 239.8
Transaction rate (transactions/sec) 61.5
Average transaction size 3.9
Elapsed time 15:01
Total time spent in SharePlex
-----------------------------------
Read from the queue 0:25
Time spent on SHAREPLEX_TRANS table 0:00
Total time spent in Oracle
--------------------------------
Oracle execute 9:03
Oracle commit 7:35
Update LOB columns 1:06
Session Oracle execute Oracle commit LOB columns
------- -------------- ------------- -----------
2 0:28 0:30 0:03
14 0:27 0:22 0:05
30 0:29 0:21 0:03
10 0:23 0:22 0:04
28 0:23 0:23 0:00
Detail
------
Number Executions Cost
Operation Count Pct Seconds Pct
--------- ----- ---- ------- ----
Insert 88301 32% 227 21%
Multi-row Insert 12672 4% 3 0%
Update 76281 27% 166 15%
Delete 24300 8% 138 13%
Commit 55445 20% 455 42%
Update Lob 1288 0% 36 3%
Insert Lob 2534 0% 29 2%
Batch Insert 1967 0% 2 0%
Batch Update 11024 4% 4 0%
Batch Delete 1474 0% 0 0%
Operation Table Avg(microsec) Count Total(sec)
--------- ----- ------------- ----- ----------
Insert "OWNER"."TABLE_NAME" 56027 14 0
.
.