Sunday, June 26, 2016

Check out the Oracle Dev Gym!

Back in April 2010, I started (with the help of many others!) the PL/SQL Challenge, which offered daily quizzes on PL/SQL, along with weekly quizzes on other technologies. In 2014, Oracle acquired the website and I suspended the daily quiz. Writing five new quizzes each week is a lot of work, and I had a big stack of new responsibilities!

But the weekly quizzes continue(d): SQL, PL/SQL, Database Design, Logic and most recently a re-started weekly quiz on Oracle Application Express, made possible by Insum. And Oracle Database technologists kept on using the site to hone their skills. We are rapidly approaching 1,000,000 answers on the site.

That all sounds good, right? So why is the title of this post "Check out the Oracle Dev Gym!"?

Because the PL/SQL Challenge has, for a while, been in dire need or a "reboot"; not just a redesign built around the massively improved APEX5, but also a rethinking of how this quiz platform could best serve the needs of the community.

Rethinking the PL/SQL Challenge

It was clear, especially from the popularity of the daily PL/SQL quiz, that developers like to actively test their knowledge, and compete with others.

It was also clear that lots of people didn't want to compete; they just wanted to learn more about core Oracle Database technologies. For example, some people told me they found the use of the word "Challenge" was off-putting: "I'm not good enough to play the quizzes."

In addition, when we kicked off the PL/SQL Challenge, we were starting from scratch: writing all new quizzes each. We still do that, of course, for our competitions, but we also now have a library of over 2,000 quizzes that the vast majority of Oracle Database developers have never taken.

Yet the current website was not at all geared towards making all those quizzes easy to take, outside of the context of the weekly competitions.  Sure, we'd built a Practice feature into the site, but it was too "heavy" - too complicated, too many toggles and switches....what you see below, for example, is just one of five pages in the Practice wizard. Wow!

In fact, "complicated" is a good way to describe the site all around; it had gotten weighed down with lots of features that people only used occasionally, and got in the way of clean, easy access to our content.

So we decided to rebuild the site using APEX5, the Universal Theme, and all the new goodies in that fantastic rapid application development tool. We also decided it was time to come up with a new name.

But a new name requires clarity around what the site offers its visitors: what makes it unique. So I went off to a remote cabin in the mountains and meditated long and hard. Well, no, actually, I took lots of walks in a nearby park.

And after a while, I had this realization:

You don't visit the PL/SQL Challenge to:

  • get answers to questions - that's what Ask Tom is for (and the OTN Forums).
  • download or run scripts demonstrating features of Oracle Database technology - that's what Live SQL is for.
You go to the PL/SQL Challenge to keep your knowledge and skills fresh, current, strong. 

In other words, you go to the PL/SQL Challenge like you'd go to the gym. So we decided to design the new version of the PL/SQL Challenge around the paradigm of a fitness center. We even have a cool, new logo:

Oracle Dev Gym - Early Adaptor

We are initially making the Oracle Dev Gym available only through the PL/SQL Challenge. We are doing this because the first and most important thing we need to find out is: how do our current PL/SQL Challenge users think of the new approach?

When you visit the home page, you will now see this image on the right:

Click on it to start exploring the Dev Gym.

The Dev Gym runs on the same platform as the PL/SQL Challenge. So when you answer the weekly SQL quiz there, it's the same (well, it sure as heck should be the same) as answering it the "old way".

But you can do so much more, including easily take quizzes on any topic you'd like; takes tests and coding challenges, even set up customized workouts through the Personal Trainer. 

Yes, that's right. The Dev Gym comes with a Personal Trainer. 

I am tempted to tell you more about the Dev Gym, but I really would rather let the site tell its own story - and find out what you, loyal user of the PL/SQL Challenge, think of the new approach. I cannot, however, resist sharing our Exercise page with you:

Tell Us What You Think!

In whatever way is easiest for you:
  • Add a comment to this blog post. 
  • Write me an email: steven dot feuerstein at oracle dot com.
  • Click on the Feedback link in the top right of either site and give us your thoughts (and bug reports).
  • Feel free to tweet about Oracle Dev Gym. Please use #oradevgym.
Don't worry about hurting my feelings. Just give it to me straight.

Game Plan for the Dev Gym

We plan to take the next several months to gather feedback from our users and also go on a wild binge of writing quizzes and attaching them to videos, blog posts and more (check out Personal Trainer and our generated workouts for an indication of how that will improve the Dev Gym experience).

We will also invite you to help us build out all that content by contributing and reviewing content in a community repository being added to the Oracle Learning Library (more to come on that in August).

Then we will announce to the world with great fanfare and widespread acclaim: the Oracle Dev Gym. 

