JDBC自动查询变得非常慢

本文介绍了JDBC自动查询变得非常慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在维护一个通过JDBC创建Oracle DB的应用程序。从今天开始这个查询：

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

由于某些oracle内部构件开始变得非常慢，因为它对我所有的分支机构来说似乎都是一样的。

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?

问候， Nunzio

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;

即使这不一定收集所有系统对象的统计信息。必须手动收集某些对象，例如 X $ KFTBUE 。虽然这是一个罕见的数据字典问题，但在这里可能并不相关。

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.

如果这不起作用，下一步可能的步骤正在寻找像SQL Tuning Advisor这样的工具来创建一个配置文件，或使用SQL计划管理强制优化程序使用以前工作过的特定计划。调整数据字典查询可能非常困难，因为您没有太多控制权。

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.