Stash-JIRA integration is slow after Stash upgrade using Oracle
Integration with JIRA becomes very slow after a Stash upgrade.
This can happen in Stash installations that use Oracle with automatic statistics collection disabled.
Using a browser or an HTTP client (such as curl) invoke the following URL:
The invocation can take several minutes to complete or even timeout (specially if Stash is running behind a proxy).
Oracle automatic statistics collection is disabled. This can cause Oracle to use the wrong index for the integration queries.
Note automatic statistics collections is enabled by default.
Verify that automatic statistics collections is disabled:
- Using Oracle visual tools check the section "Automated Maintenance Tasks" and make sure "Optimizer statistics gathering" is disabled.
- Or run the following queries to confirm that automatic statistics collections is disabled and when it was last run:
SELECT client_name, status FROM dba_autotask_operation; SELECT * FROM dba_autotask_schedule; SELECT task_name, status, to_char(last_good_date, 'YYYY-MM-DD HH24:MI:SS') last_good_date, last_good_duration FROM dba_autotask_task WHERE client_name = 'auto optimizer stats collection';
Calculate new statistics and possibly enable automatic statistics collection as suggested by Oracle's documentation.
Example of statistics collection instruction:
exec dbms_stats.gather_database_stats(estimate_percent => 100, method_opt => 'for all columns size auto',options => 'Gather' ,cascade => true, degree => 4);
Note this can be a very time and cpu consuming operation.
To enable automatic statistics collection refer to Oracle's documentation.