CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
Maddy
Maddy

Reputation: 3816

Calculate Actual execution of query in oracle

Could someone please suggest how can we calculate the actual execution of the query. With out IO operations.

currently, I am using following approch:

variable n number
exec :n := dbms_utility.get_time
PROMPT The time consumed with Function Call
SET SERVEROUTPUT ON;
variable n number
exec :n := dbms_utility.get_time
set term off;

  select   -- My Select Query

SET TERM ON;
exec dbms_output.put_line( dbms_utility.get_time - :n )

Is this the correct way of calculating the query execution time?

Upvotes: 0

Views: 109

Answers (1)

Nickolay Komar
Nickolay Komar

Reputation: 340

I suppose you to use SQLTrace.

before running you query:

     ALTER SESSION SET sql_trace = true;
     ALTER SESSION SET tracefile_identifier = [sqltrace_identifier_which_will_be_included_into_output_filename];

then run your query and set trace back to OFF:

     ALTER SESSION SET sql_trace = false;

now you have a trace file generated. Look for trace file in USER_DUMP_DEST folder. But it is not human-friendly. Use tkprof utility to convert it to readable format (replace "input.trc" with your file name. your filename contains identifier you set into tracefile_identifier parameter):

     tkprof input.trc output.prf

Read output.prf file and enjoy :)

Some detailed explanations:

http://www.orafaq.com/wiki/SQL_Trace http://www.orafaq.com/wiki/TKProf

Upvotes: 1

Related Questions