Oracle select 查询花费的时间太长

Question

我们有一个基于.Net 的内部应用程序，它调用Oracle (10g) 中的某些过程。运行这些查询之一来获取这些过程的输入/输出参数。这是一个非常简单的选择查询。但即使在最好的情况下，也需要 3 秒钟。每天至少有几次它开始花费超过 40 秒并导致我们的 .Net 应用程序超时。

选择查询是：

SELECT   a.argument_name,
           a.data_type,
           a.in_out,
           NVL (a.data_length, 0) AS data_length,
           NVL (a.data_precision, 0) AS data_precision,
           NVL (a.data_scale, 0) AS data_scale
    FROM   ALL_ARGUMENTS a, all_objects o
   WHERE   o.object_id =
              (SELECT   object_id
                 FROM   all_objects
                WHERE       UPPER (object_name) = UPPER ('resourcemanager_pkg')
                        AND object_type = 'PACKAGE'
                        AND owner = 'OFFICEDBA')
           AND UPPER (a.object_name) = UPPER ('p_search_roles')
           AND a.OBJECT_ID = o.OBJECT_ID
ORDER BY   a.position ASC

该查询返回特定过程的输入/输出参数。

Resourcemanager_pkg 是包名称，p_search_roles 是过程名称。 我们将此查询称为过程的每个数据库调用。

这个查询有什么问题吗？

Answer 1

删除 oracle 视图上对 UPPER() 的所有调用。它们已经是大写的了。我还将包名称查询移至“with 子句”，以便调用一次。

WITH PACKAGE AS
     (SELECT object_id, owner, object_name NAME
        FROM all_objects
       WHERE object_name = UPPER ('SOME_PACKAGE_NAME')
         AND object_type = 'PACKAGE'
         AND owner = 'SOME_SCHEMA_OWNER_NAME')
SELECT   a.argument_name, a.data_type, a.in_out,
         NVL (a.data_length, 0) AS data_length,
         NVL (a.data_precision, 0) AS data_precision,
         NVL (a.data_scale, 0) AS data_scale
    FROM ALL_ARGUMENTS a, PACKAGE
   WHERE a.package_name = PACKAGE.NAME AND a.owner = PACKAGE.owner
   --This is the 'procedure' name within the package.
   AND a.OBJECT_NAME = 'SOME_PROCEDURE_NAME'
ORDER BY a.POSITION ASC

Answer 2

您是否能够修改正在生成的查询？它似乎正在对 ALL_OBJECTS 表进行无关的连接。看来您的查询与此相同，

SELECT   a.argument_name,
           a.data_type,
           a.in_out,
           NVL (a.data_length, 0) AS data_length,
           NVL (a.data_precision, 0) AS data_precision,
           NVL (a.data_scale, 0) AS data_scale
    FROM   ALL_ARGUMENTS a,
           (SELECT   object_id
              FROM   all_objects
             WHERE       UPPER (object_name) = UPPER ('resourcemanager_pkg')
                     AND object_type = 'PACKAGE'
                     AND owner = 'OFFICEDBA') o
    WHERE  UPPER (a.object_name) = UPPER ('p_search_roles')
      AND  a.OBJECT_ID = o.OBJECT_ID
    ORDER  BY a.position ASC

我还希望使用 ALL_PROCEDURES 而不是 ALL_OBJECTS 来获取 OBJECT_ID 会更有效。

你收集过字典统计数据吗？针对数据字典视图的查询通常很难调整，因为您无法添加索引或其他结构来加快速度。但至少收集字典统计信息可以为优化器提供更好的信息，以便能够选择更好的计划。

最后，您是否可以将数据字典中的数据物化到定期刷新的物化视图中，以便您可以建立索引？这意味着结果不会立即反映程序定义的更改。另一方面，您通常不希望实时更改过程定义，并且您始终可以在进行架构更改后刷新物化视图。

Answer 3

您确实应该考虑使用 Oracle Enterprise Manager 来监视数据库。这是一个相当用户友好的网络应用程序，它将为您分析所有查询，并快速告诉您运行缓慢的原因。有关详细信息，请参阅 Oracle 网站。

我一开始没有看到查询有任何问题，但这实际上取决于您的表结构、索引以及在减速时遇到的其他负载、并发问题。

Answer 4

数据库性能问题通常很难通过查看查询本身来解决。

以下是诊断问题时需要遵循的一些简单步骤

• 解释计划，这会告诉你查询速度慢的地方
• 检查你的索引，你在 UPPER(object_name) 上有索引吗？
• 检查您的统计数据，它是最新的吗？

Answer 5

试试这个。我故意保留了 UPPER 调用，因为如前所述，这将导致索引不被使用。

SELECT   a.argument_name,
       a.data_type,
       a.in_out,
       NVL (a.data_length, 0) AS data_length,
       NVL (a.data_precision, 0) AS data_precision,
       NVL (a.data_scale, 0) AS data_scale
from all_arguments a
    inner join all_objects o ON o.object_id = a.object_id
where o.object_name = 'resourcemanager_pkg'
    and o.object_type='PACKAGE'
    AND O.OWNER = 'OFFICEDBA'
    AND A.OBJECT_NAME='p_search_roles'
ORDER BY   a.position ASC