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;