Teradata Dignostics commands

Diagnostic commands are undocumented features of Teradata. These commands come in handy to DBAs when dignosing performance problems. I use a couple of the Diagnostic commands regularly in troubleshooting  performance issues.

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION; 
The above command makes Teradata to include additional  info in the output of an explain command.  The output from all subsequent explain commands

• qualify Spool File on join columns in the explain output
• specify hash distribution column names for spool files.

To turn off additional information in the output of the explain command just issue the following command.

DIAGNOSTIC VERBOSEEXPLAIN NOT ON FOR SESSION; 

Below is another command that is quite helpful

DIAGNOSTIC HELPSTATS ON FOR SESSION;

The above command makes Teradata to include the collect statistics possibilities in the ouput of expplain command

To turn off collect statistics possibilities in the output of the explain command just issue the following command.

DIAGNOSTIC HELPSTATS NOT ON FOR SESSION;