SQL Insight by Isidian Technologies, Inc.

In addition to the wonderful explain plan are a range of other features designed to help tune SQL and PL/SQL - a full PL/SQL debugger, a good SGA browser, DDL creation wizards, a powerful describe tool, a statement comparison tool and a graphical query builder amongst various other features. At the end of the day you will be able to develop and tune SQL and PL/SQL better than ever before.

Absolutely anyone concerned with the performance of SQL or PL/SQL in Oracle should try using SQL Insight. The ability to view the SGA contents, and extract SQL statements from files automatically also makes it particularly appropriate when given the task of tuning an application that very little is known about.

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)

SQL Editor
The first screen you are presented with (following a successful login to a database) is the SQL Editor. The editor is similar to many other products - the top half of the screen let's you type in statements whilst the bottom half can toggle between query results, explain plans and dbms output. There are other windows that you can open and close, such as the statement history list, and all the borders can be dragged around to your liking. Two toolbars are laid across the top of the screen. The first contains what I consider standard buttons (open, close, print, copy) and some auto-formatting and searching buttons, whilst the second toolbar contains buttons like "execute statement", "explain plan", "connect to database" and links to the other tools provided by SQL Insight.

This basic layout is quite typical for this type of tool and I almost glossed over the editor in search of "cool stuff" in the product. This turned out to be poor judgement on my behalf and thankfully I spent some time finding out just what the editor itself can do. PL/SQL developers, in particular, will really appreciate the effort put into designing the editor. I have never seen an SQL editor as flexible as this one, let me try to explain:

Auto completion
Auto completion allows you to type a combination of letters then hit SHIFT-SPACE and a code template replaces the letters. For example, typing just "cr" generates an entire template for creating a new function. A large range of PL/SQL code snippets are already built-in and they are formatted very well but you can modify them and define you own at will, which makes this feature very powerful.

Completion proposal
Completion proposal may be more familiar to people. If, for example, you are writing a select statement and type the alias for a table then all columns are displayed in a window - type the first couple of characters and hit enter. This functionality isn't just limited to tables either, SQL Insight recognizes users, views, packages and all sorts of objects.

Syntax highlighter
The syntax highlighter of SQL Insight is another feature that Isidian Technologies have implemented particularly well. Out of the box, SQL Insight is aware of SQL, C++, Java, HTML, Perl and XML formats. Of course it's fully flexible and it goes like this: You can define additional syntax's entirely, each syntax can consist of any number of elements, and each element can have foreground color, background color and text styles (bold, italic, etc) defined. Finally, each element can have any number of keywords assigned to it. What does this all mean? Simple - it can highlight text in almost any way imaginable, and if you're not happy with the formatting you can very easily change it.

Statement formatter
Finally, the statement formatter comes along to finish the job. As per usual, SQL Insight allows an unlimited number of formats to be defined, but this is really just there so you can share format definition files between users - create the corporate format and share it amongst all developers. A format consists of a large number of keywords. For each keyword you can choose to line-break before or after, indent or un-indent, choose whether to indent with tabs or spaces (of course you choose how many) and also define lists of characters to always perform these operations upon. My recommendation is to use the preset format and only tweak it as necessary (particularly if your organization has existing standards) - someone has already done the hard work of creating it from the ground up and I wouldn't waste time trying to do the same.

PL/SQL Debugger / Profiler
The professional version of SQL Insight also includes an integrated PL/SQL debugger and profiling tool. This includes advanced breakpoint options (the ability to enable/disable breakpoints without removing them, define their behavior), variable watch and modify functionality, and a call stack list.

Query Results
Of course, once you have written a statement using the SQL editor you might actually want to run it and inspect the results. The query results tab appears in the lower half of the SQL editor. Once the results appear you can re-arrange columns, sort by columns, group by columns (similar to MS Outlooks grouping functionality), add custom summary footers, save the results to XLS, HTML, XML or TXT, or export the result. The results tab also supports LOB's, within inline viewing of common image types and the ability to export any LOB to a file. Finally, it is in this window that you find the autotrace functionality - which provides statistics very similar to the SQL*Plus autotrace feature. It really looks like Isidian have thought of pretty much everything.

Explain Plan
The explain plan in SQL Insight is amazing - it's hard to think of any improvements that could be made. Firstly, a typical tree-type view is presented with icons indicating the type of action performed and numbers indicating the execution sequence. Immediately below the tree though is a natural language explanation of the steps. This section matches the numbering of the tree and copies the icons, but explains the operation in natural language and links the steps together.

Two other windows change as various steps in the explain plan are selected. The first of these windows gives some additional details on the step whilst the second window is actually a describe type window for the associated table (when relevant). This describe window shows the columns, indexes (including their status), referential integrity, DDL script, information, dependencies and even data. You also have an option to analyze the table. Overall, this makes for a stunning explain plan - everything is clearly laid out and can be used by both novices and tuning guru's.

