Improving startup performance with OJDBC, an Oracle DB, and Confluence

[Randall Fisher]

During the startup of Confluence, the following query (among others) is being called repeatedly:

SELECT NULL AS pktable_cat,

p.owner as pktable_schem,

p.table_name as pktable_name,

pc.column_name as pkcolumn_name,

NULL as fktable_cat,

f.owner as fktable_schem,

f.table_name as fktable_name,

fc.column_name as fkcolumn_name,

fc.position as key_seq,

NULL as update_rule,

decode (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) as delete_rule,

f.constraint_name as fk_name,

p.constraint_name as pk_name,

decode(f.deferrable, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6) deferrability

FROM all_cons_columns pc, all_constraints p, all_cons_columns fc, all_constraints f

WHERE 1 = 1

AND f.table_name = :2

AND f.owner = :4

AND f.constraint_type = 'R' AND p.owner = f.r_owner

AND p.constraint_name = f.r_constraint_name

AND p.constraint_type = 'P'

AND pc.owner = p.owner

AND pc.constraint_name = p.constraint_name

AND pc.table_name = p.table_name

AND fc.owner = f.owner

AND fc.constraint_name = f.constraint_name

AND fc.table_name = f.table_name

AND fc.position = pc.position

ORDER BY pktable_schem, pktable_name, key_seq

Unfortunately, this query is taking 1:30-2:00 minutes to execute.

This is just one of the 'SYS' tables that is being queried.  Because of it's long running time, it has floated to the top of the list of concerns.  This SQL is generated by getImportedKeys() method of DatabaseMetaData class in OJDBC.

From what I have learned, Confluence is not making this query directly.  It is calling a method in JDBC and the client is in turn making the query.

We have recollected Data Dictionary stats, but that has not improved performance.

Any ideas on how to improve performance in this area?

[Randall Fisher]

We currently are using the following two views to achieve some performance gain.

create or replace view confdb50.all_constraints as
 select "OWNER",
 "CONSTRAINT_NAME",
 "CONSTRAINT_TYPE",
 "TABLE_NAME",
 "SEARCH_CONDITION",
 "SEARCH_CONDITION_VC",
 "R_OWNER",
 "R_CONSTRAINT_NAME",
 "DELETE_RULE",
 "STATUS",
 "DEFERRABLE",
 "DEFERRED",
 "VALIDATED",
 "GENERATED",
 "BAD",
 "RELY",
 "LAST_CHANGE",
 "INDEX_OWNER",
 "INDEX_NAME",
 "INVALID",
 "VIEW_RELATED",
 "ORIGIN_CON_ID"
 from sys.user_constraints;

create or replace view confdb50.all_cons_columns as
 select "OWNER",
 "CONSTRAINT_NAME",
 "TABLE_NAME",
 "COLUMN_NAME",
 "POSITION"
 from sys.user_cons_columns;

CONFDB50 is the schema where that our Confluence install runs against.

Atlassian support does not recommend these two views (of course).