v10 vs v9: Performance problems with "Compare User Objects" and "Export User Objects"

[Bernhard S]

I have just measured how much worse perfomance is in v10 vs v9 using "Compare User Objects".
In 9.0.6.1665 it took 08 minutes 52 seconds
In 10.0.1.1694 it took 22 minutes 35 seconds
=> more than 2.5 times as long !!

That makes this feature quite unusable in v10 for me.

In both cases I compared the same two schemas. Each contains 724 objects, among those 265 indexes, 126 tables, 109 views, 9 packages.

Please get this performance problem fixed soon.

 

[Marco Kalter]

We're looking in to this.

 

[Bernhard S]

It's even worse with "Export User Objects" of the same schema.
In 9.0.6.1665 it took 7 minutes
In 10.0.1.1694 it took 29 minutes
=> more than 4 times as long !!

 

[Jeff81]

It is also slower in version 10 when trying to select a schema. I think it is waiting to get the list of objects back. Version 9 is much faster at getting the list back.

 

[Bernhard S]

Could you give any indication when this problem will be fixed? Currently it forces - probably not only - me, to use PL/SQL Developer v9 for using those features just to have an acceptable performance.

 

[Marco Kalter]

We should have a fix for 10.0.2. I will send you a pre-release for verification when it is available.

 

[Bernhard S]

So is this fix included now in 10.0.2? Is this what is described in the enhancement list as "Performance optimization fetching table DDL"?

 

[Marco Kalter]

Yes, this did indeed cause some performance degradation in several functions.

 

[Bernhard S]

I've measured again just the "Compare User Objects" so far.
Performance has improved compared to v10.0.1, but it's still about 30%..60% worse than it is with v9.0.6.
With v10.0.2 I also run into the problem mentioned in Compare User Object new behaviour in v10. If I compare identical schemas where only the schemaname is different, PSD wants to drop the triggers on the target site. However when I click on the "Show differences" icon, it shows the table generation scripts identical without any trigger at all on either site!

 

[AdamG]

Looks more like problem described in PL/SQL Developer 10 - "Compare User Objects..." - triggers. Because in my case there are triggers in destination schema which are not in source, but I don't want to loose them...
And here is the case when two compared triggers are different only by schema owner (regardless that you are connected to both schemas as schema owner)

 

[Bernhard S]

Oh, ok. Thank you for clarifying the trigger issue.

 

[Bernhard S]

I've measured again performance for "Compare User Objects".
Again I compared the same source and target schemas.

In 9.0.6.1665 it took this time 08 minutes 36 seconds
In 10.0.3.1701 it took 17 minutes 58 seconds
=> more than 2 times as long !!
So compared to 10.0.2.1697 performance has worsened a lot again and is now almost as bad again as with 10.0.1.1694. Currently the only option is using 9.0.6.1665 again to get acceptable performance.

 

[Bernhard S]

Looks like nothing has been fixed about this issue in the latest version 10.0.4.1708.
Compared to 9.0.6.1665 my "Compare User Objects" still takes more than 2 times as long!!
Any idea when this performance issue will be fixed?

 

[Marco Kalter]

It should be the same now. Do you still have both versions available? If so, can you run the same compare in both versions with the DebugSQL parameter and send me the debug.txt files?

 

[Worker]

For what it's worth, I haven't been able to upgrade to v10 yet, but I just tried the 30-day trial (10.0.4.1708) and:

* "Compile Invalid Objects" seems just as fast as in v9. (I posted about slowdown in the beta forum and I assume it's related.)
* This slowdown problem with "Find DB Objects" appears to be gone.

I don't know if the change that was made in 10.0.4 fixed these things, but if it did, thank you!

 

[Bernhard S]

As you suggested I've done the comparison with DebugSQL parameter and sent you the resulting Debug.txt files.
With DebugSQL parameter the slow-down-factor of runtime in v10 vs in v9 got even worse and is almost 3 now!

 

[Maxmix]

If it's required to set up db links between databases in order to have 10 times faster "Compare objects" operation, running on the server side, then I vote for it.

Also, if it's required to set up some temporary tables in the database in order to have Search running on the server side, then I vote for it.

We have rather high-latency connections between databases and "Compare User Objects" (main task, even more frequently used than editing PL/SQL units ;-) ) is really slow.

P.S. How Toad could be so fast in searches and comparisons?

 

[Dezsoe Kados]

This is also an issue for me. Especially comparing tables seems to be very, very slow. (version 10.0.4.1709)

 

[Andrew Lacwik]

Marco,

Are you going to improve "Compare User Objects..." in PL/SQL Developer 10? So far you ask to send you debugging info from "Compare User Objects..." in version 9 and 10 (I sent it to you long time ago).
There is no constructive response to this issue so far.
Pleas do something with it. This is really annoying....

Andrew

 

[Bernhard S]

Hi Marco,
what is the status here?
Is it meanwhile clear why the performance is so bad here?
When will this really bad performance problem be finally fixed?

 

[Bernhard S]

Hi Marco,
would you please answer my questions above?
This performance problem is really a huge annoyance and has been going on for so long now already. It needs to be fixed!

 

[Marco Kalter]

We are still looking into this issue, but do not yet have a solution.

 

[Bernhard S]

Please join the Poll about fix of this Performance Problem! Thank you.