SQL can be hard to read. Execution plans can be EVEN HARDER to read. It doesn’t get any easier when you are joining a table to itself. Or maybe you are having a hard time finding the portion of a plan to correlates to a subquery.

We do have a solution!

QB_NAME Hint (Docs)

/*+ QB_NAME ( qb_name ) */

So why am I talking about this today? Well one of our SQL Advocates, Connor McDonald, recorded a nice video on this technique. To make it easy for you to follow along, I’ve included it and his Twitter profile below. Be a dear and watch the video and add Connor to your ‘Follow’ list.


The Video

My take, in a nutshell…

…if you don’t know if your code is self-documenting or not, assume it isn’t and comment it. Or to put it more simply, try to always comment it. Even if the SQL itself is self-documenting, add comments as to WHY you wrote the SQL in the first place. What’s the business requirement, yada yada yada.

Anyways, with all of that taken care of I wanted to show you very quickly how to see the named query blocks in your SQL Developer execution plans.

I changed Connor’s code – to go against HR.EMPLOYEES and HR.DEPARTMENTS vs SCOTT.EMP and SCOTT.DEPT. I THINK I translated it OK, but hard to tell since he didn’t document it 😉 Sorry, couldn’t resist that Connor.

But anyways, the query text itself is moot here, this is about the hints.

SELECT employees.*
FROM employees, 
 ( SELECT /*+ QB_NAME(Connor1) */
     trunc(hire_date, 'YYYY'), MAX(employee_id) employee_id
   FROM employees
   WHERE employee_id > 0
   GROUP BY trunc(hire_date, 'YYYY')) x,
 ( SELECT /*+ QB_NAME(Connor2) */
     department_id, avg(salary)
   FROM employees
   GROUP BY department_id) y
WHERE x.employee_id = employees.employee_id
AND y.department_id = employees.department_id;
The query block name hints are in red text here.
The query block name hints are in red text here.

Now I go to run an Explain or an AutoTrace (I like AutoTrace for the stats and increased accuracy over Explain):

Now we can see which employees is being acted on.
Now we can see which employees is being acted on.

If you don’t see this info in your plan, check your preferences:

Turn these columns 'ON' to see the query block names
Turn these columns ‘ON’ to see the query block names
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

3 Comments

Write A Comment