Estimating Query Execution Time in Oracle

 As an Oracle database administrator or developer, running heavy, long-running, and critical production queries can be nerve-wracking, especially in cloud environments. One crucial aspect is estimating the query execution time to plan and manage resources effectively. In this post, we'll explore a valuable query that provides an approximate ETA (Estimated Time of Arrival) for parallel queries on large datasets.

Knowing the ETA helps:

• Plan resource allocation and utilization
• Manage expectations and prioritize tasks
• Identify potential performance bottlenecks
• Optimize queries for better performance

ETA Query:

-- Set column widths for better readability

col sid for 999999

col QC_SID for 999999

col QC_INST for 9

col username for a10

col operation_name for a20

col target for a20

col units for a10

col start_time for a18

-- Main query

SELECT

  px.sid,

  CASE

    WHEN px.qcinst_id IS NULL THEN username

    ELSE username || ' - ' || LOWER(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME) - 4, 4))

  END AS "Username",

 

  SUBSTR(opname, 1, 30) AS operation_name,

  SUBSTR(target, 1, 30) AS target,

  sofar,

  totalwork,

  ROUND(sofar / totalwork * 100) AS pct_done,

  units,

  start_time,

  ROUND(totalwork / (sofar / ((SYSDATE - start_time) * 1440))) AS eta_min,

  CASE

    WHEN px.qcinst_id IS NULL THEN s.sid

    ELSE px.qcsid

  END AS QC_SID,

  px.qcinst_id AS QC_INST

FROM

  gv$px_session px,

  gv$px_process pp,

  gv$session_longops s

WHERE

  px.sid = s.sid

  AND px.serial# = s.serial#

  AND px.inst_id = s.inst_id

  AND px.sid = pp.sid (+)

  AND px.serial# = pp.serial# (+)

  AND sofar <> totalwork

ORDER BY

  CASE

    WHEN px.QCINST_ID IS NULL THEN px.INST_ID

    ELSE px.QCINST_ID

  END,

  px.QCSID,

  CASE

    WHEN px.SERVER_GROUP IS NULL THEN 0

    ELSE px.SERVER_GROUP

  END,

  px.SERVER_SET,

  px.INST_ID;

This query joins three dynamic performance views:

• gv$px_session (parallel execution sessions)
• gv$px_process (parallel execution processes)
• gv$session_longops (long-running operations)

It calculates:

• eta_min: estimated time to completion in minutes
• pct_done: percentage of work completed

To customize the query:

• Filter by specific username or operation name
• Add additional columns for more detailed information
• Use gv$session instead of gv$px_session for non-parallel queries

By using this query, you'll gain valuable insights into your critical production queries and make informed decisions about resource allocation and optimization.