Query monitor shows 700K request CPU and the query duration 2 hours.Now if i check same session on dbql the ampcputime is 12K only.Can i get help in understanding which parameter is most imp in query monitor to catch high CPU queries and why dbql and request CPU differ lot.Is request CPU =cpu requested in running active step in explain tab.
system usage is going 98% but when I saw highest consumners I found user which Viewpoint showing 700K request CPU whereas dbql shows 12K.
Request CPU is a snapshot of cumulative CPU actually used for the current request, and typically is not more than a minute or two old.
There will be a delay (by default up to 10 minutes) between the completion of the request and when the information about that request is available in DBQL. And with the original DBQL collection algorithm, if you abort the request then CPU consumed by the (incomplete) last query step may not be captured at all.
As well as the points made by Fred, think about the timing of when you look at the Viewpoint display vs.when you query DBQL vs. when the query runs.
If the query is still running then DBQL will not show anything for that query, DBQL data is only for finished queries.
Therefore, if DBQL only shows 12k cpu seconds for teh session and the '700K query' is still running then that is very possibly the reason.
No DBQL is from past 02/05.Here is snapshot from viewpoint and dbql.Also what shoukld be my parameter to catch session responsible for system critical health.
Check the AbortFlag/ErrorText & DataCollectAlg columns in dbccQryLogV.
If the query was aborted and the collection algorithm is #1 the last (=failing) step is not recorded in DBQL.
And then it's time to switch on the new algorithm #3 :-)
yes i aborted query as the system was 98% and critical and highest request CPU was from this session.
ErrorText=The transaction was aborted by the user.
How to switch to new algo.
Now the issue is same query was submitted again same day evening and it ran fine with 11k CPU in 6 mins compared to 12k 2 hours(the one i killed)
Default "DBQL CPU / IO Collection" algorithm can be set to 3 in dbscontrol general settings, or you can specify MODE=3 for individual rules via BEGIN/REPLACE QUERY LOGGING.
Either stats or data (or both) probably changed between runs. Look at step-level DBQL or Viewpoint "Explain" tab and you will likely see a different query plan and/or step costs.