The enhanced Extended DBQL statistics allows for more granular data collection to isolate specific express step executions at the AMP level and their associated user SQL.
Syntax Updates
- The BEGIN/REPLACE QUERY LOGGING statements include the DETAILDIAG option; an optional sub-option STEPETTHRESHOLD can be specified when the user supplies a valid DETAILDIAG option.
Any effective SUMMARY or THRESHOLD option can be overridden to diagnose SLA failures. This capability will allow you to collect detailed logs in DBC.DBQLogTbl and DBC.DBQLStepTbl.
- FLUSH QUERY LOGGING includes AMPDATA as a new flush option. Note that when ALL or ALLDBQL options are used, it includes AMPDATA option as well to flush.
- SHOW QUERY LOGGING output shows the effective DBQL options for a given user. .
Use Case
Let's say your tactical workload consistently fails to meet the SLA for user1 and user2. Extended DBQL statistics can accurately determine which specific express request corresponds to the user requests that may be causing these failures.
The elapsed times of step execution can be readily determined at the AMP level and be as precise as microseconds due to the new data being collected. Additionally, you can obtain the associated resource usage to identify potential bottlenecks such as CPU or IO.
- Enable extended DBQL logging or AMP diagnostics based on what parameters to check. Note that enabling Extended DBQL logging also collects detailed AMP level granular diagnostics.
- In the following example, the DETAILDIAG options ExtDBQLData (256) and SumThreshOverride (1024) are enabled on user1 and user2. See REPLACE QUERY LOGGING Syntax Elements for more information on these options.
REPLACE QUERY LOGGING with STEPINFO DETAILDIAG=1280 on user1, user2;
- To enable both options, DETAILDIAG is set 1280 (256 + 1024). The SHOW QUERY LOGGING output in the following example verifies that ExtDBQLData and SumThreshOverride are now both set to T (for TRUE).
SHOW QUERY LOGGING on user1,user2;
Result:
Rule UserName user1 (From a System and a User rule) Rule UserId 00007C05 Rule ZoneId 00000000 Account (Rule for any Account) DBQL RULE: Active System User Explain F F F Object F F F SQL T T F Step T F T XMLPlan F F F StatsUsage F F F UseCount F F F Param F F F FeatureUsage T T T Verbose F F F StatsDetails F F F UtilityInfo T T F NoColumns F F F Summary F F F Threshold F F F Text Size Limit 200 200 200 DETAILDIAG RULE: User AMPDataDiag F ExtDBQLData T SumThreshOverride T Rule UserName user2 (From a System and a User rule) Rule UserId 00007D05 Rule ZoneId 00000000 Account (Rule for any Account) DBQL RULE: Active System User Explain F F F Object F F F SQL T T F Step T F T XMLPlan F F F StatsUsage F F F UseCount F F F Param F F F FeatureUsage T T T Verbose F F F StatsDetails F F F UtilityInfo T T F NoColumns F F F Summary F F F Threshold F F F Text Size Limit 200 200 200 DETAILDIAG RULE: User AMPDataDiag F ExtDBQLData T SumThreshOverride T
Note that enabling Extended DBQL logging (ExtDBQLData 256) also collects granular AMP diagnostics (AMPDataDiag 128). Thus, it is unnecessary to enable AMPDataDiag (128) when ExtDBQLData (256) is enabled. However, as can be seen in the SHOW QUERY LOGGING statement, the flag for AMPDataDiag (128) will remain F (for FALSE) unless explicitly enabled in the DETAILDIAG rule.
- In the following example, the DETAILDIAG options ExtDBQLData (256) and SumThreshOverride (1024) are enabled on user1 and user2. See REPLACE QUERY LOGGING Syntax Elements for more information on these options.
- The REPLACE QUERY LOGGING SQL enabled extended DBQL logging with SUMMARY and THRESHOLD overridden. That means if the request did not meet SLA even though STEPINFO option not enabled, there will be rows logged in DBC.DBQLStepTbl for corresponding requests irrespective of SUMMARY and or THRESHOLD rules being active for the user due to any other applicable rule hierarchy.
- Use the new data from detailed AMP diagnostics (DBC.QryLogAMPDataV) to understand the express request elapsed times:
SELECT StartTime, FirstStepTime, b.stepstarttime, b.stepstoptime, ((b.stepstoptime - b.StepStartTime) hour(4) to second) (Named ExpElapsedTime), b.StepClass, b.StepKind from dbqlogtbl a, qrylogampdatav b where a.queryid = b.queryid and b.stepclass='EXP' and b.stepstarttime >= a.starttime and b.stepstoptime <= a.firststeptime and ResponseTimeMet is NULL sample 5;
Result:
StartTime FirstStepTime StepStartTime StepStopTime ExpElapsedTime StepClass StepKind -------------------------- -------------------------- -------------------------- -------------------------- ------------------ --------------- --------------- 2024-03-08 13:55:29.297572 2024-03-08 13:55:29.298092 2024-03-08 13:55:29.297844 2024-03-08 13:55:29.297995 0:00:00.000151 EXP RERX3 2024-03-08 13:53:03.744152 2024-03-08 13:53:03.761678 2024-03-08 13:53:03.745033 2024-03-08 13:53:03.745199 0:00:00.000166 EXP RAR 2024-03-08 13:52:51.403219 2024-03-08 13:52:51.418419 2024-03-08 13:52:51.416195 2024-03-08 13:52:51.416300 0:00:00.000105 EXP FTH 2024-03-08 13:53:05.889118 2024-03-08 13:53:05.903333 2024-03-08 13:53:05.902449 2024-03-08 13:53:05.902563 0:00:00.000114 EXP FTH 2024-03-08 13:53:07.143486 2024-03-08 13:53:07.158304 2024-03-08 13:53:07.157241 2024-03-08 13:53:07.157363 0:00:00.000122 EXP FTH
- The above SQL shows which requests did not meet the SLA and their express request elapsed times before the first AMP execution step started. The steps shown are an example; a single request can include multiple express requests that might be contributing to the SLA failure.
- Disable the diagnostic rule after logging is complete:
end query logging on user1, user2;