X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

Optimizing the PL/SQL Challenge VI: The Problem with Group By Subqueries

Chris Saxon
Developer Advocate

So far in this series we've made substantial improvements to the performance of the Library page query on the PL/SQL Challenge. This was taking several minutes to execute; our efforts have reduced this to a few seconds.

These efforts were all directed at the main body of the query. There are also a number of subqueries in the select clause. Oracle is still doing a lot of work processing these, as we can see from the following autotrace summary:

The SORT (AGGREGATE) steps are all queries nested within the select clause of the statement. The FILTER is the main body of the SQL. We can see that the first of these subqueries is doing double the amount of work as the main query!

To figure out why, I expanded this step. When doing so, something caught my eye (columns are, from left to right, last CR buffer gets, last output rows, last starts):

That full index scan is processing over 300,000 rows! No wonder the buffer gets are so high.

Now that we've identified where to direct our efforts, the next questions are why is this happening and what can we do about it?

Notice that there are no filter or access predicates listed on this operation. This means Oracle's processing all the rows in the index. Twenty times. This explains why this step is returning so many rows.

We're getting somewhere. But why is Oracle fully scanning the whole index?

The presence of VIEW -> SORT steps above the index scan are a clue to what's going on here. These steps mean that Oracle is fully executing all operations under the VIEW before joining these results back to the rest of the query. This usually happens because there's an inline view (subquery) in the statement that it can't merge into the main query.

This can happen with "complex" views that include operations such as group by, distinct, outer joins and other functions that aren't basic joins.

Let's take a look at our query to see if we can find any of these. The object listed at the top of the autotrace output, qdb_correct_comp_events_v is a view. Its definition is:

with chc as (
  select qs1.question_id, count (*) choice_count
  from   qdb_questions qs1, 
         qdb_mc_options mco1
  where  qs1.question_id = mco1.question_id
  group by qs1.question_id
), per_quiz as (
  select qz.comp_event_id , qz.quiz_id , qr.user_id ,
         case
           when qt.only_one_choice_correct = 'N' then 
             sum (case
                   when mco.is_correct = rc.user_choice then 1
                   else 0
                  end
             )
          else
            case
              when sum (case
                          when mco.is_correct = rc.user_choice then 1
                          else 0
                        end
                   ) < chc.choice_count then 0
              else sum (case
                          when mco.is_correct = rc.user_choice
                          then 1
                          else 0
                        end
                   )
            end
        end correct_answers
  from  qdb_quizzes qz , 
        qdb_quiz_results qr ,
        qdb_mc_options mco , 
        qdb_result_choices rc ,
        qdb_questions qs , 
        qdb_question_types qt , 
        chc
  where qz.quiz_id        = qr.quiz_id
  and qz.question_id      = mco.question_id
  and qr.result_id        = rc.result_id
  and mco.mc_option_id    = rc.mc_option_id
  and qz.question_id      = qs.question_id
  and qs.question_type_id = qt.question_type_id
  and chc.question_id     = qs.question_id
  and nvl (qr.saved_by_user, 'Y') = 'Y'
  group by qz.comp_event_id , qz.quiz_id , qr.user_id ,
    qt.only_one_choice_correct , chc.choice_count
)
select eva.comp_event_id , 
       eva.user_id , 
       sum ( per_quiz.correct_answers ) correct_answers
from   qdb_compev_answers eva, per_quiz
where  eva.comp_event_id = per_quiz.comp_event_id
and    eva.user_id       = per_quiz.user_id
group by eva.comp_event_id, eva.user_id ; 

Aha!

The chc subquery includes a group by and count. The fully scanned index, qdb_mc_options_idx, is on the qdb_mc_options table in this subquery. Looks like we've found our culprit.

Now we've identified this, what can we do about it?

