I support a half dozen databases, almost all of Solaris 8, running either 8i or 9i. We are in the process of converting to 10g, but that may not happen for a while. I was planning on running this query from a stored procedure on each database everyday to compile all of the long running sql. I will run this from one database. Then I can check the output on a regular basis and use it to try and tune any badly performing SQL that I find.
What do other people do to find and fix poorly tuned SQL?
Code:
SELECT v$sqlarea.sql_text,
Trunc(v$sqlarea.disk_reads/
Decode(v$sqlarea.executions,0,1,v$sqlarea.executions)) reads_per_exec,
v$sqlarea.buffer_gets,
v$sqlarea.disk_reads,
v$sqlarea.executions,
v$sqlarea.sorts,
v$session_longops.LAST_UPDATE_TIME,
v$session_longops.TIME_REMAINING,
v$session_longops.ELAPSED_SECONDS
FROM v$sqlarea, v$session_longops
WHERE v$sqlarea.address = v$session_longops.SQL_ADDRESS
AND v$session_longops.ELAPSED_SECONDS > 5;