SQL Insight by Isidian Technologies, Inc.

A wizard drives the installation, only asking a minimum of questions, and it literally took less than 10 seconds to install. There is no need to restart the computer before using the product and no server-side installation to complete - I don't know how it could be made any simpler.

Closely related to the installation process is the upgrade process. Each day when the application is first started it "calls home" via the internet and checks for any possible upgrades. If one is found it provides a brief description of the upgrade and provides links to download and apply the latest version. Kudos for making this task so straight forward and automatic - no-one wants to have to constantly visit sites checking for new releases.

In addition to these features are a range of other components designed to help tune SQL and PL/SQL - a full PL/SQL debugger and profiler, a good SGA browser, DDL creation wizards, various DBA related features, 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. It has world-class explain-plan and statement profiling functionality. The automatic query tuning wizard is unheard of in any product in its price range - and the potential return on investment that a tool like this provides is amazing. Also, 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 a pre-existing 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 within 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. If the combination of letters typed match several auto-completion options then CTRL and the cursor keys scrolls through the options before hitting SHIFT-SPACE. For example, typing just "cr" matches 11 possibilities - mostly templates for creating various types of database objects. 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. Naturally, once you have syntax highlighting configured to your liking its simple to transfer the rules between machines and share it with your colleagues.

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. This debugger is excellently featured and rated higher than any other product in the OraFaq comparison chart (see link in Competitive Products, or provide direct link here). In particular, its ability to inspect and modify complex datatypes coupled with some nice useability features like automatic variable identification makes it a particularly efficient debugger to work with.

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 as displayed to XLS, HTML, XML or TXT, or export the raw result set. The results tab also supports LOB's and complex datatypes, with inline viewing of common image types, nested tables, etc and the ability to transfer any LOB to/from 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 should be the explain plan by which all others are measured - it is simply wonderful. Firstly it presents a typical tree view of the operations performed, although the tree is augmented with symbolic icons and each entry in the tree is accurately numbered to indicate the order in which the steps will be performed. Below the tree is a list of natural language explanations - linked back to the items in the tree so that selecting an item in either the tree or the list highlights its partner in the other display. These natural language steps make the explain plan read like a story, which really helps new users interpret what is typically a somewhat obscure tree of steps.

Two additional panes provide even more information. One pane gives details of the currently selected step such as cost, cardinality, options and a good general description of the type of operation. The fourth, and final, pane is a describe tool that dynamically updates to show details of the object associated with the selected operation.

Perhaps this sounds a little complex but when you see it in action it all makes perfect sense. Everything is clearly laid out and seamlessly integrated and the result is an explain plan tool that can be used efficiently by novices and tuning guru's alike. Indeed, it is quite probably the best explain plan tool for teaching people about the inner workings of the Cost Based Optimizer around.

SQL Comparison Tool
The SQL Comparison tool is a really useful and novel tool. The easiest way to access it is to open a new Statement Comparison window (either via File -> New or from the icon on the toolbar) and then drag statements to it from virtually anywhere within the application. The comparison tool can then execute each selected statement a configurable number of times before displaying the results.

The results are presented as a range of graphs representing various statistics. The default graphs shown are: Elapsed Time, Session Logical Reads, CPU Used by Session and Physical Reads. Eleven other metrics 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 that deserve attention. For example, a long running script with multiple statements can be processed by the comparison tool and it will identify which individual components of the script are time / resource hungry - allowing the user to quickly locate and address the problem.

Auto-Tune Query / Check Query Joins
The automatic query tuning functionality is new in Version 3 and, from speaking to the developers, is only the first iteration of an intelligent tuning agent with more features being added in the immediate future. Having said that, it's obvious from the description below that this wizard already performs a wide array of tuning actions and effectively automates the knowledge of professionals with years of experience in Oracle performance tuning. Here is a summary of what it currently does:

Firstly it checks for missing query joins (a feature that can be called individually if required). This looks for referential integrity between tables included in the query and provides suggested additions to the WHERE clause automatically - it has a nice interface and is aware of concepts like table aliases which makes it very quick and friendly to use. Even if you have manually defined some of the joins, the tool is smart enough to identify just the missing items.

