Toad for Oracle by Quest Software
Index
- Summary
- Product History
- Supported Operating Systems and Versions
- Installation
- What it does
- What problems does it solve?
- Who should use it?
- Competitive products
- Detailed review
- Shortcomings
- Cost and where to buy
9 December 2005
Author: Mark Richard
Toad for Oracle is the product that almost all other Oracle Development and Administration tools are compared against. The product began life in 1995 and essentially pioneered the category of Oracle Development tools. Today the marketplace has changed considerably with more competition than ever before, however Toad continues to grow and remains at the leading edge of the pack in terms of functionality and quality.
Toad is also one of few database development and administration tools that has expanded to include support for various database technologies. The range now includes Toad for Oracle, Toad for SQL Server, Toad for MySQL and Toad for DB2. With an increasing number of organisations employing a blend of database technologies the practicalities of having an application suite that provides DBA’s and Developers with a consistent interface across all platforms is particularly attractive. The following review focuses specifically on Toad for Oracle.
Read on to learn about some of the great new functionality incorporated into the product. I’ve personally been using Toad since 1999 and with every release there has been a multitude of new functionality, ranging from support of new Oracle features to some really nice user interface enhancements.
| Usefulness to DBA |  |
| Usefulness to developers | |
| Functionality, how much does it do | |
| User interface, intuitive/friendly | |
| Software quality, integrity, robustness | |
| Documentation quality and scope |  |
| Technical support availability | |
| Value for money | |
| Ease of integration with other tools and systems | |
| Overall rating | |
Toad has more history than any other Oracle development product that I have used. Development began way back in 1995 by an individual programmer. During 1998, Quest software acquired the product and continued development, with the first official Quest release being Version 5 in October 1998.
Since then Toad has had approximately two major releases per year. Version 8.5, released in July 2005, added JIT debugging, Citrix support, RAC and enhanced 10g support amongst other items and, most recently, Version 8.6 (October 2005) includes support for the Oracle E-Business Module, improved data modelling, master/detail browser enhancements and a new reporting engine.
Another significant historical event is the July 2005 update to the Freeware version of Toad. Since Quest acquired Toad back in 1998 there has always remained a slightly restricted freeware version of the product available, however it was based on an old release of the product. The July 2005 update brings the freeware version essentially up to date – adding support for Oracle 9i and 10g, along with a lot of other new functionality. It is reassuring to see Quest’s continued support of the freeware edition. More information about the freeware edition can be found at http://www.toadsoft.com/
Supported Operating Systems and Versions
Toad runs on all recent 32-bit Windows platforms including Windows 2000/NT/XP/2003. Basic hardware requirements start at 256MB of RAM and 44MB of disk space, although the disk space requirements go up depending on the optional modules purchased. Oracle SQL Net, Net 8 (32-bit) and Net 9 clients are supported. Toad for Oracle 8.6 is compatible with Oracle versions 7.3.4, 8.1.7, 9i, 9r2 and 10g running on any hardware platform.
Installing Toad takes only a few minutes. A wizard guides you through the entire process which, depending on the purchased modules, can require several database changes. Thankfully the wizard steps through this and presents all SQL that is going to be applied – giving users an opportunity to review all changes before they are applied, which can be particularly important in tightly controlled production environments.
The first time Toad is started it asks some initial configuration questions - such as the preferred format of the Schema Browser, and whether to process queries in separate threads. This is a convenient way to configure some options that users may not otherwise even be aware of – Toad is the first product that I have seen to take this approach and it would be nice to see other products adopt a similar approach.
What doesn’t it do? Toad for Oracle is almost certainly the most fully featured Oracle development tool on the market, thanks largely to its long life and extensive user base. Even the base feature set of Toad for Oracle is incredibly broad and most users will find that they are rarely, if ever, constrained by the product.
All standard development tools are available – Schema Browsers, an advanced Query execution module, Session monitoring, PL/SQL development. The feature set just keeps growing though - Script execution and debugging, ER Diagram generation, Schema Documentation generator, Object creation and maintenance wizards, Import & Export wizards and utilities, Network utilities and Team Coding.
The different versions available, and additional add-on modules, further extend the application of the tool into particular focus areas - such as advanced debugging and code analysis, or perhaps the DBA add-on module is more appropriate for your needs.
To step through every feature of the product would simply take too long and, to be honest, there will be features within the product that you may never use. For example, I have never worked on a database with Java stored within it, nor have I ever used the Team Coding facilities - but at the same time I'm sure there are people who absolutely love these features. It's certainly nice to use a product that has such broad coverage and depth - and this is one of the reasons why so many different Oracle professionals have used the product.
Toad for Oracle provides an extremely robust and well-featured Oracle development and administration environment. Its various modules allow the product to be tailored to the needs of the individual user and it is difficult to think of an appropriate feature that is missing from the product.
Toad addresses the complexities of administering production databases, developing SQL, PL/SQL, SQL Scripts, data analysis and performance tuning. It also extends into more specific areas of functionality such as benchmark testing and physical data-model architecture. To give an example of it’s flexibility here are just some of the features that developers will appreciate beyond regular query and stored procedure development... It can create subsets of test data, create ER diagrams, search for duplicated data, compare schema structures, manage stored procedures and estimate future object growth.
The easiest answer to this question is: People who use Oracle. In particular, people whose career revolves around Oracle and people who need to be highly productive when working with Oracle. It is a powerful tool and the more time you spend with Oracle each day the more beneficial a product like Toad is. For me, its one of the first applications that I start each Monday and one of the last to be shutdown on Friday - I don’t use Toad all day but I frequently find myself switching back to it to investigate a problem, or check an index structure, or monitor performance, or one of a hundred other possible uses.
PL/SQL Developers will almost certainly want the Professional Edition, which bundles the optional debugger module and the Code Xpert that performs tasks such as code analysis. DBAs will be more interested in the Segment Management, Session Browser, SGA interface and the additional DBA module that provides advanced interfaces for items such as Rollback Usage. Data Analysts will be more concerned with its flexible multi-threaded SQL Editor that allows controlled execution of several queries at once, the powerful Master/Detail data browser, the Explain Plan tool, and the powerful data import and export utilities and interfaces.
As you can see - the product has features that will appeal to different people in different scenarios, and if you fit across several of these categories (like myself) then the product is even more appealing.
Toad is the product that the competition is regularly measured against - partly because of it’s broad coverage. Therefore, many of the products below will compete in certain aspects but be left lacking in other areas. Having said that, they may be totally appropriate for your particular needs. Also, be sure to have a look through the OraFaq comparison chart - it gives an idea of where certain products are particularly strong or weak. It is important to note, however, that some ratings in the comparison chart reflected features not available in all versions of Toad - the ratings are based on "Toad for Oracle Suite 8.6".
For a list of Competitive Products, refer to orafaq.com/tools/competitive
For some products, this section of the review provides an overview of virtually the entire products functionality. For Toad this simply isn’t practical. Instead, this section will detail some of the recent features added or significantly enhancement since earlier versions of Toad. The best way to discover the full functionality of Toad is still going to be achieved by downloading the trial version from the Quest website and experimenting - even after a month long trial there will probably be components of the application left undiscovered.
User Interface
The Toad for Oracle user interface continues to improve with each release. With a product as complex as Toad managing the screen real estate and behavior becomes an art form. Most recently, Toad has started to make enhanced use of fly-out windows that can be pinned in place. In use, this approach works particularly well in a lot of scenarios. For example, a fly-out of recent (and personal and named) statements beside the SQL Editor makes it quick and efficient to recall old statements and options to filter the list and open statements into a new tab really enhance productivity.
Also, the interface of Toad 8.6 feels a little more responsive than 8.5 even. Everything from requesting an explain plan to opening a new Schema Browser window is as fast as could be reasonably expected.
Support of new Oracle functionality
Toad rapidly responds to new Oracle features and functionality. The newest versions of Toad provide the ability to efficiently administer RAC environments and provide interfaces to new utilities such as Data Pump. The object creation wizards also support many of the new configuration options. Components of the application dynamically adjust based on the currently connected database - if you’re connected to 10g and are creating a table then you will see all of the 10g options, connect to an 8i instance and only the appropriate 8i options will remain visible.
The schema browser includes new sections as appropriate for new objects, as well as dynamically changing based on the connected Oracle instance version. For example, connecting Toad 8.6 to an Oracle 10g instance revealed 39 different object tabs. It’s easy to sympathise with the developers who must keep track of the functionality matrix and make the application respond correctly when a user simply selects a different entry from the list of open connections.
Export File Browser
Toad for Oracle 8.5 introduced a new Export file browser. It allows you to access a dump file and inspect and work with its contents without importing it into a schema. This is particularly useful for inspecting the contents of a dump file and then importing just selected objects into a schema without having to work with the command line "imp" utility.
The dump file is shown in a hierarchical format, starting at the schema level and then drilling down into various object types and then the specific objects. Once an object is selected its DDL can be viewed and, where appropriate, the exported data is also available. A couple of nice features like the ability to compare the schema of the dump file against a database, or generate DDL for a branch of the dump file are included.
In terms of performance, opening a 300MB dump file on a P4 3.0GHz desktop took less than a minute, and small dump files opening virtually instantly, so it seems entirely viable to work with reasonable size exports via Toad. Once the file has been opened browsing its structure and data contents is virtually instant. Also of interest to users will be Toad’s new Data Pump import and export wizards - supporting the latest functionality of this high performance import/ export tool.
Updated Master/ Detail Browser
The Master/ Detail Browser has been a component of Toad for quite some time, and it’s always been one of the most flexible and useable implementations, however Toad 8.6 enhances the feature even further. Filtering and sorting is possible on not only the master grid, but also any of the detail grids, each grid can be resized as appropriate, and reports can be generated based on any of the grids. The ability to manually modify the SQL used to populate each grid is even presented - offering unprecedented flexibility.
One of the strengths of Toad’s Master Detail browsers is the ability to custom define relationships between tables in addition to using any existing referential integrity. This is particularly useful when working with databases that don’t have RI built into the schema, or when the existing RI does not reflect the particular relationship you wish to traverse. Combining this with the ability to save the structure and all relationships into a "Master Detail" XML definition file makes it easy to set-up required configurations and instantly return to them at any time.
SQL Modeler
The SQL Modeler within Toad is particularly well featured for those people who like graphical representations of queries. The tool supports concepts like sub-queries and models can be saved and re-opened as necessary. A hierarchy on the left-hand side of the screen provides access to all elements of the query and allows options like table aliasing. Generated queries can be exported to the SQL Editor or Tuning wizard (if installed), and the generated diagram can be printed. Amongst the Toad 8.6 enhancements is the ability to generate statements conforming to the ANSI SQL syntax (selectable in the SQL Modeler options).
E-Business Module
The new E-Business Suite Module, available as an optional module for Toad 8.6, provides dedicated functionality for managing E-Business databases. It includes a customised browser (as shown below), a customised monitor (similar to the regular database monitor), a reporting module that includes about 10 built-in reports with the ability to modify and add more as required and a lookup finder to help Oracle Apps developers.
The purpose of this module is to provide a simple, yet functional, interface to what is actually a particularly complex application. The browser provides a convenient interface to commonly accessed objects such as Applications, Lookups, Patches and Users – this combines to make both development of new programs and administration of running systems more efficient. The monitor will show details such as current transactions and identify peak periods of activity. The reporting module includes pre-defined reports appropriate for the E-Business application, whilst also providing the ability to modify and create additional reports. Finally, the lookup finder is a handy utility for searching through the vast number of lookup tables to locate the one that either requires maintenance or should be used within new programs.
Obviously, anyone working with Oracle E-Business on a regular basis would be well advised to download a trial of this new module to see how it can help them out. Including this functionality as an add-on module makes perfect sense and it will be interesting to see if Quest expands the range of add-on modules in the future to accommodate other applications.
ADDM Report Generator
Toad for Oracle 8.6 introduces a new ADDM Report Generator, which is available when connected to Oracle 10g instances. ADDM is Oracle’s new Automatic Database Diagnostic Monitor - a tool that provides human readable performance enhancement suggestions. The suggestions are made based on analysis of previous workload statistics that are captured by the database.
When the report generator is opened the only user input required is to nominate the Start Time for the analysis, and the End Time. Default 10g installations capture details on an hourly basis for a 7-day rolling window, but this can be reconfigured if necessary. This allows the report to focus on a particular timeframe if required. The report is then generated with the findings and suggestions on how to improve database performance, focusing on tasks that are most resource intensive.
Other enhancements
The list of incremental enhancements in Toad 8.6 is substantial. Some of the highlights include:
- Enhancements to the PL/SQL Debugger, including compound conditional breakpoints.
- Connection color coding - This provides an outline around window icons, and also a large colored panel in the lower-right of the screen indicating the current connection. Coloring production connections differently to test and development environments makes it easy to confirm which database the action will be performed against.
- CodeXpert can now process multiple files/ folders at a time - allowing for aggregated results
- Enhancements to the report writer, allowing query results to be quickly transformed into professional quality reports.
As you can see - even though the version number only moved from 8.5 to 8.6 the number of changes, including some very major enhancements, is very large. If your most recent Toad experience is with an older version of the product then you are in for even more additions - far too many to document in any readable fashion. It’s good to see that Quest continues to push the functionality of the product, rather than stagnating, even though the product is already so feature rich.
Possibly the biggest short-coming of the product is only applicable depending on your specific needs - If you are a PL/SQL Developer then you will almost certainly need the PL/SQL Debugging module, which is included in the Toad for Oracle Professional package (and upwards). Besides that, there is very little to complain about. The product is mature, stable and refined so there are no quality issues and the interface design is very good yet still finds ways to improve further with each release. A few releases ago it felt like the interface was stagnating a bit but recent versions have implemented some great concepts, such as the fly-out SQL Recall window and configurable Schema Browser. The new hierarchical approach to Session Browsing is also particularly useful for monitoring environments with many concurrent connections.
There can be no complaint in terms of functionality or features - it does virtually everything you can think of and even includes little ancillary features like a SSH / Telnet interface, FTP client and a task scheduler. The product also supports new Oracle features such as new RAC administration support. One feature I would like to see added in the future would be a configurable automatic update check - It would be great to be informed when an update is available without having to check the website periodically.
Toad comes in a range of configurations. Starting with the base product, Toad for Oracle, there are three main upgrade options: Professional Edition, Xpert Edition and Suite. In addition to the main product there is a series of add-on modules that can be purchased to further extend the products functionality. The following prices are all quoted in US Dollars and include one year of maintenance.
More information about the product can be found on the Quest website: http://www.quest.com/toad. A free 30-day trial of the product can also be downloaded, which is particularly useful for determining which configuration to purchase. Downloading the trial and using it for a couple of weeks is essential to get a good understanding of all the features contained within the product - So what are you waiting for?
About the Author
Mark Richard has been working with Oracle databases for over 6 years. During this time he has worked primarily as a database developer specializing in performance tuning on both Datawarehouse and OLTP applications. He can be contacted at . Mark has no connections with Quest Software besides being a licensed user of Toad for many years.