Teradata Visual Explain adds another dimension to the EXPLAIN modifier by depicting the execution plans of complex SQL statements visually and simply. The graphical view of the statement is displayed as discrete steps showing the flow of data during execution.
However, Visual Explain was using QCD to captured the query execution plan steps in relation tables to generate visual explain for query diagnostic. QCD has performance issues due to the many inserts required in QCD de-normalized tables. DBS has implemented DBQL and QCD XML plan logging for TD 13.10. These enhancements provide additional capabilities to users wishing to tune queries and applications in order to achieve better performance.
Visual Explain 13.10 enhanced to captured the query explain plan in QCD in relation and XML format (using Launch QCF feature) and generate the visual explain from XML documents using client xml parser. The User can also capture the plan in DBQL XML and generate the visual explain on DBQL XML plans.
The following Query “SELECT * FROM DBC.TABLES;” was captured in DBQL, Relation QCD and XML QCD for performance observation.
Captured and generate Visual Explain in Normal QCD ---> 125 secs
Captured and generate Visual Explain in XML QCD ---> 21 secs (Improvement 596%)
Generate Visual Explain form DBQL XML ---> 18 secs (Improvement 694%)
Steps to Log XML queries in DBQL:
Step 1 - Getting Started using VEComp13.10 Select Execution Plan(s) to Open – DBQL Plan:
Step 2 - Select Plans from DBQL Dialog:
Step 3 - DBQL Filter Options:
Step 4- Add Plans–Loads Xml Plans-Select Execution Plan to Open Dialog:
Step 4- Visual Explain:
Step 1 - Insert Execution Plans in XML using Launch QCF:
Step 2 -Browse QCD XML Plans to Open /Compare: