Registering database every time , taking long time

Compares and synchronizes Oracle data

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

Registering database every time , taking long time

Postby ginjupalli.pavan » Wed Jan 09, 2013 5:52 pm

We have recently brought Oracle deployment suite from Redgate.
I am trying to save connection definitions in a new project and trying to open the saved project various times to compare different tables. But every time its trying to register both the databases and its taking longer than 30 mins.Then i am unchecking unnecessary tables for comparision.

Is there a better way to point to two similar tables between source and target directly.
ginjupalli.pavan
 
Posts: 7
Joined: Wed Jan 09, 2013 5:45 pm

Postby Michael Christofides » Fri Jan 11, 2013 11:23 am

Hi,

Thanks for your message. Currently projects make it easy to save specific objects to compare, but we are aware it takes roughly the same time again if you wish to change those objects.

Could we ask roughly how many tables you are comparing here?

We have some ideas on how to improve this in a future version, but hope to get more information before making a decision.

Please do email us on Oracle at red-gate dot com if you prefer.

Michael Christofides
Product Manager - Oracle tools
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby ginjupalli.pavan » Fri Jan 11, 2013 5:03 pm

One of the reasons we have purchased red gate is to see if it can help to deploy data changes between two different tables in different environments quickly.
It works great in identifying the changes but taking enormous amount of time when registering databases and creating deploy scripts.For example on a given day if we have to bring down data from a huge table , why does it need to register entire database ,currently it was taking around 30 mins to register source and target databases.
We frequently bring data from production databases to staging and development environments, it involves around 120 tables , but to start with we want to pick 3 or 4 huge tables(rows ~ 3 million) and see how much time it will take to do data comparison and deploy differences, instead of copying down entire tables.Also please see if you can develop the functionality of selecting a list of tables for comparison before registering entire databases.

Thanks
ginjupalli.pavan
 
Posts: 7
Joined: Wed Jan 09, 2013 5:45 pm

Postby Michael Christofides » Fri Jan 11, 2013 5:43 pm

Thank you for the extra detail.

Selecting/filtering a list of tables before registering is certainly one way we could do this.

The internal feature request ID ODC-250, I'll update this thread if we have any updates or require further information.
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby ginjupalli.pavan » Fri Jan 11, 2013 10:59 pm

Thanks Michael for such prompt response.
Few inputs i can add to the behavior of the internal feature request ID ODC-250,which can come great help
>> Selection of tables that are filtered to be saved, so that if there are 20/100 tables that need to be selected every time, user can load the saved template and make edits to the list and save them.
>> Or if above is not possible ,provide a place to dump a list of table names saved on users machine.For example: If i maintain a excel spreadsheet with list of 100 tables, i should be able to input that list at once instead of manually selecting tables.
ginjupalli.pavan
 
Posts: 7
Joined: Wed Jan 09, 2013 5:45 pm

Postby Michael Christofides » Mon Jan 14, 2013 11:52 am

OK great, I've added that to the request. I think the first option is a likelier implementation if or when we do this, but we'll be in touch at the time.

Thanks again.
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Michael

Postby ginjupalli.pavan » Wed Mar 13, 2013 6:55 pm

I just visited another tool and downloaded the trial version "DB Forge for Oracle", it has what something i am looking for, I am not aware of this when purchasing Redgate,the initial db registration was very quick and data comparison is so flexible and quick and even the deploy scripts are very straight forward and broken down into multiple parts which make database not to hang for a large deploy script.

Is there something like that red gate is planning to implement ?
ginjupalli.pavan
 
Posts: 7
Joined: Wed Jan 09, 2013 5:45 pm

Postby Michael Christofides » Mon Mar 18, 2013 11:43 am

Hi again,
Regarding he initial db registration, we are planning to release a version of Data Compare soon that should speed this up in some cases, but please do let us know specifics if you've hit an issue.
Regarding the database hanging for a large deploy script, has this happened to you? If so please do provide us details and explain what you believe to be the cause, or how breaking the deployment scripts down could help.
Best regards,
Michael
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby ursusmaj » Wed Apr 10, 2013 10:01 am

I am also seeing very slow database registration when comparing large schemas (e.g. PeopleSoft).

The application memory usage in particular is the main issue I see - it grows to 3.8 Gb on a 4 Gb server and then causes swapping which makes the performance spectacularly bad. This is on the 64-bit version of Data Compare on 2008 R2.

