QUERY  LOGGING IN TERADATA

Share via:
QUERY  LOGGING

Dear Readers,

In this article, we will discuss QUERY  LOGGING

What is query logging in teradata database and how it is implemented?

  • Database Query Logging (DBQL) is a feature in Teradata that logs data in the predefined tables created under               DBC during Teradata Database installation.
  • Starting with V2R5, this feature logs processing behaviour of database by logging user activity.
  • It helps you to analyze workload, resource usage and perform query performance tuning based on theanalysis of         data present in these tables.
  • These tables are not populated automatically, a user controls whether the logging should be enabled or not.
  • If you use not to use the feature, these tables remain empty.
  • Depending how detailed data a user wants to log, he can choose the various logging options available.
  • The privileges required by a user to execute “BEGIN QUERY LOGGING” is :
  • EXECUTE privilege on DBC.DBQLAccessMacro.
  • To use the MODE option, EXECUTE privilege on DBC.DBQLModeMacro.

How to enable query logging ?

Query logging is controlled by the following commands :

Logging can be enabled at various levels :

All users or specific users

Accountname

A database

Note : Only USECOUNT option is allowed with databases

If you try to use any other option other than USECOUNT on a database, it will throw an error.

An application

WITH keyword

It is a keyword which is used in specifying logging options.

Example :

The logging options available are :

    • ALL
    • NONE
    • EXPLAIN
    • OBJECTS
    • SQL
    • PARAMINFO
    • FEATUREINFO
    • STATSUSAGE (Detailed)
    • STEPINFO
    • USECOUNT
    • UTILITYINFO
    • XMLPLAN (Verbose)

Let’s understand these options in a little brief :

ALL                    : logs data in DBC.DBQLOGTBL  + EXPLAIN, OBJECTS, SQL and STEPINFO options.

Example            :

The above SQL logs default rows for all users, applications and accounts.

NONE                : Turn off query logging for the items you specify.

If you specify WITH NONE, you cannot specify additional options. To remove the WITH NONE setting,

you must use the corresponding END QUERYLOGGING statement.

EXPLAIN           : logs data in DBC.DBQLOGTBL + Explain text for the request is logged.

You cannot specify the EXPLAIN option with the SUMMARY or THRESHOLD options.

OBJECTS          : logs data in DBC.DBQLOGTBL + Database, table, column, and index information is logged.

You can specify this option with SUMMARY or THRESHOLD.

SQL                    :  logs data in DBC.DBQLOGTBL + full text of all SQL statements.

THRESHOLD option can be used with this option.

PARAMINFO    : logs data in DBC.DBQLOGTBL +Parameter values and metadata are logged in DBQLParamTbl.

FEATUREINFO : Log feature usage information into the FeatureUsage column of DBQLogTbl

STATSUSAGE  : logs data in DBC.DBQLOGTBL + Optimizer statistics and usage recommendations.

If you also specify XMLPLAN, the data from STATSUSAGE and XMLPLAN is logged in one XML document.

DETAILED STATUSAGE : logs in DBC.DBQLOGTBL + the following statistics details for allthe database objects

referenced in the plan for arequest

  • StatTimeStamp
  • Version
  • OrigVersion
  • NumColumns
  • NumBValues
  • NumEHIntervals
  • NumHistoryRecords
  • NumNulls
  • NumAllNulls
  • NumAMPs
  • NumPNullDistinctVals
  • PNullHighModeFreq
  • AvgAmpRPV
  • HighModeFreq
  • NumDistinctVals
  • NumRows
  • CPUUsage
  • IOUsage

STEPINFO                    : logs data in DBC.DBQLOGTBL + AMP step-level information.

This option can be used with THRESHOLD.

USECOUNT                 : logs data in DBC.DBQLOGTBL + use count information onthe specified databases or users,

regardless of who is accessing the database objects.

If you specify USECOUNT for a:

  • user, you can specify any of the other logging options.
  • database, you cannot specify any other logging options.

Examples :

UTILITYINFO               : logs data in DBC.DBQLOGTBL + Utility information in DBC.DBQLUTILITYTBL.

XMLPLAN                    : logs data in DBC.DBQLOGTBL + plan is logged in XML format for DDL, DML and DCL requests.

VERBOSE XMLPLAN : logs data in DBC.DBQLOGTBL + VERBOSE EXPLAIN text in XML format.

The VERBOSE keyword is a modifier for the XMLPLAN option.

The LIMIT option :

As the name itself suggests, it sets a limit to the amount of information to be stored for query logging.

1.SQLTEXT =n                : Length of SQL text i.e number of SQL characters to capture

n is the number of characters. It can be between 0 and 10,000. Default is 200.

Example                          :

2.SUMMARY =n1,n2,n3 : Requests are grouped and logged by duration intervals or number of I/Os.

It does not log in DBC.DBQLOGTBL.

You can specify up-to 3 intervals and a fourth interval is created by default as anything longer than n3.

SUMMARY cannot be used with LIMIT THRESHOLD or logging options.

Example                           :

The above SQL logs the count of the number of requests performed by user KTEXPERTS in DBQLSUMMARYTBL in the following interval sets of:

  1. 0-1 second
  2. 1-3 seconds
  3. 3-7 seconds
  4. > 7 seconds

3.THRESHOLD=n           : Requests that complete in n or less are counted and queries that exceed n are logged.

Rows are generated in DBC.DBQLOGTBL depending on certain thresholds defined.

If the measure is below the threshold, only a summary row is written into

DBC.DBQLSUMMARYTBL.

Example           :

If the query completion time (in seconds) is <= 4, then the requests are recorded only as counts in the QueryCount column of DBC.DBQLSUMMARYTBL.

If the query completion time (in seconds) is> 4, then SQL text is logged in DBC.DBQLOGTBL.

Where are the rules stored ?

The logging rules are stored in the table DBC.DBQLRULETBL”

How to check for the rules that are active for a user ?

You can either query DBC.DBQLRULESV or use the below SQL to check the rules enabled :

Syntax :

Example :

Way 1 :

Way  2:

What happens when no logging is enabled ?

NO RULE FOUND

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment