How to log the slow queries in Oracle

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

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.

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.

If you want to log slow queries, then the solution given by Tony is the good one. But if you cannot modify the source code of your applicaiton, you may consider installing an sql spy. I never did it but can be done easily. Note that it will slow down your application : if you are troobleshooting a production server then it is not the good solution.

IMHO the best solution is not to log slow queries but to find them (the difference is subtle ... :) ). Oracle provides plenty of tools to find this kind of information :

• AWR report is my prefered tool. You will find a paragraph named "SQL ordered by Elapsed Time", which is basicaly what you are looking at
• gridcontrol give you this information too, but I don't know this tool much. it is simpler to use than AWR report
• if you don't have access to one of these tools, you can try simply launching the SQL given in this other stackoverflow post : Top 5 time-consuming SQL queries in Oracle