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 .. 158 159 160 161 162 163 < 164 > 165 166 167 168 169 170 .. 218 >> Next

LIMIT THRESHOLD THRESHOLD also is useful for short, high-volume queries, but in addition to incrementing a count for qualifying queries, THRESHOLD logs a default row for any query that exceeds the specified time. This enables you examine the processing steps and the query structure. You can combine THRESHOLD with SQLTEXT if you want to capture more than the first 200 characters of a query that runs longer than THRESHOLD seconds (because the SQL text of short queries is not logged in DBQLogTbl). Note: NCR recommends you do not request the [WITH] SQL, OBJECT, or STEPINFO detail in combination with LIMIT THRESHOLD. You define the threshold of execution time, in seconds, which determines whether to log a query or just count it, as follows:
IF a query . THEN DBQL .
completes at or under the threshold time Increments the query count and the query seconds Stores the final count for the session as a row in DBQLSummaryTbl In the summary row, sets the value in the LowHist field to the THRESHOLD time and in the HighHist field to 0 (to identify it as a THRESHOLD row)
runs beyond the threshold time logs a default row for the query in DBQLogTbl so you can examine its structure and the number and level of processing steps.


14 - 2

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

The DBQL Components

The DBQL Components

The DBQL logs are a series of system tables created in database DBC during the Teradata RDBMS installation process. The suite of DBQL components includes a security macro and a view for each table, which are created in database DBC by the DIP utility during installation. (For details on running DIP scripts, see "Database Initialization Program (DIP)" on page E-2.)

The purpose of each object is as follows:

THIS dictionary object . IS a . THAT is used to . AND is created by .
DBQLogTbl table store default rows. Tableinit (invoked by the Sysinit utility), during installation. Note: Because Sysinit clears all data and reformats
DBQLObjTbl table store information on the target objects of the query being logged. One row is logged for each object referenced in the query.
DBQLRuleCountTbl table store the cardinality of DBQLRuleTbl (for internal use only). the disks, do not run it after data rows are loaded.
DBQLRuleTbl table store the rules resulting from each BEGIN QUERY LOGGING statement. One row exists for each set of specifications, which are made up of user and/or account plus any options or limits set for the user.
DBQLSQLTbl table store the full SQL text of the query. One query string may require more than one row.
DBQLStepTbl table store information about each processing step used to satisfy the query. One row is logged for each step.
DBQLSummaryTbl table stores queries that meet the criteria for a rule specifying the SUMMARY or THRESHOLD option.

14 - 2 Teradata RDBMS Database Administration

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

The DBQL Components

THIS dictionary object . IS a . THAT is used to . AND is created by .
DBQLAccessMacro macro (empty) control authority of users to execute the Teradata SQL BEGIN/END QUERY LOGGING statements. the DIP utility, when it runs the DIPVIEWS script.
DBQLRules view display the current rules in DBC.DBCQLRuleTbl (to a user with DBC or SystemFE privileges).
QryLog view access the DBQLogTbl
QryLogObjects view access the DBQLObjTbl
QryLogSummary view access the DBQLSummaryTbl
QryLogSteps view access the DBQLStepTbl
QryLogSQL view access the DBQLSQLTbl

14 - 2

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

Logging Implementation and Validation

The DBQL Logging Fields and Values

The fields available for population by DBQL logging are detailed in the rest of this section.

DBQLogTbl

When you do not specify a LIMIT option, one default row of query-level information is logged in DBQLogTbl for each query processed during a session that is initiated by any user for whom a query logging rule exists.

Default rows are stored in DBQLogTbl, the foundation of the DBQL feature.

If you specify options that result in more detailed information, a default row is still generated in DBQLogTbl (except with the SUMMARY option or a query that completes within the limit specified with the THRESHOLD option), plus one or more additional logs are populated with one or more additional rows.

The Default Row

The fields of the default row provide general query information that is usually adequate for investigating a query that is interfering with performance. When no options are specified, a default row includes at least the:

User name under which the session being logged was initiated

Unique ID for the process, session, and host (client) connection

Account string, expanded as appropriate, that was current when the query completed
Previous << 1 .. 158 159 160 161 162 163 < 164 > 165 166 167 168 169 170 .. 218 >> Next