Some thoughts on Quest’s Toad for Oracle

Last weeks I have been discussed with several guys from Quest, on subject "Toad for Oracle". Initially I have started to chat with Steve Hilker, product management director for Oracle solutions at Quest Software, about some new enhancements in Quest's Toad for Oracle.

First I have suggested Steve to modify way how user can add "new tab" in SQL Editor. In current version only "right click" option from menu is available so I have suggested a little bit more easy way-double click on toolbar. Beside that, if user want to close tab, again action is started with right click and popup menu... So my suggestion was to implement "close button" feature (small "x" on each tab) to close tab with left click on it. I find those two enhancements very easy in implementation and very useful for end users in the same time.

Very soon, I got forwarded answer from  John Pocknell, product manager at Quest Software based out of the European HQ in Maidenhead UK, who has on my surprise, promised to include both features in next Toad release (first one that will come after initial 10.0.0.41)!
Mine next idea was to give better support on one big Toad’s problem–handling Oracle connections. Every Toad user has faced in past problem with “dead connection” … i.e. when some connection was not used for a longer time and then user tried to open some query/table/view ... application in most of the cases crashes with known TOAD error dialog:

All previous versions until recent 10, were really suffering big problems on this part and it was not rare that one broken connection would pull all others opened and the result will be complete crash of Toad. In 10g, stability is, generally speaking, greatly enhanced (I haven’t notice this serial crashes), but with still visible problems in connection part.
For this problem I have one hand made solution. Before starting any action on such a sessions, I would always choose menu option Session|Test Connections (Reconnect). In this way I would always force to use disco/conn method implicitly. Because I do not have Toad’s source code I’m just guessing that this option works in described way…without any official confirmation from Quest. Another restriction on this solution is that it would work only if When closing connections option is set to Rollback, what cause implicit rollback without asking about previous session when reestablishing a new one, what is not so unacceptable to some users. This solution was far from from perfect but has satisfied my and mine needs because I never leave unfinished transaction without immediately proper ending (and long actions I always run through sqlplus not any third party tools).

Mine idea for improvements was to create two kinds of connections "week" which should never be tried to reestablished (i.e. some on wireless or known "time limited connections") and "strong", which should always tried to reestablish current connection (like it is now). In this way user can help Toad to behave more easy in situation when communication is broken what will directly lead to more stable application.
Unfortunately , Steve Hilker, informed me, according information from his development team, that core problem is in Oracle Data Access Components (ODAC) component, Delphi component which Toad use for accessing Oracle, so there is not too much to be done in implementation of mine idea. Steve informed that this problem is analyzed for a longer time with little success and progress. However I’m pretty sure that future Delphi to Microsoft .NET transition (which is already in progress from version 9.x) would improve this part in incoming releases of Toad for Oracle.

HTML Schema Doc Generator

Whenever user need to create documentation based on Oracle database objects, classic problem arise-how to do that efficiently in few steps with acceptable result? Solution comes as out of the box module, named HTML Schema Doc Generator, nice and very useful feature inside Toad for Oracle. Module which is available through Database|Report|HTML Schema Doc Generator Toad’s menu option. It is not so known and famous to many users even experienced one. In core it generates HTML documentation with inner reference and links all around chosen objects area (mostly schema but can be highly customizable) in just a few clicks. This module should use not use only DBAs but any kind of system or any other IT specialist all around the world that deal with Oracle.
From a time when I started to be DBA until today and especially time when I was APPS DBA, I have used that feature quite a lot in next situations:

• as reference HTML documentation … like some kind of dirty manual (distributing to other parties on project)
• as reference code snapshot … to have backup for quick reference "what was changed in between"
• when I want to understand more deeply some source code (this was a case in APPS schema where exists a huge and interesting repository of Oracle most sophisticated programming code to learn).

One standard advice is to use HTML Schema Doc Generator with "one file per object" option in any situation you will use:

Problem

Regardless previously defined option (which really reduce overhead and create proper sized files), when generating huge number of objects (like in APPS schema where you are dealing with cca. 21,000 packages!), because Toad create inner reference of all objects, it use huge amount of memory for that because of quicker reference and editing new coming links. Toad starts to work and when cca 1.9 GB of memory is filled it crashed (out of memory problem) with never finishing any part of the report. However, in even recent 10 versions this operation will never finished because Toad will crash with next error:

This is happening regardless there is still left 1GB on Win32 (like in my case) or even more memory on Win64 desktops/servers with /3GB and /PAE switch in boot.ini in all kind of combination. The problem is not solved even in a case of creation of huge Windows swap file (cca. 4 GB on 4GB real RAM server)! Obviously problem was not a memory but the way this memory is used ... what could be fixed somehow.

This is really a bug!?

When I mentioned this bug to Steve Hilker, he has addressed me to John Dorlon, developer who wrote the Toad module-HTML Schema Doc Generator.
Very soon he has faced the same because bug is reproducible (ha has tested on one of available APPS environment) with system modifications which clearly pointed out that "out of memory" is really not a problem! The problem seems a little bit more complex when John realize that even exception log file is not generated ... what would lead to some quick fixes.

At the end of mail conversation, John said that this fix would rely on recoding part of generation of HTML files (there must be a leak in logic) and will be included as soon he finishes other more important obligations that he has in this moment. Honest and fair enough! This is what I appreciates most-be honest, nobody is perfect!

Workaround

For those who has faced with previously described error, I have workaround in a way to run report against huge schema in several steps. In my case I have decided to split whole process in three runs:

1. all "non package" objects in APPS schema
2. all packages A-M
3. all packages N-Z

After that I merge generated HTML pages by hand in one scope file.

Toad on 64 bit Windows

If you have to use Toad on 64 bit Windows (desktop/server) then the easiest solution is to install in separate $ORACLE_HOME, 32 bit Oracle client (any version). In this way Toad will functioning with no problems.

However installing any other 32 bit software (for any other purpose then mentioned) is highly not recommended because Oracle strongly advice to use 64 bit Oracle software on 64 bit OS (Metalink note: 334528.1-Running 32-bit Oracle Software on 64-bit Windows OS).

For the end

For the end let me express very positive thoughts about all guys from Quest software with whom I was exchanging posts. Regardless their position, they are all open minded and not afraid of discussion about bugs and enhancements on their products in all means. Pretty cool property from my point of view indeed. This assure me to believe in even more bright future of this application.

Cheers!