Logging Overhead
Before you enable logging, first consider 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 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.
The following table lists examples that describe the overhead incurred per user and account.
|
IF the collection type is … |
THEN logging is per … |
AND overhead entails … |
IN the following log(s) … |
|
default (no options specified) |
query |
one default row per query |
DBQLogTbl |
|
individual, because the query ran longer than THRESHOLD seconds |
query that runs in more time, or used more CPU or I/O limits |
one default row per long-running query |
DBQLSqlTbl, DBQLStepTbl, DBQLObjTbl, and DBQLogTbl |
|
counts of queries that complete within SUMMARY intervals |
query that runs in less time, or used less CPU or I/O limits |
when the query count is >0, one row per logging interval (every 10 minutes). Possible maximum is four rows every 10 minutes for the duration of the session |
DBQLSummaryTbl |
|
steps process detail |
query |
one row for each step generated |
DBQLStepTbl |
|
object detail |
query |
one row for each object used to resolve the query |
DBQLObjTbl |
|
explain detail |
query |
as many rows as it takes to capture the complete explain text. The explain text is not formatted. Collecting explain detail has some performance impact. |
DBQLExplainTbl |
|
SQL text detail |
query |
as many rows as it takes to capture the complete text of the SQL request |
DBQLSQLTbl |
|
XML query plan detail |
query |
as many rows as it takes to capture the complete XML plan. The XML plan includes the SQL request and other things such as the EXPLAIN text Note: For more information, see SQL Request and Transaction Processing. |
DBQLXMLTbl |
|
XML lock logging |
lock of qualifying duration |
as many rows as it takes to capture the XML lock plan |
DBQLXMLLOCKTbl |