Why does schema Compare take longer in smaller schema

phyllis.helton_1150 · August 16, 2013, 7:27am

We have two schemas that we compare on a fairly regular basis between our test, staging and production environments. One has a fairly small number of objects the other a fairly large number of objects. However, we have found that when we run a schema compare, the smaller schema takes ages (i.e. minimum of 2 hours) to run while the schema with the huge number of objects goes fairly quickly. Is there something we can look at to get this to run more quickly?

The only major difference I can think of between the two schemas is that the one that takes so long does have many scheduled programs and scheduled jobs, however we are excluding these from the comparisons we are doing.

schema 1 count
schema 2 count
object type
89
3496
INDEX
1
7
PROCEDURE
1
0
SCHEDULE
73
853
TABLE
121
36
VIEW
26
0
TRIGGER
2
0
DATABASE LINK
0
5
FUNCTION
0
27
MATERIALIZED VIEW
13
5
PACKAGE BODY
1
0
SYNONYM
31
3
SEQUENCE
2
16
LOB
13
5
PACKAGE

NormTeamT · August 16, 2013, 7:47am

Hi Phyllis,
On 16/08/13 15:27, phyllis.helton_1150 wrote:

We have two schemas that we compare on a fairly regular basis between
our test, staging and production environments. One has a fairly small
number of objects the other a fairly large number of objects. However,
we have found that when we run a schema compare, the smaller schema
takes ages (i.e. minimum of 2 hours) to run while the schema with the
huge number of objects goes fairly quickly. Is there something we can
look at to get this to run more quickly?
I wonder if I'll get a reply in before Bert does!
Bert has written about the possible reasons for this. Your DBA (or you, from a DBA enabled account) needs to gather dictionary stats and gather fixed object stats so that queries against the data dictionary can be hugely improved.
Bert's blog posts on the matter are:
http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/06/20/speed-up-toad-for-oracle-s-schema-browser.aspx
http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2007/10/02/toad-sometimes-slow-on-oracle-10g.aspx
http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2010/11/23/go-toad-go.aspx
HTH
-- Cheers,
Norm. [TeamT]

JohnDorlon · August 16, 2013, 7:52am

My guess is that for some Oracleish reason, the queries are taking longer on the small schema. (Stats out of date?)

The schema compare code should be able to process this amount of data, once fetched from the database, in no time.

From: phyllis.helton_1150 [mailto:bounce-phyllishelton_1150@toadworld.com]

Sent: Friday, August 16, 2013 9:28 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Why does schema Compare take longer in smaller schema

Why does schema Compare take
longer in smaller schema

Thread created by phyllis.helton_1150

We have two schemas that we compare on a fairly regular basis between our test, staging and production environments. One has a fairly small number of objects
the other a fairly large number of objects. However, we have found that when we run a schema compare, the smaller schema takes ages (i.e. minimum of 2 hours) to run while the schema with the huge number of objects goes fairly quickly. Is there something
we can look at to get this to run more quickly?

The only major difference I can think of between the two schemas is that the one that takes so long does have many scheduled programs
and scheduled jobs, however we are excluding these from the comparisons we are doing.

schema 1 count

schema 2 count

object type

89

3496

INDEX

1

7

PROCEDURE

1

0

SCHEDULE

73

853

TABLE

121

36

VIEW

26

0

TRIGGER

2

0

DATABASE LINK

0

5

FUNCTION

0

27

MATERIALIZED VIEW

13

5

PACKAGE BODY

1

0

SYNONYM

31

3

SEQUENCE

2

16

LOB

13

5

PACKAGE

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Bert_Scalzo · August 16, 2013, 8:17am

You beat me to the punch – thanks
J

From: Norm [TeamT] [mailto:bounce-NormTeamT@toadworld.com]

Sent: Friday, August 16, 2013 9:48 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Why does schema Compare take longer in smaller schema

Re: Why does schema
Compare take longer in smaller schema

Reply by Norm [TeamT]

Hi Phyllis,

On 16/08/13 15:27, phyllis.helton_1150 wrote:

We have two schemas that we compare on a fairly regular basis between

our test, staging and production environments. One has a fairly small

number of objects the other a fairly large number of objects. However,

we have found that when we run a schema compare, the smaller schema

takes ages (i.e. minimum of 2 hours) to run while the schema with the

huge number of objects goes fairly quickly. Is there something we can

look at to get this to run more quickly?

I wonder if I'll get a reply in before Bert does!

Bert has written about the possible reasons for this. Your DBA (or you,

from a DBA enabled account) needs to gather dictionary stats and gather

fixed object stats so that queries against the data dictionary can be

hugely improved.

Bert's blog posts on the matter are:

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/06/20/speed-up-toad-for-oracle-s-schema-browser.aspx

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2007/10/02/toad-sometimes-slow-on-oracle-10g.aspx

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2010/11/23/go-toad-go.aspx

HTH

--

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

phyllis.helton_1150 · August 16, 2013, 12:17pm

Thanks guys. I'll check on the stats, though I had thought we were up to date on those. Sorry it didn't occur to me that this could be the same issue as for the schema browser. . .

On Fri, Aug 16, 2013 at 11:17 AM, Bert Scalzo bounce-Bert_Scalzo@toadworld.com wrote:

RE: Why does schema Compare take longer in smaller schema

Reply by Bert Scalzo
You beat me to the punch – thanks
J

From: Norm [TeamT] [mailto:bounce-NormTeamT@toadworld.com]

Sent: Friday, August 16, 2013 9:48 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Why does schema Compare take longer in smaller schema

Re: Why does schema
Compare take longer in smaller schema

Reply by Norm [TeamT]

Hi Phyllis,

On 16/08/13 15:27, phyllis.helton_1150 wrote:

We have two schemas that we compare on a fairly regular basis between

our test, staging and production environments. One has a fairly small

number of objects the other a fairly large number of objects. However,

we have found that when we run a schema compare, the smaller schema

takes ages (i.e. minimum of 2 hours) to run while the schema with the

huge number of objects goes fairly quickly. Is there something we can

look at to get this to run more quickly?

I wonder if I'll get a reply in before Bert does!

Bert has written about the possible reasons for this. Your DBA (or you,

from a DBA enabled account) needs to gather dictionary stats and gather

fixed object stats so that queries against the data dictionary can be

hugely improved.

Bert's blog posts on the matter are:

http://gyk.r.mailjet.com/redirect/rh8ghzhns4rwj8llrfzqqr/www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/06/20/speed-up-toad-for-oracle-s-schema-browser.aspx

http://gyk.r.mailjet.com/redirect/s043t4455r622tv1z7j1pr/www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2007/10/02/toad-sometimes-slow-on-oracle-10g.aspx

http://gyk.r.mailjet.com/redirect/3rrrhr4wxy1zsmqypt5f1m/www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2010/11/23/go-toad-go.aspx

HTH

--

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

BI MIddleware Engineer

Cru | Technology
Office 407-515-4452

phyllis.helton@cru.org

damir.vadas_250 · September 19, 2013, 11:35pm

Do you compare table rows or just tables as objects? If you compare table rows, then your “small” schema might have more rows in total then big one?

Second, in small schema you have db_link. If your objects depend on data from that db_link, then you might have problems with network (accessing data through that db_link).

Just mine 2c thoughts…

Damir