Index   Search   Add FAQ   Ask Question  

Support the Oracle FAQ by supporting our sponsors. Click here!

Index > Other 3rd Party Tools > Software Tools > SQL Insight

SQL Insight by Isidian Technologies, Inc.


  • Summary
  • History
  • Supported OS Versions
  • Installation
  • What it does
  • Problems Solved
  • Who should use it
  • Competitive products
  • Detailed review
  • Shortcomings
  • Cost and where to buy
  • 12 May 2003
    Mark Richard



    SQL Insight is a tool aimed at writing good code and performance tuning existing code. The editor is first class in terms of features designed to make life easier. Meanwhile, the explain plan is the best I have ever used and the ability to benchmark statements against each other is extremely useful, both for confirming tuning results and even for identifying which statements to tune in the first place.

    Additional functionality is being introduced as the product grows, meaning that users are less likely to need to leave the tool. Need to analyze a table quickly, check an init.ora parameter or even FTP something to the database server? No problem, it's all built in. I personally spend a lot of time performance tuning SQL and it didn't take long at all to realize the benefits that SQL Insight offers.

    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

  • Back to top of file

  • History

    SQL Insight is the flagship product of Isidian Technologies, Inc which was formed in 2000 as a consulting and training company for enterprise level Oracle database developers. A homegrown application was developed to initially provide explain plans - and this was used extensively to conduct performance tuning and teach the concepts to their clients.

    Early in 2001 the decision was made to continue development of the application and convert it into a commercial tool - SQL Insight was born. SQL Insight continues to be developed today and is rapidly growing into a complete integrated development environment suitable for all levels of database developers, beginner to advanced. This review is based on version 2.0 of SQL Insight, although future versions are planned and underway, including a point release in May 2003.

  • Back to top of file

  • Supported Operating Systems and Versions

    SQL Insight runs on all 32-bit Windows platforms including Windows9x/NT/2000/XP. Basic hardware requirements include 64MB of RAM and 30MB of disk space. Oracle Net8 and higher clients are supported but not necessary - SQL Insight can connect using TCP/IP directly. Oracle Database server versions 8 and higher are supported. A Linux version is also in the pipeline, however there is no release date currently set.

  • Back to top of file

  • Installation

    Installing SQL Insight is both quick and easy. A simple 5MB download from the SQL Insight website includes the full application, as well as both HTML and MS Word versions of the documentation. I have no idea how Isidian Technologies have squeezed so much functionality into such a lightweight download but I certainly won't complain. An installation wizard guides users through the installation process with a minimum of questions. There is no need to restart the computer following installation - a much-appreciated feature for those of us with a habit of having dozens of applications running all the time. There is no server side installation required to run SQL Insight.

  • Back to top of file

  • What it does

    SQL Insight was initially created to provide explain plans and it does this job better than any product I have used. The explain plan includes the familiar tree view (with steps numbered in execution order), a natural language explain plan, details for each step when selected, and a description window for the associated table when an appropriate step is selected. In addition to this they have added an extremely powerful and flexible editor which deserves special attention.

    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.

  • Back to top of file

  • Problems Solved

    SQL Insight addresses the problems of generating SQL and PL/SQL code that complies with standards, is formatted well, and performs well. It is also particularly useful for tuning applications about which little information is known. The range of options for understanding statement execution paths, and comparing statements enables users to approach the dilemma of tuning from a variety of angles.

  • Back to top of file

  • Who should use it?

    Firstly, anyone tasked with creating SQL or PL/SQL will find SQL Insight useful. The SQL editor is truly first class in terms of features and flexibility. The auto completion and statement formatting functions will particularly impress any corporation attempting to employ company wide standards in this area, and developers certainly complain a lot less if the tool performs this task automatically.

    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.

    Additionally, anyone who wanted to gain a better understanding about how Oracle actually executes a query will love the detailed explain plans, which even include a natural language explanation. Users can very quickly understand exactly what Oracle is doing, and why, by experimenting with a few statements.

  • Back to top of file

  • Competitive products

    SQL Insight is very strongly focused on two goals: SQL & PL/SQL code writing, and performance tuning. There are many other tools that play in these two markets however they rarely (if ever) have the depth of coverage provided by SQL Insight. SQL Insight doesn't cover all of the functionality provided by some of these products, but what it does cover is exceptionally well implemented.

    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)

  • Back to top of file

  • Detailed review

    SQL Insight started life as an in-house explain plan tool but has grown into a fully fledged IDE for SQL and PL/SQL development, with a strong focus on code generation and performance tuning. The following sections provide detailed information on the main areas of functionality found within the product.

    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).

  • Back to top of file

  • Shortcomings

    SQL Insight has only a couple of minor annoyances. I had a little trouble working out how to create bookmarks initially (press CRTL+SHIFT+n, where n is 1..9), and hit a bug when calling SQL*Plus which will be fixed in the next point release. I was also surprised to not see DDL wizards for Index and Referential Integrity creation, although RI can be created within the table creation wizard, and indexes can be created within the Describe tool. A final quirk relates to Explain Plans - currently statements with a trailing semi-colon can be executed but the explain plan button complains about the semi-colon. This is being fixed in the next release, but in the meantime just delete it manually.

    One other issue to keep in mind is that the advanced sorting and aggregation functions available in the query results obvious have the drawback of needing to load all rows into RAM on the client. I accidentally did this on a very large table and eventually crashed the application. To get around this you can either add the equivalent SQL to the statement (pushing the work to the database), work on smaller datasets, or export the results to an external file (which fetches and writes the file without needing to store the entire result set in RAM). Since the size of the result set is unknown it has no option except to attempt to load all rows - this isn't a bug, just something to be aware of.

  • Back to top of file

  • Cost and where to buy

    SQL Insight comes in two versions - Standard and Professional. The two versions are almost identical except that the Professional edition includes the PL/SQL profiling and debugging tools. Therefore the choice is largely based on how much time you spend working in PL/SQL.

    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 Finally, a support agreement is available for $US195 per annum.

    The product can be purchased online at the SQL Insight website: 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.

  • Back to top of file

  • About the Author

    Mark Richard has been working with Oracle databases for over 5 years. During this time he has worked primarily as a database developer specializing in performance tuning on both Datawarehouse and OLTP applications. He can be contacted at Mark has no connections with Isidian Technologies, Inc.

    Can you write reviews and articles like this one? Why not become an editor or reviewer for the Oracle FAQ? Find out how you can make money, get noticed and advance your career click here