Orange for ORACLE by WareValley Software

The Orange Configuration Admin prompts for a SYS connection to the database, and is aware of the new 9i sysoper and sysdba concepts. It then asks you to select a regular and temporary tablespace before proceeding to create a user and a series of objects within the database to support the product. All changes are logged to the screen. There is no automated process to reverse these changes, however the manual explains that a "drop user orange cascade" will get the job done.

The good news is that Orange provides all of this functionality in a tool that behaves exceptionally well and is very easy to navigate. Orange enables you to work on a problem from beginning to completion without leaving the tool, whether that "problem" is developing new code, solving a performance problem, or monitoring a database. If you spend the majority of your day working with Oracle then Orange could quite possibly become your new best friend.

The closest match that most people will be aware of is probably TOAD - another tool which plays in the "Does almost everything for Oracle" market. Users familiar with TOAD will be quickly at ease when using Orange, as a lot of the concepts are similar. At the same time however the differences definitely warrant downloading a trial and comparing the two in real life. Even after using TOAD for several years I have been pleasantly surprised - Orange is obviously well suited to the way I like to tackle problems.

TOAD - Quest Software ( Orafaq review )
RapidSQL - Embarcadero ( Orafaq review )
SQLNavigator - Quest ( Orafaq review )
SQL Programmer 2001 for Oracle - BMC (recently purchased from Sylvain Faust) ( Orafaq review )
ManageIT SQL-Station - CA (Computer Associates)
Expediter/SQL - Compuware
PL/SQL Developer - Allround Automation ( Orafaq review )
Golden, PL Edit, GoldView, GoldLoad - Benthic Software (shareware)
EZSQL - John Dorlon( Orafaq review )
Keeptool (Hora, ER-Diagrammer, PL/SQL-Debugger)

The product itself appears as a series of toolbars, with enough icons to make your jaw drop. One toolbar provides links to all of the various tools within Orange, as well as some basic functionality like connecting and disconnecting from databases. A second toolbar is context sensitive to the particular tool currently in use. Above both toolbars is a list of icons representing the various tool windows currently open, allowing you to jump back and forth between screens as well as close them. Finally some of the common screens, such as SQL and Plan introduce the concept of named tabs to keep track of the statements you are working on.

The linking within Orange is extremely thorough. If you right click on something in Orange there is a very good chance that a context sensitive menu will appear with the task you wish to perform. For example, click on an Index Scan entry in an explain plan and you can choose to see the Index details or the Base Table details. Right click on a table in the schema browser and you can Analyze the table, or perhaps create an index within the table, or view the list of columns, or edit the data in the table, or … you get the idea. It's very comprehensive and once you get the hang of it very useful. When you first start using the product I recommend right clicking on everything just to get an idea of the options available.

Application Development
In terms of application development Orange steps in and provides all the functionality you are likely to require - it even implements a check-in / check-out style source control system within the database. It provides syntax highlighting and can create syntax templates of common DML, DDL, code-control structures, built-in functions and hints at the click of a button. You can also call up the graphical query builder and have it paste the results back into your code. When your cursor is over a table pressing Alt+C brings up a floating column-list window, whilst F4 opens the comprehensive Describe Tool - which provides every detail you could imagine and even one or two more.

An integrated PL/SQL debugger with all the desired breakpoint, step, watch and back trace functionality is built in. Handy editing functions include the ability to comment in/out selected blocks of text, copy and paste to/from various programming-language formats, and all the usual formatting features you might ever want to use.

SQL Statement Performance Tuning
Orange provides a lot of functionality to facilitate SQL Statement tuning as well as broader application tuning. One of the biggest aides in SQL statement tuning is the Plan Tool, which enables you to:
(a) View the explain plan in three different formats;
(b) View the descriptions of all objects related to the statement in question;
(c) Execute, process and display trace file output of the query (at all available trace levels) and even;
(d) Execute the query and view the results.

Orange includes built-in templates for all of the hints available within Oracle, lets you adjust the array size and fetch options as desired, assign bind variables, select the parsing schema and choose the optimizer mode. An automatic formatter can also help make sense of queries reverse engineered from the database using the SQL Monitor or Session Monitor tools.