So Is PL/SQL Challenge Going To Disappear?

Not at all!

The Oracle Dev Gym does not offer all the functionality of the PL/SQL Challenge. It takes a long time and careful thought to reimplement a site that has been built up over six years, often in direct response to user requests. We're just getting started.

In addition, I expect that there will be a "hard core" bunch of users who like the traditional site, don't like change, and are not interested in switching to a different style.

Finally, we will continue to use the PL/SQL Challenge to submit, review, edit and approve quizzes for playing. 

Assuming we get a positive response to the Dev Gym, we will gradually shift more and more functionality over (but also keep the new site clean and simple).  But the PL/SQL Challenge will be around for a long time to come.

Run, Don't Walk, To The Dev Gym

If you want to build stronger Oracle Database muscles, if you want to get more flexible when it comes to leverage features packed into SQL and PL/SQL, then head over to the gym and work up a good (virtual) sweat. We offer a tour of the Dev Gym on the site, but here's a link to a video tour in case you just can't wait.

Thursday, June 16, 2016

Table functions to the rescue....again! A refactoring story

I've published several posts on table functions. Here's another one! Why?

A few days ago I tweeted the following:

Two of my followers immediately asked to hear the story: "those are the fun ones, but pics or it didnt happen ;-)" and "may be interesting to view the step you do to find solutions more then result."

Very good points; I especially agree with the latter. As I was going through the revisions to my code, I was thinking (as I have often done before): "It might be helpful to show the process I go through, because it sure feels like a powerful, positive way to build and improve code."

The problems I run into when actually sitting down to tell the story are:

1. Time: yes, I know, we are all busy. Plus, isn't it my job to share thoughts on PL/SQL programming?  Yes it is! Well, part of my job, anyway. And I have been neglecting my blog. But right now, at this moment, I am very focused on finishing the "early adaptor" release of our new skin over the PL/SQL Challenge.  That's the code I was refactoring, in fact.

2. The details: I am working on very specific tables and logic. Do I explain all of that to my readers? Do I modify the code to simplify/redact it? All of that takes extra time and pushes me away from the post.

