SQL Performance (MOSC)

MOSC Banner

how to get timing for a single execution of a specific SQL

edited Sep 9, 2015 10:00AM in SQL Performance (MOSC) 2 commentsAnswered

Hi. I am running oracle version 11.2.0.4 and working on researching some application timeouts from the DB perspective. I have turned on database tracing with level 12 and that generated multiple tracefiles while the timeouts have been ocurring. When I run a tkprof

tkprof p_ora_56659.trc p_ora_56659.log explain=/sys=no waits=yes aggregate=no

it created an output file which included the following data for one of the problematic SQLs

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        7      0.00       0.00          0          0          0           0

Execute   1547      0.02       0.02          0          0          0           0

Fetch    29376      0.67       0.70          0      29376          0      287576

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    30930      0.69       0.73          0      29376          0      287576

So it looks like 1547 executions which overall took 0.73 sec. What I am looking for however is the time of each of the 1547 executions. I was expecting to see 1547 EXEC lines for this particular SQL in the trace files, but only found one line.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center