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 .. 161 162 163 164 165 166 < 167 > 168 169 170 171 172 173 .. 218 >> Next


DBQL behavior resulting from the SUMMARY option is unique in that:

No default rows are generated to DBQLogTbl for summarized queries

For each query submitted during a logged session, one of the summary counters is incremented

Summary counters are maintained in cache. The contents are committed to DBQLSummaryTbl every 10 minutes, when the cache is flushed

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

Logging Implementation and Validation

SUMMARY maintains a count of the number of queries in a session that complete within one of the time intervals you specified. For example, assume you defined SUMMARY as 5, 10, 15 (for the time intervals 0-5, 5-10, 10-15, and >15) for a user. Four intervals are possible:

0 to 5 seconds

Longer than 5 seconds to 10 seconds

Longer than 10 seconds to 15 seconds

Longer than 15 seconds

If, during that user's next logging period, two queries ran under 5 seconds, three queries ran for 7 seconds, and one query ran for over 15 seconds, the following rows would be written to DBQLSummaryTbl for the session:

COUNT SECONDS LOWHIST HIGHHIST

2 1 0 5

3 21 5 10 1 200 15 32767

For this example, there were:

No queries between 10 and 15 seconds

To determine the average time for each query counted, divide SECONDS by COUNT (for example, the two queries in the first row averaged 0.5 seconds each; the three queries in the second row averaged 7 seconds each).

Row Values

The fields available for population in DBQLSummaryTbl are as follows:

DBQLSummaryTbl Field Description
ProcID Unique processor ID of the dispatcher and part of the multi-column NUPI (also see CollectTimeStamp).
CollectTimeStamp Time the row was inserted and part of the multi-column NUPI (also see ProcID).
UserID The unique ID of the user whose query is logged.
AcctString The account string at the time the row was committed. Because accounts can be changed dynamically at the query or session level (see "SET SESSION ACCOUNT" under "Changing the Performance Group of a Running Job" on page 5-23), this may not be the same account that: DBQL verified for the user at logon time Was in effect when processing of this query began
SessionID Identifies the session for which queries are being summarized (counted).

14 - 10 Teradata RDBMS Database Administration

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

Logging Implementation and Validation

DBQLSummaryTbl Field Description
QueryCount Number of queries run in the last 10 minutes.
QuerySeconds Total run time of queries in this period for this histogram.
LowHist Low value of the query time limit for the SUMMARY option Equal to the THRESHOLD time for the THRESHOLD option (the default THRESHOLD time is 5)
HighHist High value for the SUMMARY option 0 (zero) for the THRESHOLD option

14 - 10

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

Reviewing or Ending Current Rules

Defining the Rules

To initiate logging, you must specify at least one user and/or an account in a BEGIN QUERY LOGGING statement. Each unique name or name pair creates a rule. You can specify options or not. If you do not, one default row will be logged per query. If you do, the tables that will be populated depend on your choices. You can specify that the recording criteria be a mix of:

Users and/or accounts

Elapsed time, where time can be expressed as:

A series of intervals

A threshold limit

Processing detail, including any or all:

Objects

Steps

SQL text

Applying the Logged Data

First-level information is captured in DBQLogTbl and DBQLSummaryTbl. For short-running, high-volume queries such as tactical ODS transactions, you can request the THRESHOLD or SUMMARY option to reduce collection overhead.

You can use this data to identify issues such as workloads that do not meet response time service-level agreements. If you suspect a particular query (for example, a query exceeded the THRESHOLD limit), resubmit it while logging with the WITH SQL option to capture the full SQL text. Then you can replay it while logging WITH OBJECT detail and/or WITH STEPINFO detail (each time setting SQLTEXT=0).

When used selectively, detail data can be invaluable. You can analyze it to:

Optimize the query structure or your query management or priority strategy; for example, by comparing the:

Results of different queries that target the same join index

Elapsed times of the same query run on different days or hours

Ascertain reasons for high consumption or poor performance by correlating DBQL data to other data collections with query characterization, including QCF, Performance Monitor, ResUsage, and DBC.AMPUsage.

Make efficient use of existing capacity and plan for future expansion by factoring in exponential growth of query demand
Previous << 1 .. 161 162 163 164 165 166 < 167 > 168 169 170 171 172 173 .. 218 >> Next