Why CURSOR_SHARING=FORCE sucks. Part II

I believe everybody who maintains Documentum repository with intensive workflow do see following query in the top of database performance reports:

UPDATE /*+ USE_NL(dmi_workitem_s) */
      dmi_workitem_s
   SET a_wq_name = :p0
 WHERE r_object_id =
          ANY (SELECT /*+ CARDINALITY(1) */
                      wis2.r_object_id
                 FROM (SELECT wis3.r_object_id AS r_object_id
                         FROM (  SELECT /*+ CARDINALITY(1) */
                                        wis.r_object_id AS r_object_id
                                   FROM dmi_workitem_s wis
                                  WHERE     wis.r_runtime_state >= 0
                                        AND wis.r_runtime_state <= 1
                                        AND wis.r_auto_method_id >
                                               '0000000000000000'
                                        AND wis.a_wq_name = ' '
                                        AND wis.r_next_retry_date < SYSDATE
                               ORDER BY r_creation_date ASC) wis3
                        WHERE ROWNUM <= 90) wis2);

This query is performed by workflow agent and their bad performance actually reveals a poor database design because dmi_workitem_s table does not contain column with high selectivity:

  • records with r_runtime_state IN (0, 1) relates to both auto and manual activities
  • records with r_auto_method_id > ‘0000000000000000’ relates to both completed and non-completed auto activities

Actually, in case of MSSQL and PostgreSQL it would possible to create “ideal” index for this query like:

CREATE INDEX idx_auto_tasls
   ON dmi_workitem_s (r_object_id, r_next_retry_date, r_creation_date)
   WHERE wis.r_runtime_state >= 0
     AND wis.r_runtime_state <= 1
     AND wis.r_auto_method_id > '0000000000000000'
     AND wis.a_wq_name = ' ';

because both MSSQL and PostgreSQL support partial indexes, in case of Oracle the solution is not so straightforward, because it either required to rewrite query to the following form:

UPDATE /*+ USE_NL(dmi_workitem_s) */
      dmi_workitem_s
   SET a_wq_name = :p0
 WHERE r_object_id =
          ANY (SELECT /*+ CARDINALITY(1) */
                      wis2.r_object_id
                 FROM (SELECT wis3.r_object_id AS r_object_id
                         FROM (  SELECT /*+ CARDINALITY(1) */
                                        wis.r_object_id AS r_object_id
                                   FROM dmi_workitem_s wis
                                  WHERE     CASE
                                               WHEN     wis.r_runtime_state >=
                                                           0
                                                    AND wis.r_runtime_state <=
                                                           1
                                                    AND wis.r_auto_method_id >
                                                           '0000000000000000'
                                                    AND wis.a_wq_name = ' '
                                               THEN
                                                  1
                                            END = 1
                                        AND wis.r_next_retry_date < SYSDATE
                               ORDER BY r_creation_date ASC) wis3
                        WHERE ROWNUM <= 90) wis2);

and create following functional-based index:

CREATE INDEX idx_auto_tasks
   ON dmi_workitem_s (
      CASE
         WHEN     r_runtime_state >= 0
              AND r_runtime_state <= 1
              AND r_auto_method_id > '0000000000000000'
              AND a_wq_name = ' '
         THEN
            1
      END);

or create materialized view like:

CREATE MATERIALIZED VIEW mv_auto_tasks
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
AS
   SELECT wis.r_object_id, wis.r_creation_date, wis.r_next_retry_date
     FROM dmi_workitem_s wis
    WHERE     wis.r_runtime_state >= 0
          AND wis.r_runtime_state <= 1
          AND wis.r_auto_method_id > '0000000000000000'
          AND wis.a_wq_name = ' ';

