Analyzing SQL Query Execution Time in Oracle Database Using SQL_ID
Performance tuning in Oracle databases requires analyzing SQL execution times to identify slow or inefficient queries. Oracle provides multiple ways to retrieve execution time for SQL statements using the SQL_ID. This article explores different methods to check execution time, including real-time monitoring, historical data analysis, and long-running query tracking.
1. Checking Execution Time from V$SQL (Recent Queries in Library Cache)
The V$SQL view stores details of recently executed SQL queries available in memory. Use the following query to check execution time:
SELECT sql_id,
elapsed_time / executions / 1e6 AS avg_exec_time_sec,
elapsed_time / 1e6 AS total_elapsed_time_sec,
executions
FROM v$sql
WHERE sql_id = '&your_sql_id';Understanding the Output:
- avg_exec_time_sec → Average execution time per run (in seconds)
- total_elapsed_time_sec → Total time spent executing this query
- executions → Number of times the SQL was executed
📌 Use this method when you need real-time performance data for active queries.
2. Checking Historical Execution Time from AWR (DBA_HIST_SQLSTAT)
If the query is no longer in V$SQL, use AWR snapshots to check historical execution time:
SELECT s.sql_id, TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snapshot_time, ROUND(SUM(s.elapsed_time_delta)/GREATEST(SUM(s.executions_delta), 1)/1e6, 3) AS avg_exec_time_sec, ROUND(SUM(s.elapsed_time_delta)/1e6, 3) AS total_elapsed_time_sec, SUM(s.executions_delta) AS executions FROM dba_hist_sqlstat s JOIN dba_hist_snapshot st ON s.snap_id = st.snap_id WHERE s.sql_id = '&your_sql_id' GROUP BY s.sql_id, st.begin_interval_time ORDER BY s.snapshot_time DESC;Understanding the Output:
- snapshot_time → The AWR snapshot timestamp (typically every 1 hour)
- avg_exec_time_sec → Average execution time per run (based on AWR data)
- total_elapsed_time_sec → Total execution time in that period
- executions → Number of times the SQL executed within the snapshot interval
📌 Use this method for analyzing SQL execution trends over time.
3. Checking SQL Execution Time from ASH (DBA_HIST_ACTIVE_SESS_HISTORY)
For queries that were running within Active Session History (ASH), use:
SELECT sql_id, COUNT(*) * 3600 / (SELECT snap_interval FROM dba_hist_wr_control) AS estimated_exec_time_sec FROM dba_hist_active_sess_history WHERE sql_id = 'your_sql_id' GROUP BY sql_id;Understanding the Output:
- estimated_exec_time_sec → Estimated execution time in seconds. If AWR snapshots occur every 1 hour, the count of rows where the SQL was active is multiplied by 3600 seconds to approximate execution duration.
📌 Use this when looking for SQL execution times within active session history, adjusting for snapshot intervals.
4. Checking Long-Running Queries from V$SQL_MONITOR (Real-Time Monitoring)
For long-running queries, use SQL Monitoring:
SELECT sql_id,
sql_exec_start,
elapsed_time / 1e6 AS elapsed_time_sec,
status
FROM v$sql_monitor
WHERE sql_id = '&your_sql_id';Understanding the Output:
- sql_exec_start → Start time of SQL execution
- elapsed_time_sec → Time spent on execution so far (in seconds)
- status → Current execution status (RUNNING, DONE, etc.)
📌 Use this for monitoring queries currently running in real time.
Choosing the Right Approach Based on Your Use Case
Post a Comment
Post a Comment