TOAD Product Review

Toad for Oracle by Quest Software product review.

Summary

Today, Toad for Oracle presents a solution for a great platform where Database Quality, Performance and Availability functions for administration and development enable you to perform daily tasks efficiently and accurately.

The new era has come since Toad became the perfect tool for multi purpose tasks. It is useful in the area of database development, optimization, database administration, as well as advanced PL/SQL editing and code correctness. It is made for both amateurs and professionals. First Toad release started for Oracle database and today Toad supports various database platforms: SQL Server, MySQL and DB2. In this review I will focus on Toad for Oracle and give a brief outlook of new features of 11.0 and 11.1 beta version of this product.

Usefulness to expert DBA
Usefulness to newbie DBA
Dangerousness to newbie 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

History

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 version 8.6 (October 2005) included support for the Oracle E-Business Module, improved SQL Modeller, master/detail browser enhancements and a new reporting engine.

Other significant historical events were the 2005 and 2010 updates 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 2010 update brings the freeware version essentially up to date – adding support for Oracle 11g, along with a lot of other new functionality and relaxation of previous feature limitations. 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.[1]In November of 2009, Quest software launched the 10.0 version of Toad. The 10.0 development cycle focused on stability, usability, and consistency. Some examples include the standardization of data grids, Unicode support and a newly designed ER Diagram tool. Toad for Oracle 10.5 debuted in April of 2010, offering greater flexibility and improved usability, not only for developers and DBAs, but also for their managers and analysts. Toad for Oracle 11.0 was was released in September of 2011 and will be reviewed in this article, as well as a preview of beta 11.1 currently available on Quest website.

Distributions of TOAD for Oracle

As Toad grew over the last ten years, it became especially great product, but needed separate setup for different purposes. That was a reason big enough for Quest Software company to make five different distributions of this software. Basically, what happened was that Quest divided distribution of this product in two groups: one made for developers, managers, analysts, and the other for DBA and DBA managers. For the first group there is Toad Development Suite for Oracle, Toad for Oracle Xpert, Toad for Oracle Professional and Toad for Oracle, and for the other there is one distribution of Toad DBA Suite for Oracle.

Toad for Oracle

It contains different features, from PL/SQL and SQL scripts, PL/SQL and SQL debugging suite to built-in knowledge basics: Knowledge Xpert for PL/SQL and Knowledge Xpert for Oracle Administration. It allows you to visually create queries and execute them, construct and manage database objects, share project assets such as templates, scripts, and code snippets, with other team members using Project Manager, record workflow steps and play them back later with Action Recall and profile your PL/SQL code to find performance bottlenecks and check for code coverage. Toad for Oracle also has its own team collaboration utility called Team Coding. Team Coding works with Toad's editor to control code access.

Toad for Oracle Professional

This piece of software enables you all features of Toad for Oracle, but it has additional possibilities through CodeXpert, which automates code quality reviews for an objective and consistent review process. CodeXpert contains incorporated rules from experts, Steven Feuerstein for example. Whit these groups of rules, your code has visual expert who advises you when entering your code. With professional distributions you have possibility to generate quality and performance great code. Toad for Oracle Professional also includes an Export File Browser including objects and data from an Oracle export file. Finally, this edition of Toad contains Oracle import/export utility wizards, which makes your work more efficient.

Toad for Oracle Xpert

It brings you closer to perfection, since, besides all the features in Toad for Oracle Professional, it contains SQL performance tuning through the SQL Optimizer. The benefits of this module are that it scans PL/SQL and external scripts and files to identify the SQL statements that may perform poorly in production. It then rewrites the SQL for you and offers alternate implementations to improve performance. These operations can be performed per SQL statement, interactively or in bulk, using a batch process.

Toad Development Suite for Oracle

Toad Development Suite for Oracle distribution is an easy, automated way to ensure PL/SQL based applications meet the essential requirements of code quality, performance and scalability before production release. Each part of the suite enables the efficient development, testing, optimization and validation of code.

Toad DBA Suite for Oracle

The Toad DBA Suite provides an integrated set of tools to ease database administration tasks, quickly diagnose problems and automate SQL tuning and optimization. It is a direct way to manage, synchronize and deploy schema changes, administer your databases including users, roles, infrastructure and utilities, diagnose and resolve database performance issues in real time, identify problematic SQL statements directly from source code or execution in Oracle, manage database storage, replay database workload to simulate production environment in development/test databases and so on.

DB Admin Module (add-on to other configurations)

