15.10 - QryLogXMLV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

ProcID

DECIMAL(5,0)

NOT NULL

-(5)9

DBQLXMLTbl.ProcID (Composite NUPI)

CollectTimeStamp

TIMESTAMP(6)

NOT NULL

YYYY-MM-DDBHH:MI:SS

DBQLXMLTbl.CollectTimeStamp (Composite NUPI)

QueryID

DECIMAL(18,0)

NOT NULL

--Z(17)9

DBQLXMLTbl.QueryID

XMLRowNo

INTEGER

NOT NULL

--,---,---,--9

DBQLXMLTbl.XMLRowNo

XMLTextInfo

VARCHAR(31000)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(31000)

DBQLXMLTbl.XMLTextInfo

XMLDocType

INTEGER

--,---,---,--9

DBQLXMLTbl.XMLDocType

XMLPlanEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

StatsUsageEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

VerboseEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

DetailedStatsEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasXMLPlanData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasStatsUsageData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasVerboseData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasDetailedStatsData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

Possible Values for HasXMLPlanData, HasStatsUsageData, HasVerboseData, and HasDetailedStatsData

For these columns, a value of Y or N can be specified. If you specify Y, the data is contained in the XMLTextInfo column on behalf of the specific logging [Option], where Option represents the STATSUSAGE or XMLPlan.

Possible Values for XMLDocType

Note: Teradata recommends that you use the Has[Option]Data and [Option]Enabled columns rather than the XMLDocType column directly, where Option represents the STATSUSAGE or XMLPlan. Queries against these columns allow you to specify a Y or N value rather than bit mapped values. For more information about these columns, see “Possible Values for HasXMLPlanData, HasStatsUsageData, HasVerboseData, and HasDetailedStatsData” or “Possible Values for XMLPlanEnabled, StatsUsageEnabled, VerboseEnabled, and DetailedStatsEnabled” on page 263.

Possible Values for XMLPlanEnabled, StatsUsageEnabled, VerboseEnabled, and DetailedStatsEnabled

For these columns, a value of Y or N can be specified. If you specify Y, the [Option], where Option represents the STATSUSAGE or XMLPlan, was enabled when the data in XMLTextInfo was logged.

Example  

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  

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

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

Example  

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  

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

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

Example  

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';

For more information about the DBQL logging options, see Database Administration.