|Index Search Add FAQ Ask Question|
Index > Other 3rd Party Tools > Software Tools > SQL Insight
SQL Insight by Isidian Technologies, Inc.
Topics12 May 2003
Author: 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.
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.
HistorySQL 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.
Supported Operating Systems and VersionsSQL 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.
InstallationInstalling 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.
What it doesSQL 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.
Problems SolvedSQL 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.
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.
Competitive productsSQL 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.
Detailed reviewSQL 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.
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 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.
PL/SQL Debugger / Profiler
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
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 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 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.
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)
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.
ShortcomingsSQL 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.
Cost and where to buySQL 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 firstname.lastname@example.org. 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.
About the AuthorMark 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 email@example.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