I was reverse engineering a schema yesterday. The schema has 170+ tables, but the database itself has hundreds of users who access the data in the schema using private synonyms. There were over 170,000 synonyms.
This fact causes my system to hang at 100% CPU and eventually it runs out of memory. Turning off synonym generation solves this problem. I’m not sure what TDM is building internally for each synonym but it takes rather a lot of memory to do it. Maybe this is an area that could be looked it in case there is a problem?
My observations following are areas where I think the database access of the TDM RE could be improved :
- Use bind variables in a parameterised statement. I see lots of the following :
SELECT table_name, parent_table_column, return_type
WHERE owner = ‘NALD_OWNER’ AND parent_table_name = ‘NALD_MAN_REP_CODES’;
and this as well :
WHERE table_name = ‘NALD_SCHED_JOBS’ AND owner = ‘NALD_OWNER’
ORDER BY column_id;
hitting my database and effectively flushing my library cache. Surely the above statements - and potentially others - should be using binds, parsed once and executed as many times as required rather than a brand new statement each and every time?
- I think that the reverse engineering tool should be removed from TDM and supplied as a separate small application that does nothing more than reverse engineer the database and create a model that is saved away somewhere for TDM proper to load and manipulate as required. Generating the HTML/RTF report could also be extracted as appropriate.
This would reduce the size of TDM proper and make for faster loading. At present it takes about 10-15 seconds to load.
Just my thoughts. Thanks for listening.