Schema compare slow

Toad for Oracle & DevOps Toolkit
1

Hi,

I am comparing two schemas. Tthey have about 850 tables each. The comparison takes about 45 minutes to run. Is that normal? Can something be done to improve performance?

If I compare similar schemas on SQL Server using Toad for SQL Server, it takes about 30 seconds.

Thanks

2

It does seem slow if that’s all that you are comparing. Most of the time in comparison is spent executing queries. Are there other object types in the schema? If so, are you comparing them too? If you are not interested in the other object types, uncheck them in the schema compare options.

3

The statusbar at the bottom should tell you what Toad is doing as the comparison progresses. Does anything in particular stand out as taking a long time?

4

Hi John,

Most of the time, is spent looking for check constraints.

I this test my schemas are identical and have the following objects:

75 tables

1 procedure

18 triggers

151 indexes

467 constraints

18 sequences

The query on tables or columns take only one of two seconds. The query on constraints take about 2 minutes.

The Oracle database is running Oracle RAC 12C on two Linux virtuals.

Thanks

François Guertin** | **** Technical
Support Administrator, R&D**

Your Supply Chain Matters.™ - TECSYS

Tel.: (514) 866-5800 ext.4259 | Fax:
(514) 866-3878

francois.guertin@tecsys.com | www.tecsys.com

De : John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Envoyé : 10 avril 2015 15:06

À : toadoracle@toadworld.com

Objet : RE: [Toad for Oracle - Discussion Forum] Schema compare slow

RE: Schema compare slow

Reply by John Dorlon

The statusbar at the bottom should tell you what Toad is doing as the comparison progresses. Does anything in particular stand out as taking a long time?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Oh, check constraints. There could be a lot of queries for check constraints. There are various ways to create a check constraint and they all end up being slightly different in the data dictionary, so I have to run some queries to distinguish between them all. Will you turn on spool SQL (Main Menu: Database -> Spool SQL -> Spool to Screen) then run the comparison, then send me the spool sql output, so I can be sure that the problem is what I think it is? You can send it to john.dorlon@quest.com. Thanks.