Oracle – How to Measure Performance of a Single SQL Execution

oracle

I need to measure the duration of individual sql executions in Oracle DB.

I have the following query but it has two major problems.

SELECT
   instance_name,   host_name,
sql_id, sql_fulltext, plan_hash_value,
    CASE
        WHEN executions > 0
        THEN ROUND(elapsed_time/executions,3)
        ELSE NULL
    END elap_per_exec,
    elapsed_time,
    executions,
    PHYSICAL_READ_BYTES/1024 read_kb,
    buffer_gets,
    rows_processed
FROM v$sqlarea,user_users 
CROSS JOIN (SELECT instance_name,host_name FROM v$instance) 
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5

  1. It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable

  2. The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.

Is the any way to get the performance for a single execution?

Best Answer

Generally not possible. You will have to time the execution of a new instance of that statement.

If you are lucky and the statement was running long enough to be sampled by ASH it should however show up in dba_hist_active_sess_history or for very recent invocations V$ACTIVE_SESSION_HISTORY.

It should show CPU time separate from DB time.

Related Question