I have a situation in which i need to see if a ID qualifies for tactical workload. I know that for tactical workload, the ID should fire very tuned queries accessing single amp and mostly select 1 row.
My question is - how do i find out from dbql if this id fires queries that access one AMP or highly tuned queries ? Any other way to find the qualification of this query for tactical workload ?
You can find "Number Of Active AMPs" in DBQL.
Look at AMP CPU Time, Total I/O Count , Number of Steps, etc. to decide whether the query is "tuned".
Consider both average and "worst case".
If most queries are "Tactical", could you identify the outliers ahead of time and classify them differently (e.g. based on estimated processing time or all-AMPs access)?
for a global aggregate yes, you can use nomofactiveamps field provided by the dbqlogtbl in order to have the evidence of the number of amps involved by the statements.
For a more in depth and specific study it's better to take the statement and execute an explain in order to review the full access path and review if it's singleAMP or not.
On the Workload designer portlet side you have the ability to classify a specific workload considering the number of AMPs used by the queries.
Hope this helps,