3. Saving copies of old code: to tell the story properly, I should take snapshots of my code and app, so that I can go back and reproduce the steps. Sounds easy enough, right? But when I am laser-focused on my code, it is soooo hard to do that. :-( 

OK, so the heck with all that! I will take some time away from programming to share my story. And I will publish my code as scripts on LiveSQL, while offering greatly truncated versions in this post.

Why Did I Need to Refactor?

Well, actually, first, you may be wondering: "What is this 'refactoring' you are talking about?"

Refactoring is, essentially, a fancy word for "the process of restructuring existing computer code without changing its external behavior." In other words, you modify the guts of your code to make it more maintainable or faster, without changing the outcome of running the code.  And you do so in a way that is careful and methodical and verified by a regression test.

So when it comes to refactoring and Steven Feuerstein: I am on board with careful. I am so-so on methodical. And I generally fail when it comes to a regression test. 

Let's get to it. Here's why I needed to make changes to my code: the PL/SQL Challenge (PLCH) development team (consisting of Eli Feuerstein, myself and in key supporting roles a number of others, including Dan McGhan, Koen Lostrie, and Shakeeb Rahman) has been working on a new "skin" over the PLCH quiz platform.

We are calling it the Oracle Dev Gym and it will be available publicly as an "early adaptor" release for current PLCH users on 27 June. Hey, that date is quickly approaching!

So it was time to move the app to stage and test it against production volumes of data. For the most part, it performed excellently. One very important page (page 3) in the app displays quizzes for the selected technology:

And here we had a BIG problem: performance degraded from < 1 second on Dev to over 60 seconds on Prod. 

Talk about unacceptable.

OK, to give the full story: the 60 second time was the first pass. When I immediately ran it again (refreshed the page), elapsed time went down to 20 seconds (caching, adaptive plan, etc.), and after a third time, the elapsed time went down to 5 seconds or so. Even more interestingly, when I executed the query in SQL Developer, it usually would finish in under 2 seconds. 

Bottom line: inside the app, this query was waaaaay tooooo sloooooow - and at least part of the performance problem had to do with materializing the result set on the page.

The page was displaying all the PL/SQL quizzes (more than 1400), along with useful information like player ratings of the quiz, average time to finish the quiz and so on. It was doing lots of work, and it was done by a SELECT statement that was actually a SELECT inside a SELECT inside a SELECT, with a number of PL/SQL function calls (context switches). Check out the full query here.  Below is a very abbreviated version:

So there are a couple of things to note at this point:
  • My innermost query gets all possible quizzes, plus it walks the topics tree, and lots of other activity as well. 
  • The page doesn't really display 1400 quizzes. It displays 18 at a time. You could then page through to the others. 
  • But the user already paid the price for preparing all 1400 cards for viewing. 
  • We present the quizzes in the following order: if you haven't yet taken the quiz it takes precedence over those you've answered in the past, and then we randomize the order. 
  • That's right. We randomize the display of quizzes so you are not constantly shown the same quizzes. 
There is no doubt about the fact that I could make changes to the query to improve its performance, including:
  • Create materialized views to pre-calculate historical data (that is, in fact, what the mv_qdb_question_summaries view shown in the image above does).
  • Remove any PL/SQL functions that can be implemented directly in the SQL statement.
  • Make sure all necessary indexes are in place.
But the performance issue also gave me pause and time to ask a very important question: does the behavior on this page match how a user will use the page? 

We all know the saying about Google: if the search result is not on the first page, it might as well not be there at all. Who ever goes to the 2nd or 3rd or 2,507th page of search results? 

Certainly not me.

And since a big point of the Dev Gym is to make it really easy and fast for a developer to find and take a quiz, it also seemed unlikely they would (or we would want them to) spend time paging through those 1400 quizzes. 

Much more likely is that they will find a quiz right there or they will give up. Or maybe they'd tell the Dev Gym: show me another 20 random quizzes to pick from.

I hope this makes sense to you. It made a ton of sense to me - and it also offered a way to dramatically improve performance on this page:
Don't execute a query with an inner SELECT that returns 1400 quizzes, and then filter them out. Instead, right at the core of that query, return just 20 randomized quizzes, and then apply all the "expensive" processing to that small set. 
Then, on the app page, provide a refresh button so that the user can quickly ask for a new set to peruse. 

Now, of course, as you would expect from your own experience, there were a number of additional complicated aspects to selecting out that set of 20: a user could specify a search filter, a difficulty level, etc. Perhaps I was better at SQL I could have done it all directly inside a SELECT.

But I could instantly see how I could leverage a table function to give me the desired effect in a relatively clean manner.

Especially because I had already written a function to return a single randomly-selected quiz for a given topic.  You can see the function in its entirety here. Here's a shortened version that gives you some sense of the inner complexities:

The function constructed a query dynamically, executed it and returned a single row. I needed a query to return up to N quizzes (currently 20, but who knows for the future?). And I needed a function that returned a collection so it can be executed inside a SELECT within the TABLE operator.

So the steps I resolved to take were:
  1. Create a new, separate function to construct and return the query
  2. Change the current random_question to function to call this function.
  3. Create a new random_questions (notice the "s" at the end!) to return a collection of question IDs, which could then be consumed by the query driving page 3 in my app.
Here's the header of my query function (all the code is available for viewing here):

Here's the refactoring of random_question, utilizing the new function:

And, finally, the the new table function needed to solve the original problem (all code viewable here):

There are several elements of this function I very much like:
  • Use of FETCH FIRST N ROWS - added in Oracle Database 12c Release 1. Nice, easy way to say: just give me the first 20. 
  • Reuse of the random_question_query function. I hate to copy/paste code!
  • The second call to the query generator is only made if there were not enough unplayed quizzes. 
  • For the layer quizzes fetch, I BULK COLLECT into a second array. Notice I just ask for 20 (question_limit_in) again. Since the first one didn't return enough, this second one will "fill in the gap" with played quizzes.
  • Then on line 44 I leverage the MULTISET UNION operator (available only for nested tables) so that the PL/SQL engine can do the "heavy lifting" of finding all the quizzes in the l_questions_played array and add them to the unplayed quizzes.
And is time to use the table function inside my page 3 query. Here's a fragment of the whole, showing the inclusion of the table function:

I have now moved lots of complexity to the table function (including the walk down through the topics tree - the CONNECT BY you saw in the original), but more importantly the outer SELECTs only apply their computations to the 30 quiz IDs returned by the table function.

The resulting performance? Outstanding! Virtually instantaneous. 

So....lessons learned:
  • Test your app as early as possible against production volumes of data so that you don't have to dig yourself out of a performance hole late in the game.
  • Watch out for the overhead of calling PL/SQL functions in SQL (context switching).
  • Leverage table functions - where and when appropriate - to move complexity (especially procedural logic) out of your SELECT.
Make sure the behavior of your app matches the way the user will most intuitively and simply want to use it.

I hope you found my story interesting and helpful. I look forward to your comments. Please do feel free to make suggestions for improving code.

Now - can I get back to programming? :-)