Db2 AI-Assisted SQL Tuning in Practice

To review: IBM has a new offering that leverages ML for z/OS, machine learning software newly available on the latest zSeries hardware. The new product is Db2 on z/OS artificial intelligence, or Db2ZAI. This software works with the Db2 Optimizer by monitoring SQL execution and gathering data on SQL syntax, data access paths and resource usage. This information is then modeled and the results passed back to the Optimizer, which can then take advantage of actual historical access path costs in its attempt to choose the lowest-cost SQL access path.

What does usage of this product imply for current applications?

Implications for Critical Production Processes
For operational applications, the expectation is a modest reduction in CPU costs. This is because a large part of operational systems contain relatively simple SQL statements whose access paths have been analyzed by past and present DBAs, who have applied various performance tuning enhancements such as more table indexes, data partitioning schemes and some SQL syntax changes. Another point is that many of these SQL statements involve keyed access to a small number of table rows, such as WHERE Account_Number = 123456789, or WHERE Customer_Name LIKE ‘Smith%’ . Such statements have obvious, high-speed access paths, usually due to the existence of appropriate indexes.

Of course, some operational application SQL can benefit from artificial intelligence. For example, consider an SQL statement whose syntax specifies a set of rows (e.g. WHERE Product_Type = ‘A’); however, during execution the application only fetches a few rows from the entire result set, perhaps a number of rows that will fit on a user’s display. If the SQL statement as-is specifies hundreds or thousands of target rows but only twenty are retrieved for display, the Optimizer can use this information to (possibly) select an access path that is optimized for only twenty rows.

The net result is that a small but significant number of SQL statements may benefit from an access path that the classical Optimizer would not normally choose. Db2ZAI has the potential to find these access paths by analyzing historical performance data. In time, this leads to lower CPU and I/O costs; however, since many of these applications are real-time and/or customer-facing, the slightly smaller single SQL elapsed times may not be large enough to be noticed by users.

One last point. Simply choosing a lower-cost access path may not always be the “best” answer. On-line applications also include insert, update and delete activity, and these activities can sometimes affect queries by locking data. The DBA must still monitor and review any access path changes recommended by Db2ZAI to ensure that locking, deadlocks, timeouts and data “hot spots” do not occur.

Implications for the Warehouse and Big Data
Unlike operational systems, data warehouse (DW) and big data applications tends to have a single, short period of intense insert/load activity followed by a long period of read-only activity. Further, queries tend to be both large (referencing a large number of tables), complex (tables joined using starjoin) and sometimes data-intensive (retrieving a large amount of data).  This last point deserves explanation.

The enterprise data warehouse has matured from a (relatively) simple business analytics platform to a one-stop data reference platform. It is now used for: (1) “What-if” queries from business analysts; (2) unloads of data for transport to departmental data marts; and, (3) a test bed for simple big data queries joining warehouse and operational tables.

Each category of SQL queries in the DW may benefit to some degree by implementing Db2ZAI. Warehouse queries that aces a lot of data are by nature I/O-intensive, and these I/Os require CPU cycles to execute. In addition, some queries require sorting, either due to SQL syntax (ORDER BY or GROUP BY) or due to access path choices (e.g. a join where a good index is not available).

In earlier times, SQL against the warehouse tended to be single-use and/or ad hoc. Since there would be little or no history of access paths and resource use, AI would not be effective. However, as the warehouse grew and matured analysts found that certain queries could be run regularly to forecast trends or spot anomalies. Hence, as more and more SQL statements are run regularly, their performance history becomes useful as a predictor of access path performance.

As the line between operational systems, data warehouse and big data begins to blur, the DBA’s ability to monitor and tune application performance becomes more difficult. Db2ZAI then becomes an important tool for the DBA.

What are the Costs of Db2ZAI?
Artificial intelligence software is large and complex, and it is not cost-free. In addition to high-volume data storage for SQL statements, access paths and historical resource costs, the software requires CPU cycles of its own. IBM has address this issue by offloading its model learning task to specialty CPU engines called zIIPs (System z Integrated Information Processor). These CPUs are not included as part of software license charges that are based on CPU usage, and can operate synchronously with the system CPUs.

Finally, no AI implementation is complete without a management interface. Db2ZAI includes a on-line interface that allows the DBA or software engineer to monitor model creation, training and deployment, as well as which SQL statements models resulted in improvements in performance.

Related Tags:
About the Author
Be the first to comment | Write Comment
Sign In to Post a Comment