|
Ask any database administrator what his top concern is for the databases he controls and heıs likely to answer "ensuring excellent performance." As proof of this, you just need to witness the standing-room-only crowds at database seminars on the topic of increasing performance or to try and get the latest tuning book for your RDBMS at the local bookstore and see if it isnıt out of stock. The funny thing is that even though a DBA can make decent performance gains appear by turning the right knobs on a database, the best increases in response time that can be expected by such actions (according to industry experts) is in the neighborhood of 30 to 40 percent. Most knowledgeable gurus will tell you that an RDBMSıs speed is directly related to how well the SQL is written by the developers. This is true across the board whether the system is an online transaction processing (OLTP) system or is analytical in nature (OLAP). I still remember a novice developer a few years back trying to write his first large query against an NCR Teradata database that housed a monstrous amount of information. Fortunately, he exercised the Teradata explain option before submitting the query that, in addition to showing the normal access path information, gives an estimate of the query time. Red faced, he told me that the time estimate for his query showed it would finish in the year 2045!
Because properly written SQL is so important to an applicationıs performance, you would think that the market would be flooded with tools to help developers with this critical task. Surprisingly, though, there are very few. This being the case, I was eager to look at a new tool from Sylvain Faust Inc. called SQL-Optimizer whose sole purpose in life is to identify, analyze, and correct poorly performing SQL. The advertisements on the box stated that the product would "improve database performanceı quicklyı easily!" It sounded good to me.
The current version of the tool I was able to review was 1.0.4, and my test run was performed on a Pentium 120MHz Windows NT 4.0 machine that was loaded with 64MB of RAM. The 32-bit product will also run on Windows 95 and its only other requirement is the 32-bit version of Oracle SQL*Net to allow for connectivity into Oracle databases. (Although the product states that it is "The Performance Optimizer for SQL Databases," the only RDBMS engine it currently supports is Oracle.) In the age of tools that consume endless amounts of space on a computerıs hard drive, it was nice to see that SQL-Optimizer only took up 2.5MB on my machine. Even though SQL-Optimizer is reported to include a couple different programs, the main executable I received was SQL-Optimizer/DBA. The other tool, SQL-Optimizer/Interceptor, which is supposed to catch and analyze SQL before it is sent to an Oracle database, was not included in my package.
The tool requires additional setup after installation in the form of creating an Oracle role that has SELECT privileges on several of the Oracle V$ views, which can then be granted to and used by Oracle logins to capture and monitor SQL statements as they are being issued to the database. Connecting to an Oracle database is easy and is done through native Oracle drivers, so no ODBC setup is needed. Users can connect to and monitor more than one Oracle instance if they would like. The product literature states that the tool supports only version 7 of Oracle databases, but just for fun, I tried to use SQL-Optimizer against Oracle8. I promptly got my hand smacked by the tool in the form of a Windows NT Dr. Watson error.
Once a connection to an Oracle7 database is established, SQL-Optimizer opens a number of windows that display current user connection information. (See Figure 1.)
An administrator can view all current threads that are operating on the RDBMS as well as drill down to information thatıs quite valuable in identifying inefficient SQL. Users of SQL-Optimizer can select sessions to see the query start and end times, the actual SQL that was issued to the database, and I/O statistics including a queryıs "hit ratio," which is a measure of how much data the query obtained from memory vs. physical I/O. A high hit ratio (normally defined as 80 percent or greater) is good, while lower hit ratios indicate table scan problems and/or inefficient SQL.
Although the information displayed by SQL-Optimizer is very useful, the GUI itself is difficult to view for long periods of time due to the constant refreshing the tool must perform to keep up with database activity. The toolıs visual presentation suffers from jerky and choppy behavior in the statistical windows displaying the connection data. Thinking it was just I who disliked this behavior, I gave SQL-Optimizer to a couple of peers for their opinions. One of them came back after a short period of time, threw the box back on my desk and said, "That thing gave me a serious headache in five minutes." To reduce the irritation of the connection updates, an administrator can use the freeze option, which will temporarily suspend any activity refreshes. Once this has been done, the GUI is much easier to use from a visual standpoint. An administrator can also "unfreeze" SQL-Optimizer, which will cause the tool to update the SQL activity windows with any changes that have occurred since the last freeze.
SQL-Optimizer offers much in the way of quickly analyzing currently running SQL. By selecting the correct options within the tool, an administrator can instantly see the actual SQL a user has submitted along with a formatted explain analysis. A number of other tools are capable of doing this (such as Platinumıs Plan Analyzer for Oracle), but SQL-Optimizer will interrogate the current SQL and, using a built-in SQL "Expert" engine, will determine if the SQL could be written differently to improve performance. Any suggestions the tool has are displayed to the user in the form of rewritten code and a new EXPLAIN analysis, which details the latest codeıs access paths.
Another nice component within the tool is the alerting feature. SQL-Optimizer allows a user to define exceptions to normal processing that the DBA should be made aware of. For example, I created an alert to notify me whenever a user sessionıs hit ratio fell below 80 percent. I then deliberately ran a query that involved many physical reads (which depresses the hit ratio) and was immediately notified by the tool that my alert threshold had been violated. The only thing I would change about this option is the generic information the tool presents regarding the alert. I would prefer to see specific Oracle data like the Oracle SID, username, and other data that would quickly pinpoint the session causing the problem. SQL-Optimizer will, however, highlight the offending session in a different color from the others that help to locate it within the tool.
The idea behind SQL-Optimizer is nice, but the tool failed to rewrite any SQL that was offered to it. I tried at first to see if the tool would flag some typical SQL statements that DBAs hate to see: Cartesian joins, statements not using indexes properly, spaghetti-coded subselects, and so on. The tool would only display "No better syntax" for any of the queries I fed it. I then connected SQL-Optimizer to a heavily used OLTP system that contains very complex sets of SQL. Once again, no matter what SQL I selected, the tool never presented any optimized SQL. When I selected extremely complex queries, SQL-Optimizer displayed a message saying "No optimized SQL available."
I encountered a number of other problems using the tool. Occasionally when selecting SQL to view, the explain window would give me "Plan Error 609" errors instead. I wasnıt exactly sure what was causing this problem because nearly all other SQL I attempted to explain was displayed properly.
Errors werenıt confined to capturing SQL either. There also seemed to be a problem in the tool with capturing SQL that executed quickly. Whenever I fired off long-running SQL from Oracleıs SQL*Plus tool, SQL-Optimizer seemed to pick it up fine. However, when I executed short index lookups or other very fast running SQL, the tool would miss the SQL entirely. I did this numerous times from within SQL*Plus and other query tools and the results stayed constant. According to contacts at Sylvain Faust, this was due to the sampling interval granularity; currently the lowest setting is one second and this causes fast running SQL to be missed occasionally.
There were other things that I think would make SQL-Optimizer easier to use. For example, the ability to narrow down the sessions it monitors by Oracle SID or user would be nice. Being able to clear the data from the monitoring windows would also help if you get flooded with statements you donıt care to review.
In the end, Iım afraid that although SQL-Optimizer makes a good start of capturing and analyzing SQL from a running database, itıs still got some kinks to work out before it can be used "as advertised." The version I received was the 1.0 release (actually 1.0.4), so like most other initial offerings, Iım sure it will show improvement with time.
SQL-Optimizer employs many windows to relate statistical information regarding SQL back to an administrator.