JDBC自动查询变得非常缓慢

Question

JDBC自动查询变得非常缓慢

5

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

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 内部原因，我的所有分支似乎都变得非常缓慢。

有人知道可能的原因以及如何应对吗？

谢谢， Nunzio

- Nunzio Visciano

你尝试过为你的查询生成解释计划吗？ - matt freake

我能做到，这有什么帮助吗？我对这个内容没有控制权。我的观点是：我们是否漏掉了任何维护步骤？这个软件每天在我们的CI中运行数十次。现在我们遇到了这个奇怪的问题。 - Nunzio Visciano

它会告诉你查询为什么很慢。如果你不知道是什么导致了缓慢，你就无法决定哪些维护步骤可能会有所作为。 - matt freake

5个回答

2

这里有另一种更优雅的解决方案。我发现使用 SQL 补丁强制执行规则基础优化器也可以起作用。需要 2 个补丁，因为有时 JDBC 驱动程序使用 :1 和 :3 作为绑定变量，有时使用 :2 和 :4。SQL 必须完全匹配才能使补丁生效。

以 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;
    /

- Sagan

1

这是一个有趣的方法，但是使用文档化的计划管理功能可能会更好，比如配置文件、大纲、SPM等。 - Jon Heller

2

问题中的查询是通过调用 java.sql.DatabaseMetaData.getExportedKeys() 生成的，该方法委派给 oracle.jdbc.OracleDatabaseMetaData.getExportedKeys() 来枚举引用给定表的外键。

正如 @Jon 的答案所述，Oracle 有时会使用次优的计划来执行此查询，可能可以通过收集统计信息来避免。

其他替代方案（如果可以更改代码）：

- 查看 Easiest way to obtain database metadata in Java? 获取替代实现。 - 重写查询并直接调用它。

第二个选项被 Liquibase 项目选择，旧版本中使用了 DatabaseMetaData 的调用。新版本使用了优化的查询和适当的联接，来自 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

- Vadzim

1

我找到了一个作弊的方法.. 在反向工程模式之前，以jdbc连接的用户身份运行此命令来欺骗模式。

CREATE TABLE all_constraints AS
  SELECT owner,
         constraint_name,
         constraint_type,
         table_name,
         r_owner,
         r_constraint_name,
         delete_rule,
         status,
         deferrable,
         deferred,
         validated,
         generated,
         bad,
         rely,
         last_change,
         index_owner,
         index_name,
         invalid,
         view_related
  FROM   all_constraints;

CREATE TABLE all_cons_columns AS
  SELECT *
  FROM   all_cons_columns;

CREATE INDEX ac1
  ON all_constraints (owner, constraint_name, table_name);

CREATE INDEX acc1
  ON all_cons_columns (owner, constraint_name, table_name);

那么这个问题中的查询确实非常耗时..不足之处是您需要不时地刷新它..也许将其制作成材料化视图？

- Sagan

0

使用Oracle EXPLAIN PLAN http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm （使用结果找到瓶颈并重新编写或更改查询以使其运行更快）。
确保在表上使用索引并更新索引。
使用分区。
清理一些不再需要的数据。
如果可以，使用Hibernate（如果这是一个遗留应用程序，则可能不容易实现），因为它会为您优化查询，您将不需要编写JDBC查询。
最后，请查看Oracle性能调整文档http://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#PFGRF02503

- grepit

1

这是一个内部查询，我无法控制这些表。顺便说一下，我们使用JPA。 - Nunzio Visciano

@NunzioVisciano，不确定您所说的内部查询和对表没有控制权是什么意思。如果您想要更好的性能，您需要对表和查询进行调整。 - grepit

我的意思是：JDBC和JPA使用一些内部数据结构（包括表）来执行它们的工作。对于这个查询，它不在我们的代码库中，它会自动启动。所有这些都意味着，根据我目前的知识，我们无法更改任何模式和查询本身。我希望现在我已经表达清楚了。 - Nunzio Visciano

你能直接在数据库上运行查询吗？那里也很慢吗？ - matt freake

2

伙计们，我认为情况不太清楚。此查询中提到的表是Oracle表，由于我们的软件在数十个不同的服务器上运行，我根本不能触及它们。在我看来，服务器发生了一些需要通过某些维护程序修复的问题。有人遇到过类似的情况吗？ - Nunzio Visciano

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jon Heller · Accepted Answer

数据字典或固定对象统计信息可能已经过时，请尝试重新收集它们：

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
alter system flush shared_pool;

即使如此，也不一定能够收集所有系统对象的统计信息。例如像X$KFTBUE这样的对象必须手动收集。虽然这是一个罕见的数据字典问题，可能与此无关。

如果这样做不起作用，下一步可以尝试使用SQL调优顾问工具创建配置文件，或者使用SQL计划管理来强制优化器使用之前有效过的特定计划。调整数据字典查询可能非常困难，因为您没有太多控制权。