I am maintaining an application creating an Oracle DB via JDBC. Starting from today this query:
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 p.table_name = :1
AND p.owner = :3
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 fktable_schem,
fktable_name ,
key_seq
started becoming really slow due to some oracle internals as it seems to be the same for all my branches.
Does somebody know one possible reason and how to face this?
Regards, Nunzio
Data dictionary or fixed object statistics might be old, try re-gathering them:
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
alter system flush shared_pool;
Even that does not necessarily gather statistics for all system objects. Some objects, like X$KFTBUE
, must be gathered manually. Although that's a rare data dictionary problem that may not be relevant here.
If that doesn't work some next possible steps are looking at tools like SQL Tuning Advisor to create a profile, or using SQL Plan Management to force the optimizer to use a specific plan that has worked before. Tuning a data dictionary query can be very difficult since you don't have much control.
here is another more graceful solution.. I found that forcing the rule base optimizer with a sql patch also works.. 2 patches are necessary because sometimes the jdbc driver uses :1 and :3 as bind variables and sometimes it uses :2 & :4.. The SQL must match exactly for the patch to work.
run this in your database as sysdba..
begin
dbms_sqldiag_internal.i_create_patch (
sql_text =>'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 p.table_name = :1 AND p.owner = :3 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 fktable_schem, fktable_name, key_seq' ,
hint_text => 'RULE',
name => 'jdbcpatch');
end;
/
begin
dbms_sqldiag_internal.i_create_patch (
sql_text =>'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 p.table_name = :2 AND p.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 fktable_schem, fktable_name, key_seq' ,
hint_text => 'RULE',
name => 'jdbcpatch2');
end;
/
The query in the question is generated by a call to java.sql.DatabaseMetaData.getExportedKeys()
which delegates to oracle.jdbc.OracleDatabaseMetaData.getExportedKeys()
to enumerate foreign keys referencing given table.
As stated in @Jon's answer, Oracle sometimes uses suboptimal plan for this query that may or may not be avoided by gathering statistics.
Other alternatives if the code can be changed:
The second option was chosen by Liquibase project that used to call DatabaseMetaData
in older versions.
New versions use optimized query with proper joins from CORE-1844:
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_constraints p
INNER JOIN all_cons_columns pc ON pc.owner = p.owner
AND pc.constraint_name = p.constraint_name
AND pc.table_name = p.table_name
INNER JOIN all_constraints f ON p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
INNER JOIN all_cons_columns fc ON fc.owner = f.owner
AND fc.constraint_name = f.constraint_name
AND fc.table_name = f.table_name
AND fc.position = pc.position
WHERE p.owner = :jdbcSchemaName
AND p.constraint_type in ('P', 'U')
AND f.constraint_type = 'R'
ORDER BY fktable_schem, fktable_name, key_seq
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With