JIRA: Additional indexes for performance

Some indices for purrformance, that piled up during the years:

-- Fix Kepler idiocies:
ALTER INDEX PK_KISSUESTATE RENAME TO ISSUE_STATE_IDX;
ALTER INDEX PK_KPLUGINSCFG RENAME TO PLUGIN_CFG_IDX;
ALTER INDEX PK_KSTATEVALUES RENAME TO STATE_VALUES_IDX;

-- Standard additions
CREATE INDEX IX_NOTIF_INST_MSGID ON NOTIFICATIONINSTANCE (MESSAGEID ASC);
CREATE INDEX IX_CHANGEGROUP_AUTHOR ON CHANGEGROUP (AUTHOR ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTHOR_CREATED ON CHANGEGROUP (AUTHOR ASC, CREATED ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTH_CREA_ISSUE ON CHANGEGROUP (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_FILEATTACHMENT_FILENAME ON FILEATTACHMENT (FILENAME ASC, '1'); -- See Note
CREATE INDEX IX_JIRAACTION_AUTHOR ON JIRAACTION (AUTHOR ASC);
CREATE INDEX IX_JIRAACTION_AUTHOR_CREATED ON JIRAACTION (AUTHOR ASC, CREATED ASC); 
CREATE INDEX IX_JIRAACTION_AUTH_CREAT_ISSUE ON JIRAACTION (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_JIRAACTION_TYPE_LEVEL ON JIRAACTION (ID ASC, ACTIONTYPE ASC, ACTIONLEVEL ASC); 
CREATE INDEX IX_JIRAISSUE_ISSUESTATUS ON JIRAISSUE (ISSUESTATUS ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJ_ASSIGNEE ON JIRAISSUE (PROJECT ASC, ASSIGNEE ASC);
CREATE INDEX IX_JIRAISSUE_PROJECT ON JIRAISSUE (PROJECT ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJECT_UPDATED ON JIRAISSUE (PROJECT ASC, UPDATED ASC);
CREATE INDEX issue_reporter ON JIRAISSUE (REPORTER ASC); -- lately introduced by Atlassian in JIRA 6.3
CREATE INDEX IX_JIRAISSUE_SUMMARY ON JIRAISSUE (SUMMARY ASC, '1'); -- See Note
CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC); -- for idiotic pagination queries
CREATE INDEX IX_JIRAISSUE_WF_PKEY_1 ON JIRAISSUE (WORKFLOW_ID ASC, PKEY ASC, '1');
CREATE INDEX IX_OS_WFENTRY_ID_NAME ON OS_WFENTRY (ID ASC, NAME ASC);

-- customfieldvalue is large. we need it to be created quickly, then turn off parallelism.
CREATE INDEX IX_CUSTOMFIELDVALUE_ISSUE ON CUSTOMFIELDVALUE (ISSUE ASC) PARALLEL 12; ALTER INDEX IX_CUSTOMFIELDVALUE_ISSUE NOPARALLEL; -- can be _really_ big depending on your CF usage

-- bitmap indexes:
CREATE BITMAP INDEX IX_OS_WFENTRY_STATE ON OS_WFENTRY (STATE ASC); -- degree of cardinality is typically below 1 ppm.

-- recreate these indexes:
DROP INDEX PRMSSN_SCHEME; CREATE BITMAP INDEX PRMSSN_SCHEME ON SCHEMEPERMISSIONS(SCHEME ASC);
DROP INDEX WF_ENTRYID; CREATE INDEX WF_ENTRYID ON OS_CURRENTSTEP (ENTRY_ID, STEP_ID);
DROP INDEX ISSUE_WORKFLOW; CREATE INDEX ISSUE_WORKFLOW ON JIRAISSUE (WORKFLOW_ID, ISSUESTATUS);

Note: On multi column indices containing a '1': The '1' here is actually a static char(1) column, always containing '1', and even if the first (the real) column's value is null, it will be stored in the index, and thus will be searchable.

JIRA: When were the watchers added to an issue?

SELECT
   SOURCE_NAME AS WATCHER,
   A.CREATED             ,
   TO_CHAR(A.CREATED, 'yyyy-mm-dd hh24:mi:ss') FANCY_CREATED
FROM
   USERASSOCIATION A
INNER JOIN JIRAISSUE I
ON
   I.ID = A.SINK_NODE_ID
INNER JOIN PROJECT P
ON
   P.ID = I.PROJECT
WHERE
   P.PKEY     = 'TICKT' AND
   I.ISSUENUM = '1114';

Java: Why is Object.wait() always Runnable?

In the Java thread dumps you see some parking threads that say: "java.lang.Thread.State: RUNNABLE", but obviously they are waiting for a monitor, so it should be "timed_wait" or "waiting".
Also, it says they are in a native frame.
How?

1. Basically, all frames that are in native code, are Runnable. The jvm just doesn't care, and also can't check what the state is. So it will always be Runnable if the frame is native.
2. When you call the Object.wait() through any kind of consequences, it will simply call Object.wait(0).
3. However, Object.wait(timeout) is a native call to a JVM-internal function. (Hence the default threadstate.)
4. Upon checking the source in the OpenJDK site, we find it simply routes the call to a function called JVM_MonitorWait, which is defined in jvm.cpp.
5. This function eventually calls ObjectSynchronizer::wait([obj], [timeout], [traps]). This wait creates an object monitor based on the passed object reference. Then, the ObjectMonitor's wait method is called. (Note: sources linked for Linux target)
6. This ObjectMonitor::wait manages the waiting queue. Calls park() on the current thread.
7. In fact, park() is a member of os::PlatformEvent, so we need to go further.
8. os::PlatformEvent:park() does an old-fashioned looped lock checking with pthread_cond_wait.

Java To Trust GoDaddy Class2 G2 Certs

Sadly, GoDaddy's "Class 2" and "Class 2 - G2" root cacerts are not included in the Java7 packages, so they are not trusted by default.

So we'll have to add the CA certificates to the Java TrustStore.

I use Apache Tomcat, so I'll need another step: configuring where to look for the truststore file, which I'll add to the CATALINA_OPTS, but you should use setenv.sh or whatever your deployment process forces you to. For some reason I haven't got the urge to look up, Tomcat does NOT pick up the truststore by default. Maybe it's just some PEBKAC in the setup somewhere else.

Align the cacerts file location to your needs.

-Djavax.net.ssl.trustStore=/opt/apps/jira/jdk/jre/lib/security/cacerts -Djavax.net.ssl.trustStorePassword=changeit

Firstly, fetch the certificates we want to trust:

1. https://certs.godaddy.com/repository/gd_bundle.crt
2. https://certs.godaddy.com/repository/gdroot-g2.crt
3. https://certs.godaddy.com/repository/gd-class2-root.crt
4. https://certs.godaddy.com/repository/gdig2.crt

Sometimes they interleave (eg. gdig2 is an intermediate cert, connecting Class2 and G2), if it throws an error about already being imported, ignore the error.

Secondly, import the certs to trust them (do it on all four):

keytool -import -trustcacerts -alias gd_bundle -file gd_bundle.crt -keystore /opt/apps/jira/jdk/jre/lib/security/cacerts

Here, I use the same alias as the filename. Again, align the cacerts's file location to your needs.
The keytool binary will ask for the password, which is changeit by default. If you change it, remember to change the tomcat parameter trustStorePassword also.

Lastly, restart Tomcat.

JIRA: Check JIRA issues with null status

So you see errors like: "java.lang.RuntimeException: Could not find workflow status for issue SOMEPRJ-123456.".
It suggests, the jiraissue.issuestatus column on that issue is null.
How to check:

SELECT ID FROM JIRAISSUE WHERE ISSUESTATUS IS NULL;

How to fix it in Oracle:
Prerequisites: For the TEMP_JIRAWORKFLOWS, see here: http://kozelljozsef.blogspot.hu/2014/11/jira-check-workflow-current-step-entries.html

MERGE INTO jiraissue USING
(
  WITH W_JIRAISSUE AS
  ( SELECT
      I.ID AS ISSUEID,
      I.WORKFLOW_ID,
      I.ISSUESTATUS,
      W.NAME AS WFNAME,
      C.STEP_ID AS CURRENT_STEP_ID
    FROM JIRAISSUE I
    INNER JOIN OS_WFENTRY W
    ON I.WORKFLOW_ID = W.ID
    INNER JOIN OS_CURRENTSTEP C
    ON I.WORKFLOW_ID = C.ENTRY_ID
    WHERE I.ISSUESTATUS IS NULL
  )
SELECT
  I.ISSUEID,
  I.ISSUESTATUS,
  WF.LINKEDSTATUSID AS LINKED_STATUS_ID
FROM W_JIRAISSUE I
INNER JOIN TEMP_JIRAWORKFLOWS WF
ON WF.WORKFLOWNAME = I.WFNAME AND WF.STEPID = I.CURRENT_STEP_ID
) GOODSTATUS ON (GOODSTATUS.issueid = JIRAISSUE.ID)
WHEN MATCHED THEN UPDATE
    SET JIRAISSUE.ISSUESTATUS = GOODSTATUS.LINKED_STATUS_ID;

This essentially looks up the workflow step associated with the failing issue, reads out the linked status id that should be written back to the issuestatus column.

JIRA: Check workflow current step entries

An issue can have an invalid status (one that does not exist for the workflow) if a Workflow Migration or Bulk Move died half way through.

An issue must have exactly 1 (one) currentstep. It tells, how did we got here.
If it has zero, we should insert one for the current wfentry based on the workflow.
If it has more than one, we should only keep the latest. (Not covered by this blog entry.)

How to check:

SELECT COUNT(*) AS PROBLEM_CNT
FROM JIRAISSUE I    
LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID 
INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL;

Essentially, this check is an antijoin, looking for issues without a corresponding currentstep (however the condition also gives us the multiple ones too) OR with an incorrect step.

How to fix:

UPDATE: Now, as converting the XML-s to a table is pretty expensive, we'll create a temporary table, and fill it in with the workflow-step-status tuples.

-- Create me once.
CREATE GLOBAL TEMPORARY TABLE TEMP_JIRAWORKFLOWS
(WORKFLOWNAME VARCHAR2(255 CHAR),
STEPID NUMBER,
LINKEDSTATUSID VARCHAR2(16))
ON COMMIT PRESERVE ROWS;

Every time we connect, we'll have to fill it out.
Note: you can create a real table with triggers to reflect the changes on the original, or have a materialized view. I choose this cumbersome way, because our DBA-s are afraid of triggers (wtf?!), and also they does not let me create materialized views.

INSERT INTO TEMP_JIRAWORKFLOWS
WITH W_JIRAWORKFLOWS AS
  ( SELECT
        WORKFLOWNAME,
        XMLTYPE.CREATEXML(REPLACE(DESCRIPTOR,
        '<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">'
        )) AS DESCRIPTOR
     FROM JIRAWORKFLOWS)
SELECT
  W_JIRAWORKFLOWS.WORKFLOWNAME,
  WF.STEPID                   ,
  WF.LINKEDSTATUSID
FROM
  W_JIRAWORKFLOWS,
  XMLTABLE('/workflow/steps/step[@id]' PASSING W_JIRAWORKFLOWS.DESCRIPTOR
     COLUMNS
        "STEPID" NUMBER PATH '@id',
        "LINKEDSTATUSID" VARCHAR2(16) PATH 'meta[@name="jira.status.id"]/text()'
     ) WF;

COMMIT;
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'Check workflow current step entries';
 
MERGE INTO OS_CURRENTSTEP USING
( SELECT
       ROWNUM+(SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME = 'OSCurrentStep') AS N,
       I.WORKFLOW_ID     AS ENTRY_ID,
       WF.STEPID         AS STEP_ID ,
       WF.LINKEDSTATUSID AS STATUS
  FROM JIRAISSUE I
  LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
  INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID
  INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
  WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL
) GOODSTEP ON (GOODSTEP.ENTRY_ID = OS_CURRENTSTEP.ENTRY_ID)
WHEN MATCHED THEN UPDATE
    SET OS_CURRENTSTEP.STEP_ID = GOODSTEP.STEP_ID
WHEN NOT MATCHED THEN INSERT
VALUES (GOODSTEP.N, GOODSTEP.ENTRY_ID, GOODSTEP.STEP_ID, 0, NULL, SYSDATE, NULL, NULL, GOODSTEP.STATUS, NULL);
 
UPDATE SEQUENCE_VALUE_ITEM 
   SET SEQ_ID = (1 + (SELECT MAX(ID) FROM OS_CURRENTSTEP)) 
   WHERE SEQ_NAME = 'OSCurrentStep';
 
COMMIT;

The fix is more complex than the others. We need an ID range taken from the SEQUENCE_VALUE_ITEM table, so later nothing tries to use our id-s. I wonder, why Atlassian does not utilize things like autoincrement fields or sequences...
So we can fetch the step_id and status_id that took us to the current step, essentially everything we need in the currentstep. Then, we increment the sequence.

Oracle: quick notes on ojdbc6.jar

(If not specified, package is "oracle.jdbc.driver". Our current driver type is thin.)
(Need more details? JD-GUI and ojdbc6_g.jar is all what you need.)

1. oracle.jdbc.OracleDriver --> OracleDriver
1. connect()
1. oci: T2CDriverExtension (driverextension id: 2) [T2 means "type 2", aka using native-API]
2. thin: T4CDriverExtension (driverextension id: 0) [T4 means "type 4", aka pure java implementation]
2. Instatiates a T4CDriverExtension
1. getConnection() --> T4CConnection
3. Casts back the T4CConnection to PhysicalConnection, then returns.
2. PhysicalConnection.createStatement(CURSOR_TYPE, CONCURRENCY_TYPE, IGNORED)
1. return new OracleStatementWrapper( T4CDriverExtension.allocateStatement() --> T4CStatement)
3. OracleStatementWrapper.executeQuery() [To have a ResultSet]
1. OracleStatement.executeQuery()
1. OracleStatement.doExecuteWithTimeout()
1. T4CStatement.executeForRows()
1. T4CStatement.doOall8(PARSE, EXEC, FETCH, DESCR, DEF, 1stRow)
1. T4C8Oall.doOALL(PARSE, EXEC, FETCH, DESCR, DEF, kind, cursorid, sqlObject.getSqlBytes() [teh query], ...)
1. ...
2. return new OracleResultSetImpl(this.[pysical]connection, this[OracleStatement]);
2. ret
4. [while] OracleResultSetImpl.next()
1. OracleResultSetImpl.close_or_fetch_from_next()
1. T4CStatement.fetch()
1. T4CStatement.doOall8(f,f,t,f,f)
1. T4C8Oall.doOALL()
1. T4CTTIfun.doRPC() [TTI: Two-Task Interface]
1. marshalFunHeader()
1. T4CTTIMsg.marshalTTCcode()
1. T4CMAREngine.marshalUB1()
1. [T4CSocketOutputStreamWrapper.write(aByte)]
2. marshal() -- abstract: TODO
3. receive()
1. T4C8Oall.readDCB()
2. T4C8TTILob.readLOBD()
5. OracleResultSetImpl.getLong(i)
1. OracleStatement,accessors[i-1]
1. LongAccessor
1. CharCommonAccessor.getLong(i-1)
1. Long.parseLong(CharCommonAccessor.getString(i-1))
1. cuts the string out of an array of bytes received as a LOB.

JIRA: Check workflow entry states are correct

Symptom: You see no transition buttons on the ticket, but when you edit it, they come back.
Cause: The workflow entry associated with the issues in screwed up. Its state is "created"(zero) or null instead of "activated"(one).
How to check:

SELECT PROJECT.PKEY || '-' || JIRAISSUE.ISSUENUM AS PKEY, JIRAISSUE.WORKFLOW_ID, OS_WFENTRY.STATE
FROM JIRAISSUE
INNER JOIN OS_WFENTRY ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
INNER JOIN PROJECT ON PROJECT.ID = JIRAISSUE.PROJECT
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0;

These should return all the issues you have the problem with.

How to fix it in Oracle:

UPDATE OS_WFENTRY SET STATE = 1
WHERE ID IN ( SELECT OS_WFENTRY.ID FROM OS_WFENTRY
INNER JOIN JIRAISSUE ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0);

There are other workflow state codes, but the official correction in the integrity checker is to set it to 1 (activated).

Oracle: JIRA: index for the pagination query

I found a select that executes with a cost over 16k, and I wondered, how could I speed this up.
(Sometimes it ran for 10.5 seconds!)

SELECT SQ_.ID FROM
  ( 
    SELECT ID FROM JIRAISSUE
    WHERE PROJECT IN(:1, :2)
    ORDER BY UPDATED DESC
  ) SQ_
WHERE ROWNUM <= 10;

It seems, only the IN-s contents are changing over the course, so we could fit an index for that. Lets see, what we have:

• ORDER BY UPDATED DESC - this will be the leading column in descending order,
• PROJECT - filter, this should be in,
• ID - as we projected this column, if we put this in the index, it can be an index-only query.

CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC);

Results in an index full scan and a count-with-stopkey. Yaay, the cost is 3 (three), in a table with ~2 million rows.

Oracle: scan types

• Full Table Scan - Highest cost. This happens, if:
• No index exists.
• There is an index, but
• Without columns that the query is using, or
• Aggregate function is used, AND index allows nulls, or
• Multi-column index, and you're not using the first column in it.
• Resultset expected to be so big, that it is cheaper to read through the table than use the index. (Fewer large IO calls are cheaper than lot of tiny ones.)
• The table is too small. (Smaller than DB_FILE_MULTIBLOCK_READ_COUNT)
• Table parallelism too high. (Small partitions are easier to scan - see previous item)
• Table Access by rowid - Getting rowids through an index.
• An index returns with a set of rowid-s.
• There are columns specified that are not in the index.
• Table Access Sample - When using block sampling.
• Table Access Inmemory - Since 12cR1, on tables using inmemory parameter.
• Index Unique Scan - unique means, at most one rowid exists for a given index entry, so the search can stop after the first match.
• Unique Index exists and can be used, and
• Equality operator being in place.
• Index Range Scan (Descending) - ordered scan of values, even for ranges.
• Index leading columns with <,>,= operators, and
• AND combination of those (at least for leading columns).
• Can be forced by using BETWEEN.
• Descending: Order By has Desc parameter in it.
• Index Full Scan - Uses index order for Order By.
• Referencing an indexed column,
• Order By present, in a notnull column,
• All columns in Order By are indexed,
• Order of columns in Order By match the order of the leading index columns.
• Index Fast Full Scan - Full index scan, not accessing the table.
•  All columns required are in the index(es).
• Index Skip Scan
• Leading column in a composite index is not used,
• Leading column has only a few distinct values,
• Non-leading, referenced column has many distinct values.
• Hash Join (Index Join Scan)
• Hash join of multiple indexes returns all the required columns (without table access),
• Table access is more expensive than an index join.
• Table Access Cluster (Cluster Scan)
• Using an indexed cluster.
• Needs a simple Cluster used by all the tables.
• Table Access Hash (Hash Scan)
• In a hash cluster, rows are grouped into the same block by their hash value.
• Needs a Hash Cluster used by all the tables.

Leading column is the first column in a composite index.

CREATE INDEX ix_thisisanindex ON table42(leadingcolumn, notleading, stillnotleading);