Stats Manager and query logging options

Viewpoint

Stats Manager and query logging options

I'm trying to make use of the STATSUSAGE and XMLPLAN features of query logging for Stats manager. Do these options need to be turned on to see statistics that are being used and statistics that are missing? I'm guessing the answer to my question is a resounding yes. I got confused after reading this document: Automated Statistics Management Teradata 14.10 2013-06, section 9.2, where it states:

 

Analyze logged queries from the Performance Data Collection and Reporting (PDCR) tool to focus the analysis on problematic queries that are in need of performance tuning. Analyze Jobs can specify the Log database as ‘PDCRDATA’, in which case the analysis will read the query log data generated by the PDCR tool. This can be very useful in those cases where PDCR was previously used to identify and isolate ‘bad’ queries.

 

If we're using PDCRDATA does that eliminate the need to turn on STATSUSAGE and XMLPLAN or do you have to turn these options on if you want to be able to see statistics that are being used and statistics that are missing?

4 REPLIES
Teradata Employee

Re: Stats Manager and query logging options

PDCR is the archiving mechanism for DBQL. It is designed to be a place to store the DBQL data for analysis rather than keeping it in the DBC tables. The data needs to be logged by DBQL per the DBQL logging options in order to then get copied to PDCR.
Teradata Employee

Re: Stats Manager and query logging options

Both the loggings (StatsUsage, XMLPlan) in addition to UseCount are required for Stats Manager's Analyze Job. The Analyze Job reviews the metadata information based on the job's setting & provide the recommendations for Stats to be refreshed, collected & discarded. The metadata information can be in DBC or PDCRData, as most Customer move the data from DBC to PDCRData. While setting an Analyze Job, you can specify the metadata information (DBQL) source database/range of DBQL to be reviewed & other additional setting. The Viewpoint User Guide offers additional information concerning the Stats Manager Portlet.

Re: Stats Manager and query logging options

Thanks, Todd!

Re: Stats Manager and query logging options

Thanks, SmarakDas!