Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 
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.

Topics

  • 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 July 2005
    Author:
    Mark Richard

    SQL Insight Pro CD Box


    Rating


    Summary

    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, whilst 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. The really brilliant feature introduced in Version 3 of SQL Insight, though, is the automatic tuning wizard. This intelligent agent can take any query, investigate a whole range of performance tuning options and graphically present its findings - tuning that could easily take an hour or more per query can now be done in minutes.

    Additional functionality in the product means 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 - download a trial version and I am sure you'll understand how good life can be.

    Since reviewing version 2 a little over a year ago the product has made significant enhancements and now earns the highest possible of rating of 5 stars for an Oracle Development tool.

    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 3.0 of SQL Insight - a little over one year since version 2.0 was reviewed.

  • 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 incredibly quick and easy. Around two years ago I reviewed version 2.0 of this product and the download was 5MB and at that time I commented that I had no idea how Isidian Technologies squeezed so much functionality into such a lightweight download. With version 3.0 the download is down under 3MB and the functionality has increased significantly!

    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.

  • 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. The products newest feature is an extremely well thought out automatic query tuning wizard - it makes performance tuning so easy and efficient and could easily become the products most popular feature. In addition to this there is an extremely powerful and flexible editor, which deserves special attention in its own right. This editor is definitely first class in terms of features and beautifully implemented.

    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.

  • Back to top of file

  • Problems Solved

    SQL Insight addresses the problems of rapidly generating SQL and PL/SQL code that complies with standards, is formatted well, and performs efficiently in terms of execution time and resource utilization. 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.

    The combination of advanced performance tuning features, excellent editing and very powerful debugging utilities make this product an incredible tool for Oracle developers. Of course that's not to say DBA's won't be impressed by what this product offers either though!

  • 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. Not to be overlooked is the development speed increases that these features provide.

    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.

    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 the products listed below, but what it does cover is exceptionally well implemented. Also, be sure to have a look through the OraFaq comparison chart (provide link) - SQL Insight rates very strongly at second place, which is even more surprising given that some of it's strongest features (automatic tuning) aren't even considered by the chart.


    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 particular focus on code generation and performance tuning. The three strongest points of this product are definitely the SQL & PL/SQL Editor, the Explain Plan tool and the Auto-Tune Query wizard. At the same time, however, it provides an array of basic DBA related functionality, making the product a well-rounded development environment. The following sections describe the key components of SQL Insight and give an indication as to how the tool can help to "get the job done".

    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 Insight's Explain Plan window

    SQL Insight's Explain Plan window

    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.

    SQL Insight's SQL Comparison Tool

    SQL Insight's SQL Comparison Tool

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

  • Back to top of file

  • Shortcomings

    When starting this review I noticed a few minor annoyances in the application, such as no ability to define bookmarks except for using keyboard shortcuts and inconsistent behaviour when statements had a trailing semi-colon. I provided this feedback to Isidian Technologies and within a couple of days they had updated the program to address these issues. This was impressive for two reasons:
    • 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.

    Of course, no product is perfect. SQL Insight has a strong development focus and is a little lacking in terms of DBA functionality. Obviously whether this is considered a shortcoming will depend on each users specific needs of the product. Also, there are probably some places in the application where a few more icons would be useful - some functionality (such as statement autotrace) is only available via keyboard short-cut or context-sensitive menus. My recommendation is to spend some time looking at the context-sensitive menus to learn what features lurk within the application - there is often more functionality than initially meets the eye.

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

  • Back to top of file

  • About the Author

    Mark Richard has been working with Oracle databases for over 6 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_andrew_richard@hotmail.com. 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


    HOME | ASK QUESTION | ADD FAQ | SEARCH | E-MAIL US