-
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?
Last edited by davey23uk; 08-22-2006 at 04:35 PM.
-
-
SQL analyze, Oracle expert, third party tools.........
"What is past is PROLOGUE"
-
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.
-
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............
"What is past is PROLOGUE"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|