TOAD Product Review
From Oracle FAQ
Toad for Oracle by Quest Software product review.
Reviewer: Luka Stepinac
Date: July 6th, 2008
Contents |
[edit] 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 and advanced PL/SQL editing. 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 put focus on to explain Toad for Oracle and give brief outlook of new features of 9.6 and 9.7 beta version of this product.
| 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 |
|
[edit] 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.
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.[1]On October 16, 2006 Quest software launched the 9.0 version. Toad for Oracle 9.0 continues the commitment to providing new features while improving and streamlining existing features and windows. The 9.0 development cycle focused on stability, usability, and consistency. Some examples include the redesign of the menus to be more intuitive, combining the editors into one window that uses shared commands. Toad for Oracle 9.5 was launched at Oracle Open World on November 14, 2007. This version offered greater flexibility and improved usability, not only for developers and DBAs, but also for their managers and analysts. Toad for Oracle 9.6 was started on April 1, 2008. and will be reviewed in this article, as well as preview of beta 9.7 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 9.7 is available for Windows 32-bit platform 2000/XP/2003/Vista and for Windows 64-bit platform 2003/XP/Vista with 32-bit Oracle Client. For good performance it is enough to have 512 MB RAM whilst for 1GB 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.
[edit] Installation
Toad for Oracle installs with 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 9.7. Similarly, at the very end of installation process, 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 the 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.
[edit] 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
[edit] Detailed review
TOAD has identical user interface in all distributions, and consequently they have the same basic components – Schema Browser, Query execution, Session monitoring, PL/SQL editor and lately it also includes Object creation and maintenance wizards, Script execution and debugging, ER Diagram generation, Schema Documentation generator, Import & Export wizards and utilities, Network utilities and Team Coding.
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 functionalities 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 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 the version 9.7 goes a step forward especially concerning the speed of usage. It is very useful and applaudable that 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.
[edit] Support of new Oracle functionality in 9.6 and 9.7 beta
Quest Software continues to add new functionalities from version to version, parallel with development of Oracle new releases. The new version of Toad offers new functionalities which are specific for 11g database, such as:
- A new tab called “Flashback Archive” has been added to the Schema Browser. Some existing windows now support Flashback Archive. These include Table Scripts, Schema Compare and Create/Alter Table.
- Oracle 11g hierarchical profiler
- Oracle StatsPack Browser and Oracle 11g, etc.
Toad Group Policy Manager
Toad Group Policy Manager is one of the most significant innovations in 9.6 release. It allows multiple copies of Toad for Oracle within an organization to share options, settings and components for better implementation of standards. It consists of a Windows Service which runs on a Windows server and publishes subsets of option data to defined groups of Toad users via TCP/IP, and an Editor which is used to define option sets and user groups. Users can be restricted from changing published Toad options or permitted to alter them.
Toad AppDesigner
Toad AppDesigner is a very useful functionality because it allows an administrator or developer a very simple and intuitive way of initiating more than one standard activity following sequences in a way that macro scripts do. This useful functionality is started in 9.6 version with name Action Recall and now this function has new name and new functions AppDesigner. In AppDesigner, 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 schema, DB Health check, send mail,...
The Toad AppDesigner
In a new 9.7 version, development of Toad App Designer (Action Recall) functionalities goes 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.
We can define those activities 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.
[edit] CodeXpert
If you are using Toad Professional distributions, then you certainly have possibility of using CodeXpert functionality which enables you to inspect quality of your code. Tested version 9.7 beta in CodeXpert Quest software adds 20 most important rules recommended by famous PL/SQL gurus like Steven Feuerstein and Bert Scalzo. Also, in 9.7 beta version, which we had a chance to check out, has additional possibility of sending a code quality report, via e-mail to people who do not need to have access to the database or have Toad installed. This functionality seams very practical and it simplifies ways of reporting, which sometimes is unnecessary burden for developers.
[edit] Database Browser
Since version 9.6, Toad has additional function called Database Browser. It comes with Admin Module add-on. Basic purpose of Data Browser is giving relevant information across multiple schemas or servers to the user, so that he can have an outlook on several schemas and servers in one view. There is a large number of information that can be accessed, which is of great use to DB administrators.
New Database Browser
All information is well organized and divided into groups: Overview, Instance, Database, Options, Parameters, Sessions, Top Sessions, Datafile IO, RBS Activity and Space Usage tabs.
For example, if we mark node from the tree view, on the right side we can see information like the Space Usage tab displays totals for MB allocated, used and so forth and the Datafile I/O tab displays the Total I/O of all your selected databases.
It proved to be very practical and ergonomic that 9.7 Toad comes with embedded Schema Browser into the DB Browser. Now you can view the granularity you need as a DBA by viewing DB and Schema objects here, for every DB being managed, without having to open separate Schema Browser windows.
[edit] 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.
[edit] 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
[edit] 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
[edit] 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
[edit] 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
[edit] 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
[edit] 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.
[edit] 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 necessary 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.
[edit] About the author
Luka Stepinac has been working with Oracle databases for 8 years and holds an OCP certificate. During this time he has worked primarily as database designer and administrator, developing mostly in OLTP environment. Luka has no connections with Quest Software.
