Examples Using QryLogXMLV - Advanced SQL Engine - Teradata Database

Data Dictionary

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-10-15
dita:mapPath
yoz1556235523563.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1092
lifecycle
previous
Product Category
Teradata Vantage™

Example: Extract Query Plan Information from QryLogXMLV

This example shows how to extract query plan information from the DBC.QryLogXMLV view.

SELECT
cast(t.result_value as char(30)) as "ColumnName"
  FROM (SELECT QueryID, XMLTextInfo
  FROM DBC.QryLogXMLV
  WHERE QueryID = 163833139835340224) as x(QueryID, XMLTextInfo),
table(
sysxml.xmlextractvalues(cast(x.QueryID as varchar(18)), x.XMLTextinfo,
null, '//Field[@JoinAccessFrequency>0]/@Name')) as t
  ORDER BY 1;

Result:

ColumnName
------------------------------
a1
b1

Example: Select Documents with Statistics Recommendations from QryLogXMLV

The following SELECT statement retrieves all documents that have statistics recommendations:

SELECT xmltextinfo FROM dbc.QryLogXMLV  WHERE HasStatsUsageData = 'Y';

Example: Select StatsUsage Information from QryLogXMLV

The following SELECT statement retrieves the number of queries where the STATUSAGE option was enabled but had no statistics recommendations:

SELECT Count(*) FROM dbc.QryLogXMLV  WHERE StatsUsageEnabled = 'Y' AND HasStatsUsageData = 'N';

Example: Select Documents with DetailedStatsEnabled from QryLogXMLV

The following SELECT statement retrieves all documents with the DETAILED sub-option enabled:

SELECT xmltextinfo FROM dbc.QryLogXMLV  WHERE DetailedStatsEnabled = 'Y';

Example: Select Documents with DetailedStatsEnabled with No Detailed Statistics Information from QryLogXMLV

The following SELECT statement retrieves the number of queries where the DETAILED sub-option was enabled but had no detailed statistics information:

SELECT Count(*) FROM dbc.QryLogXMLV  WHERE DetailedStatsEnabled = 'Y' AND HasDetailedStatsData = 'N';