I am trying to look for queries on the Query Spot light portlet that have had skewed processing by sorting the queries run in a month based on their IMPACT CPU value.
For quite a few queries (with high IMPACT CPU values) now I've noticed that the IMPACT CPU value is greater than the the CPU value.
Isn't IMPACT CPU just the extra CPU cycles the query has consumed due to skewed processing?
If yes then shouldn't that value be quite less comprared to the over all CPU consumed by the query?
For example I have a query that in an aggregation step has a PJI value of 1,211 a CPU value of 1,473,855 and an IMPACT CPU value of 170,935,840
It makes no sense that the CPU overhead for this query is so large!!
Any explanations on this?
The Impact CPU will always be greater than or equal to the Request CPU. Impact CPU is the CPU used on the hottest AMP multiplied by the number of AMPs involved in the query. In other words, it's the actual "impact" the system felt from running the query. If the Impact CPU is around 100x bigger than the Request CPU, then the query is highly skewed.
We are observing CPU Utilization under "System" to be more than 60% for 15-20 minute period. We would like to know what exactly is running under the "System" categorization which is contributing towards this high CPU Utilization. If I am not wrong, "Viewpoint", "tdwm", "DBC" CPU Usage are calculated under "User" categorization. Is there any way for DBAs to find the queries behind "System" categorization.
System CPU utilization of 60% sounds extremely high. This question should be directed to the database team and might warrant a customer incident.
Is there a possibility that stale stats or no stats can affect impact cpu - in a context where without stats or updated stats, the impact cpu was less but after collecting/refreshing the stats, the impact cpu has increased? The change of value was around 20-30
Does the volume of data affect impact cpu by any chance?
One more "quick" question. I understand the concept of ImpactCPU and know that when ImpactCPU (ICPU) and TotalCPU (TCPU) or nearly the same, then there is almost no amp skew. So, what is the factor or ratio of when ICPU is bad for a query, is it 2x, 6x, 10x? Just wondering what the threshold is when looking for poor performance metrics....
Thanks In Advance,
So, I asked the question 2 months ago about Impact CPU and what the threshold might be when assuminng poor performance based solely on ImpactCPU alone? Is 2x,3x,4x the Request CPU or is there any general rule of thumb to go by.
No one responded, why?
Its hard to give One threshold that suits all. It depends on the Query Logical requirements and data , CPU and Run Time. (for example OLAP function may result in unavoidable SKEW ). If Query has CPU =5 then 2x ICPU is ignoreable. But same is not true if CPU 500K- It also depends on your system size - You should have first threshold on CPU and then ICPU. CPU threshold could vary based on your system Total Capacity and /or Number of AMPS. (ICPU = HOTAMPCPU*Number of AMPS ).
You can also view it as a ration - Parrallel Effeciency. Higher the better. ICPU/CPU = PE . 1 (100%) will be ideal . 2X ICPU means PE is 50% and 4X menas 25%. Any thing below 10% is questionable. 50% could be acceptable as some queries have to have some Skew due to the Data demographics .
At Individual Query level you can target any thing above 3X as tuning option. You may set a first threshold on CPU and run time and then on ICPU. You can always raise the bar later. Adhoc User queries can fluctuate but For routine /batch queries ICPU should be low and you can have a different threshold .
Thank-you for your response. That is fundamentally all I was looking for, a general rule of thumb or best practices guideline. 3x seems fair, although on long running jobs, 2x might be a significant red-flag as well.
Again, thanks for the reply.