This is another SQL Tuning case, which was to tune a long-running SQL.

Problem

Client got "Time Out" error when click one of our web pages, which is supposed to be displayed in a few seconds.

Trouble shooting

Click the page mentioned by client. Then check the long-running SQL from SQLBoost. It's not hard to find the SQL by checking the SQL text shown in the table. 

Click "Drilll In SQL" to check the SQL details.

The buffer gets is very high, which is unusual. And we cannot see a very high load in the execution plan. So we decide to choose a test case to further analyze this SQL.

Click "Var" to check the captured bind variables lists, check their performance statistics data, and choose one of them as the test case.

The click "SQL Tree" tab to parse the SQL to a SQL tree, and click "Analyze" to analyze the target SQL.

Note: Those uncaptured bind variables were complemented automatically after a prompt window popped out.

Tens minutes later, we got the analyze results.

Scroll down the analyze results, check the first long-running SQL part, check the data. Though it took 43 seconds to complete the query, and its Consistent Gets is high, considering it returned 1.3M records, we suppose this result is expected.

Move on to the next long-running SQL.

It took 36 seconds to finish, but have no records returned! This is odd.

Further look into the SQL, it involves a WITH inline view (instructor_lp), which is exactly the one returned 1.3M records. That means all of those data had been filtered by other (join) conditions.

Let's take off this view (and its join conditions) from this SQL, see what will happen.

As we expected, query was finished quickly (0.125 seconds)!

Look back the query, we found the inline view (instructor_lp) had no columns involved in the SELECT clause, means we can move it to EXISTS clause to force it be scan in the last step. 

Then we restructured this SQL, moved instructor_lp into a EXISTS clause, and added "no_unnest" hint to make sure it will be not unnested.

The result is satisfying! It finished quickly and have few Consistent Gets!

Then we applyed this re-structuring to the original SQL.

That's it, the tuned SQL and result (quick response and less GC) we wanted!

SQLBooster makes the SQL Tuning much more simple!