Since Oracle 12c, the query optimizer is able to change execution plans at runtime. This feature is called “Adaptive Plans”. Something similar happened on the ODTUG Community Service Day at the Kscope conference in Orlando.
The ODTUG Community Service Day always takes place the Saturday before the Kscope conference of ODTUG (Oracle Developer Tools User Group). Conference attendees and speakers are asked as volunteers to provide help. Because I attended Kscope for the first time, my Trivadis colleague Kim Berg Hansen recommended to join this day because it’s a good chance to meet people and have interesting chats.
In the morning, we did several jobs in a member grocery store of UP Orlando, a super market for people in poverty. My job was to clean the shelves of the “crackers and cookies” department and to check the food in the shelves for the best-before date (as a craft beer brewer, I’m familiar with this kind of work).
Photo: Twitter / @wrkingtechworld
In the afternoon, we had to pack more than 1000 bags for the conference attendees with marketing flyers and advertising gadgets. During this “boring” job, it was interesting to see, how many smart people with experience in performance optimization were helping here. What happened, was a kind of optimization at runtime, an adaptive plan to improve packing performance during its execution.
Default Plan to Pack Conference Bags
The original plan was to collect the material in two lines of tables (real tables, not database tables). Several slave processes, called “Community Service Day Volunteers”, were in a line, each of them took one piece of paper after another from a stack. Each collection of advertising flyers was then packed into a bag, and afterwards the bag was filled with additional gadgets like water bottle, USB charger, bottle opener, etc.. At the end of the line, the packed bag was placed on a cart and the slave process walked back to the start of the line.
Photo: Twitter / @OliverLemm
Increase Parallel Degree
Some of the volunteers immediately realized that this process can be optimized by increasing the parallel degree. They collected two sets of material concurrently, but unfortunately, this slowed down the queue and created wait events for the succeeding volunteers. A more appropriate way of optimization was to collect the stuff from both sides of the tables. So the parallel degree of the whole packing line could be increased from 2 to 4. This helped to reduce the wait events on the paper line.
Build a More Advanced Plan
Because grabbing the paper sheets took more time than collecting the gadgets, the workload at the beginning of each queue was much higher than at the end, So, we decided to build a more advanced execution plan with three steps:
- Four parallel groups of “Paper Grabbers” (A), worked in the Paper Section. Each of them collected the marketing flyers for one bag and gave them to the “Bag Fillers” (B). Because collecting the sheets of paper takes a lot of elapsed time, four parallel queues were allocated for this job.
- Two “Bag Fillers” (B) took the marketing flyer collections from the “Paper Grabbers” and put them into empty ODTUG conference bags. Then they gave each bag to one of the “Gadget Collectors” (C).
- In two parallel queues, the “Gadget Collectors” (C) added all the additional gadgets provided in the Gadgets Section into the pre-filled bags. At the end, they put the bag on a cart that was brought to the reception desk at the end.
This plan worked well, and we already had plans to improve it even more. But unfortunately, all the work was already done. So, there was no need for further improvement.
As you can see in this example, it is a good approach to improve a process during its execution. This can be done in manual work like filling ODTUG conference bags, but also in executing a SQL statement. The query optimizer of Oracle 12c has a similar approach with Adaptive Plans – although the implementation is slightly different from what we did on the Kscope Community Service Day.