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.

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).

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/174 ... ns/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.

Michael Christofides

Thank you Cliff, much appreciated.

ginjupalli.pavan

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"

Michael Christofides

ginjupalli.pavan wrote:

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"

Please do feel free to email the screen shot in to oracle@red-gate.com, and send the error report in to if there was one.

Best regards,
Michael

Michael Christofides

Thank you for sending in the screenshot and error report Pavan.

For anybody else that comes across a similar error in future, this one turned out to be an "ORA-01555: snapshot too old".

ursusmaj

The approach we have taken to dealing with this performance issue has been to create a specific user and role in Oracle ("the data compare user") with access to only a sub-set of the tables within the schema we are trying to compare.

The somewhat simplistic approach we have taken is to use a stored procedure to only grant access to non-empty tables to the data compare user. This reduces the number of tables visible to the DCO user to around 3500 which has a dramatic effect on the performance for us:

1. The project stored on disk is some 20 Mb rather than 700 Mb+.
2. The time taken to open/modify the project is reduced from an hour to a couple of minutes.
3. The memory usage of the DCO application is reduced significantly.

Obviously, there are some issues with the approach we have taken:

(a) You need to choose the list of tables to which access is granted AND ensure the same list is granted in each database since the same set of tables may not be empty in each one.
(b) The list of accessible tables needs to be updated as changes occur.

However, compared to the alternative performance issue these are minor from my perspective.

I hope this helps others experiencing this issue.

Michael Christofides

Cliff, thank you for sharing this, and nice idea.

I'll make sure this gets to the right people for next time we're planning Data Compare work.

Best regards,
Michael