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?
FROM v$sqlarea, v$session_longops
WHERE v$sqlarea.address = v$session_longops.SQL_ADDRESS
AND v$session_longops.ELAPSED_SECONDS > 5;