Compile invalid objects in PL/SQL developer is very slow with 10g database

[impulse]

I upgraded an oracle 8i database to 10g. "Compile invalid objects" is very very slow comparing to when it was an 8i database. How can I solve this problem?

 

[Marco Kalter]

Maybe there is a problem with the performance of the dictionary views? The Compile Invalid Objects tool needs to determine the dependencies of all invalid objects, so if the performance of sys.all_dependencies is poor, the performance of this tool will be poor as well.

 

[impulse]

Can you write me the exact query which you use in PL/SQL Developer to select de invalid objects?

 

[orca777]

Hi,

have you increased shared_pool_size, 10g needs a lot more of it then 8i.

i am not sure (Rule based optimizer is disabled in 10g) but sometimes it help to give a rule hint for the dictionary query.

you could also generate a trace of the recompiling session, and to see where the elapsed time is going, best is to acrivate trace with extended trace 10046 event; with this oracle can report waiting events. (tkprof option waits=yes)

if query is slow you could try to conatct metalink and post your performance issue.

Regards

Carl

Carl

 

[Marco Kalter]

Is the initial query slow (displaying the invalid objects), or is the recompilation process slow?

 

[DomGo]

We're dealing exactly with the same problem. The shared_pool_size was already significantly raised by our administrator.
The first (initial) query needs up to 60 seconds to show any invalid object.

Is there a solution to this problem in the meantime?

Regards,
Dominik

 

[Marco Kalter]

Invalid objects are determined by executing the following query:

select object_name, owner, status
from sys.all_objects
where object_type = :object_type
order by object_name

Perhaps you can try this query?

 

[DomGo]

I am not quite sure why this query uses a filter for object_type as we want to query invalid objects. In fact this query takes much longer (10 seconds) compared to

select object_name, owner, object_type
from sys.all_objects
WHERE status 'VALID'
order by object_name
(0,5 seconds).

But nevertheless your query is much faster than the dialog "compile invalid objects". Using your query for each possible object_type would explain the long running quest for invalid objects, but would make no sense as there is one query to retrieve all candidates.

 

[Marco Kalter]

We can indeed optimize this, though it should really not be necessary. It's easy enough though.

Let me know if you need a pre-release.