Registering database every time , taking long time

[ginjupalli.pavan]

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.

[Michael Christofides]

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

[ginjupalli.pavan]

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

[Michael Christofides]

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.

[ginjupalli.pavan]

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.

[Michael Christofides]

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.

[ginjupalli.pavan]

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 ?

[Michael Christofides]

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

[ursusmaj]

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

[Michael Christofides]

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

[ursusmaj]

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

[Michael Christofides]

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/174014-oracle-tools/suggestions/3724558

Please do add your votes/comments to that request if that would be helpful.

[ursusmaj]

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

[Michael Christofides]

Thank you Cliff, much appreciated.

[ginjupalli.pavan]