Platinum Plan Analyzer is a tool that claims to let Oracle developers quickly optimize their SQL queries, ensuring that they deliver the fastest possible SQL syntax for their programs. A lofty claim for a tool that sells for less than $2000 per seat.
A little background for the Oracle neophyte: Oracle's SQL can use two optimizers: the rule-based optimizer, which uses heuristics (rules-of-thumb) to choose a path for the query, and the cost-based optimizer, which chooses paths based on the characterist ics of the data. In the Oracle world, a query path can be changed to override these suggestions. The override must be done with "hints," which are placed within each individual SQL statement. It is a very tedious and time-consuming process to evaluate an d tune each and every SQL statement. Consequently, there is a real need for a tool that enables this manual optimization to happen quickly. In fact, Platinum Plan Analyzer even lets you compare the different plans in a condensed and easily understood man ner.
Platinum Plan Analyzer is one of the few tools in the Oracle marketplace that lives up to its claims. In addition to quickly showing the developer the SQL plans, Platinum Plan Analyzer goes one step further and describes, in plain English, why a path was chosen. An especially nice feature called "Visualize" lets developers step through the execution plan for a SQL statement, showing an explanation of each step in English.
As Platinum Plan Analyzer explodes the plan for a query, developers can get a complete listing of objects accessed and a list of alternative access paths. Better yet, Platinum Plan Analyzer has a table analysis screen that shows all of the detailed stati stics from the Oracle analyze table command, color-coded to highlight optimal and alarm conditions. This feature alone is worth the price of the product. For example, you can use Platinum Plan Analyzer to identify indexes whose structures have become ine fficient because of heavy update activity. (See Figure 1.) When it comes to picking the best plan, Platinum Plan Analyzer excels. It provides a facility to test the execution of each SQL statement for the different optimization modes, offering more resource consumption details than anything provided by Oracle. Figure 2 illustrates the Server Statistics dialog used for the testing.
The Server Statistics dialog gives you every option Oracle has for the execution of a SQL statement. For those of you building client/server applications, the options to reduce the network messaging will be of the most interest. Drill-down dialogs are av ailable with advanced information, even including wasted server memory if the SQL isn't shared properly. Every option that Oracle provides for the execution of SQL is available in Platinum Plan Analyzer.
Platinum Plan Analyzer is one of the easiest tools to install and operate that I have encountered in many years. The front end is so simple and intuitive that I was able to install the software, populate Oracle with its views, and establish a connection within 15 minutes, without even opening the manual. In fact, I did not need to refer to the documentation until I was well into the testing of a SQL statement.
The principal author of Platinum Plan Analyzer, Ed Kosciuszko, is an acknowledged Oracle SQL guru and was one of the first 30 employees when he joined Oracle in 1981. It is clear from the initial install that Platinum Plan Analyzer was written by someone with superior knowledge of Oracle. For example, Platinum Plan Analyzer requires some internal views on the server-side, and the install prompts the user through the process quickly and without confusing or long-winded explanations. I was immediately imp ressed by the intuitive design of the product. It was easy for me to capture some SQL and begin analyzing the SQL statement, comparing the rule-based with the cost-based optimizer.
One major problem with evaluating Oracle SQL happens when you evaluate SQL in a mixed, cost-based and rule-based environment. One of Oracle's features is that it uses the presence or absence of table statistics to determine which optimizer to invoke. A t uned SQL query may behave differently if the statistics are dropped or recalculated.
Still, in a world of $15,000-per-seat Oracle tools, Platinum Plan Analyzer is a huge bargain at $1995 per seat, especially when you consider its power. I highly recommend this product to developers who must optimize their Oracle SQL and to DBAs who want a tool to see the table and index statistics quickly. In a world of vaporware, it is refreshing to see a product that is priced fairly and lives up to its claims.
