Oracle select query taking too long

We have an internal application based on .Net which calls certain procedures in Oracle (10g). One 开发者_如何学Pythonof these queries is run to get in/out parameters of these procedures. It's a pretty simple select query. But even under the 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:

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.

Is there anything which is wrong with this query?

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.

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

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

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.

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.

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.

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.

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
• Check your indexes, have you got an index on UPPER(object_name)?
• Check your statistics, is it current?

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