-
Title
Posting Speed is Slow -
Description
The posting speed is too slow.
-
Resolution
Set up debug on the post process to determine what table is taking too long.
From sp_ctrl on the target issue the following commands
sp_ctrl> set param SP_OPO_DEBUG_FLAG 0x0f000800
Bounce post process (not needed for 5.1 above since it is a live parameter)
Let it run in debug mode for 10-20 minutes, then turn off debug
sp_ctrl> set param SP_OPO_DEBUG_FLAG 0x00000000
Bounce post process (not needed for 5.1 above since it is a live parameter)
The opo log located in the vardir/log directory will have debug that will tell the time shareplex spent in each process. The debug will look like
OWNER.TABLENAME: ODR_INSERT1
E: 5058 <- time spent in oracle
Q: 180 <- time spent in queues
C: 25103 <- time spent getting a commit from oracle
If the time for the E operation is taking too long, proper indexes should be placed on the table.
If the time for the C operation is taking too long the log buffer should be resized.
For more information of sizing the log buffer please check metalink-----------------------------------------------------------------------------
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
.
.