and take advantage of query rewrite:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                   |                    |     1 |    70 |     3  (34)| 00:00:01 |
|   1 |  UPDATE                            | DMI_WORKITEM_S     |       |       |            |          |
|   2 |   NESTED LOOPS                     |                    |     1 |    70 |     3  (34)| 00:00:01 |
|   3 |    VIEW                            |                    |     1 |    10 |     3  (34)| 00:00:01 |
|*  4 |     COUNT STOPKEY                  |                    |       |       |            |          |
|   5 |      VIEW                          |                    |     1 |    10 |     3  (34)| 00:00:01 |
|*  6 |       SORT ORDER BY STOPKEY        |                    |     1 |    28 |     3  (34)| 00:00:01 |
|*  7 |        MAT_VIEW REWRITE ACCESS FULL| MV_AUTO_TASKS      |     1 |    28 |     2   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN               | D_1F024BE98000018C |     1 |    60 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(ROWNUM<=90)
   6 - filter(ROWNUM<=90)
   7 - filter("MV_AUTO_TASKS"."R_NEXT_RETRY_DATE"<SYSDATE@!)
   8 - access("R_OBJECT_ID"="WIS2"."R_OBJECT_ID")

Unfortunately, due to CURSOR_SHARING=FORCE recommendation neither option is applicable, and the only “option” is use hex editor to modify documentum binary – in case of oracle we need to place CURSOR_SHARING_EXACT hint and modify where clause.

Dealing with workflow methods. Part II

Well, relying to previously posted diagram, what are the main problems in implementation of workflow engine in Documentum? Timeouts and error handling! Let’s explain why.

Actually, I have no idea what EMC was doing all that time, but current implementation of workflow engine is completely unreliable – workflow agent manages the execution of automatic activities in extremely odd way: it just sends http-requests to JMS and waits for response, in case of timeout it pauses the execution of workflow but meanwhile JMS continues to execute automatic task and sooner or later you will get something like:

DfException:: THREAD: http-0.0.0.0-9080-1; MSG: [DM_WORKFLOW_E_ACTION_NOT_ALLOWED]error:  "This operation is not allowed when the state is 'finished' for workitem '4a0011ec8004f500'."; ERRORCODE: 100; NEXT: null
    at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
    at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
    at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getException(DocbaseMessageManager.java:137)
    at com.documentum.fc.client.impl.connection.docbase.netwise.NetwiseDocbaseRpcClient.checkForMessages(NetwiseDocbaseRpcClient.java:310)
    at com.documentum.fc.client.impl.connection.docbase.netwise.NetwiseDocbaseRpcClient.applyForBool(NetwiseDocbaseRpcClient.java:354)
    at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection$1.evaluate(DocbaseConnection.java:1151)
    at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection.evaluateRpc(DocbaseConnection.java:1085)
    at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection.applyForBool(DocbaseConnection.java:1144)
    at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection.apply(DocbaseConnection.java:1129)
    at com.documentum.fc.client.impl.docbase.DocbaseApi.witemComplete(DocbaseApi.java:1193)
    at com.documentum.fc.client.DfWorkitem.completeEx2(DfWorkitem.java:505)
    at com.documentum.fc.client.DfWorkitem.completeEx(DfWorkitem.java:499)
    at com.documentum.bpm.DfWorkitemEx___PROXY.completeEx(DfWorkitemEx___PROXY.java)

such errors are extremely painful because before restarting failed workflow activities you always need to investigate whether you are actually need to re-execute activity’s body or not, i.e. if auto-activity get failed due to timeout and it’s body does something like i=i+i you will get wrong data upon restart. And it is not a joke, when restarting failed auto-activities you can specify wether it is required to execute activity’s body or not – webtop does allow to perform such thing:

there is just a mistake in API reference manual:

in order to skip execution of activity’s body you need to perform something like:

API> fetch,c,4a024be980001502
...
OK
API> get,c,l,r_runtime_state
...
5
API> get,c,l,r_act_seqno
...
0
API> get,c,l,r_workflow_id
...
4d024be980001101
-- this places auto-activity into 
-- DM_INTERNAL_MANUAL_COMPLETE queue
-- and workflow agent won't pick it up
API> restart,c,4d024be980001101,0,T
...
OK
API> revert,c,4a024be980001502
...
OK
API> get,c,l,a_wq_name
...
DM_INTERNAL_MANUAL_COMPLETE
API> complete,c,4a024be980001502
...
OK
API> 

So far, so good, now we know how to skip execution of activity’s body, but it is still required to investigate the root cause of why auto-activity got failed. Is it possible to prevent these painful timeouts at all? I do think that timeouts is a design gap in workflow engine because workflow agent is executed not inside JMS context. However, we are forced to work with current odd implementation and try somehow resolve such issues. Typically, java code which servers auto-activity execution looks like:

