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
Community showcase
Posted Oct 11, 2018 in Confluence

What are your project planning tips?

Hello Community,  Jessica here from the Confluence product marketing team! Today I wanted to get your takes on project planning –– what works, what doesn’t, how do you know if you’re doing it r...

327 views 2 4
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