I’ll call this article ‘Explain Plan 201’. This article will cover some intermediate features of the previously discussed tools that produce Explain Plans.
I will discuss query blocks in explain plans show how to show how to use the QB_NAME hint to document where the sub query shows up in the explain plan.
The cost-based optimizer (CBO) uses query blocks to store row-sets while executing the SQL. These query blocks are also used to store the results of sub-queries so that these row-sets can be used to satisfy related where-clause predicates. The query block SEL$1 for example, is the main query row-set. SEL$ and a number is the row-set from a sub-query.
Oracle10 allows us to add a hint that renames this generic SEL$ query block name to something useful. Using the QB_NAME hint allows us to give a useful name to the query blocks. You can even use this in the main part of the query and call it something useful like ‘MAIN’ (ie: select /*+ QB_NAME(Main) */ …
I use this hint to give useful labels to the sub queries that then aids me on exactly where the sub query shows up in the explain plan.
Below is a simple query using the QB_NAME hint to label the 2 different sub-queries. I illustrate showing the QBLOCK_NAME column in the PLAN_TABLE to show where the sub queries appear in the resulting Explain Plan.
I easily modified my show_plan.sql script to include the QBLOCK_NAME column. Notice I use a column format SQL*Plus command to give the column a meaningful name in the output. I don’t find the non-renamed query blocks that also start with SEL$ to be useful information so I use DECODE to remove that text from the output.
column id format 999 heading ‘ID’
column parent_id format 999 heading ‘P_ID’
column cost format 999 heading ‘Cost’
column access_plan format a25 heading ‘Access|Plan’
column access_path format a12 heading ‘Access|Path’
column object_name format a12 heading ‘Object|Name’
column qblock_name format a13 heading ‘QB Name’
set heading on
set termout on
select cost,id, parent_id, lpad (‘ ‘, 2 * level) || operation Access_Plan,
options Access_Path, object_name , decode(substr(qblock_name,1,4),’SEL$’,”,qblock_name) qblock_name
from plan_table
connect by prior id = parent_id
start with id = 0;
Remember last weeks discussion on JS Tuner? How I now use the tool as a launch point for newer Oracle RDBMS explain plan items? Well, I’ve included the above script in this tool as well.
Put in the appropriate hints (these hits are available on the ‘Hints’ button) into the SQL and execute the SQL. Notice the Query Block column in the ‘Enhanced Explain Plan’ tab.
I sent this information into the TOAD people and they added it to their tool! They left the SEL$ syntax behind, see below.
This column is made visible (and can easily be made its own column as well…) by right-mouse clicking on the explain plan panel and selecting ‘Adjust Content’ from the popup menu.
The QB_NAME hint in conjunction with the QBLOCK_NAME PLAN_TABLE column can help shed light on those SQL queries that contain multiple sub-queries.
Hope this tip helps you in your SQL tuning efforts!
No comments:
Post a Comment