Capture and compare plans faster with Visual Explain and XML plan logging

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Capture and compare plans faster with Visual Explain and XML plan logging

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 Performance Benefit:

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%)

DBQL XML Visual Plans:

Steps to Log XML queries in DBQL:

  • Connect to Teradata Database 13.10 using BTEQ / SQLA / SQL Assistant JE.
  • Begin query logging with XMLPLAN on <User1>;
  • Enter user’s queries …..
  • End query logging with XMLPLAN on <User1>;
  • User submitted queries will be logged in the DBC.DBQLXMLTBL.
  • The submitted queries execution steps in XML format are stored in XMLTextInfo column of DBC.DBQLXMLTBL.

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:

QCD XML Visual Plans:

Step 1 - Insert Execution Plans in XML using Launch QCF:

Step 2 -Browse QCD XML Plans to Open /Compare:

Tags (2)
2 REPLIES
Enthusiast

Re: Capture and compare plans faster with Visual Explain and XML plan logging

Wow.. has been waiting this feature for years. Cannot wait to try this out . QCD performance issue was always a headache while using visual explain in the past!
Teradata Employee

Re: Capture and compare plans faster with Visual Explain and XML plan logging

Just try this out. Connect to DBS 13.10 latest. QCD setup is not needed for XML DBQL plan. Captured the plan in DBQL in XML and browse and open the plan form VECOMP for analysis.

You can also capture the plan in QCD in XML using Launch QCD (INSERT EXPLAIN in XML). You can see the huge performance difference while capturing the plans in normal QCD vs XML QCD. VECOMP using own XML parser to parse the XML document and generate visual explain so no database operations required once XML document loaded in VEC and XML services also not required for 13.10 DBC. However, XML service should be installed in DBS box for XML plans from DBS 13.0.