We have an appliance system. There is a single ETL query that runs early morning which has UII factor of 2.1.
Query uses complex and nested views and I observed that at execution step no 630 and (Merge Join, redistrubution parallel block of steps)
and step no 642 (Merge Join, redistrubution parallel block of steps) the WaitIO touches 80% in metric analysis portlet of viewpoint.
In both the execution steps the redistribution step completes in just 30-50 seconds.
As shown in above screen shot I observed the WaitIO spike of 80% at step 630 starting from 2:08 AM to 2:16AM i.e. 8 minutes.
For step 642 there is a spike starting from 2:19AM to 2:25AM i.e.6 minutes.
Normally UII factor >3 means there is more IO happening on the system. If here in this case if UII is 2 then is it performance degradation?
Does merge join is causing WaitIO of 80%?