Transcript Capturing Query Plan

Visual Query Insight
491 Presentation
KanakaDurga Rajanala
Overview
 Introduction
 Intended Users
 Technologies
 Tool Overview
 Tool Functionality
 Conclusions
 Demo
Introduction
 VisualQueryInsight(VQI) tool is a database front end
tool designed to provide visual insight into a given
query.
 Captures object definitions, demographics, query
plan and run time information from the database.
 Highlights the hotspots.
 Save and retrieve the query details for future
reference and comparison.
 Helps to tune a given query.
Targeted Users
 DBA’s
 Developers
Technologies
 Teradata Database
 Java
Modules
 Capture object definitions in the given query
 Capture table demographics
 Capture the query plan
 Capture runtime information
 Save and retrieve
 Query tuning
 Re-Optimize
System Overview
User Authentication/Connect
Query tag
Query Wizard
Query
Syntax check/Access Rights
No
Pass
B
Error Report
Yes
Query Plan
Demographics
Presentation Engine
A
Objects Def
Save To file
Runtime
System Overview
A
Performance
Not Acceptable
Get Optimizer Recommendations
B
Apply Recommendations
Capturing Object Definitions
 SHOW QUALIFIED < Query Text >;
 The definition of each object is returned in an
independent result set by the Teradata database.

Example

Query
SHOW QUALIFIED
SELECT *
FROM Employee e, Department d
WHERE e.dno = d.dnumber;

Result



Returns 2 result sets .
First result set contains object definition of the table Employee
Second result set contains object definition of the table Department
Capturing Table Demographics
 HELP STATISTICS <table name>;
 Returns the list of columns on which statistics
are collected with summary demographics.
 HELP STATISTICS <table name> COLUMN
<column name [,column name]>;
 Returns the detailed demographics for the
given column(s).
Capturing Runtime Information




BEGIN QUERY LOGGING WITH ALL ON <User>;
SELECT /*<Query_Tag>*/ …rest of the query;
END QUERY LOGGING WITH ON ALL <User>;
Retrieving Demographics
SELECT *
FROM dbc.dbqlsteptbl
WHERE queryid IN (SELECT queryid
FROM dbc.dbqlsteptbl
WHERE sqltextinfo LIKE ‘%Query_Tag’
ORDER BY steplev1num, step2ev1num);
 Returns Runtime information such as Actual CPUtime,
EstimatedCPUtime, Actual RowCount, Estimated RowCount,etc.
Capturing Query Plan
 EXPLAIN <query text>;
 Example :
EXPLAIN
SELECT *
FROM Employee e, Department d
WHERE e.dno = d.dnumber;
 Returns the query plan as a single record set.
Save and Restore
 Captured details can be saved to persistent
storage (to a file)
 Can be retrieved when required
 Useful to log the query plans for critical
queries before a database or hardware
upgrade.
 Can be used to compare with the new plans
for poorly performing queries
Query Tuning
 Two phase approach

Identify what went wrong?




Look into the run time information
Identify the hotspots
Compare the estimates and actuals
Determine what needs to be done?




Capture optimizer recommendations
Determine manual recommendations
Apply recommendations
Re-optimize
Conclusions
 Help DBA’s and developers visualize all the
components in a given query.
 Speed up query tuning process.
 Improves system throughput and response
times.
Demo