Proactive tuning

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;

---

you do know your worst sql might not show up in v$session_longops?

statspack

SQL analyze, Oracle expert, third party tools.........

My goal was just to get a list of SQL that needed to get tuned. I can certainly run Statspack. In fact I will probably work on it at some point.

Quote:

---

Originally Posted by gandolf989

My goal was just to get a list of SQL that needed to get tuned.

---

sure you can do it through SQL analyze GUI tool will catch all the worst performing queries with all cost calculations and index recommendations. You can play around before implementing.

If like doing scripting use statspack, analyze, tkprof and............