Published on 29-Oct-2010
Validated on 20 Nov 2013
"With IBM Optim Query Workload Tuner, we were able to reduce mainframe CPU consumption by 38 percent for a highly used transaction processing (TP) program, resulting in significant cost savings." - Roberta Barnabè, DB2 Specialist, UGF Assicurazioni
Integrated Data Management
IBM® Optim™ Query Workload Tuner helps reduce costs by automatically targeting problem application queries and recommending ways to address performance issues. UGF Assicurazioni makes extended use of the Workload and the Query Environment Capture functions; the Query, Statistics and Index Advisors; the Dynamic Statements Trace; the Access Plan Graph; Query Annotations; and What-if Analysis.
To improve service levels, UGF Assicurazioni wanted to develop best practices for optimizing database queries and workloads. Designing efficient queries and workloads helps deliver high-performance applications, while reducing costs.
IBM Optim Query Workload Tuner identifies problematic database queries/workloads and recommends alternatives to improve application performance and reduce costs.
Provided significant time and cost savings; reduced mainframe CPU consumption by 38 percent for a highly used transaction processing (TP) program; delivered a time savings of 400 processing minutes per hour by reducing the average elapsed time for a single dynamic program.
UGF Assicurazioni S.p.A., one of the most prominent insurance companies in the Italian market, comprises over 1,650 agencies and 2,300 operators. As a subsidiary of the UNIPOL Gruppo Finanziario (UGF) S.p.A., UGF Assicurazioni offers health, accident, corporate, motor and household insurance, as well as pension funds.
Application performance remains a high priority. The software applications at UGF Assicurazioni support daily operations, bring new insurance offerings to market and provide a convenient service channel for clients. Developers and DBAs work together to deploy technology that improves performance and reduces costs.
The mainframe environment comprises IBM® z/OS® 1.9, with six subsystems in IBM DB2® 8 New Function Mode (NFM). There are 50 subsystems in IBM CICS®/TS 3.1 that process eight million transactions daily. There are also eight instances of IBM WebSphere® Application Server 6.1 on z/OS, processing 4.5 million threads daily. Applications are written in COBOL/CICS (static and dynamic), Assembler, SQLJ, JDBC, as well as DELPHY and .NET on workstations.
As a DB2 specialist at UGF Assicurazioni, Ms. Roberta Barnabè’s responsibilities include CICS, DL1, DB2 architecture, database administration and tuning. The company has implemented best practices for database tuning and selected the IBM Optim™ Query Workload Tuner to automate analysis and save time.
Best practices for application tuning
As a UGF Assicurazioni best practice, database tuning is performed whenever developers promote new database objects (tables or indexes) in the quality assurance environment, when online or batch performance monitoring indicates higher costs, and when applications are upgraded or have extensive modifications.
“We started using IBM Optim Query Workload Tuner because it helps us quickly direct our efforts for improving performance,” Ms. Barnabè explains. “First, it provides query detection. It is not easy to design queries in an easy readable way. Sometimes the complexity of dynamic SQL queries can impact performance. Secondly, the Optim Query Advisors recommend ways to improve performance.”
The Optim Query Workload Tuner has an Eclipse-based user interface and can collect SQL from various sources (database catalog, statement cache or captured file). Users can analyze queries, workloads and associated “costs” using visual displays and automatically generated annotations. Developers can view DB2 Optimizer Query Transformations and analyze a single query or an entire workload, including access paths, statistics and indexing. What-if analysis helps compare results.
“Our DBAs have realized the value of the Optim Query Workload Tuner for SQL tuning, collecting statistics and designing indexes,” says Ms. Barnabè. “We make extended use of the Workload and Query Environment Capture functions, as well as the Query, Statistics and Index Advisors, the Dynamic Statements Trace, the Access Plan Graph, Query Annotations and What-if Analysis.”
Query workload tuning for new database objects
When promoting new database objects into the quality assurance environment, tuning all related queries helps confirm that new indexes are well structured. Tuning a new database object can be simple or complex depending on the number of programs involved, and whether any new programs work with existing programs and objects.
Ms. Barnabè and her team use the Workload function to optimize overall performance. First, they identify all the programs involved. Next, they create a workload, filtered by program or collection name. It is easy to restrict analysis by creating a new filter to target all the SQL statements related to a specific table. Then, they run all advisors on these statements.
“The Statistics Advisor recommends the statistics to collect that will influence the access paths,” says Ms. Barnabè. “The Query Advisor recommends how to rewrite the query, and the Index Advisor suggests new indexes and uses the DB2 Virtual Index feature. Automation allows us to focus on other tasks, and we can easily analyze the results and recommendations.”
Periodic query tuning for optimal performance
For monitoring application performance, Ms. Barnabè and her team receive batch scheduling alerts and reports that detail online processing costs. Daily reports indicate dramatic changes in CPU consumption for specific programs. Weekly and monthly reports indicate general trends in consumption or indicate when processes extend beyond a dedicated nightly batch window.
For example, a transaction processing (TP) program may show increased CPU consumption over a specific elapsed time or flag high usage. For programs that are accessed nearly 50,000 times a day, a small deviation can generate huge performance issues in a production environment.
“When creating indexes, it is necessary to balance query performance versus cost,” Ms. Barnabè explains. “In one case, the Index Advisor suggested creating three new indexes but, as you know, every new index has a CPU cost for its maintenance, so I decided to remove two of the three indexes and rerun the What-If Analysis to see what would happen.”
She adds, “A single display lets you review results for two access paths side by side. Since the program in question was called 50,000 times a day, we calculated a CPU savings of 25 minutes a day and elapsed time savings of 3 hours per day, which was significant. We decided to create only one index to take advantage of the cost savings.”
Query tuning following application upgrades and enhancements
Following an application upgrade or extensive application modifications Ms. Barnabè and her team perform database tuning based on stress test results. Often extensive changes can alter application performance.
UGF Assicurazioni’s call center application, SERTEL, manages 250,000 calls from customers and agencies each month. This application is written in dynamic SQL. The goal was to evaluate performance by changing literals with a parameter marker. This action should improve performance and lower development costs because less time is needed to prepare dynamic SQL statements using the dynamic cache feature. However, if the data is skewed, this might degrade performance by creating faulty access paths.
“We had to find out potential threats to our system,” says Ms. Barnabè. “We used the Query Tuner Statement Cache Capture feature to identify the dynamic queries related to our application and determine which queries had the highest costs after the literal-to-parameter marker change. Next, the Query and Statistics Advisors helped us analyze which objects (tables and columns) were most interesting for our queries. After we reviewed and implemented the recommendations, the performance results for the SERTEL application were astonishing.”
On the first program, the average CPU time was reduced from 0.0013 to 0.000931 with a savings of 7 minutes per hour (as this program is called nearly 1.2 million times during peak hours), and the average elapsed time was reduced from 0.03 to 0.01 to achieve a time savings of 400 processing minutes per hour. On the second program, the average CPU time was reduced from 0.0014 to 0.0007 for a savings of 9 minutes per hour, and the average elapsed time went from 0.03 to 0.01 for a savings of 133 processing minutes per hour.
“These results are incredible,” says Ms. Barnabè. “In these examples, I actually calculated the results three times because I thought there was an error. But, the results are true and real. Using Optim Query Workload Tuner to perform the literal-to-parameter marker change allowed us to reduce mainframe CPU consumption by 38 percent for a highly used transaction processing (TP) program, resulting in significant time and cost savings.
For more information
To learn more about IBM 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 UGF Assicurazioni S.p.A, visit: http://www.ugfassicurazioni.it
Additionally, financing solutions from IBM Global Financing can enable effective cash management, protection from technology obsolescence, improved total cost of ownership and return on investment. Also, our Global Asset Recovery Services help address environmental concerns with new, more energy-efficient solutions. For more information on IBM Global Financing, visit: ibm.com/financing
Products and services used
© Copyright IBM Corporation 2010 IBM Corporation Software Group Route 100 Somers, NY 10589 U.S.A. Produced in the United States of America October 2010 All Rights Reserved IBM, the IBM logo, ibm.com 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 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. IMC14599-USEN-00