Session Queuing and Workload Management
By Jean-Pierre Dijcks-Oracle on Sep 08, 2010
My initial reaction (and theoretical solution) is to not have the connect group, instead go by estimated execution time. That would cut out the Connect group and thus the switch back to the start problem. It does imply that we are going on optimizer estimates and that implies that we need good stats across the board...
The main advantage of doing the switch before actually running is that the parallel processes (assuming we are running stuff in parallel) can be throttled as well. In other words, the main group, which runs the bulk of the workload would allow higher DOPs (and therefore faster runtimes), the Slow group might cap it at a medium DOP to allow quite a few statements to run. It would also allow a balance between CPU and Parallel resources.
The way the plans are set up now, a statement gets an assigned DOP and allocates the processes and memory. As we move that statement into the slower group it keeps that set of processes (even of the target group has lower DOPs enforced) and is throttled on CPU (and IO in later version on Exadata).
For those on 11.2, you could do the same (use estimated time) and add statement queuing to the mix. In that case you can make sure that even if the Slow group fills up (as we don't use session queuing) statement queuing ensures you do not run out of parallel processes allocated.
Anyways, kind of fun to debate stuff like this and quite timely in my little series on workload management.