Poor performance when Searching/Comparing objects in PL/SQL Developer

[OraDotNetDev]

We have a requirement, as I'm sure many organisations do to carry out Code Search (Impact Analysis) across all schemas and Compare Objects (create Change Scripts.) Our DB is around 1.5 TB with ~250K objects ~9000 are stored code across 100 schemas. the numbers are reduced by a third* if we could exclude system objects.

The code search has always been painfully slow for us taking +/- 2 hours, whilst another well known tool only takes 10 minutes. This is simply unusable and is forcing us to start considering other more expensive tooling. The comparison performance is also a similar experience.

Working with our DBAs and tracing the searches carried out by PL/SQL Developer it appears (though I could be wrong) that there is no use of Binding Variables and every query is being hard parsed, fetched and processed client side rather being parameterised and driven by common cursors using effective join conditions which would let our very powerful Oracle Servers do the heavy lifting.

I've raised this as an issues with support directly before but was told that it wasn't a concern!

Is this not an issue for other users? Is there any chance of this being resolved this year? If not we will probably have to migrate to another tool which would be a pity.

similar thread from 2005
Another poll on this issue

There are some additional enhancements, I think might also help: Excluding objects without any schemas from pick lists. When comparing all objects, the ability to exclude system users, in the object comparison standard functionality such as select all, Select all up/down is broken.

 

[IvanZ]

Why would you even use Tools for search?
10 minutes is already very slow.

We use handwritten queries.

 

[Maxmix]

Yes, it's problem, even though our databases are not so big.

To exclude system (provided by Oracle) schemas, I created new Browser filter, and now have to select for Object criteria(each time) :
Browser filter:
Owner:

 

[OraDotNetDev]

Yes, it's problem, even though our databases are not so big.

To exclude system (provided by Oracle) schemas, I created new Browser filter, and now have to select for Object criteria(each time) :
Browser filter:
Owner:

Yeah I missed the exclude system objects that definitely helps . What testing shows is that excluding tables and views
from the search plus the above brings it down to 10 minutes, though obviously this is not a complete enough search in many cases.
However looking at the trace there are all sorts of objects being searched which do not appear to be required and many which could be
made optional - table/view/column comments etc

Why would you even use Tools for search?
10 minutes is already very slow.

We use handwritten queries.

If a query can be run to so this in a few minutes so should the tool? Additionally a GUI should be making this an easier process,
allowing customisation, providing a quick choice of objects/criteria without having to tweak a range of hand coded queries. Allowing the export
of results in a range of formats. Finally there is the additional complexity of querying views, and further querying views beyond 32k.
If our developers and analysts were all so comfortable running their own queries for such tasks perhaps we could just get them
to use SQL*Plus instead of a proper IDE

 

[Claus Pedersen]

I have earlier posted requests about the same subject: Request, enhancement of Find Database Objects and Request: include DBMS_Jobs in Find database objects, but no progress yet.

 

[OraDotNetDev]

I have earlier posted requests about the same subject: Request, enhancement of Find Database Objects and Request: include DBMS_Jobs in Find database objects, but no progress yet.

Hi Claus,

That all sound good to me :-D