Using SQLab Xpert Tuning

Ever wished you could push a button and make all your SQL tuning performance issues go away? Well, you can with TOAD's SQLab Xpert Tuning add-on. SQLab Xpert is by far the world's greatest SQL Tuning tool ever made. SQLab Xpert truly provides a world-class SQL tuning workbench with both automatic tuning and database structural advice, elevating any DBA or SQL developer to the status of SQL tuning guru. Moreover, the steps are both straightforward and simple:

1. In TOAD, you identify SQL code that either runs too slowly or has a suspect explain plan (for example, too many nested loops ) as shown in Figure 10.7. Then you merely click the SQLab Xpert Tuning icon on the toolbar to tune the SQL.

   Figure 10.7. TOAD SQL Editorinefficient SQL.

   

2. SQL Xpert displays an effective and efficient SQL tuning workbench with your problematic TOAD SQL copied over as shown in Figure 10.8. You can review the SQL execution plan either via the explain plan tree (which includes the SQL from the WHERE clause attached to each plan step) or the English textual version displayed beneath the tree. You can also review very detailed describe information displayed on the right-hand side for tables, columns , indexes, and statistics.

   Figure 10.8. SQLab XpertTuning Workbench.

   

3. You can click the Advise button to obtain SQLab Xpert advice as shown in Figure 10.8. Recommendations can include use of hints, creating indexes, dropping indexes, collecting statistics, dropping statistics, and numerous other schema changes as shown in Figure 10.9. Highlighting any recommended advice topic also displays detailed documentation regarding it. Although all the advice is generally educational and useful, you may want to skip directly to the next step if all you want is to tune the SQL.

   Figure 10.9. SQLab XpertTuning Advice.

   

4. You can click the AutoTune button to have SQL Xpert generate all possible SQL rewrites as shown in Figure 10.10. Note how SQL Xpert knows to generate only rewrites that produce unique explain plans. You may then manually select the ones to keep by using the Select button, although generally it's advisable to use all proposed solutions. You also can specify control parameters for benchmarking the proposed solutions via the Preferences button. You then click the Execute button and wait for SQLab Xpert to benchmark each proposed rewrite (note that this can take a while if the tables are large or many rewrites are proposed).

   Figure 10.10. SQLab XpertAutoTune SQL generation.

   

5. Now review the benchmark results and pick your winner as shown in Figure 10.11. In the sample scenario, rewrite #4 is better than the original SQL. Although both ran in about the same time, rewrite #4 performed approximately 30% fewer logical I/Os. Moreover, it's not at all uncommon for SQLab Xpert to find multiple rewrites that beat the original. And remember that finding a superior SQL rewrite such as in the example takes only three mouse clicks (launch SQLab Xpert, start AutoTune, and Execute the rewrites). SQL tuning has never been so easy.

   Figure 10.11. SQLab XpertAutoTune test results.

   

6. You can review the winning SQL rewrite by merely clicking its tab as shown in Figure 10.12. If you like what you see, just select, copy, and paste it back into TOAD. Your inefficient SQL has now been tuned .

   Figure 10.12. SQLab Xpertthe best SQL rewrite.