Database Monitoring / Tuning
Orange provides a range of database monitoring tools and where practical links these tools together, allowing you to quickly navigate the nuts and bolts of Oracle to locate any problems. For example, you might begin at the SQL Monitor and choose a statement. From here you can view the Session List of sessions executing the statement and then choose detailed Session Info for one of those sessions. Finally you might choose the statement and ask for the Explain Plan, or choose to enable tracing for more detailed analysis.

Other monitoring tools include a Transaction Monitor, a Lock / Latch Monitor, and a graphical Instance Monitor. A special ERP Monitor is also provided. Finally there is an overall Database Information screen which shows every fundamental value from parameters to datafile lists to wait event statistics.

Taking a slightly different approach is the Health Check screen. It performs an array of tests against the database and prepares a HTML based report. The report includes the purpose, description, pass criteria and a guide to resolving any problems for each measurement taken. The Health Check is purely a report - it doesn't attempt to resolve problems automatically - but it's a great way to gain a high level understanding of a database, or save the generated reports and compare them over time to track progress. The Health Check can also act as a great introduction to the world of Oracle performance tuning since it explains each statistic in detail.

Finally, a Graph and Report tool allows the scheduling and execution of pre-defined and user-defined reports. Output is either to screen or Excel format. There are about 100 pre-defined reports that allow you to choose the X and Y values to graph, however the ability to add your own user-defined reports is a definite bonus. It's not likely that you will think of a generic report that they missed, but you might want to add reports specific to your application such as "Orders Processed per Day". Thankfully, reports are categorized into logical groups, making it easy to find one appropriate to your needs.

Schema Browsing
Orange provides two ways of viewing the schema - the Schema Tool and the Schema Browser. The functionality provided between the two is virtually identical, however the Schema Browser is presented in a window which can either sit beside another window, or float entirely free. Personally I really appreciate the floating window because on a dual monitor PC it can be dragged to the other screen and left enabled constantly, leaving the normal product unaffected.

The Schema Tool / Browser is presented as an array of tabs which reflect the types of objects available within a typical database. Each tab then presents a hierarchical tree, starting at schema level. Some of the trees provide multi level drill-down functionality, which is a really useful approach. The Table tree for example can drill down and show the Columns, Indexes, Triggers, Snapshot Logs and Constraints applicable to a table. Depending on which item in the tree you select affects what information is displayed in the bottom half of the screen, and what context sensitive menu options appear.

The good news is that once you get the hang of the Schema Browser the wealth of information displayed and the functions that you can perform with the click of a button are amazing. Also provided is the Description tool that serves a similar purpose. It allows you to enter a partial or complete object name and then displays a wealth of information about that object. If you need to know absolutely everything about an object then the Description tool is ideal.

Configuration / Administration functions
The Network Configuration tool provides a tidy interface to testing database connections and maintaining the tnsnames.ora file. Connections can be verified in three ways - a basic ping, a tnsping, and a database logon test - to provide some basic diagnosis of connection problems.

The Database Information tool provides 16 tabs of information about a database. The information is best suited to DBA's and covers topics such as System Parameter settings, Event Wait statistics, SGA statistics and Rollback Segment usage. I have to say that I was impressed by the layout of this screen.

The Space Manager tool is another amazing screen. On casual inspection it would be easy to pass this off as a "graph of tablespace usage" - but doing so would be an injustice. Two hierarchical views offer the entrance to the tool - A physical hierarchy (Database->Tablespace->Datafile) and a logical hierarchy (Database->Schema->ObjectType->Object). The remainder of the screen provides further drill down and a variety of charts and diagrams. The net result is the ability to view space usage in ways you may never have even considered before - all at the click of a button.

The only minor annoyances I found where the fact that it requires a "sys" connection to each database to configure the product for full functionality. Whilst this isn't a problem for most people, I do know of some environments where access to the sys user is heavily protected from developers, and may require some coercion before being run against production environments.

Orange is available in two versions - Standard and DBA Extended. Pricing for the Standard edition of Orange is currently US$695, whilst the DBA Extended edition is selling for US$1240. These prices include unlimited maintenance, upgrade and support - there are no additional charges. Volume discounts are available when purchasing 4 or more copies.

The DBA Extended version and Standard version differ slightly in the range of tools provided. I recommended downloading the DBA Edition for trial and determining how useful you find the DBA specific tools personally. The following tools are specific to the DBA Edition:

• Analyze Manager
• Health Check
• Graph Report
• Instance Monitor
• Security Manager
• Space Manager