It’s been long time since my last post. Got busy in cricket season and some more interesting stuff ;). So I was holding back on some ideas I could share with you wonderful people.
This post will focus on determining long-running sql’s in your Oracle schema. My team has gone through some of the worst times in Oracle response time. So finally we were able to get access to some cool Oracle dictionary tables that could help identify time-consuming sql’s.
Below is the query that would return details of long-running sql’s:
SELECT s.username, s.schemaname, p.spid os_process_identifier, s.status, s.osuser, s.machine, s.program, s.state, sa.sql_fulltext, (sa.cpu_time /1000000) cpu_time_in_secs, (sa.elapsed_time/1000000) elapsed_time_in_secs FROM v$session s, v$sqlarea sa, v$process p WHERE s.sql_id = sa.sql_id AND s.paddr =p.addr ORDER BY sa.cpu_time DESC ;
You might not have privileges to access V$ tables. Bug you DBA until he/she gives in :P.
In Oracle 10g, you could also use below query to find long running operations. But I have a feeling, it keeps the record in the table even after query has finished execution.
SELECT * FROM v$session_longops a ORDER BY a.elapsed_seconds DESC ;