# Why does schema Compare take longer in smaller schema

**URL:** <https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323>\
**Category:** Toad for Oracle - Beta\
**Created:** [August 16, 2013, 7:27am UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323 "2013-08-16T07:27:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![phyllis.helton\_1150](https://avatars.discourse-cdn.com/v4/letter/p/ac8455/32.png) [@phyllis.helton\_1150](https://forums.toadworld.com/u/phyllis.helton_1150)\
**Post date:** [August 16, 2013, 7:27am UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/1 "2013-08-16T07:27:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [August 16, 2013, 7:47am UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/2 "2013-08-16T07:47:20Z")

</div>

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/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/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](http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2010/11/23/go-toad-go.aspx)  
> HTH  
> -- Cheers,  
> Norm. [TeamT]

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [August 16, 2013, 7:52am UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/3 "2013-08-16T07:52:21Z")

</div>

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](mailto:bounce-phyllishelton_1150@toadworld.com)]

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

**To:** [toadoraclebeta@toadworld.com](mailto: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](http://gyk.r.mailjet.com/redirect/b01qtjbhkn1yb8zj9nxgxd/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx)**

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](http://gyk.r.mailjet.com/redirect/nlzbtno5d1jxj1mtfpfo0t/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx?subscribe=false) on this subject.

Or [  
Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/nq98tj628g6olotfcjlpct/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether.

Toad for Oracle - Beta Discussion Forum

[Flag](http://gyk.r.mailjet.com/redirect/9mhhhw383gr92748r9gl34/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx?AbuseContentId=dbff14f0-98af-4668-9949-618da9f62685&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true)  
this post as spam/abuse.

![](http://gyk.r.mailjet.com/ccd06d5e/4tIDCA.gif)

---

<div class="post-metadata">

**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\
**Post date:** [August 16, 2013, 8:17am UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/4 "2013-08-16T08:17:21Z")

</div>

You beat me to the punch – thanks  
J

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

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

**To:** [toadoraclebeta@toadworld.com](mailto: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](http://gyk.r.mailjet.com/redirect/qgkzh8jyl6dwykns3753xw/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40717.aspx#40717)**

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/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/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](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](http://gyk.r.mailjet.com/redirect/p4l3tb5440wmp3b2l7rcgv/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx?subscribe=false) on this subject.

Or [  
Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/wk7ttt608hlvb2v1wswx1p/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether.

Toad for Oracle - Beta Discussion Forum

[Flag](http://gyk.r.mailjet.com/redirect/wmmhh63tjp8zw9hmhfrb8b/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40717.aspx?AbuseContentId=4a5f0616-0cec-44d9-89e4-763e17457546&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true)  
this post as spam/abuse.

![](https://global.discourse-cdn.com/quest/original/3X/5/0/50b30fd5f87c85fe5cba2635cb83316ca71250d7.gif)

---

<div class="post-metadata">

**Author:** ![phyllis.helton\_1150](https://avatars.discourse-cdn.com/v4/letter/p/ac8455/32.png) [@phyllis.helton\_1150](https://forums.toadworld.com/u/phyllis.helton_1150)\
**Post date:** [August 16, 2013, 12:17pm UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/5 "2013-08-16T12:17:22Z")

</div>

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](mailto:bounce-Bert_Scalzo@toadworld.com) wrote:

> **[RE: Why does schema Compare take longer in smaller schema](http://gyk.r.mailjet.com/redirect/x2fcty76l1b1nn614z0loj/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40719.aspx#40719)**

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

> 

> **From:** Norm [TeamT] [[mailto:bounce-NormTeamT@toadworld.com](mailto:bounce-NormTeamT@toadworld.com)]
> 
> **Sent:** Friday, August 16, 2013 9:48 AM
> 
> **To:** [toadoraclebeta@toadworld.com](mailto: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](http://gyk.r.mailjet.com/redirect/8lkzhod183fttp312jl9c9/gyk.r.mailjet.com/redirect/qgkzh8jyl6dwykns3753xw/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40717.aspx#40717)**

> 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/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/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](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](http://gyk.r.mailjet.com/redirect/n29zhfx8o12f6v7qppq2t6/gyk.r.mailjet.com/redirect/p4l3tb5440wmp3b2l7rcgv/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx?subscribe=false) on this subject.
> 
> Or [  
> Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/stj4t4l7zhdwczw6dg3sd4/gyk.r.mailjet.com/redirect/wk7ttt608hlvb2v1wswx1p/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether.
> 
> Toad for Oracle - Beta Discussion Forum

> [Flag](http://gyk.r.mailjet.com/redirect/rcrft5k4d99hyt4mmcdjj8/gyk.r.mailjet.com/redirect/wmmhh63tjp8zw9hmhfrb8b/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40717.aspx?AbuseContentId=4a5f0616-0cec-44d9-89e4-763e17457546&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true)  
> this post as spam/abuse.

> 

> To reply, please reply-all to this email.
> 
> [Stop receiving emails](http://gyk.r.mailjet.com/redirect/7t6lhhhsh86f6yhbq1t4mb/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20132.aspx?subscribe=false) on this subject.
> 
> Or [Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/l4prhvm4bqwomr96npo55w/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether.
> 
> Toad for Oracle - Beta Discussion Forum

> [Flag](http://gyk.r.mailjet.com/redirect/qk36h96wxpsgofrv1lyj78/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20132/40719.aspx?AbuseContentId=457d9333-4250-4be6-9c8c-d5f7df529919&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse.

> 

--  
**Phyllis Helton**

BI MIddleware Engineer

Cru | Technology  
Office ☎ 407-515-4452

[phyllis.helton@cru.org](mailto:phyllis.helton@cru.org)  
 ![](https://global.discourse-cdn.com/quest/original/3X/9/4/94bf0c4f5e9dec7932932b0cb0b94169a403b1fd.png)

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [September 19, 2013, 11:35pm UTC](https://forums.toadworld.com/t/why-does-schema-compare-take-longer-in-smaller-schema/26323/6 "2013-09-19T23:35:20Z")

</div>

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
