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?
We currently are using the following two views to achieve some performance gain.
create or replace view confdb50.all_constraints as
create or replace view confdb50.all_cons_columns as
CONFDB50 is the schema where that our Confluence install runs against.
Atlassian support does not recommend these two views (of course).
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG