Published on 22-Dec-2010
Validated on 07 Oct 2013
"Before, it would take us hours or days to identify problem queries.…Now, we can run the queries using the advisors and, in most cases, have a solution within minutes." - Lloyd Matthews, Principal Software Specialist and Database Administrator, US Senate
United States Senate
Integrated Data Management
US Senate database administrators and software specialists reduce query response time from 20 to 30 seconds to less than two seconds when they implement IBM InfoSphere Optim Query Workload Tuner for database tuning.
US Senate software specialists needed to identify, analyze and resolve issues with dynamic SQL queries quickly and cost-effectively to maintain high performance levels for the Senate’s financial management system.
IBM InfoSphere Optim Query Workload Tuner provides the expert query tuning advice needed to optimize database query design and proactively resolve problems before application performance is affected.
Significantly reduced time required to analyze queries; reduced query response time from 20 to 30 seconds to less than two seconds; decreased the time to identify problem queries from days to minutes
For United States Senate software specialists and database administrators (DBAs), making sure that senators and their staff can access the organization’s financial management system is of paramount importance. Service level agreements (SLAs) require that the system provide users with responses to online queries in three seconds or less.
“We must provide a cost-effective system that meets our performance goals and SLAs, while delivering the integrity and security that our users have come to expect and demand,” explains Lloyd Matthews, a principal software specialist and database administrator with the US Senate.
Matthew’s team had been able to efficiently monitor and tune static SQL queries in both online and batch processes using a third-party tool. However, as the financial management system was transitioned from a static Cobol/CICS® batch environment to a dynamic web-based platform, the existing tool no longer supported their needs.
“Tuning dynamic SQL queries required much more time and effort,” says Matthews. “Because we need to run our financial management information system on both mainframe and distributed platforms, we needed a tool that could support multiple platforms, while reducing the learning curve for DBAs.”
Proactively identifying and resolving issues
The organization’s DBAs and software developers evaluated tools from a number of vendors including IBM, Computer Associates and BMC. According to Matthews, as they learned about IBM® InfoSphere™ Optim™ Query Workload Tuner software, they knew that they had found the right solution.
“We developed a list of success criteria that we presented to our IBM technical sales specialist,” says Matthews. “He recommended IBM InfoSphere Optim Query Workload Tuner for proof of testing and I’m happy to say that the solution exceeded our expectations. InfoSphere Optim Query Workload Tuner made it possible for us to capture both dynamic and static SQL queries and provided the analysis we so desperately needed. The software’s Eclipse-based user interface is able to work in multiple platforms. This was a big selling point for us.”
InfoSphere Optim Query Workload Tuner enables the team’s DBAs, software developers and quality assurance staff to tune both a single query, as well as sample workloads throughout the day, so they can proactively identify and resolve emerging issues before application performance is affected. Query Advisor and Index Advisor reports provide insight for improving database query performance. DBAs can compare access paths side by side, view relevant statistics and conduct “what if” analysis to immediately determine if a change in query design will improve application performance. Additionally, the solution’s profile monitoring capabilities make it easy to identify and analyze queries based on a specific set criteria and monitor poorly performing queries in real time.
Says Matthews, “With its rich set of advisors and reports and the capabilities for monitoring and tuning, it has proven extremely valuable for our most challenging queries. When we run the Statistics Advisor, we can apply the statistics to our objects and then re-analyze the query or workload. As a result, we’ve immediately seen a tremendous improvement in CPU requirements, input/output and elapsed time, as well as improved access paths.”
Reducing query response time by 95 percent
According to Matthews, application users have noticed the team’s ability to identify, respond and resolve poorly performing queries in a timely fashion.
“Our users are impressed and have benefitted tremendously,” says Matthews. “Before, it would take us hours or days to identify problem queries. IBM InfoSphere Optim Query Workload Tuner has significantly reduced our analytic time. Now, we can run the queries using the advisors and, in most cases, have a solution within minutes. And when I compare the CPU time from before and after, the delta between the two is amazing. In many cases, by running the Statistics Advisor, we’ve been able to reduce queries taking 20 to 30 seconds to only one to two seconds.”
For more information
To learn more about IBM InfoSphere Optim software, please contact your IBM sales representative or IBM Business Partner, or visit the following website: ibm.com/software/data/optim
To increase the business value of Information Management software, participate in independently run Information Management User Groups around the world. Learn about opportunities near you at: ibm.com/software/data/management/community.html
For more information on the United States Senate, visit: www.senate.gov
Products and services used
IBM products and services that were used in this case study.
© Copyright IBM Corporation 2010 Software Group Route 100 Somers, NY 10589 U.S.A. Produced in the United States of America December 2010 All Rights Reserved IBM, the IBM logo, ibm.com, InfoSphere and Optim are trademarks of International Business Machines Corporation in the United States, other countries or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the web at “Copyright and trademark information” at ibm.com/legal/copytrade.shtml Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both. Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries. Other company, product and service names may be trademarks or service marks of others. References in this publication to IBM products or services do not imply that IBM intends to make them available in all countries in which IBM operates. IMC14618-USEN-00