Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 162 163 164 165 166 167 < 168 > 169 170 171 172 173 174 .. 218 >> Next


You can also input detail data for Baseline Profiling, Target Level Emulation, and Teradata client tools and utilities such as Meta Data Services, Teradata Manager, and Teradata Visual Explain. Client tools aid in analysis and present

Teradata RDBMS Database Administration

14 - 15 Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Reviewing or Ending Current Rules

the information in a graphic form that is easily manipulated and understood. For more information, see

Chapter 13: "Database Analysis"

Teradata Visual Explain User Guide

Logging Volume per User and/or Account

Once logging has begun for a user or account, rows continue to be generated during every session until you end logging for that user or account.

Possible user/account combinations are:

One specific user (no specific account). This defaults to all accounts defined for that user, which means that every query run during every session initiated by this user will be logged.

One specific user with one or more specific accounts. (NCR recommends a maximum of 100 user/account pairs in a single BEGIN QUERY LOGGING statement. Use several statements to log several hundred users.)

A list of specific users.

ON ALL users with a list of specific accounts. Any user who logs on using one of the specified accounts will be logged.

ON ALL users (no specific accounts). Avoid logging all users, because it logs every query of every session to the DBQLogTbl. You can reduce the amount of rows generated by using the LIMIT THRESHOLD option, but typically all users is only appropriate in special testing environments where growth can be kept to a minimum

Caution: The results of the ON ALL (users) option can fill up the DBQLogTbl very

rapidly. DBQL rows consume PERM space in database DBC and remain until you explicitly delete them (see "Purging the System Logs" on page 4-16). Be aware that DBC space is required by other system tables and also by your Transient Journal (see "Transient Journal (TJ) and DBC Space" on page 7-30).

When a BEGIN QUERY LOGGING statement is processed, a row is placed in the DBQLRuleTble for each user and/or account string specified (or all users if none is specified) . When the logging status of a user is verified, an entry is placed in the rules cache.

Logging Overhead

Before you enable logging, give some thought to how much and what sort of data you need for adequate analysis. The more information you ask for and the more users you track, the higher the potential cost to performance and the faster the logs will grow. However, you can specify summary and threshold limits to obtain meaningful data with minimal cost.

14 - 16

Teradata RDBMS Database Administration 14 - 15 Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Reviewing or Ending Current Rules

The overhead incurred per user and/or account is illustrated in the following examples:

IF the collection type is . THEN logging is per . AND overhead entails . IN this log .
default (no options specified) query one default row per query DBQLogTbl
individual, because the query ran longer than THRESHOLD seconds long-running query one default row per long-running query
counts of queries that completed within THRESHOLD seconds or a SUMMARY response interval session when the query count is >0, one row per response time interval per logging interval (every 10 minutes). Possible maximum is four rows every 10 minutes for the duration of the session DBQLSummaryTbl
process detail query one row for each step generated DBQLStepTbl
object detail query one row for each object used to resolve the query DBQLObjTbl
request detail query as many rows as it takes to capture the complete text of the SQL request DBQLSQLTbl

Examples of Logging Results

Examples of BEGIN QUERY LOGGING options and the logged results are as follows:

IF you define accounts as follows . AND you submit the following statements . THEN logging results are .
MODIFY PROFILE WebUsers AS ACCOUNT='WebQry&D&H'; BEGIN QUERY LOGGING WITH OBJECTS ON ALL ACCOUNT=('WebQry&D&H'); A row in DBQLObjectTbl for each object for each query during each 'WebQry&D&H' session.
MODIFY PROFILE TactUsers AS ACCOUNT='$HTactQry&D&H'; BEGIN QUERY LOGGING WITH STEPINFO ON ALL ACCOUNT=('$HTactQry&D&H'); A row in DBQLStepTbl for each step of each query during each '$HTactQry&D&H' session
MODIFY PROFILE StratUsers AS ACCOUNT='$LStratQry&D&H'; BEGIN QUERY LOGGING LIMIT THRESHOLD=3 ON ALL ACCOUNT=('$LStratQry&D&H'); For each '$LStratQry&D&H' session: - One row of count data in DBQLSummaryTbl for all queries that completed in less than three seconds (within the 10-minute logging intervals) - For each query that ran longer than three seconds, one row of default data in DBQLogTbl

Teradata RDBMS Database Administration

14 - 15 Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Reviewing or Ending Current Rules
Previous << 1 .. 162 163 164 165 166 167 < 168 > 169 170 171 172 173 174 .. 218 >> Next