History / Pin List
Two lists are provided for keeping track of statements. A resizable History List keeps track of every statement executed in SQL Insight. This provides a quick way to retrieve previously executed statements and shortcuts provide a variety of options (such as saving, executing, copying to editor, etc). Once the defined number of statements has been exceeded the oldest statements are removed.

The Pin List serves a slightly different purpose. Statements must be nominated for the pin list, and can have a named assigned to them. The naming can be quite useful, especially for retaining commonly used queries where searching the history would be difficult.

SQL Script Viewer
The SQL Script viewer tool allows you to select an SQL file and the tool presents a list representing each of the statements found within the script. From here users can drag statements back into the editor, execute them, request explain plans or select multiple statements at once and compare them.

The ability to compare the statements can be quite useful from a tuning perspective, especially when presented with a large amount of SQL and don't know where to focus your efforts. Simply compare all the statements at once and use the results to determine which statements deserve the most attention. Of course, when taking this approach to tuning, remember to consider the number of times the statement will be executed in a given time frame. If a statement executes in 1 second and is called once per day then there is no use tuning it, but if the statement is called thousands of times per day then even a fraction of a second gain becomes significant.

SQL Comparison Tool
The SQL Comparison tool is tucked away somewhat. You can either select multiple statements from the SQL Script viewer, select multiple bookmarks in the SQL Editor, or select multiple statements from the Pin List and then choose to open the comparison tool. The comparison tool then executes each selected statement a configurable number of times before displaying the results.

The results are presented as a range of bar graphs representing various statistics. The default graphs shown are: Elapsed Time, Session Logical Reads, CPU Used by Session, Consistent Gets and Physical Reads. Many other graphs are also available from a list. The comparison tool is particularly useful for verifying (and even proving) tuning results of equivalent queries, or for identifying statements which deserve attention.

SGA Browser
The SGA browser is a relatively simple tool, but can be essential when you have limited information about an application that requires tuning. Like most SGA browsers, the top half of the screen is a list of statements currently in the SGA. The table includes statistics such as the executing user, number of executions, number of disk reads, etc. The table can be sorted and grouped in a manner similar to standard query results.

Selecting a statement then shows the formatted SQL in the lower half of the screen, with the ability to also display an explain plan, copy the statement back to the SQL editor or display statistics about the statement. The statistics represent most of the columns in the statement list, shown as percentages compared to the average for all statements. This approach highlights statements with an unusually high statistic. Finally, an option to flush the SGA is provided.

DDL Wizards (Object creation)
A group of DDL Wizards have been incorporated into the tool. These include templates for creating: Functions, Packages, Procedures, Sequences, Tables, Triggers, Types and Views. The wizards are quite flexible and are being constantly improved to support new features as Oracle implements them. Obviously the complexity of the wizard depends on the object. For most objects the wizard then generates the code into a new tab in the SQL editor ready for fine tuning or additional content before execution, tables and sequences are created automatically but provide a "view source" button to capture the SQL if required.

Query Builder
A visual query builder let's users build basic queries by dragging objects (such as tables and views) from a list onto a screen and then dragging relationships between columns. There are obviously limits to the complexity of statements that can be constructed this way but for basic statements the approach is fine. The bonus of this tool is that you can print the graphical diagram of the query.

Variable lists can be created, and the join conditions allow all standard operations such as equality, less than, outer joins, etc. Interestingly, the graphical queries can be saved and then used as sub-selects in other visual queries - the SQL text generated includes the contents of the sub-select statement. Finally the statement can be copied back to the editor for final modifications, tuning and execution.

Miscellaneous Features
SQL Insight also includes several other pieces of functionality, all aimed at getting the job done a little easier. Some of these include:

• Direct Source Code Control integration, enabling you to check in and out files directly from SQL Insight.
• Display Oracle Parameters. This lists all the standard Oracle parameters (typically modified by the init.ora file) and indicates whether the parameter is the default value or not.
• The ability to turn quickly Oracle's tracing facility on and off. Unfortunately there is no option to set the level of tracing currently.
• Finally, a FTP Client is built into SQL Insight. This is particularly appropriate for retrieving code from servers and is also convenient for retrieving trace files (see above point).

Currently SQL Insight 2.0 Standard edition retails for $US295 ($US49 upgrade from v1.x), whilst the Professional edition is $US395 ($US149 upgrade from v1.x; $100 to upgrade from v2 Strandard Edition). Bulk licensing is available and can be discussed by sending email to sales@sqlinsight.com. Finally, a support agreement is available for $US195 per annum.

The product can be purchased online at the SQL Insight website: http://www.sqlinsight.com. Trial versions of the product can also be downloaded from the website - the trial edition is fully functional and works for 15 days, with a further 15 day extension option.

The features page of the SQL Insight website also shows screen shots of the products functionality.