Quest prepared special addition which can be added to other distributions of Toad. What is really interesting in this distribution is workflow which allows moving thought different tasks in simple way thanks to powerful browser-based view. Toad DB Admin module gives us functionalities such as: navigate across multiple databases and perform object management with the Database Browser, view Oracle performance metrics using StatsPack Browser or AWR Browser, compare and synchronize database schemas, generate synchronization scripts, execute a 86-point health check, complete with 23 vulnerability assessment checks, schedule batch jobs.

Supported Operating Systems and Versions

TOAD for Oracle 11.0 is available for Windows 32-bit platform 2000/XP/2003/Vista/Win7 and for Windows 64-bit platform 2003/XP/Vista/Win7 with 32-bit Oracle Client. For good performance it is enough to have 1 GB RAM whilst for 2 GB workstation it presents platform for very comfortable work. Concerning the disk space, it depends on the choice of your distribution: for example, for Expert edition it will take between 75MB and 100MB. When it comes to supported versions of Oracle databases almost all the versions are supported, from 7.3.4 to the last 11g database. To connect you can use clients for Oracle SQL Net, Net 8 (32-bit only) Net 9, Net 10, Net 11 or Instant Client. Toad version 11.1 will be compiled in 64-bit and will support 64-bit Oracle clients. The projected release for 11.1 is spring 2012.

Installation

Toad for Oracle installs with a wizard, which takes the user through the process dependent on distribution by giving him relevant data, even for the person who uses it for the first time. Speaking from my own experience, I can say that I have never had problems with installing the tool, and this is as well with the new version 11.0. Similarly, at the very end of installation process, the wizard offers a possibility for the user to check on all the changes occurred directly before installation which he can then reject or accept. After Toad starts first time, the user is led through initial setup, like choosing the way of access to database elements in Schema Browser and similar customizations.

Comparison with other products

Today there are many tools for administration and development in Oracle environment, which are always compared with Toad. For me personally, in most cases, Toad represents the best solution, which I tried to prove myself in a way that I tried to use the other competitive products in my work environment during the database administration and development. It has nothing to do with habit and simplicity it offers, but with the fact that most of other tools cannot perform the tasks that Toad can. Toad is a criterion by which we measure other products. This is mostly because the other products can compete with it, but usually they lack some of its functionalities. It depends on what you need. I suggest you to look at OraFAQ comparison chart which gives you the idea on what products you need for specific areas. For a list of Competitive Products, refer to orafaq.com/tools/competitive

Detailed review

TOAD has an identical user interface in all distributions, and consequently they have the same basic components – Schema Browser, query execution, session monitoring, an integrated editor and lately it also includes object creation and maintenance wizards, script execution and debugging, ER diagram generation, Schema Doc generator, import and export wizards and utilities, and a newly-rewritten Team Coding module.

In the next few paragraphs we are going to describe the most significant and the most useful functions, and I warmly recommend that you try the trial version which offers all the necessary functionality for 30 days.

User Interface

Toad for Oracle user interface improves and upgrades in each version, depending on the additional functionalities. It is astonishing that after so many additional options the user interface is still as simple as it always was. If there is a need for additional functions you do not need to overcrowd the screen. It is so because Toad has started to make enhanced use of fly-out windows that can be pinned in place. This advantage was implemented in 8.x version and now version 11.0 goes many steps forward especially concerning the speed of usage. It is very useful and applaudable that the user interface is customizable, extending to the toolbars and menus, and saved settings stored and persisted between upgrades and can also be shared between desktops.

Toad Automation Designer

Toad Automation Designer is a very useful tool because it allows an administrator or developer a very simple and intuitive way of initiating more than one standard activity (or action) following sequences in a way that macro scripts do. This useful functionality started in version 9.6 with the name Action Recall and now this tool has evolved to become Automation Designer with a host of new actions including the Code Analysis action and others. In Automation Designer, activities can be various: execute SQL script, run shell scripts or programs, import and export data, send some file to FTP server, TNS ping, Compare schemas, DB Health check, send mail,...

Automation Designer

Since version 9.7, development of Toad Automation Designer (Action Recall) functionality has gone even further, so from now on you can include IF..THEN..ELSE, WHILE ... DO, REPEAT...UNTIL, LIST ITERATOR, FILE ITERATOR logic over all activities, and depending on a result of actions taken, it can start activity which logically follows. Additionally, Automation Designer allows the use of system variables as well as user-defined variables.

We can define these sets of actions (called apps) as much as we want and put them in a most suitable order. That way we speed up activities and save some time, for example data setup just before code testing or importing new data from necessary source and sending information via e-mail to those who need to be informed. After you get familiar with this functionality, ideas are simply flowing and finally you realize that scenarios and possibilities are unlimited.

CodeXpert Becomes Code Analysis

