Session Queuing and Workload Management

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.


what are we trying to do here. Are we want to create a warehouse system or an OLTP system. Warehouse system are non scalable by nature. Means that the Warehouse SQL queries tends to use all the resources of systems using maximum possible resources. down grade a long runing query will increase its processing time further. our goal should not be to run multiple queries like we did in oltp system but to run a sing huge query with least possible time.

Posted by Amir Riaz on October 14, 2010 at 08:53 PM PDT #

Hi Amir, Hmmm, I think that is a bit of outdated wisdom. Yes, sometimes we will need to run those huge queries, but I do think we can run it combined with much smaller, not less important, queries. Here is why (IMHO): 1) The current systems, like Exadata, are giving us a lot more throughput and run large IO bound queries really fast. So your huge queries is now just something large. It allows us to run a much bigger total workload if we are careful to manage it 2) Demands simply say that the data warehouse is used to feed other than just analysis systems. Recommendations are near real-time (e.g. at web speed) and no OLTP system has my purchase info for the past 5 years. So it needs to go to the dw, which means it gets specific queries for a single predicate (the user). We need to run those queries while other analytics are running 3) Due to #2, we need to service more users. We cannot do this in 2 logically separate systems because it is too expensive and because you run the risk of discrepancies. Now if you are talking "standby" systems or something active-active as a DW, you can run a much larger workload and do split the queries. On #3, I think we'll write a post on the remaining pieces of architecture... notably splitting a workload over standby systems etc. JP

Posted by jean-pierre.dijcks on October 15, 2010 at 09:41 AM PDT #

Post a Comment:
Comments are closed for this entry.

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.


« June 2016