.net - Oracle 选择查询花费的时间太长

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 View 上对 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