Usage lists

A usage list is a database object that records each DML statement section that references a particular table or index. A section is the executable form of the query. Statistics are captured for each statement section as it executes. Use usage lists when you want to determine which DML statements, if any, affected a table or index.

Data is collected in a usage list only when the usage list is active. Each entry in a usage list contains data for every DML statement that references the table or index for which the usage list was created. Each entry includes information about the number of times that the section executed and aggregate statistics indicating how the section affected the table or index across all executions.

Consider the following example of using usage lists. As part of routine monitoring, you see a high value for the rows_read monitor element for a specific table in the output for the MON_GET_TABLE table function. You can use a usage list on that table to identify which DML statements contributed to the high value. If you determine that a problem exists, you can use the statistics from the usage list to determine which specific statements might require further monitoring or tuning.

You can create more than one usage list for a table or index. However, activating more than one usage list at a time might negatively affect database performance and memory usage.