The objects section tells which tables and indexes the session waited on. In the console, each object opens a page with ready-made queries for you to run on the trace's database and get to know the object: owner, columns, indexes and statistics.
What the section shows
The Objects with the most wait time section (in the index, Objects) lists the objects that added up the most wait time, from most to least. The object comes from the obj# of the WAIT lines; the name comes from the STAT lines of the trace itself, when the object appears in a plan. The section appears only when some wait carries obj#.
How to read it
| Column | What it says |
|---|---|
| obj# | The object_id of the object in the trace's database. In the console, it leads to the object page; in the downloaded HTML, to the object detail in the file itself, with the type, the wait and where the object appears in the trace, without the ready-made queries. |
| Name | The name the trace's plan gives the object. Blank when the object does not appear in any plan. |
| Wait (s) | The sum of the waits with that obj#. |
| Occurrences | How many waits there were. |
| Top events | The events that added up the most time on the object, from most to least. |
Read the object together with the events: reads (db file sequential read, db file scattered read) point to how the plan accesses the object; locks and contention (enq: TX - row lock contention, buffer busy waits) point to other sessions using the same object.
In the sample report, object 50, table T, adds up 0.001 s in 2 db file sequential read reads.
The object page
In the console, the object page opens from the obj# in this section, from the obj column of the plan and from the object_id in the deadlocks. The title is Object N with the name, and the line below gives the type (table, index, view or sequence, from the plan operation) and on which database to run the queries: the instance, the container (PDB) and the service of the trace. The queries use the DBA_ views; without that privilege, switch to ALL_.
When the trace does not give the type, a warning asks you to start with the identification query, because the table and index queries return rows only for the matching type. The Where it appears in the trace card lists the plan operations that use the object, such as Statement #1, operation 1, with a link to the plan row.
| Block | Appears for | What it returns |
|---|---|---|
| Identify the object | every object | Owner, name, type and status, from dba_objects. |
| Table: owner, name and columns | table or unknown type | The columns, with the statistics of each one. |
| Table: indexes and their columns | table or unknown type | The indexes of the table, with the columns in index order. |
| Index: details, table and columns | index or unknown type | Height, leaf blocks, clustering factor and columns. |
| View: columns and text | view | The columns and the text of the view. |
| Sequence: cache and last number | sequence | The cache and the last number of the sequence. |
Each block has the Copy button. The first query, with object 50 from the sample, is this one:
select owner, object_name, subobject_name, object_type, status, created, last_ddl_time
from dba_objects
where object_id = 50;For tables and indexes, the Statistics section has blocks to refresh the statistics and to restore them from the backup: Refresh table statistics, Restore table statistics from backup and the two index equivalents.
- Before gathering, the block saves the current statistics in
SYSTEM.JB_BACKUP_STATSand shows the backupstatid. The restore block uses thatstatid. - The gathering uses
no_invalidate => FALSEand runs with degree 1. Changec_paralelotoTRUEto use more processes, on Enterprise Edition only. - Run as SYS or as a user with
ANALYZE ANYand access to the data dictionary.
The object_id belongs to the trace's database: in another database, the same number is a different object. When the trace has the name, the statistics block stops with an error if the name in the database is different. The gathering reads the whole object and invalidates the cursors that use it, which then hard parse again: pick the time window carefully in production.
From the PL/SQL stack of a deadlock, the same page opens for the PL/SQL object, with Object details (without the body), Objects it uses, Parameters, Object body and the excerpt from 10 lines before to 10 lines after the stack line.
When it is a concern
- One object concentrates most of the wait: that is where the plan or the concurrency weighs. Go to the operations that use it, through the Where it appears in the trace card.
- Many reads on an index or a large table: check whether the statement's filter is selective and whether the index has the right columns, with the table and index queries.
- Lock waits on an object: another session holds rows or the table (lock held by another session).
- Old or missing statistics in the table query, with a wrong estimate in the plan: candidates for a refresh (wrong row estimate).
Where to go next
- Objects in the sample report.
- Execution plan, to see how the statement accesses the object.
- Reads, for the time and the rereads of the blocks.