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

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?

 

1 answer

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).

 

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Friday in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

285 views 11 10
Join discussion

Atlassian User Groups

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!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you