I plan to add additional memory since the actual required memory looks to be around 5-6 Gb in my specific case.

Given that in most cases (I suspect), people are looking to compare a sub-set of the schema, I think a mechanism to speed the initial registration up is essential.

As an aside, I have used previous versions of Data Compare on other sites and did not see this behaviour previously - granted it was never "quick" for large schemas but it does seem to have got noticeably worse in the newer versions.
Regards,
Cliff
ursusmaj
 
Posts: 4
Joined: Wed Apr 10, 2013 9:49 am
Location: United Kingdom

Postby Michael Christofides » Wed Apr 10, 2013 11:03 am

Hi Cliff,

Thanks for getting in touch. The latest release 2.1.0.325, which is available via help->check for updates, has an option called "Check tables for data". This should now be unchecked by default, which should increase the speed of registering large schemas.

Do you have this version? If so could you look to see if this option is unchecked?

We added the functionality not too long ago in order to help filter out empty tables of which several users had lots. We have now added it as an option for those users, but hopefully this takes the performance back to the level experienced previously for you.

Hope that helps, let us know how you get on.

Michael
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby ursusmaj » Wed Apr 10, 2013 12:16 pm

Thanks for the quick response. I do have version 2.1.0.325 and the option "Check tables for data" is unchecked in my project.

Some further info:

Registering database one goes to 5% immediately and then quite quickly to 30%, 10 minutes to go to 50%, another 10 to hit 70%. It sits there for more than 30 minutes on 70%. When it eventually completes, the same is repeated on the second database, although timings are much worse as the system is swapping heavily by this time. In fact the swapping occurs quite quickly after 30% on the first database.

In terms of tables in the schema there are approximately 66,300 in the schema(s).
Regards,
Cliff
ursusmaj
 
Posts: 4
Joined: Wed Apr 10, 2013 9:49 am
Location: United Kingdom

Postby Michael Christofides » Mon Apr 15, 2013 11:29 am

Thank you Cliff, that's frustrating.

We've looked into this and the SQL we're using at those stages is the same in previous versions, did you say this had been noticeably faster on the same schema(s) before?

You mentioned 66,300 in the schema(s), are you comparing multiple schemas at once here? If so, please do try them one at a time, but I guess you may mean in each of the source and target schemas.

Of the 66,300 or so tables, how many are you wishing to compare immediately? We've been considering a feature to allow up front filtering of tables, described here:
http://redgate.uservoice.com/forums/174 ... ns/3724558

Please do add your votes/comments to that request if that would be helpful.
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby ursusmaj » Mon Apr 15, 2013 12:15 pm

To be clearer, my reference to "before" was on another site, so the number of tables involved will have been different i.e. probably under 60,000 but not much below that level. Also, the hardware and OS were likely much different too.

The 66,300 tables is the number in each schema (i.e. both source and target DB's have that number of tables). This is a PeopleSoft Financials installation, so it is all in the one schema.

In reality, as I suspect is often the case with ERP installs, the number of tables I want to compare is a small subset of the total number. Perhaps 1% or even less.

The idea of up front filtering would work well for me in theory, but is depends very much on the flexibility of the actual implementation. I will provide my comments against the suggestion.
Regards,
Cliff
ursusmaj
 
Posts: 4
Joined: Wed Apr 10, 2013 9:49 am
Location: United Kingdom

Postby Michael Christofides » Mon Apr 15, 2013 12:18 pm

Thank you Cliff, much appreciated.
Michael Christofides
 
Posts: 95
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Re:

Postby ginjupalli.pavan » Mon Apr 22, 2013 7:36 pm

Michael Christofides wrote:Hi again,
Regarding he initial db registration, we are planning to release a version of Data Compare soon that should speed this up in some cases, but please do let us know specifics if you've hit an issue.
Regarding the database hanging for a large deploy script, has this happened to you? If so please do provide us details and explain what you believe to be the cause, or how breaking the deployment scripts down could help.
Best regards,
Michael


I cannot find any option in this forum for me to attach the screenshot of the error but here is the text of error
X Creating Deployment Script
"Exception has been thrown by the target of the invocation"
ginjupalli.pavan
 
Posts: 7
Joined: Wed Jan 09, 2013 5:45 pm

Next

Return to Data Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests