How to log the slow queries in Oracle

Question

I have to log slow queries , let's say queries which takes more than 3ms to run should be logged Most of the times these queries are SELECT how to log them in Oracle ?

Answer 1

Within application code you can do something like this:

v_start_time := dbms_utility.get_time();
-- Your query here
-- ...
v_end_time := dbms_utility.get_time();
if v_end_time - v_start_time > v_max_time then
   insert into log_table ...;
end if;

dbms_utility.get_time returns the time in 1/100ths of seconds - so you can't use this for 3ms accuracy. You could perhaps use SYSTIMESTAMP instead.

Realistically, I wouldn't log any query that took less than half a second, you'd be logging virtually everything.

Answer 2

If you are using AWR this will be logged from ASH, you will just need to mine it out, either using OEM or with hand-written SQL.

3ms is very quick indeed, a 5-20ms response time is good in Oracle on modern hardware, all things considered.