Oracle query execution time

Question

I would like to get the query execution time in Oracle  I don t want the time Oracle needs to print the results - just the execution time   In MySQL it is easy to get the execution time from the shell   How can I do this in SQL Plus

User · Answer

I d recommend looking at consistent gets logical reads as a better proxy for  work  than run time  The run time can be skewed by what else is happening on the database server  how much stuff is in the cache etc   But if you REALLY want SQL executing time  the V SQL view has both CPU TIME and ELAPSED TIME

User · Answer

Use   set serveroutput on variable n number exec  n    dbms utility get time  select        exec dbms output put line   dbms utility get time- n  100       seconds           Or possibly   SET TIMING ON   -- do stuff  SET TIMING OFF       to get the hundredths of seconds that elapsed   In either case  time elapsed can be impacted by server load etc   Reference    ASKTOM - SET TIMING ON OFF

User · Answer

One can issue the SQL Plus command SET TIMING ON to get wall-clock times  but one can t take  for example  fetch time out of that trivially   The AUTOTRACE setting  when used as SET AUTOTRACE TRACEONLY will suppress output  but still perform all of the work to satisfy the query and send the results back to SQL Plus  which will suppress it   Lastly  one can trace the SQL Plus session  and manually calculate the time spent waiting on events which are client waits  such as  SQL Net message to client    SQL Net message from client

User · Answer

select LAST LOAD TIME  ELAPSED TIME  MODULE  SQL TEXT elapsed from v sql   order by LAST LOAD TIME desc   More complicated example  don t forget to delete or to substitute PATTERN    select   from     select LAST LOAD TIME  to char ELAPSED TIME 1000   999 999 999 000        ms  as TIME           MODULE  SQL TEXT from SYS  V   SQL      where SQL TEXT like   PATTERN       order by LAST LOAD TIME desc     where ROWNUM  lt   5

[oracle] Oracle query execution time

Examples related to oracle

Examples related to sqlplus

Examples related to execution