Script: Finding Session With Downgraded Parallel Execution
I was working with the data warehouse team at a customer site and at some point we realized that some parallel executions are not getting enough resources (downgraded).
Not getting enough parallel processes in such a complex environment is really bad. That means that since everybody is hogging the CPU, some sessions will not be able to complete inside the night ETL time frame. If that happens – some ETLs will go on into the day providing wrong data to the customer in even worse performance for the morning shift. Another aspect is the memory usage for large sorts or hash joins. Using less processes will mean some of the data will not reside in the memory and will need to be allocated in the temporary tablespace.
The customer asked how can he find those downgraded (meaning, not getting enough parallel processes) at real time. This is the script we created for that.