Zutshi Group LLC

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== SQL Time consumed by UNKNOWN SQL Statements in COBOL Trace ======

==== ISSUE: ====
When running trace on Global Payroll process(GPPDPRUN) with the "COBOL Statement Timings" Debug Flag set,  a significant amount of SQL time is consumed by "UNKNOWN" statements. What are the "unknown" statements? 
  
==== SOLUTION: ====
The above issue has been highlighted in few cases and has been reviewed by the development. Unfortunately, there isn't a direct/definite solution for this problem. The GP Core development was able to offer the following explanation. 

"Unknown" is basically the execution time for the dynamic SQL (not including the retrieve, compile, and fetch timings).  The trace doesn't accurately assign the time for dynamic statements and so puts it in the unknown category. 

The dynamically constructed statements in GP come from these elements: Arrays, Writable Arrays, and Historical Rules.The actual SQL that is constructed for arrays can be displayed via a checkbox on the Array definition.Arrays would have the name GPPUARSL_NNNNNNNN where NNNNNNNN is the PIN number.Historical rules are prefixed with GPPUHIST and Writtable Arrays with GPPUWAPR. 

While the dynamic statements do indeed appear in the timing summary, much of the execution time for them is misassigned to "Unknown". This is just a defect of the SQL timing summary. You could comment out the dynamic statements and observe the change in the "Unknown" category to verify this. You could also selectively comment them out to ascertain which of them are causing the worst performance hit. 

The SQL trace does not correctly allocate time for dynamic statements, instead lumping much of their time together under the status of "Unknown".  To determine an individual dynamic statement's time, therefore, requires an indirect approach. 

Assume we start with a baseline run with 2 hours of "Unknown" time.  If we "comment out" a dynamic statement and the unknown time drops to half an hour, it would be reasonable to assume that the commented out statement was responsible for about 1 & 1/2 hour's processing.  By commenting out I mean remove from processing.  To be processed, an element either needs to appear on a section or be called by another element.  If the element is removed from the section and/or is not called by another element, it will not be processed.  It is by this process of selectively removing elements from processing and comparing the processing time before and after the removal that one can ascertain what elements are causing the bottlenecks.  (Note: of course, with elements commented out, the results won't be correct, but we are not concerned with result values here, only performance time.) 

It is not difficult to identify what dynamic statements are being used in batch.  In the SQL trace, Arrays will appear with the preface GPPUARSL, Writable Arrays with GPPUWAIN_I_, and Historical Rules with GPPUHRUL_S_.  All of these prefaces are then followed by the PIN number of the element.  Use the "View Element Relationship" page to determine where an element is called from.  For example, in a SQL trace, if we see an entry for GPPUARSL_00130038.  If we look at the View Element Relationship page for this element, we see that it is only used by a Section element called INITIALISE.  If we remove it from this section, it should not get processed. 

The most room for improvement will be found in the Array elements.  Historical Rules are costly, but this is because they use the very large result tables - there isn't that much that can be done to improve the performance.  Arrays, on the other hand, can use all sorts of tables, and it is possible that the indexes on these tables are not appropriate for how the table is used by the Array.  It also is sometimes possible to rewrite an array to return fewer rows by adding further bind variables.  An Array that returns a single row should process much faster than one that returns 100 rows.