What to Capture
For any business-critical or frequently executed query, record the following:
| Metric | How to Get It (GV$SQL) |
|---|---|
| SQL_ID | SELECT sql_id FROM gv$sql WHERE sql_text LIKE '%...%' |
| Elapsed time (avg) | elapsed_time / executions |
| Rows processed | rows_processed / executions |
| Buffer gets | Logical I/O (work done) |
| Disk reads | Physical I/O (slower) |
| Plan hash value | Unique ID for execution plan |
If the query is already running, use DBMS_SQL_MONITOR or
DBMS_XPLAN.DISPLAY_CURSOR to capture real-time stats.
A SQL ID is a unique identifier for a SQL statement in Oracle. It is constructed by hashing the text of the SQL statement.
The SQL ID is generated using a hash function that takes the SQL statement text as input. The resulting hash value is then converted to a 13-character string, which is the SQL ID.
The hashing algorithm used to generate the SQL ID is not publicly documented, but it is designed to produce a unique identifier for each distinct SQL statement.
Here are some key points to note about SQL IDs:
- Case sensitivity: SQL IDs are case-sensitive, so the same SQL statement with different casing will produce different SQL IDs.
- Whitespace and formatting: SQL IDs are sensitive to whitespace and formatting, so the same SQL statement with different formatting will produce different SQL IDs.
- Literal values: SQL IDs are sensitive to literal values, so the same SQL statement with different literal values will produce different SQL IDs.
- Bind variables: If a SQL statement uses bind variables, the SQL ID will be the same regardless of the values bound to the variables.