Object usage
When SQL statements are executed, they use various database objects, such as tables and indexes. Knowing which database objects a statement accesses and how the statement affects them can help you identify targets for monitoring or performance tuning.
The following table shows the entities that you can use to explore
the relationship between database objects and statements.
| Mechanism | Definition | Usage |
|---|---|---|
| Usage list | A usage list is a database object that records each DML statement section that references a particular table or index and captures statistics about that section as it executes. | Identify the statements that affected a table or index. If you notice an unusual value for a metric when monitoring a database object, use a usage list to determine whether a particular statement contributed to that metric. You can also view statistics for each statement that affected the object. |
| Section explain with actuals | A section explain is a set of information about the access plan that the optimizer chose for an SQL statement. You can capture section actuals as part of the explain. Section actuals are runtime statistics that are collected when a section executes. | Identify the tables or indexes that a statement affects. You can view statistics for each table or index and use these statistics to determine how the statement affects each object and where tuning might be required. |
You can use the information in a usage list or section explain with actuals as baseline data for performance tuning. Collect information about object usage before tuning statements or database configuration parameters. After tuning, collect the information again to verify that tuning improved performance.