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.
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;
Now I go to run an Explain or an AutoTrace (I like AutoTrace for the stats and increased accuracy over Explain):
If you don’t see this info in your plan, check your preferences:
This chokes on a DR (physical standby) with an ORA-00604.
Excellent info. There is a catch with QB_NAME. If a query block is merged with another, you lose the QB_NAME, and it goes back to an Oracle created one, which is a bit less reader friendly. (See my blog post http://ricramblings.blogspot.com/2015/02/are-you-using-qbname-hint-you-should-be.html) It’s of course still a great idea to name your query blocks, it will help tremendously. Every time we make the query easier to read it’s a very very good thing!!
Thanks for the warning and info for our customers Ric!