-
Title
Post is slow and the total time spent in COMMIT is very high relative to the total execution time in Oracle -
Description
Post is slow. When trace is run on a slow Post queue, it is observed that the total time spent in COMMITs is very high relative to the total execution time in Oracle (as shown below in the sample output of trace file):
Total time spent in Oracle
--------------------------------
Oracle execute 8:42
Oracle commit 4:07
-
Cause
The time spent in commit is very high -
Resolution
First run a trace on the Post as below:
sp_ctrl>trace post [queue_name] 10 (in this example, the trace is run for 10 minutes which is suitable for most situations but this is an arbitrary interval and can be changed as needed).
Once the trace finishes, examine the *trace* file generated in target's /vardir/log directory which will have various stats including the total time spent in Oracle. If the time spent in COMMIT is high relative to the total time spent in Oracle during that 10 minute trace, then the following parameter can be set to reduce the total time spent in COMMIT and thereby improve Post performance:
sp_ctrl>set param SP_OPO_COMMIT_NOWAIT 1
sp_ctrl>stop post [queue <queue_name>] (make sure it is “stopped by user” and not “stopping”. In case of latter wait till it is stopped by user)
sp_ctrl>start post
Once the Post queue drains, the parameter can be set to its default value as follows and then the Post will need to be stopped/started as done in the para above:
sp_ctrl>reset param SP_OPO_COMMIT_NOWAIT
When the parameter is set to 1, Post submits the messages from the queue to the database (which include DML and COMMIT among others) but does not wait for the database to return the outcome. This can speed up Post if the COMMITs form a large time slice of the total time taken by Post in applying the changes to the target. Most common situations that can cause this are small transactions where the COMMITs form a large percentage of the DML. With the setting of 1 for this parameter, Post calls Oracle COMMIT_WAIT parameter at the session level. COMMIT_WAIT is an advanced parameter in oracle used to control when the redo for a commit is flushed to the redo logs. Oracle returns the commit done signal back to post quicker when this parameter is set to nowait and this allows post to move on quickly.
-
Additional Information
What constitutes a high value for commits relative to the total time is a matter of individual opinion/preferences but COMMITs taking 10% or more of the total time can be considered a good candidate for tuning.