public final int execute(Map params, PrintWriter printWriter) throws Exception {
	parseArguments(params);
	IDfSession session = null; 
			
	try {
		session = getSession();
		IDfWorkitem workitem = getWorkItem();
		if (workitem.getRuntimeState() == IDfWorkitem.DF_WI_STATE_DORMANT) {
			workitem.acquire();
		}
		
		// perform business logic
		
		workitem.complete();
		
		return 0;
	} finally {
		if (session != null) {
			release(session);
		}
	}
}

but the correct one is:

public final int execute(Map params, PrintWriter printWriter) throws Exception {
	parseArguments(params);
	IDfSession session = null;

	try {
		session = getSession();
		session.beginTrans();
		IDfWorkitem workitem = getWorkItem();
		if (workitem.getRuntimeState() == IDfWorkitem.DF_WI_STATE_DORMANT) {
			// this puts exclusive lock on workitem
			// in underlying database and prevents
			// workflow agent from pausing workitem
			workitem.acquire();
		} else if (workitem.getRuntimeState() == IDfWorkitem.DF_WI_STATE_ACQUIRED) {
			// in case of restart workitem state is already
			// acquired, so, we are unable to call acquire,
			// but still need to put exclusive lock in database
			workitem.lock();
		} else {
			throw new DfException("Invalid workitem state");
		}

		// perform business logic

		workitem.complete();
		session.commitTrans();

		return 0;
	} finally {
		if (session != null) {
			if (session.isTransactionActive()) {
				session.abortTrans();
			}
			release(session);
		}
	}
}

next challenge is error handling. The problem is when we are dealing with Documentum we may face with a lot of weird errors, and some of these errors are soft (for example, DM_SYSOBJECT_E_VERSION_MISMATCH) – in order to resolve such errors we just need to reiterate the execution of code, others are not – we need to investigate the root cause, and it is a good idea in case of soft errors restart failed auto-activities automatically, so I invented following pattern:

@Override
public final int execute(Map params, PrintWriter printWriter) throws Exception {
	parseArguments(params);
	IDfSession session = null;
	IDfWorkitem workitem = null;
	try {
		try {
			session = getSession();
			session.beginTrans();
			workitem = getWorkItem();
			if (workitem.getRuntimeState() == IDfWorkitem.DF_WI_STATE_DORMANT) {
				// this puts exclusive lock on workitem
				// in underlying database and prevents
				// workflow agent from pausing workitem
				workitem.acquire();
			} else if (workitem.getRuntimeState() == IDfWorkitem.DF_WI_STATE_ACQUIRED) {
				// in case of restart workitem state is dormant
				// so, we are unable to call acquire, but still
				// need to put exclusive lock in database
				workitem.lock();
			} else {
				throw new DfException("Invalid workitem state");
			}

			// perform business logic

			if (isSomethingWrong()) {
				haltWorkitem(workitem);
				session.commitTrans();
				return 0;
			}

			workitem.complete();
			session.commitTrans();

			return 0;
		} finally {
			if (session.isTransactionActive()) {
				session.abortTrans();
			}
		}
	} catch (DfException ex) {
		if (!isSoftException(ex)) {
			throw ex;
		}
		haltWorkitem(workitem);
		return 0;
	}
}

protected void haltWorkitem(IDfWorkitem workitem) throws DfException {
	IDfSession session = workitem.getSession();
	IDfWorkflow workflow = (IDfWorkflow) session.getObject(workitem.getWorkflowId());
	// here transaction may be already inactive
	boolean txStartsHere = !session.isTransactionActive();
	try {
		// we need to start new transaction
		// in order to lock workitem
		if (txStartsHere) {
			session.beginTrans();
		}
		// exclusive access to workitem
		workitem.lock();
		workitem.revert();
		// restarting workitem - we are in transaction,
		// so workflow agent won't pickup it
		// actually we need to check both workitem
		// and workflow states
		workflow.restart(workitem.getActSeqno());
		workitem.revert();
		// let dm_WFSuspendTimer job to restart
		// our workitem
		workflow.haltEx(workitem.getActSeqno(), 1);
		if (txStartsHere) {
			session.commitTrans();
			txStartsHere = false;
		}
	} finally {
		if (txStartsHere && session.isTransactionActive()) {
			session.abortTrans();
		}
	}
}