If you are using Toad Professional distributions, then you certainly have the opportunity of using Code Analysis (formerly CodeXpert) which enables you to inspect the quality of your code. Version 11.0 features an expanded ruleset and better looking, more informative report output. Running Code Analysis as an action has replaced the old-style command line utility, though it can still be executed from a DOS prompt. If you have lots of old CodeXpert param files saved from previous Toad versions, they can easily be converted to Code Analysis actions in Toad 11. And new for the upcoming Toad 11.1 release is an even larger ruleset and the ability to create your own custom rules.

Team Coding

The Team Coding interface has been entirely redesigned for version 11, and is now much simpler to install and configure. Also, the source control toolbars have been combined and simplified. Now you can perform all of your source control tasks from one toolbar, regardless of whether you use Team Coding, just a VCS, or both. Code Control Groups have been replaced by Code Collections. Code Collections combine information in Code Control Groups and the Team Coding Mask in a single user view, thereby making it easier to set up groups for Team coding. In the next Toad version, certain features of Team Coding will work in conjunction with Code Analysis, allowing managers to enforce code quality and standards before objects or files can be checked in.

RMAN script template

Database administrators often come across RMAN tools, so they are forced to write complicated RMAN scripts, which can take a lot of time. Toad 9.7 version has samples of often used RAMN scripts, which can be chosen from Database Browser, adjusted to our needs and saved for future usage.

Data Generation

Developers and DB administrators very often have to generate data for the needs of development and testing environment. Because of the safety reasons and data protection, real data cannot and should not always be spanned into testing environment, and developers and DB administrators are forced to generate test data themselves.

Data Generation

Data generation is an excellent new Toad functionality which gives user (developers or DB administrators) a good and simple way of creating data in one or more tabs, whilst respecting Referential Integrity.

Export File Browser

Export file browser was introduced in Toad for Oracle 8.5. 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.

The Export file browser

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 usable implementations. However Toad 9.7 enhances the feature even further. Filtering and sorting is possible on not only the master grid, but also on any of the detail grids; each grid can be appropriately resized, and reports can be generated based on any of the grids.

One of the strengths of Toad’s Master Detail browsers is the ability to define custom 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.

The Master/Detail Browser

Query Builder

The Query Builder within Toad is particularly well featured for those 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. In version 9.0 and above, Toad has very useful functionality which allows reverse-engineered into Query Builder from already existing SQL statements.

Query builder

ADDM Report Generator, AWR Browser and StatsPack Browser

Toad for Oracle 8.6 introduced 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 time frame 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.

Toad for Oracle 9.6 has a new AWR (Automatic Workload Repository) Browser monitor for database performance metrics, by enabling some useful graphs and charts. Data is represented by a user-determined range of DB snapshots.

Great advantage is StatsPack Browser which is completely independent of version of Oracle and diagnostic pack. It displays performance metrics between statspack snapshots in great detail. You can use this ability to track, compare and organize your statistics. From the Statspack browser window, you can select snapshots, create new snapshots, select, display and print charts, program custom charts, and various other combinations. The browser window makes it easy to access the power of the data collected by Oracle's Statspack feature. By its functionality StatsPack Browser is very similar to AWR Browser, which certainly plays important role while working with older versions prior to 10g, like Oracle 8i and 9i.

Automatic Workload Repository Browser

Other enhancements

The list of incremental enhancements in stable Toad 9.6 and new in 9.7 beta is quite impressive. Some of the highlights include:

• Health Check: Vulnerability Assessment
• AWR Browser with StatsPack and AWR Advisories
• Trace File Browser
• Flashback archives for Oracle 11g
• Tighter workflow integration with Toad Data Modeler
• Very good PL/SQL debugger
• Knowledge Xpert for PL/SQL and Oracle Administration
• Import/Export utility wizards
• RMAN template scripts
• Tighter workflow integration with Quest Code Tester for Oracle 1.8
• Support for the Oracle 11g hierarchical profiler
• Get started Videos
• Support Subversion version control system

Integration

Toad for Oracle has a strong support for integration with other Quest tools, such as Xpert for PL/SQL and Knowledge Xpert for Oracle Administration, Toad Data Modeler, Quest Code Tester for Oracle, Toad for Data Analysis, and in final release there’s also the integration with Benchmark Factory for Databases.

Useful information

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? For more information contact ToadWorld (http://www.toadworld.com/).

Conclusion

Quest Toad for Oracle is a tool which is useful for any serious developer or DBA involved in working within one or more of the three stages – development, optimization or validation. Depending on needs and habits, many of above mentioned functions will never be used, but when you are aware of them and when there is a need for their usage, it simply cannot leave you indifferent.