First up, let's analyze what's going on this query. It has two subqueries defined in the with clause: chc and per_quiz. Per_quiz returns a row for each quiz. Chc counts how many options there are to choose from for each question. We use this for "only one correct" quizzes to check whether the number of correct answers a player has given matches the number of possible answers for the question. If they do, they get 100%, otherwise they score 0.

Unfortunately, Oracle's not been able pass the question_id into the chc query. In effect, it's executing the following query once for each question marked as "only one correct":

select mco1.question_id, count (*) choice_count
from   qdb_mc_options mco1
group by mco1.question_id

Notice there's no where clause. Therefore Oracle is counting how many options there are for every quiz, not just the one we're interested in.

Let me repeat that:

This processes all the rows in an index on qdb_mc_options for every question that is marked as only one correct.

This isn't going to scale well. Relatively few questions have this property. There only needs to be a handful in the results for this to impact query performance however.

To fix this, let's check what we're trying to achieve. We want to count how many options there are for a specific question - i.e. join the query above on question_id.

Fortunately there's a simple way to do this. We can remove the query in chc from the with clause and place it directly in the select. Then we can join it to the current quiz, like so:

with per_quiz as (
  select qz.comp_event_id , qz.quiz_id , qr.user_id ,
         case
           when qt.only_one_choice_correct = 'N' then 
             sum (case
                   when mco.is_correct = rc.user_choice then 1
                   else 0
                  end
             )
          else
            case
              when sum (case
                          when mco.is_correct = rc.user_choice then 1
                          else 0
                        end
                   ) < (select count (*) choice_count
                        from   qdb_mc_options mco1
                        where  qs.question_id = mco1.question_id
                       ) 
                then 0
                else sum (case
                            when mco.is_correct = rc.user_choice
                            then 1
                            else 0
                          end
                     )
            end
        end correct_answers
  from  qdb_quizzes qz , 
        qdb_quiz_results qr ,
        qdb_mc_options mco , 
        qdb_result_choices rc ,
        qdb_questions qs , 
        qdb_question_types qt 
  where qz.quiz_id        = qr.quiz_id
  and qz.question_id      = mco.question_id
  and qr.result_id        = rc.result_id
  and mco.mc_option_id    = rc.mc_option_id
  and qz.question_id      = qs.question_id
  and qs.question_type_id = qt.question_type_id
  and nvl (qr.saved_by_user, 'Y') = 'Y'
  group by qz.comp_event_id, qz.quiz_id, qr.user_id, qt.only_one_choice_correct, qs.question_id
)
select eva.comp_event_id , 
       eva.user_id , 
       sum ( per_quiz.correct_answers ) correct_answers
from   qdb_compev_answers eva, per_quiz
where  eva.comp_event_id = per_quiz.comp_event_id
and    eva.user_id       = per_quiz.user_id
group by eva.comp_event_id, eva.user_id ;

We're now explicitly asking to count the number of choices for a given question. Oracle is able to apply the question_id filter to the qdb_mc_options table. As a result it should only process the handful of rows for each question, instead of the whole table!

In theory this looks like a huge saving. How does it bear out in practice?

Wow, a massive decrease in work!

Those 16,000 buffer gets have dropped down to under 500. The 15,000 plus gets doing the index scan are almost entirely eliminated.

Notice also that an extra SORT (AGGREGATE) has appeared (there's four instead of the original three). Expanding this step out we see:

 

The object listed is the index on qdb_mc_options (question_id). Oracle's manipulated the SQL so it's only executing this subquery once, rather than for each row.

Let's recap.

Subquery factoring (the "with" clause) is a neat way of saving queries you can reference later in the same statement. This can help make them easier to understand.

The risk is if these subqueries include group by statements or other complex operations then Oracle may have to process all the underlying data - not just the rows that you're interested in. In some circumstances Oracle can access just the rows you need via complex view merging. Unfortunately it didn't in this case.

To figure out if this case is affecting a query, look for VIEW steps in the execution plan. If these are processing unexpectedly large numbers of rows you may need to rewrite your query to improve its performance.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha