Total time execution of a query

oracle

I`m trying to obtain the total time of a query execution

I have this query but not allways get the output that I hope, For example for the same parallel 4 execution of a query sometimes I obtain 5 rows (query controller and 4 parallels) but other times I only obtain 2 parallel process result (the parallels processes only open in the same instance)

Is this query correct or I need to add something more?

select inst_id, sql_exec_id,session_id, QC_SESSION_ID,program,sql_id,sql_plan_hash_value,round((count(*))/60/60, 2) as Horas 
from    gv$active_session_history ash
where   sql_id='7u0wv0g5r3y2n' 
and sql_exec_id is not null       
group by    inst_id, sql_exec_id,session_id,QC_SESSION_ID,program,sql_id,sql_plan_hash_value      
order by    2,1,4,5 desc;

Best Answer

I use set events 10046, for the explain plan and 10053 for the waits. Once you run the queries that you want timings for, assuming that you are using 10g/11g/12c go to the diag trace folder, find your trace files and run tkprof against the trace files.

ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

/*
run your queries here
*/

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';


go to the <diag>/rdbms/<sidname>/<sidname>/trace directory
tkprof <in file name> <out file name> waits=yes explain=syste/<pwd>

This will give really good information about how the queries ran, how long, how much logical/physical IO, explain plan and waits. Plus you don't need any optional components to get the information.

-- use logging package to log and not reraise errors
CREATE OR REPLACE TRIGGER application_logon_trace
       AFTER LOGON ON DATABASE
DECLARE
   v_db_user VARCHAR2(64) := SYS_CONTEXT ('SESSION_USERID', 'PROXY_USER' );
   v_host    VARCHAR2(64) := SYS_CONTEXT ('USERENV',        'HOST'    );
   v_ddl_cmd VARCHAR2(2000);
BEGIN
-- You can have an IF clause that will only turn on logging for certain users if you like
-- or you can only log sessions coming from certain servers/pcs if you want.
   IF INSTR(v_host, '.') > 0
   THEN
      v_host := SUBSTR( v_host, 1, INSTR(v_host, '.')-1);
   END IF;

   v_ddl_cmd := 'ALTER SESSION SET tracefile_identifier = ''application_logon_trace_'|| v_db_user || '_' || v_host ||'''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10046 trace name context forever, level 12''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10053 trace name context forever, level  1''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;
EXCEPTION WHEN OTHERS THEN NULL; -- feel free to call you favorite error logging package here.
END;
/
Related Question