After the joins have been validated the auto-tune functionality then performs a quick inspection on each involved object to ensure statistics exist. If statistics are not found then it provides the option to immediately analyze the objects, presenting the user with typical options such as compute and estimate. Next, the wizard looks for redundant tables in the query. If found it tests the query both with and without the redundant table to determine if performance is negatively or positively impacted (sometimes an index on a redundant table makes its inclusion worthwhile). If several redundant tables are found it iteratively works through all permutations. Next a similar test is performed for rewriting UNION and UNION ALL clauses.

Not content to stop here, the wizard next looks at the structure of the query and provides suggestions regarding potential indexes. It achieves this by creating virtual indexes that allow the auto-tune wizard to estimate the new cost. Conveniently, it also provides the syntax to create the suggested index if desired. Obviously, creating the index automatically isn't desirable - indexes have a negative impact of DML performance so you need to understand things like the DML versus SELECT query relationship of the given table and factor in the overall impact on performance. This tool, however, makes that decision process easier and certainly saves a look of time manually inspecting complex WHERE clauses and comparing them to existing index definitions.

From this point onwards the automatic tuning agent tries variations of the supplied query trying options such as index hints and ordered clauses. Finally, it executes each variant a set number of times and averages the results - presenting the findings graphically in a version of the SQL Comparison tool. Importantly the tool also reports on any changes in the query results such as ordering that may be important. By default the window only shows queries that perform faster than the original, so don't be surprised if nothing appears sometimes, although this is configurable via the product options.

Obviously this tool is wonderful for anyone tasked with performance tuning Oracle. Even if you know all of these individual tricks the simple fact that it automates them and then presents the finding in a graphical format is a wonderful time saving device. The news just gets better though... According to Isidian Technologies the auto-tune wizard currently only implements about 30% of its planned future functionality. Yes, that's right, all of the steps I have described above are simply the beginning of what this tool will be capable of shortly. The people at Isidian are experts at tuning Oracle and with each upcoming release this intelligent agent will share more of their experience with you.

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. As a tip - enable the Preview Pane by clicking the icon to the left of the title, it makes the history list much more efficient to use.

The Pin List serves a slightly different purpose. Statements must be nominated for the pin list, and can have a descriptive name assigned to them. The naming can be quite useful, especially for retaining commonly used queries where searching the history would be difficult. Also useful is the ability to Save and Load pin lists - making it easy to share common queries amongst a group of users.

SQL Script Viewer
The SQL Script viewer tool allows you to select any 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 using the graphical comparison tool.

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.

SGA Browser
The SGA browser is another tool that can be essential when you have limited information about an application that requires tuning. By inspecting the SGA all sorts of problems can be identified, such as a lack of bind variables, poor caching on the application side and inefficient queries from black-box applications. 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 quickly 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 a percentage 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 are incorporated into the tool. These include templates for creating: Database Links, 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 allows users to build 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).

• Companies that are responsive to feedback always encourage me. Oracle is a constantly moving target and it's impossible to test every scenario, so it's comforting to know that if you raise an issue it will be looked at.
• The program automatically "calls home" upon start-up to determine when an upgrade is available. Therefore, not only was I informed of the opportunity to receive the fixes but so was every other SQL Insight user as well.

Currently SQL Insight 3.0 Standard edition retails for $US395 ($US49 upgrade from v1.x or v2.x), whilst the Professional edition is $US595 ($US100 - $US200 upgrade depending on already licensed product). Also, the Professional edition is available as an academic licence for $US89. Bulk licensing and/or training is available and can be discussed by sending email to sales@isidian.com. One year of premium support (including all upgrades) is included with the product and continuing support arrangements are available.

The product can be purchased online at the SQL Insight website: http://www.isidian.com. Trial versions of the product can also be downloaded from the website - the trial edition is fully functional and works for 30 days, with a further 30 day extension option. Finally, the website contains considerable information about the specific features present within the product and shows many screenshots of the features in use. Go and download the trial now - this product is definitely worthy of closer inspection.