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

我们有一个基于 .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 是过程名。 我们为过程的每个数据库调用调用此查询。

这个查询有什么问题吗？

请您参考如下方法:

删除 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