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

We have an internal application based on .Net which calls certain procedures in Oracle (10g). One of these queries is run to get in/out parameters of these procedures. It's a pretty simple select query. But even under best of circumstances it is taking 3 seconds. At lease few times a day it starts taking more than 40 seconds and causes our .Net application to time out. Select query is:

我们有一个基于。net的内部应用程序，它调用Oracle中的某些过程(10g)。其中一个查询是运行以获取这些过程的参数。这是一个非常简单的select查询。但即使在最好的情况下，也需要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

This query returns the in/out parameters of particular procedure.

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

resourcemanager_pkg is package name, p_search_roles is procedure name. We call this query for every database call for procedures.

resourcemanager_pkg是包名，p_search_roles是过程名。我们对每个调用过程的数据库调用都调用这个查询。

Is there anything which is wrong with this query ? Any help will be greatly appreciated. Please let me know if any additional information is required.

这个查询有什么问题吗?如有任何帮助，我们将不胜感激。如果需要更多的信息，请告诉我。

Thanks, Vikalp Jain

谢谢,Vikalp耆那教徒的

5 个解决方案

#1

Do you have the ability to modify the query that is being generated? It appears that it is doing an extraneous join to the ALL_OBJECTS table. It appears that your query is equivalent to this

您是否有能力修改正在生成的查询?它似乎正在对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

I would also expect that using ALL_PROCEDURES rather than ALL_OBJECTS to get the OBJECT_ID would be more efficient.

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

Have you gathered dictionary statistics? Queries against the data dictionary views are generally rather hard to tune since you can't add indexes or other structures to speed things up. But at least gathering dictionary statistics may give the optimizer better information to be able to pick a better plan.

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

Finally, is it possible that you could materialize the data from the data dictionary in a materialized view that refreshes periodically that you could index? That would mean that the results wouldn't immediately reflect changes to the definition of procedures. On the other hand, you don't generally want to be making changes to procedure definitions live and you can always refresh the materialized views after making schema changes.

最后，是否有可能将数据字典中的数据以物化视图的形式具体化，并定期刷新以便进行索引?这意味着结果不会立即反映对程序定义的改变。另一方面，您通常不希望对过程定义进行实时更改，您总是可以在进行模式更改后刷新物化视图。

#2

Remove all the calls to UPPER() on the oracle views. They are in uppercase already. I've also moved the package name query to a 'with clause' so it is called once.

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

#3

You should really consider monitoring your database with Oracle Enterprise Manager. It's a reasonably user-friendly web app that will profile all your queries for you and tell you quickly why your running slowly. See Oracle's web site for details.

您应该考虑使用Oracle Enterprise Manager监控数据库。这是一个用户友好的web应用程序，它将为您分析所有查询，并快速告诉您为什么您的运行速度很慢。详见Oracle网站。

I don't see anything at the start that's wrong with the query but it really depends on your table structure, indexes and what other loads, concurrency issues you've got going at the time of slowdown.

我一开始没有看到查询有什么问题，但它确实取决于表结构、索引和其他负载，以及在放缓时遇到的并发问题。

#4

A database performance problem is generally hard to solve by looking at the query itself.

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

Here are some simple steps that you need to follow to diagnose the problem

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

• explain plan, this will tell you where your query is slow
• explain plan，这将告诉您查询的速度
• Check your indexes, have you got an index on UPPER(object_name)?
• 检查索引，上面有索引吗(object_name)?
• Check your statistics, is it current?
• 检查你的统计数据，它是当前的吗?

#5

Try this out. I've left the UPPER calls out on purpose because as mentioned earlier, that will cause the Indexes not to be used.

试试这个。我故意省略了上面的调用，因为如前所述，这会导致索引不被使用。

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