REVIEWER: Wendy Penfold, programmer/analyst for Ithaca College.

BACKGROUND: Founded in 1892, Ithaca College is a private, coeducational college with more than 6,000 undergraduate and graduate students. The college offers more than 100programs of study in business, communications, health sciences and human performance, humanities and sciences, music and interdisciplinary studies.

PLATFORM: Oracle 10g.

PROBLEM SOLVED: Our IT department provides programming and technical support for a large variety of packaged and custom software. We have used Quest’s Toad for Oracle Professional for a long time, and it is a cornerstone of our development environment. However, the number of high performance applications our department must support is increasing, and those applications often must support a very high volume of activity for brief periods. We needed a tool to help tune those applications for maximum performance. After struggling with a more manual tuning method, we learned about Quest SQL Optimizer for Oracle, which is included in the Xpert edition of Toad for Oracle. We upgraded from the Professional edition of Toad to the Xpert edition, and have found that Quest SQL Optimizer for Oracle has greatly reduced the time it takes to tune SQL statements.

PRODUCT FUNCTIONALITY: We use Toad Xpert for Oracle to create, debug, tune and test PL/SQL code and applications and to simplify database maintenance and troubleshooting. It’s important that we have a tool to help us quickly tune applications for maximum performance, especially when students register for courses or select housing over the Web. The competition for space in classes and dorm rooms is intense, and those applications have to perform at peak levels to support the very high volume of activity during registration periods. SQL Optimizer for Oracle reduced the time for tuning SQL statements from hours or days to minutes. We had complex queries executed across database links that were taking an unacceptable 10 minutes, and with SQL Optimizer we were able to reduce it to .09 seconds.

STRENGTHS: It’s unlikely we could have found the fastest SQL statement tuning alternative using a manual process, and, with SQL Optimizer for Oracle, we actually can tune more frequently and do other work while the optimizer is running. With SQL Optimizer, application performance improvement can be verified on a consistent basis, and we have actually learned to write better SQL by looking at the changes made by SQL optimizer. Plus, because SQL Optimizer is part of Toad for Oracle Xpert, we also get all of the benefits provided by the Toad tool itself. Toad allows us to connect to multiple databases and move data and objects easily between them, making us much more productive. Toad is a huge help with troubleshooting. Its table grids and filter capabilities quickly find and fix errors, and its PL/SQL debugger allows you to step through code, change variable values and quickly identify and correct problems.

WEAKNESSES: We have experienced only some minor glitches. When switching tabs, the selected alternative does not always stay selected, and the very nice optimization report mistakenly shows the text of the alternate SQL as being the same as the original SQL. Also, it’s a little difficult to figure out what to do the first time you go in to optimize a SQL statement – a simple “getting started” wizard would be nice.

SELECTION CRITERIA: We chose the Toad for Oracle Xpert edition because it would allow us to significantly reduce the amount of time needed to tune high-demand SQL statements for maximum performance and to improve efficiency by streamlining database-related tasks into a single tool. It also allowed us to continue reaping the benefits of Toad for Oracle Professional, which we had used for a long time and were very satisfied with. Our IT staff was already well-versed in Toad and didn’t need a lot of additional training.

DELIVERABLES: Toad for Oracle Xpert delivers improved SQL query performance and IT efficiency. It also provides a simpler, more efficient means of database troubleshooting.

VENDOR SUPPORT: Working with the staff at Quest has been wonderful. Any issues with the product have been promptly addressed. Developers and support staff are knowledgeable, helpful, polite and open to new ideas.

DOCUMENTATION: Online documentation is easy to navigate and search. We can usually find the answers to any of our questions there. Toad World, Quest’s online community for Toad solutions, is also a great resource for additional education, expertise and collaboration.

Toad for Oracle Xpert
Quest Software
5 Polaris Way
Aliso Viejo, CA 92656
(949) 754-8000

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access