Huge Memory Usage when using Data Compare

Toad Data Point & Toad Intelligence Central
1

Hello,

I am currently using Toad Data Point Pro v3.6.1.3294. When I attempt to do a data compare, two things are happening:

  1. The data compare does not correctly capture all key matches between the result sets. I even made sure the data types and contents were exact, but the left and right source both show the data as missing on the other side. I attached a screenshot (1 - Data Compare Key Mismatch.jpg).

  2. While the data compare is executing, the memory jumps up significantly. Most of the time, Toad throws out an OutOfMemory error. At that point, I need to restart Toad, open and run only the two queries I want to compare, and then it will work. At that point, the memory will jump from around 100MB or so to over 800MB. Even if I close the data compare tab, the memory stays that high. It is not until I close the query tabs that the memory drops back down. I have also attached some screenshots of this as well (screenshots 2a - 2i).

This issue has not happened in the past. I recently upgraded to this patch build (3294).

Thank you all for your help with this.

2

Hello Joe,

Sorry for the delay. When you get a chance could you please provide me with the Oracle versions that you are using as well as the DLL for the tables if possible.

Thanks.

3

Hello Joe,

I mistakenly forgot to ask about the number of rows that you are trying to compare. From the screenshots it looks like roughly 500k but I thought I would ask and make sure.

Thanks.

4

Hi Michael,

Thank you for your response.

The Oracle database version we are using is 11.2.0.3. I am using the Oracle Instant Client, version 11.2.0.1.0, which is used across the organization. The rows are indeed about 500,000 for each result set. I have compared more rows in the past with no issues.

If you meant DDL/DML for the results, I am unable to provide that information as the queries are based on confidential information from our company. My question is once the result set is returned, does Toad re-query the database to do the compare, or does it just take the in-memory results of the queries and compare those? Either way, querying the result sets themselves only takes a few MB of memory, but when running the compare, it jumps up rapidly to the 800 MB or so. Seems way too high to do a simple compare.

Thanks,

Joe

5

Hello Joe,

Thank you for the information it will help us try to replicate in a similar environment.

In regards to the DDL, I just wanted to get an idea of what data types were being compared. There could perhaps be a Oracle extended data type that is causing an issue.

Regarding the query, when I tested it the in-memory results were used and the query did not run again.

We will continue to look at this and I will post back when I have more information.

Thanks.

6

I did a compare on two tables that had 1 million rows. No issues.

I understand the data in the tables would not be something you can send us. But most people can send the table DDL and we can enter in our own dummy data. The number of columns and exact column types are very important in reproducing any issues. If you can send us the table DDL I can work on this some more

7

I guess I should have mentioned that there is no DDL as I am comparing results from queries that are run against several different tables. I can, however, give a list of data types being selected. I added them to the end of the post.

I think we are getting somewhere though. I loaded the data into two tables using CREATE TABLE … AS. When I right clicked one of the tables, selected Data Compare, and went through the wizard, the compare ran perfectly fine against the tables. However, if I do one of the following, it causes it to crash:

  • Right click a table, click Sent To, and then Diff Viewer as Data
  • Right clicked one result set, clicked Compare To, and picked the second result set
    So it appears that the Data Compare feature works, but the Data Diff Viewer does not.

Here are the data types for both tables:

CHAR(10)
CHAR(5)
DATE
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)

Thank you again for all your help.

  • Joe
8

This is very useful data. We have been looking into this under task TSY-1102. It appears tha OOM issue occur when using a right click from a grid when sending to compare. We have made some changes but they are rather invasive so I am not sure we will include in our upcoming TDP 3.7 release. Do you get OOM if you use data Compare wizard? From Tools | COmpare | Data Compare?

9

Hi Debbie,

Thank you for the follow-up. I do not get the same exception when using the wizard, so I can definitely use that as an easy workaround to the issue. I will consider your response an answer to my question.

Thank you again for all your help!