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.

A FATAL error has occurred. Part II

20 months ago I described a bizarre behaviour in webtop, now it is time to describe how to solve such problem (actually, customer have shared a simple testcase when user changes his password via Ctrl+Alt+Del on Windows computer and after that he need to clear cookies in order to force webtop to work). I do think the best option here is to replace actual user’s password by login ticket and the best candidate for that is com.documentum.web.formext.session.AuthenticationService:

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.documentum.fc.client.IDfSession;
import com.documentum.fc.client.IDfSessionManager;
import com.documentum.fc.common.DfException;
import com.documentum.fc.common.DfLoginInfo;
import com.documentum.fc.common.IDfLoginInfo;

/**
 * @author Andrey B. Panfilov <andrey@panfilov.tel>
 */
public class AuthenticationServiceCustom extends AuthenticationService {

    public AuthenticationServiceCustom() {
        super();
    }

    @Override
    public void login(HttpSession httpSession, String principalName,
            String docbase, HttpServletRequest req)
        throws DfException {
        super.login(httpSession, principalName, docbase, req);
        replaceTicket(docbase);
    }

    @Override
    public void login(HttpSession httpSession, String principalName,
            String docbase)
        throws DfException {
        super.login(httpSession, principalName, docbase);
        replaceTicket(docbase);
    }

    @Override
    public void login(HttpSession httpSession, String docbase,
            String userLoginName, String userPassword, String domain)
        throws PasswordExpiredException, DfException {
        super.login(httpSession, docbase, userLoginName, userPassword, domain);
        replaceTicket(docbase);
    }

    @Override
    public void login(HttpSession httpSession, String docbase, String domain,
            Object binaryCredential)
        throws DfException {
        super.login(httpSession, docbase, domain, binaryCredential);
        replaceTicket(docbase);
    }

    @Override
    public void login(HttpSession httpSession, String docbase, String domain,
            Object binaryCredential, HttpServletRequest req)
        throws DfException {
        super.login(httpSession, docbase, domain, binaryCredential, req);
        replaceTicket(docbase);
    }

    @Override
    public void login(HttpSession httpSession, String docbase,
            String userLoginName, String password, String domain,
            HttpServletRequest req)
        throws DfException {
        super.login(httpSession, docbase, userLoginName, password, domain, req);
        replaceTicket(docbase);
    }

    private void replaceTicket(String docbase) throws DfException {
        IDfSessionManager sessionManager = SessionManagerHttpBinding
                .getSessionManager();
        IDfSession session = null;
        try {
            int dotIndex = docbase.indexOf('.');
            if (dotIndex != -1) {
                docbase = docbase.substring(0, dotIndex);
            }
            session = sessionManager.getSession(docbase);
            int timeout = session.getServerConfig()
                    .getInt("max_login_ticket_timeout");
            String ticket = session.getLoginTicketEx(null, "docbase", timeout,
                    false, docbase);
            String userName = session.getLoginUserName();
            if (sessionManager.hasIdentity(docbase)) {
                sessionManager.clearIdentity(docbase);
            }
            IDfLoginInfo loginInfo = new DfLoginInfo(userName, ticket);
            sessionManager.setIdentity(docbase, loginInfo);
        } finally {
            if (session != null) {
                sessionManager.release(session);
            }
        }
    }

}