Finding Inline Candidates - Part II

 Last month, I reviewed how to identify user-defined functions that are a candidate for inlining from the perspective of finding functions that are small and simple with the usage of a system catalog object. Another method is to identify the user-defined functions that are being used most frequently on your system. With this approach, you maximize the performance impact of any user-defined function (UDF) that can be made inline eligible because the UDF is run so frequently. 

This methodology requires a Plan Cache Snapshot. A Plan Cache Snapshot is a point in time copy of the data from the “live” Plan Cache. When a snapshot is created, the result is a Db2 table. The rows in this table contain information about the query implementation along with query execution statistics. The query execution statistics include a count of the number of UDF executions that were not inlined for each SQL statement. Given that this execution information is stored in a Db2 table, we’ll see that it is relatively simple to write a query to find these candidate UDFs.

 

The first piece of information you’ll need for your query is the name of the snapshot table. If the IBM i Access Client Solution (ACS) SQL Performance Center was used to create a snapshot, then you’ll need to figure out the name it generated for the snapshot object. This task is easily accomplished by bringing up the ACS SQL Performance Center and navigating to the Plan Cache Snapshot tab as shown in the figure below.

Now that we have name of the table for the query, let’s look at the following query it will be plugged into. The QQSMINT8 column is the key column for our inline UDF candidate analysis – this is the column that contains the number of times that an SQL statement invoked a UDF that is currently not being inlined by the Db2 engine. This query uses this column on the WHERE clause to only return queries which used non-inlined UDFs that in addition to returning the count of UDFs that were not inlined in the final result set.

SELECT qqsmint8 AS SQL_UDFs_Not_Inined, qvp15f AS Times_Run,
       CASE
         WHEN LENGTH(RTRIM(qqc104)) = 0 THEN 'No Program'
         ELSE RTRIM(qqc104) CONCAT '/' CONCAT qqc103 END AS Pgm, 
        qq1000 AS sql_stmt
  FROM snaplib.snapshot_table
  WHERE qqrid = 1000 AND qqsmint8 > 0
  ORDER BY qvp15f DESC

 

The other QQRID column referenced on the WHERE clause is also critical because the 1000 record type is the row in the snapshot table which contains the query execution statistics. Most of the other record types contain data regarding the query implementation. The SELECT list also includes the QVP15F column which contains the number of times the query was run while the query plan resided in the Plan Cache. This count helps you understand how many times the non-lined UDFs are being invoked. The more times that these UDFs are run, the bigger the performance impact will be on your system if you’re able to make the UDF inline eligible.

 

Here's an example of the output this query can return using a snapshot on my system.  It only found one query that’s dependent on non-lined UDFs.  However, that query has been run over 500 times to this point. 

The QQ1000 column contains the query text, so that you can review and find the name of the non-inlined UDFs for further analysis. Hopefully, this method used along with the previously discussed method will help you identify user-defined functions that can be made inline eligible in order to improve the performance of your applications and servers.