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