DBQL can be used to:
- Capture query/statement counts and response times. You can look in the DBQL logs to see if this is the first time this query has been broken and what the historical pattern of its execution has been.
- Validate that nonunique secondary indexes (NUSIs) are used. You can free up space and improve table update performance by deleting NUSIs.
- Determine which aggregate join indexes (AJIs) are used the most. This allows you to make a cost-benefit analysis on their AJIs, comparing usage against size and time to build.
- Analyze SQL text and process steps. View the SQL text that inadvertently wiped out the system using DBQL historical data and then try to rebuild the lost data.
- Log optimizer queries as XML documents.
- Make further refinements to workload groups and scheduling.
- Discover potential application improvements.
- Understand basic workload trends, such as growth patterns and workload mix, and identify outliers.
- Determine whether the query has redrive protection and if the request was redriven after a database failure.
DBQL provides a series of predefined tables and views that store historical records of queries and their duration, performance, and target activity based on rules you specify. See Monitor Queries for details about the DBQL tables, which are stored in the td_metric_svc database.
DBQL is flexible enough to log information on the variety of SQL requests, from short transactions to longer-running analysis and mining queries that run on DBQL can log information for an application name, a user or group of users, or a list of accounts.